win32com.client to format an Excel columns

Question

I am writing output data to excel using pandas. I want to format excel using win32com.client as I do not easier method for this.

my code is from some internet search is

    Xlsx = win32.DispatchEx('Excel.Application')
    Xlsx.DisplayAlerts = True
    Xlsx.Visible = True
    book = Xlsx.Workbooks.Open('C:\\temp\\test.xlsx')
    ws = book.Worksheets("Sheet1")
    ws.Columns.NumberFormat = "0,0"
    ws.Columns.AutoFit()
    ws.Range("A1:P1").Interior.ColorIndex = 20
    book.Save()
    book.Close()
    Xlsx.Quit()
    del book
    del Xlsx 

Here i do not want all columns of excel in decimal format e.g. I want column b, d, f in integer format and all other columns having number data in decimal format up to 2 decimals and all text in "center" how can I do this ?

Secondly if you know any other module to easily achieve this simple formatting pl suggest.

Answer 1

I realize this is an old thread but pandas has a to_excel method that allows you to write excel workbooks directly from dataframes without using win32com.

The syntax looks like this:

# Establish the writer object
writer = pandas.ExcelWriter("output_spreadsheet.xlsx", engine="xlsxwriter")

# Assign the dataframe to an excel sheet
df.to_excel(writer, sheet_name="something meaningful", index=False)

# Save the file
writer.save()

Now since you want to specify the number of decimals to display you can do this two different ways. You can use the method specified by @Kruzchy Klaperman (which is quick and easy to read) or you can do it by styling the sheet using xlsxwriter, which we specified as the engine when creating the excel writer object, or even by using the pandas assign method. This second method can also be used to format your output so the values are centered.

Lets imagine an approach that combines both of these methods since it is the cleanest:

# Again, establish the writer object first
writer = pd.ExcelWriter("output_spreadsheet.xlsx", engine="xlsxwriter")

# Assign the dataframe to a workbook worksheet again and assign the column types all in one go
df.assign(
    "column_a"=lambda x: x.column_b.astype(float).round(2),
    "column_b"=lambda x: x.column_b.astype(int)
).to_excel(writer, sheet_name="Data", index=False)

# create the workbook and worksheet objects for xlsxwriter
workbook = writer.book
sheet = writer.sheets["Data"]

# create a workbook format object for integers, floats, and other types
int_frmt = workbook.add_format({
    "align": "center",
    "num_format": "#,##0"
})
flt_frmt = workbook.add_format({
    "align": "center",
    "num_format": "#,##0.00"
})
gnrl_frmt = workbook.add_format({
    "align": "center"
})


# add your column headers to the formatted workbook
for column, value in enumerate(pivot.columns.values):
    sheet.write(0, column, value)
# loop through the dataframe formating each cell depending on its value
for row, values in enumerate(df.values):
    for column, value in enumerate(values):
        if type(value) == int:
            sheet.write(row, column, value, int_frmt)
        elif type(value) == float:
            sheet.write(row, column, value, flt_frmt)
        else:
            sheet.write(row, column, value, gnrl_frmt)

# Save the writer object
writer.save()

For more information about formatting excel workbooks using xlsx writer look at the docs here.

Answer 2

I know this was asked some years ago, but for future reference I`d like to suggest those who might stick to win32 to use numberFormat, below I'm converting Serial Number to date:

import win32com.client as win32

excel_path = r'somePath/toExcel.xlsx'
worksheet_name = 'sheetName'
excel = win32.gencache.EnsureDispatch('Excel.Application')
# excel.Visible = True
wb = excel.Workbooks.Open(excel_path)
ws = wb.Worksheets(worksheet_name)
ws.Columns("A").NumberFormat = 'dd/mm/yyyy'
wb.Close(SaveChanges=1)
excel.Application.Quit()

Answer 3

I think casting the columns in pandas then writing it to an excel file is much easier using astype and round
for example
df['column_B'].astype(int) to convert the column to int
df['column_A'].astype(float).round(2) to convert the column to float and then rounding off up to 2 decimals