How to change the format of dataframe output when using pandas to_excel with ExcelWriter and xlsxwriter

Question

I am using Python 3.4 on Windows. Functionally, I have no problem using pandas to write a dataframe named "df" to an Excel xlsx file this way:

import pandas as pd
import xlsxwriter

writer = pd.ExcelWriter('Test Data\simple.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='TAV',index=False)

I do see the dataframe "df" nicely written to the xlsx workbook in the correct worksheet. This is extremely useful as it allows a separate QA path for Python logic and calculations.

However, the default formatting is not to my liking. For instance I would like to see a font size of 8, maybe adjust the font, and remove bolding(?) and borders.

Or perhaps even better, maybe there is a way to use the default Excel worksheet formatting (which can be set by the user in Excel)?

I have tried to find documentation on ExcelWriter() for pandas, but somehow I haven't found anything. I have researched the xlsxwriter documentation quite a bit, but nothing has helped adjust the formatting of the worksheet after writing the dataframe. Perhaps there is a way to choose this format as an argument in the ExcelWriter() step?

Aveedibya Dey · Accepted Answer · 2015-10-27 18:57:30Z

1

#This link can be helpful: http://xlsxwriter.readthedocs.org/working_with_pandas.html
#you need to get the workbook and worksheet objects after writing the dataframe:
#Create a writer object
    writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
#Write the dataframe's data in the sheet
    df.to_excel(writer, sheet_name = 'Sheet1') 
#Get the workbook and worksheet objects
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']

#after you have workbook and worksheet, follow xlsxwriter formatting method:
    worksheet.merge_range('A1:A2', 'My title', format_my_title)

answered Oct 27, 2015 at 18:57

Aveedibya Dey

112 bronze badges

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

Collectives™ on Stack Overflow

How to change the format of dataframe output when using pandas to_excel with ExcelWriter and xlsxwriter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related