Pivot Tables on Python! Edit Spreadsheets Without Opening Them

To programmatically create an Excel pivot table and save the file, you can use a library like openpyxl in Python. Here's an example of how you can accomplish this:

python

import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.drawing.image import Image
from openpyxl import load_workbook
from openpyxl.pivot import PivotTable, TableStyleInfo

# Create a new workbook and select the active sheet
workbook = Workbook()
worksheet = workbook.active

# Create sample data (you can replace this with your own data)
data = [
['Category', 'Product', 'Sales'],
['Electronics', 'TV', 500],
['Electronics', 'Mobile', 300],
['Clothing', 'Shirt', 200],
['Clothing', 'Pants', 250],
]

# Add data to the worksheet
for row in data:
worksheet.append(row)

# Create a pivot table
pivot_table = PivotTable(worksheet, min_col=1, min_row=1, max_col=3, max_row=6)

# Set the pivot table's field names and positions
pivot_table.add_field('Category', 'A')
pivot_table.add_field('Product', 'B')
pivot_table.add_field('Sales', 'C')

# Specify the location where the pivot table will be placed
pivot_table.location = 'E1'

# Add the pivot table to the worksheet
worksheet.add_pivot_table(pivot_table)

# Save the workbook
workbook.save('pivot_table_example.xlsx')

In this example, we're using the openpyxl library to create a new workbook, add data to the worksheet, create a pivot table, specify the field names and positions, add the pivot table to the worksheet, and finally save the workbook as "pivot_table_example.xlsx".

Make sure you have openpyxl installed in your Python environment before running the code. You can install it using pip:

pip install openpyxl

Once you run the code, you should have a new Excel file with the pivot table created and saved in the specified location. Cheers!