CODEWITHSUNDEEP
pythonpandasexport-to-excelopenpyxlxlsxwriter
Pythonista anonymous
Pythonista anonymous

Reputation: 8950

Pandas: how to format both rows and columns when exporting to Excel (row format takes precedence)?

I am using pandas and xlsxwriter to export and format a number of dataframes to Excel.

The xlsxwriter documentation mentions that: http://xlsxwriter.readthedocs.io/worksheet.html?highlight=set_column

A row format takes precedence over a default column format

Precedence means that, if you format column B as percentage, and then row 2 as bold, cell B2 won't be bold and in % - it will be bold only, but not in %!

I have provided an example below. Is there a way around it? Maybe an engine other than xlsxwriter? Maybe some way to apply formatting after exporting the dataframes to Excel?

It makes no difference whether I format the row first and the columns later, or viceversa.

It's not shown in the example below, but in my code I export a number of dataframes, all with the same columns, to the same Excel sheet. The dataframes are the equivalent of an Excel Pivot table, with a 'total' row at the bottom. I'd like the header row and the total row to be bold, and each column to have a specific formatting depending on the data (%, thousands, millions, etc). Sample code below:

import pandas as pd

writer = pd.ExcelWriter('test.xlsx')
wk = writer.book.add_worksheet('Test')

fmt_bold = writer.book.add_format({'bold':True})
fmt_pct = writer.book.add_format({'num_format': '0.0%'})

wk.write(1,1,1)
wk.write(2,1,2)

wk.set_column(1,1, None, fmt_pct)
wk.set_row(1,None, fmt_bold)

writer.close()

Upvotes: 3

Views: 3208

Answers (2)

Charlie Clark
Charlie Clark

Reputation: 19507

As @jmcnamara notes openpyxl provides different formatting options because it allows you essentially to process a dataframe within a worksheet. NB. openpyxl does not support row or column formats.

The openpyxl dataframe_to_rows() function converts a dataframe to a generator of values, row by row allowing you to apply whatever formatting or additional processing you like.

Upvotes: 2

jmcnamara
jmcnamara

Reputation: 41574

In this case you will need to create another format that is a combination of the row and column formats and apply it to the cell.

In order to do that you will need to iterate over the data frame and call XlsxWriter directly, rather then using the Pandas-Excel interface.

Alternatively, you may be able to do using OpenPyXL as the pandas Excel engine. Recent versions of the Pandas interface added the ability to add formatting to the Excel data after writing the dataframe, when using OpenPyXL.

Upvotes: 1

Related Questions