How to write a Python Pandas DataFrame to an .XLS file with specific column type formatting?

Question

I want to first start off by showing an example that works with XLSX (but unfortunately I need XLS and this method does not work for that). Just to re-clarify, my main issue is the column-type formatting for a resulting XLS file.

# Method that works for XLSX
import pandas as pd

# Example Dataframe
df = pd.DataFrame({'Numbers': [1000, 2000, 3000, 4000, 5000],
                   'Names': ['Adam','Blake','Chad','Drake','Erak'],
                   'Weights': [1.05, 2.10, 3.15, 4.20, 5.25],
})

# Create a Pandas Excel writer
writer = pd.ExcelWriter(r'c:\users\3619678\desktop\example_file.xlsx')

# Convert the dataframe to an Excel object
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Get the xlsxwriter workbook and worksheet objects
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Define cell formats
number_format = workbook.add_format({'num_format': '0.00'})

# Set a certain column's width and apply certain format
worksheet.set_column('A:A', 20, number_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Now here is the closest attempt I have so far for the XLS version:

import pandas as pd

# Example Dataframe
df = pd.DataFrame({'Numbers': [1000, 2000, 3000, 4000, 5000],
                   'Names': ['Adam','Blake','Chad','Drake','Erak'],
                   'Weights': [1.05, 2.10, 3.15, 4.20, 5.25],
})

import xlwt

# Create a workbook and add a worksheet
workbook = xlwt.Workbook(r'c:\users\3619678\desktop\example_file.xls')
worksheet = workbook.add_sheet('sheet1',cell_overwrite_ok=True)

# Create formats
number_format = xlwt.easyxf(num_format_str='0.00')

# Iterate over the data and write it out row by row
for x, y in df.iterrows():
    for z, value in enumerate(y):
        worksheet.write(x, z, value, number_format)

# Save/output the workbook
workbook.save(r'c:\users\3619678\desktop\example_file.xls')

However the problem with this version is that not only does it not show the headers, it applies the format to all cells. Would really appreciate if someone could help me out, I've spent forever on this!

Can you specify what kind of formatting you need? If it's just basic number formatting, have you seen xlsxwriter.readthedocs.io/example_pandas_column_formats.html ? — AMC
– AMC, Commented Feb 9, 2020 at 20:53
@AMC, yes I have seen that page but the problem is when you change the output URL to XLS instead of XLSX, you can no longer use the add_format or set_column arguments, you get an error similar to "XLWT Writer doesn't have". Either way thank you for looking out, it seems Quinn's solution with the column format dictionary and proper for loop syntax gets the job done! — Sky Limit Theory
– Sky Limit Theory, Commented Feb 10, 2020 at 20:03
you can no longer use the add_format or set_column arguments, you get an error similar to "XLWT Writer doesn't have". What's the exact error message? — AMC
– AMC, Commented Feb 10, 2020 at 22:54
@AMC, sorry I was slightly wrong, the exact error message is: 'Workbook' object has no attribute 'add_format'. This happens when you change the xlsx part of the url to xls, otherwise the formatting piece works. (Also ExcelWriter is indeed able to export XLS files, the problem arises when combining formatting+XLS) — Sky Limit Theory
– Sky Limit Theory, Commented Feb 11, 2020 at 17:00
I only just thought of this, how often will you need to create the XLS file? Is converting it from an XLSX file manually a viable option? — AMC
– AMC, Commented Feb 11, 2020 at 18:48

Quinn · Accepted Answer · 2020-02-10 00:11:30Z

2

using df.to_excel should be much easier

df.to_excel('c:\users\3619678\desktop\example_file.xls', sheet_name='sheet1', float_format = "%.2f")

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

[Edit] It looks like this may not be good enough. based on the xlwt documentation, there may not be a tool out there that you want to use. As such, I've added headers and an easy way for you to format by column

import pandas as pd

# Example Dataframe
df = pd.DataFrame({'Numbers': [1000, 2000, 3000, 4000, 5000],
                   'Names': ['Adam','Blake','Chad','Drake','Erak'],
                   'Weights': [1.05, 2.10, 3.15, 4.20, 5.25],
})

import xlwt

# Create a workbook and add a worksheet
workbook = xlwt.Workbook('example_file.xls')
worksheet = workbook.add_sheet('sheet1',cell_overwrite_ok=True)

# Create dictionary of formats for each column
number_format = xlwt.easyxf(num_format_str='0.00')
cols_to_format = {0:number_format}

for z, value in enumerate(df.columns):
    worksheet.write(0, z, value)

# Iterate over the data and write it out row by row
for x, y in df.iterrows():
    for z, value in enumerate(y):
        if z in cols_to_format.keys():
            worksheet.write(x + 1, z, value, cols_to_format[z])
        else: ## Save with no format
             worksheet.write(x + 1, z, value)

# Save/output the workbook
workbook.save('example_file.xls')

edited Feb 10, 2020 at 0:11

answered Feb 7, 2020 at 23:20

Quinn

911 silver badge5 bronze badges

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

15 Comments

AMC Over a year ago

Doesn’t this only write XLSX files, though?

Quinn Over a year ago

According to the ExcelWriter page, you can write xls files pandas.pydata.org/pandas-docs/stable/reference/api/…

Quinn Over a year ago

I guess my answer is just do the same thing but that seems like the easiest solution.

Sky Limit Theory Over a year ago

Hey Quinn, ExcelWriter can indeed write XLS files however the problem is that you lose the capability to format the columns, it shows an error similar to "xlwtwriter doesn't have add_format/set_column". If you keep the first code I shared the same except change the file destination to XLS instead of XLSX, it will work as long as I don't specify column format, but that's what I need.

AMC Over a year ago

@Quinn According to the ExcelWriter page, you can write xls files That doesn't necessarily prove anything about to_excel(), although I found another answer which states that you can indeed output XLS files with to_excel().

|

Collectives™ on Stack Overflow

How to write a Python Pandas DataFrame to an .XLS file with specific column type formatting?

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related