DEV Community

Allen Yang
Allen Yang

Posted on

Create and Customize Excel Pivot Tables Using Python

#python #excel #pivottable #xlsx

Automate Excel Pivot Table Creation with Python

Excel pivot tables are powerful tools for summarizing and analyzing large datasets, helping users quickly uncover trends and insights from raw data. However, creating and maintaining pivot tables manually can be time-consuming and error-prone, especially when reports need to be updated regularly.

Python offers an efficient way to automate this process. By generating pivot tables programmatically, you can ensure consistency, reduce manual effort, and improve the reliability of your reporting workflows.

In this article, you’ll learn how to create and customize Excel pivot tables using Python. We’ll cover environment setup, data preparation, basic pivot table creation, and common customizations such as row and column fields, aggregation methods, and report filters—allowing you to turn repetitive Excel reporting into a streamlined, automated task.

Setting Up Your Environment and Preparing Data

To begin our automation journey, we need a reliable Python library for interacting with Excel files. For this tutorial, we will be using Spire.XLS for Python, a robust and comprehensive library designed for reading, writing, and manipulating Excel documents.

First, install the library using pip:

pip install spire.xls
Enter fullscreen mode Exit fullscreen mode

Next, let's create some sample data and write it to an Excel sheet. For simplicity, we'll leverage spire.xls to write data into a new Excel workbook.

from spire.xls import *

# 1. Create sample data using Python list
headers = [
    "Region", "Product", "Salesperson",
    "Units Sold", "Revenue", "Date"
]

data = [
    ["East",  "A", "Alice",   10, 1000, "2023-01-15"],
    ["West",  "B", "Bob",     15, 1500, "2023-01-20"],
    ["East",  "A", "Alice",   12, 1200, "2023-02-10"],
    ["South", "C", "Charlie",  8,  800, "2023-02-25"],
    ["West",  "B", "Bob",     20, 2000, "2023-03-05"],
    ["North", "A", "Alice",   11, 1100, "2023-03-12"],
    ["East",  "C", "Charlie",  9,  900, "2023-04-01"],
    ["South", "B", "Bob",     14, 1400, "2023-04-18"],
    ["North", "C", "Alice",    7,  700, "2023-05-01"],
    ["West",  "A", "Charlie", 18, 1800, "2023-05-10"],
]

# 2. Initialize a new Excel workbook and add a sheet
workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]
sheet.Name = "RawData"

# 3. Write headers to Excel
for col_index, header in enumerate(headers, start=1):
    sheet.Range[1, col_index].Text = header

# 4. Write data rows to Excel
for row_index, row in enumerate(data, start=2):
    for col_index, value in enumerate(row, start=1):
        sheet.Range[row_index, col_index].Value = str(value)

# 5. Auto-fit columns for better readability
for col in range(1, len(headers) + 1):
    sheet.AutoFitColumn(col)

print("Sample data written to 'RawData' sheet.")

# Save the workbook (optional)
# workbook.SaveToFile("DataForPivot.xlsx", ExcelVersion.Version2016)
Enter fullscreen mode Exit fullscreen mode

Here's a preview of the generated Excel sheet:

Generated Excel Sheet Using Python

The Core: Creating a Basic Pivot Table

With our data prepared, the next step is to create the pivot table itself. We'll specify the data range from our "RawData" sheet and then add a new pivot table to a separate worksheet.

# Continue from the previous script

# Calculate the last row based on list length
# +1 because row 1 is the header
last_row = len(data) + 1

# Get the data range for the pivot table (A1 to F last row)
data_range = sheet.Range["A1:F" + str(last_row)]

# Add a new worksheet for the pivot table
pivot_sheet = workbook.Worksheets.Add("PivotReport")

# Create a pivot cache based on the data range
cache = workbook.PivotCaches.Add(data_range)

# Add the pivot table to the new sheet
# 1st parameter: pivot table name
# 2nd parameter: start cell
# 3rd parameter: pivot cache
pt = pivot_sheet.PivotTables.Add(
    "SalesPivot",
    pivot_sheet.Range["A1"],
    cache
)

print("Basic pivot table structure created.")
Enter fullscreen mode Exit fullscreen mode

At this point, we have an empty pivot table shell. If you were to save and open the Excel file, you would see a blank pivot table area ready for field configuration.

Customizing Your Pivot Table for Deeper Insights

The real power of pivot tables comes from configuring their fields. We can define row fields, column fields, data fields (for aggregation), and report filters to slice and dice our data dynamically.

Adding Row Fields

Row fields define the categories that will appear as rows in your pivot table.

# Continue from the previous script

# Add 'Region' and 'Product' as row fields
row_field_region = pt.PivotFields["Region"]
row_field_region.Axis = AxisTypes.Row # Assign to row axis

