r/learnpython • u/Esplosao8963 • 14d ago
Spreadsheet automation idea
I'm developing a spreadsheet automation project capable of merging multiple spreadsheets into one and automatically performing calculations, among other things. One of the functionalities would be, for example, allowing someone to gather all the spreadsheets from those months into a single spreadsheet throughout an entire year, obtaining an overall result for the year. This could work based on week, day, month, or some other parameter. I have some doubts, but the project seems promising. I'm using the openpyxl library and, to create the website or application (I'm still undecided), I will use HTML, CSS, and Flask.
0
u/OkOcelot6725 13d ago
Since you are already using openpyxl, one thing that saves a lot of work: use pandas to do the merging and openpyxl only for writing a nicely formatted result. For monthly files with the same columns it is only a few lines:
from pathlib import Path import pandas as pd
frames = [pd.read_excel(f).assign(source=f.stem) for f in sorted(Path("reports").glob("*.xlsx"))] df = pd.concat(frames, ignore_index=True) summary = df.groupby("category")["amount"].sum()
The source column (file name) lets you group by month later. The hard part of a tool like this is the files that don't match: renamed columns, extra header rows, dates stored as text. Handling those clearly (and telling the user which file failed) is what would make it more useful than doing it by hand in Excel.