Using pyExcelerator / xlrd

Question

How can I create a worksheet (in an Excel workbook) using a pivot table using python libraries like pyExcelerator / xlrd? I need to create a daily report with a pivot table to summarize data on other sheets. One option is to have an empty template that I copy and populate with data. In this case, is there a way to update the core from the code? Any other suggestions?

Answer 1

Please explain (by editing your question) whether the “sheet” is an abbreviation of the “spreadsheet” and means the entire XLS file or the abbreviation “worksheet” that makes up the “workbook”.

If by "pivot table" you mean the Excel engine, you're out of luck, because it can only be created by Excel. However, if you mean the cross-tab that you create yourself using Python and the appropriate library, you can do this using the trios xlrd, xlwt, and xlutils.

xlrd that you seem to know about.

xlwt is a fork pyExcelerator with bug fixes and several improvements. pyExcelerator is not supported.

xlutils is a utility module package. xlutils.copy can be used to create the xlwt workbook object from the Book xlrd object so that you can make changes to the xlwt workbook and save it in a file.

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

Answer 2

I don't think you can programmatically add a xlwt table using xlwt .

But your second approach (filling out a preconfigured workbook) seems reasonable.

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

VBA code for updating pivot table:

 Sheet1.PivotTables(1).PivotCache.Refresh 

Answer 3

Try a look at this: Python: update pivot tables in a worksheet

If you figure out how to create pivot tables, you can use my code to update them.