6

Is there a way we can create pivot table in Excel using openpyxl library? I saw old threads mentioning its not possible, but I saw some documentation about creating one on below link:

https://openpyxl.readthedocs.io/en/2.5/api/openpyxl.pivot.table.html

Although I cant find any practical example of how to use the information provided in above link.

Parth Karia
  • 265
  • 1
  • 4
  • 11
  • You could import data into `pandas`, pivot it and save it... – Susensio Mar 25 '18 at 14:33
  • @Susensio thank you for your response. But I guess with Pivot created using Pandas, we cannot get options like 'expand/collapse' buttons, row and column totals and other formatting options that we get in Pivot table created within Excel – Parth Karia Mar 27 '18 at 17:12
  • 2
    It doesn't appear that the pivot support is intended to be used to create pivot tables from scratch, more to allow existing files with pivot tables to load and save correctly. From [issue 1056](https://bitbucket.org/openpyxl/openpyxl/issues/1056/guideline-example-for-using-pivot-package) in the openpyxl BitBucket: "Pivot tables are very complicated and support was mainly added to preserve them in existing files. I do not expect that we will ever provide full documentation of how to create them in code." – SpaceDog Jul 04 '18 at 10:53
  • Thanks for response @spaceDog – Parth Karia Jul 05 '18 at 11:48

1 Answers1

9

It cannot be done. As mentioned already in the comments and also in the official docs, the pivot module of openpyxl is only there to preserve existing pivots.

I suggest you create a template.xlsx workbook with raw data on one sheet and your pivot table on another one. In the Excel pivot options, you activate refresh data when opening file. Then you use openpyxl to: Load this template, update the raw data and save it somewhere.

Excel will update the pivot table content when the file is opened the next time.

Woodly0
  • 187
  • 2
  • 13