Pivot tables in Excel spreadsheets are used to summarize the data in an interactive way. Suppose, you have the data of a number of invoices and you want to get their total grouped by the customers or products. The pivot tables come into play in such cases. In this article, you will learn how to deal with pivot tables in Excel in Python. Particularly, you will come to know how to create a pivot table in Excel in Python. Furthermore, we will demonstrate how to format cells in the Excel pivot tables programmatically.
- Python Library to Work with Excel Pivot Tables
- Create a Pivot Table in Excel in Python
- Format Cells in an Excel Pivot Table
- Get a Free License
Python Library to Create Pivot Table in Excel - Free Download
Aspose.Cells for Python is a high-speed library, which is designed to create and process Excel files. We will use this library to create pivot tables in Excel spreadsheets.
You can download its package or install it from PyPI using the following pip command.
pip install aspose-cells
Create a Pivot Table in Excel in Python
The following are the steps to create a pivot table in Excel using Python.
- Create a new or load an existing Excel file using the Workbook class.
- Populate the worksheet with data (optional).
- Get collection of pivot tables into a PivotTableCollection object using Worksheet.getPivotTables() method.
- Add a new pivot table using PivotTableCollection.add(string, string, string) method and get its reference in an object.
- Set options such as grand total, formatting, etc.
- Add fields to area using PivotTable.addFieldToArea(int, int) method.
- Save the workbook using Workbook.save(string) method.
The following code sample shows how to add a pivot table in Excel in Python.
Excel Data
Pivot Table
Format Cells in Excel Pivot Tables in Python
The following are the steps to format cells in Excel pivot tables in Python.
- Create a new or load an existing Excel file using the Workbook class.
- Populate the worksheet (optional).
- Get reference of the worksheet where pivot table is located.
- Get reference of the pivot table by index using Worksheet.getPivotTables().get(index) method.
- Create new style using Workbook.createStyle() method and get its reference.
- Set desired properties of Style object.
- Assign Style object to the pivot table.
- Save the workbook using Workbook.save(string) method.
The following code sample shows how to format an Excel pivot table in Python.
Python Library to Create Excel Pivot Tables - Get a Free License
You can get a free temporary license to use the Aspose.Cells for Python without evaluation limitations.
Conclusion
In this article, you have learned how to create a pivot table in Excel sheets in Python. Furthermore, you have seen how to format cells in pivot tables in Excel programmatically. Besides, you can explore more about Python Excel library using documentation. In case you would have any questions, feel free to let us know via our forum.