2

How can I go about creating a worksheet (within an excel workbook) with a pivot table using python libs like pyExcelerator / xlrd? I need to generate a daily report that has a pivot table to summarize data on other sheets. One option would be to have a blank template that I copy and populate with the data. In this case, is there a way to refresh the pivot from code? Any other suggestions?

Raj N
  • 955
  • 1
  • 9
  • 10

3 Answers3

5

Please clarify (by editing your question) whether "sheet" is an abbreviation of "spreadsheet" and means a whole XLS file, or whether it's an abbreviation of "worksheet", a component of a "workbook".

If by "pivot table" you mean the Excel mechanism, you are out of luck, because that can be created only by Excel. However if you mean a "cross-tab" that you create your self using Python and an appropriate library, you can do this using the trio of xlrd, xlwt and xlutils.

xlrd you appear to know about.

xlwt is a fork of pyExcelerator with bugs fixed and several enhancements. pyExcelerator appears not to be maintained.

xlutils is a package of utility modules. xlutils.copy can be used to make an xlwt Workbook object from an xlrd Book object, so that you can make changes to the xlwt Workbook and save it to a file.

Here is your one-stop-shop for more info on the three packages, together with a tutorial, and links to a google-group/mailing-list which you can use to get help.

John Machin
  • 81,303
  • 11
  • 141
  • 189
0

Try to have a look at this: Python: Refresh PivotTables in worksheet

If you figure out howto create the pivot tables then you can use my code to refresh them

Community
  • 1
  • 1
Norfeldt
  • 8,272
  • 23
  • 96
  • 152
0

I do not believe you can programatically add a pivot table using xlwt.

But your second approach (populating a pre-configured workbook) seems reasonable.

You can refresh the pivot table using a VBA macro in the template workbook. To do this automatically, create a WorkBook_Open event handler.

The VBA code to refresh a pivot table is:

Sheet1.PivotTables(1).PivotCache.Refresh
codeape
  • 97,830
  • 24
  • 159
  • 188
  • using the VBA code in excel only gives an error `AttributeError: 'function' object has no attribute 'Refresh'` - I can get the Sheet1.PivotTables(1) object – Norfeldt Nov 04 '13 at 13:46
  • Using `Sheet1.PivotTables(1).PivotCache().Refresh` gives `>`but nothing seems to be refreshed – Norfeldt Nov 04 '13 at 13:50
  • Try ``.PivotCache().Refresh()`` – codeape Nov 04 '13 at 20:02
  • Hi @codeape I actually tried that (and it worked) some time after writing the comment but forgot to update my comment. Thank you for your answer though – Norfeldt Nov 05 '13 at 08:44
  • I can see you have updated our answer - just want to note that it's now the python win32code that you have changed it to and not VBA code which doesn't use the last two () – Norfeldt Nov 05 '13 at 09:13
  • That's fine. You could just say the `The VBA code..` and then `The equalent python win32com code is Sheet1.PivotTables(1).PivotCache().Refresh()` – Norfeldt Nov 05 '13 at 09:42