How to save a pandas pivot table with xlsxwriter in excel

Question

I want to save a pandas pivot table proberly and nice formatted into an excel workbook.

I have an pandas pivot table, based on this formula:

table = pd.pivot_table(d2, values=['Wert'], index=['area', 'Name'], columns=['Monat'],
                          aggfunc={'Wert': np.sum}, margins=True).fillna('')

From my original dataframe:

df
Area Name2 Monat Wert
A    A      1     2
A    A      1     3
A    B      1     2
A    A      2     1

so the pivot table looks like this:

          Wert
     Monat 1   2  All
Area  Name 
A     A    5   1  6
      B    2      2
All        7   1  8

Then I want to save this in an excel workbook with the following code:

import pandas as pd
import xlsxwriter
workbook = xlsxwriter.Workbook('myexcel.xlsx)
worksheet1 = workbook.add_worksheet('table1')
caption = 'Table1'
worksheet1.set_column(1, 14, 25) #irrelevant, just a random size right now
worksheet1.write('B1', caption)
worksheet1.add_table('B3:K100', {'data': table.values.tolist()}) #also wrong size from B3 to K100
workbook.close()

But this looks like this (with different values), so the headers are missing:

How can I adjust it and save a pivot table in excel?

If I am using the pandas command .to_excel it looks like this:

Which is fine, but the column name is not respecting the width of the names and the background color is not looking nice, and I am also missing a capturing.

Have you considered simply exporting the pandas Dataframe using its `.to_excel()` method? — Oliver W., Nov 22 '19 at 12:38
I did this, but then it looks not so nice, do you think its better to export it first and then adjust it? — PV8, Nov 22 '19 at 12:39
Define “not so nice”. If it's the index that's bothering you, there's the option `index=False` to prevent it from appearing. — Oliver W., Nov 22 '19 at 12:40
Cell width is an option on the viewer, it has nothing to do with pandas, nor with xlsxwriter. Number format you can tweak with the `float_format` option of `to_excel` and layout, well that is unclear to me. Can you perhaps add these details to your question? — Oliver W., Nov 22 '19 at 12:42

PV8 · Accepted Answer · 2019-11-22T13:31:09.770

I found the solution with combination of this topic:

flattened = pd.DataFrame(table.to_records())
flattened.columns = [column.replace("('Wert', ", "Monat: ").replace(")", "") for column in flattened.columns] ##only for renaming the column headers

And then:

workbook = xlsxwriter.Workbook(excelfilename, options={'nan_inf_to_errors': True})
worksheet = workbook.add_worksheet('Table1')
worksheet1.set_column(0, flattned.shape[1], 25)
worksheet.add_table(0, 0, flattened.shape[0], flattened.shape[1]-1,
    {'data': flattened.values.tolist(),
    'columns': [{'header': c} for c in flattened.columns.tolist()],
    'style': 'Table Style Medium 9'})
workbook.close()

How to save a pandas pivot table with xlsxwriter in excel

1 Answers1