Use win32com in python to assign column width value, wrap text and add cell border to Excel file

Question

I have some code to convert an excel file from excel to PDF. Although I know that openpyxl has methods to assign column width value, wrap text and add cell border, I am looking for a way to do it with win32com module. This is because I already have opened the Excel file with win32com and I can save execution time by not having to load the Excel file with openpyxl again.

# Import Module
from win32com import client
  
# Open Microsoft Excel
excel = client.gencache.EnsureDispatch('Excel.Application')

# Make excel work in the background without appearing
excel.Visible = False
  
# Read Excel File
wb = excel.Workbooks.Open(r'C:\Spaced out data.xlsx')
ws = wb.Worksheets('Sheet1')

# Adjust page setup to landscape
ws.PageSetup.Orientation = 1

# Set Zoom to false because you want to fit all columns to the width of 1 page.
ws.PageSetup.Zoom = False

# Allow rows to be on multiple pages
ws.PageSetup.FitToPagesTall = False

# Fit all columns to the width of 1 page.
ws.PageSetup.FitToPagesWide = 1

# Convert into PDF File
ws.ExportAsFixedFormat(0, r'C:\Spaced out data.pdf')

wb.Close(SaveChanges=False)
excel.Quit()

Mark Tolonen · Accepted Answer · 2022-07-25 03:12:59Z

1

My "go to" is to record an Excel macro and use it as a basis to write the Python code. After recording a column width change, enable wrap, and change some borders I came up with this:

from win32com import client

excel = client.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False
wb = excel.Workbooks.Open(r'c:\users\metolone\test.xlsx')
ws = wb.Worksheets('Sheet1')

ws.Range("A:F").ColumnWidth = 10
ws.Range("A1:F1").WrapText = True
ws.Range("A1:F15").Borders(client.constants.xlEdgeLeft).LineStyle = client.constants.xlContinuous
ws.Range("A1:F15").Borders(client.constants.xlEdgeLeft).Weight = client.constants.xlThick

ws.PageSetup.Orientation = 1
ws.PageSetup.Zoom = False
ws.PageSetup.FitToPagesTall = False
ws.PageSetup.FitToPagesWide = 1
ws.ExportAsFixedFormat(0, r'c:\users\metolone\test.pdf')
wb.Close(SaveChanges=False)
excel.Quit()

answered Jul 25, 2022 at 3:12

Mark Tolonen

181k26 gold badges183 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nandini Chaurasiya Over a year ago

What If user want to change width for columns from x to y range, where x = 1 and y = 5 ?

Nandini Chaurasiya · Accepted Answer · 2022-08-24 11:56:16Z

0

Set the column width for specific range(row or column) or specific column: import os import win32com.client as win32 from win32com.client import Dispatch

file = os.getcwd() + os.sep + 'Portfolio.xls'
excel = Dispatch('Excel.Application')
workbook = excel.Workbooks.Open(file)
worksheet = workbook.Worksheets("Portfolio")

excel.DisplayAlerts = False
excel.Visible = False

worksheet.Range("A1:A14").ColumnWidth = 25  #Specify the rows in range
worksheet.Columns(1).ColumnWidth = 25       #Specific Column number
worksheet.Range("B:B").ColumnWidth = 25

worksheet.Columns.AutoFit()                 #Use autofit

workbook.Save()
workbook.Close()
excel.Application.Quit()

answered Aug 24, 2022 at 11:56

Nandini Chaurasiya

1011 silver badge5 bronze badges

Collectives™ on Stack Overflow

Use win32com in python to assign column width value, wrap text and add cell border to Excel file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related