row_field_product = pt.PivotFields["Product"]
row_field_product.Axis = AxisTypes.Row # Assign to row axis

print("Row fields 'Region' and 'Product' added.")
Enter fullscreen mode Exit fullscreen mode

Adding Column Fields

Column fields define the categories that will appear as columns, often used for cross-tabulation.

# Continue from the previous script

# Add 'Salesperson' as a column field
col_field_salesperson = pt.PivotFields["Salesperson"]
col_field_salesperson.Axis = AxisTypes.Column # Assign to column axis

print("Column field 'Salesperson' added.")
Enter fullscreen mode Exit fullscreen mode

Defining Data Fields (Value Fields)

Data fields are where the actual calculations happen. You specify which field to aggregate and what aggregation function to use (e.g., Sum, Count, Average).

# Continue from the previous script

# Add 'Units Sold' as a data field with Sum aggregation
data_field_units = pt.PivotFields["Units Sold"]
pt.DataFields.Add(data_field_units, "Sum of Units Sold", SubtotalTypes.Sum)

# Add 'Revenue' as a data field with Sum aggregation
data_field_revenue = pt.PivotFields["Revenue"]
pt.DataFields.Add(data_field_revenue, "Sum of Revenue", SubtotalTypes.Sum)

# You can also add the same field with a different aggregation, e.g., Average Revenue
pt.DataFields.Add(data_field_revenue, "Average Revenue", SubtotalTypes.Average)

print("Data fields 'Units Sold' (Sum) and 'Revenue' (Sum, Average) added.")
Enter fullscreen mode Exit fullscreen mode

Applying Report Filters

Report filters allow users to filter the entire pivot table based on specific criteria, providing interactive data exploration.

# Continue from the previous script

# Add 'Date' as a report filter
report_filter_date = PivotReportFilter("Date", True)
pt.ReportFilters.Add(report_filter_date)

print("Report filter 'Date' added.")
Enter fullscreen mode Exit fullscreen mode

Optional: Layout and Styling

While extensive styling might be done interactively in Excel, spire.xls does offer options for basic layout and built-in styles to enhance readability.

# Continue from the previous script

# Apply a built-in pivot table style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

# Set the report layout to tabular form for better readability
# This repeats item labels for better clarity in hierarchical row fields
pt.Options.RowLayout = PivotTableLayoutType.Tabular
pt.Options.RowHeaderCaption = "Region/Product" # Custom caption for row header

# Ensure item labels are repeated for clarity in tabular layout
pt.PivotFields["Region"].RepeatItemLabels = True
pt.PivotFields["Product"].RepeatItemLabels = True

# Calculate the pivot table to ensure all changes are applied
pt.CalculateData()

print("Pivot table style and layout applied.")
Enter fullscreen mode Exit fullscreen mode

Saving and Reviewing Your Automated Report

Once all the configurations are complete, the final step is to save the Excel workbook. This will generate a fully functional Excel file with your programmatically created and customized pivot table.

# Continue from the previous script

# Save the workbook to a file
output_file = "Automated_Sales_Report.xlsx"
workbook.SaveToFile(output_file, ExcelVersion.Version2016)
workbook.Dispose()

print(f"Pivot table report saved to '{output_file}'")
print("Automation complete!")
Enter fullscreen mode Exit fullscreen mode

The generated report will look like this:

Excel Pivot Table Report Created Using Python

Now, when you open Automated_Sales_Report.xlsx, you will find two sheets: "RawData" containing your original dataset and "PivotReport" with a pre-configured pivot table. This table will summarize sales data by region and product, broken down by salesperson, and allow filtering by date. This entire process, from data preparation to a fully formatted report, was executed with a single Python script.

Conclusion

Automating Excel pivot table creation with Python offers a significant leap forward in data analysis and reporting efficiency. We've demonstrated how to leverage Spire.XLS for Python to not only write data to an Excel sheet but also to construct, configure, and refine a complex pivot table entirely programmatically.

By following this tutorial, you've learned to:

  • Set up your environment and prepare data for pivot table creation.
  • Initiate a basic pivot table structure.
  • Customize pivot tables by adding row fields, column fields, data fields with various aggregation functions, and report filters.
  • Apply basic styling and layout options for improved readability.
  • Save the final, automated Excel report.

The benefits are clear: increased efficiency, reduced manual errors, and reproducible workflows. This approach frees up valuable time for data professionals, allowing them to focus on interpreting insights rather than wrestling with repetitive manual tasks. We encourage you to integrate these powerful automation techniques into your own projects, explore further customizations, and apply them to your unique datasets. The future of data analysis lies in smart automation, and Python is your key to unlocking it.

Top comments (0)