3

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?

Alan G.
  • 61
  • 4

1 Answers1

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)
Aveedibya Dey
  • 5
  • 1