Automating Excel Pivot Table Generation with Python and Packaging as a Standalone Executable

The author explains a practical workflow for turning repetitive Excel pivot‑table tasks into an automated Python tool, then sharing it as a ready‑to‑run executable so users without a Python environment can benefit.

Outline

Clarify the requirement: automatically generate a pivot table (replaceable with any repetitive task).

Install third‑party libraries: tkinter and pyinstaller.

Code implementation: Python script for generating the pivot table and a desktop GUI for interaction.

Package the Python program into an .exe file.

Resolve the potential large size of the .exe by using a virtual environment.

1. Requirement Background

The goal is to use the fields "Supplier Name", "Month" and "Incoming Amount" to automatically produce a pivot‑table format, eliminating manual repetition.

2. Install Third‑Party Dependencies

Tkinter is the built‑in GUI library for Python; after installation it can be used directly. PyInstaller bundles the script into an executable.

pip install tkinter

pip install pyinstaller

3. Code Implementation

excel_to_pivot.py – reads an Excel file, creates a pivot table with pandas, and provides a method to filter data.

import pandas as pd
import numpy as np

class ExcelToPivot(object):
    def __init__(self, filename, file_path):
        self.file_name = filename
        self.file_path = file_path
    """
        excel自动转透视表功能
        返回透视结果
    """
    def excel_Pivot(self):
        print(self.file_path)
        data = pd.read_excel(self.file_path)
        data_pivot_table = pd.pivot_table(
            data,
            index=['供应商名称', '月份'],
            values=["入库金额"],
            aggfunc=np.sum
        )
        return data_pivot_table
    """
        按条件筛选，并保存
    """
    def select_data(self, name, month):
        data_pivot_table = self.excel_Pivot()
        data_new = data_pivot_table.query('供应商名称 == ["{}"] & 月份 == {}'.format(name, month))
        data_new.to_excel('{}.xlsx'.format(str(self.file_name).split('.')[0]))
        return '筛选完成！'

if __name__ == '__main__':
    filename = input("请输入文件名字：")
    path = 'C:/Users/cherich/Desktop/' + filename
    pross = ExcelToPivot(filename, path)
    print(pross.select_data("C", 4))

opration.py – builds a simple Tkinter window that lets the user upload an Excel file, invoke the pivot‑generation logic, and filter the results.

from tkinter import Tk, Entry, Button, mainloop
import tkinter.filedialog
import excel_to_pivot
from tkinter import messagebox
from tkinter import ttk

def Upload():
    global filename, data_pivot_table
    try:
        filename = tkinter.filedialog.askopenfilename(title='选择文件')
        pross = excel_to_pivot.ExcelToPivot(str(filename).split('/')[-1], filename)
        data_pivot_table = pross.excel_Pivot()
        messagebox.showinfo('Info', '转换成功！')
    except Exception as e:
        print(e)
        messagebox.showinfo('Info', '转换失败！')

def select(name, month):
    try:
        data_new = data_pivot_table.query('供应商名称 == ["{}"] & 月份 == {}'.format(name, month))
        data_new.to_excel('{}.xlsx'.format(str(filename).split('.')[0]))
        messagebox.showinfo('Info', '筛选完成并生成文件！')
        root.destroy()
    except Exception as e:
        print(e)
        messagebox.showinfo('Info', '筛选失败！')

root = Tk()
root.config(background="#6fb765")
root.title('自动转透视表小工具')
root.geometry('500x250')

e1 = Entry(root, width=30)
e1.grid(row=2, column=0)

Button(root, text=' 上传文件 ', command=Upload).grid(row=2, column=10, pady=5)

box1 = ttk.Combobox(root)
box1['value'] = ('A', 'B', 'C', 'D', '供应商')
box1.current(4)
box1.grid(row=5, sticky="NW")

box2 = ttk.Combobox(root)
box2['value'] = (1,2,3,4,5,6,7,8,9,10,11,12,'月份')
box2.current(12)
box2.grid(row=5, column=1, sticky="NW")

def func(event):
    global b1, b2
    b1 = box1.get()
    b2 = box2.get()

box1.bind('<<ComboboxSelected>>', func)
box2.bind('<<ComboboxSelected>>', func)

Button(root, text=' 筛选数据 ', command=lambda: select(b1, b2)).grid(row=30, column=10, pady=5)

mainloop()

4. Package Python Program into an Executable

Open a command prompt, navigate to the directory containing the two .py files (avoid Chinese characters in the path), and run: pyinstaller -F -w opration.py The command creates build and dist folders; the dist folder holds the standalone opration.exe that can be distributed.

5. Reduce Executable Size with a Virtual Environment

Large executables often result from many installed packages. Creating a clean virtual environment, reinstalling only the required libraries, and rebuilding the executable can shrink the size dramatically (e.g., from 660 MB to 31 MB).

pip install virtualenv
pip install virtualenvwrapper-win
mkvirtualenv -p="C:\Users\cherich\AppData\Local\Programs\Python\Python38\python.exe" py38

After activating the environment, install the minimal dependencies ( pandas, openpyxl, etc.), reinstall pyinstaller, and repeat the packaging step.

Conclusion

The tutorial shows how to turn a repetitive Excel task into an automated Python tool, wrap it with a simple GUI, and distribute it as a lightweight executable, while offering practical tips such as avoiding non‑ASCII paths and minimizing imports to keep the final binary small.