CODEWITHSUNDEEP
pythondataframeformattingxls
Sky Limit Theory
Sky Limit Theory

Reputation: 23

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

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!

Upvotes: 2

Views: 8884

Answers (1)

Quinn
Quinn

Reputation: 91

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

Upvotes: 2

Related Questions