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

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()

CodePudding user response：

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()