I'm trying to read an .xlsm file using Python, however it contains pivot tables that cannot be read directly. I want to be able to specify all the parameters in the pivot table's filters then get the selected data. I've searched for some libraries but no result. Can anyone help me with this?
2 Answers
Using pandas you can make a pivot table in Python (http://pandas.pydata.org/pandas-docs/stable/generated/pandas.pivot_table.html) from a .xlsm file.
2 Comments
Liu Su
I've seen this before but how can I read the pivot structure out of the workbook because it can only create an pivot table.
FluxIX
I don't know of a platform agnostic library to read a pivot table in excel from Python. If you're on a Windows platform you try using the Python for Windows Extensions library (sourceforge.net/projects/pywin32), but I don't personally have experience with it (although pythonexcels.com/automating-pivot-tables-with-python shows how to make pivot tables in Excel from Python, so I assume there would be some way to read arbitrary data from an Excel file using it).
I believe you have to read in the data and then create a pivot based on that data set. See the link below for some info on how to get started.
http://pbpython.com/pandas-pivot-table-explained.html
This looks good too.
http://mourafiq.com/2016/01/01/generating-excel-report-with-python.html
So, as you can see, you need to pivot the data set yourself. I don't believe Python will interpret an Excel Pivot Table object.
1 Comment
Liu Su
the excel workbook used in but pbpython.com does not contain a pivot table in the first place. What if the workbook I have now already contains a pivot table?