automate-excel

Excel automation processing

Apply this skill when the user needs to process Excel files, tabular data, batch conversion, or report generation.

What you want to do	Script to call	Typical parameters
Merge multiple Excel files or multiple sheets into one table	merge_sheets.py	--inputs 文件或目录 --output out.xlsx
Export a sheet to CSV	excel_to_csv.py	--input file.xlsx --output file.csv
Convert CSV to Excel	csv_to_excel.py	--input a.csv --output a.xlsx or --inputs a.csv b.csv
Filter rows by condition (equals/greater than/contains)	filter_excel.py	--where "列名=值" or "列名>100"、"列名~北京"
Split into multiple files by row count, or split by values in a column	split_excel.py	--by-rows 5000 or --by-column 地区
Deduplicate by a column	deduplicate_excel.py	--keys 编号 --keep first/last
Group by column and sum/count/average	aggregate_excel.py	--group-by 地区 --agg "销售额:sum"
Check required columns, duplicate keys, empty rows	validate_excel.py	--require-cols 列名 --key-cols 列名
Keep/rename only some columns	select_columns.py	--columns 列1,列2 --rename "旧:新"
Merge two tables by aligning on one column (VLOOKUP)	merge_tables.py	--left a.xlsx --right b.xlsx --on 键列
VLOOKUP the main table against multiple tables in sequence	vlookup_multi.py	--main 主.xlsx --lookups "表1.xlsx:键列" "表2.xlsx:键列"
Transpose rows and columns	transpose_excel.py	--input in.xlsx --output out.xlsx
Use a data table to fill {{column name}} in a template row by row	template_fill.py	--template t.xlsx --data d.csv --output out.xlsx
Rename worksheet	rename_sheets.py	--rename "Sheet1:新名" or --prefix "2024_"
Conditional formatting (greater than/less than/duplicate values/color scale)	format_conditional.py	--column C --rule gt --value 100 --fill red
Display a column as text to avoid scientific notation	format_columns_as_text.py	--columns 身份证号,订单号

Run directly locally: Enter the directory where this skill is located (or add scripts/ to the path), first pip install -r scripts/requirements.txt, then execute python scripts/脚本名.py --help to see the parameters, or run with parameters as in the table above.

Tech stack

• Read/write .xlsx：openpyxl(preserve formatting, formulas, multiple worksheets)
• Data analysis/pivot：pandas + openpyxl engine
• Legacy format .xls：xlrd(read-only)

Prefer openpyxl for cell-level operations and formatting preservation; when filtering, aggregating, or merging multiple tables is needed, use pandas。

Dependencies

pip install openpyxl pandas xlrd

Or use the skill's built-in:pip install -r scripts/requirements.txt

General workflow

1. Confirm input: file path, worksheet name or index, whether there is a header, encoding (for CSV).
2. Read: use openpyxl or pandas to read the entire table/range as needed.
3. Process: transform, filter, merge, calculate according to user requirements.
4. Write output: specify output path and format (.xlsx/.csv); preserve the original format or create a new workbook when necessary.
5. Validate: check row counts, key columns, duplicate values, or business rules.

Read Excel

Whole table as list of dict (preserve header):

import openpyxl

wb = openpyxl.load_workbook("input.xlsx", read_only=True, data_only=True)
ws = wb.active  # 或 wb["Sheet1"]
rows = list(ws.iter_rows(min_row=1, values_only=True))
header = rows[0]
data = [dict(zip(header, row)) for row in rows[1:]]
wb.close()

Use pandas (suitable for analysis, filtering, merging):

import pandas as pd

df = pd.read_excel("input.xlsx", sheet_name=0, engine="openpyxl")
# sheet_name 可为 0、"Sheet1" 或 [0, 1] 多表

Specified range:

# openpyxl
for row in ws["A1:D10"]:
    ...

# pandas
df = pd.read_excel("input.xlsx", usecols="A:D", header=0, nrows=100)

Write Excel

Create and write (openpyxl):

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

wb = Workbook()
ws = wb.active
ws.title = "结果"
ws.append(["列A", "列B", "列C"])
for row in data_rows:
    ws.append(row)
ws["A1"].font = Font(bold=True)
wb.save("output.xlsx")

Use pandas to write multiple tables:

with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df1.to_excel(writer, sheet_name="汇总", index=False)
    df2.to_excel(writer, sheet_name="明细", index=False)

Append to an existing file (load first, then write):

wb = openpyxl.load_workbook("existing.xlsx")
ws = wb["Sheet1"]
for row in new_rows:
    ws.append(row)
wb.save("existing.xlsx")

Common tasks quick reference

Task	Approach
Merge multiple files	merge_sheets.py or pandas read_excel + pd.concat + to_excel。
CSV ↔ Excel	excel_to_csv.py / csv_to_excel.py or pandas read/write.
Filter by condition	filter_excel.py or df[df["列"] == 值] / df.query()。
Split by row/by column	split_excel.py(split files by row count or by values in a column).
Deduplicate	deduplicate_excel.py or df.drop_duplicates(subset=["列"], keep="first")。
Aggregate by column	aggregate_excel.py or df.groupby("列").agg({"数值列": "sum"})。
Validate	validate_excel.py。
Select/rename columns	select_columns.py。
Merge two tables by key	merge_tables.py（left/inner/outer）。
Transpose rows and columns	transpose_excel.py。
Template filling	template_fill.py({{column name}} placeholders).
Rename sheet	rename_sheets.py。
Multi-table VLOOKUP	vlookup_multi.py(main table + multiple lookup tables left-joined in sequence).
Conditional formatting	format_conditional.py(greater than/less than/between/duplicate values/color scale).
Format column as text	format_columns_as_text.py(avoid scientific notation for long numbers).
Write data while preserving original formatting	Use openpyxl to load the original file, only rewrite the target cells, then save.

Batch processing

When the user needs to process multiple Excel files in a directory:

1. Use pathlib.Path("目录").glob("*.xlsx") to enumerate files.
2. For each file: read → process → can aggregate into one DataFrame or write out separately.
3. Output can be a single merged file, or conventions such as 原名_out.xlsx ; specify the output naming rules explicitly in the SKILL.
4. On error, record the file name and exception, continue processing the remaining files, and summarize the error list at the end.

Validation and error handling

• Before reading, use Path(file).exists() to check that the file exists.
• When a table is empty or missing expected columns, give a clear message (column names/row count).
• Before writing, if the target file already exists, overwrite or rename it as requested by the user; for large files consider write_only=True or chunking.
• Catch openpyxl.utils.exceptions.InvalidFileException、KeyError(worksheet name), etc., and return readable error messages.

Tool scripts

Under the skill directory, scripts/ provides executable scripts; prefer running scripts in the user's environment rather than temporarily writing long code.

Script	Function
merge_sheets.py	Merge multiple Excel files or multiple sheets in the same file into one table
excel_to_csv.py	Export the specified sheet as CSV
csv_to_excel.py	Convert CSV to Excel (single/multiple CSV → multiple sheets)
filter_excel.py	Filter by column conditions (=, >, <, ~ contains) and output Excel/CSV
split_excel.py	Split into multiple files by row count or by the values of a certain column
deduplicate_excel.py	Deduplicate by specified columns, keeping first/last
aggregate_excel.py	Group and aggregate by columns (sum/count/mean/min/max)
validate_excel.py	Validate required columns, duplicate keys, empty rows
select_columns.py	Select/rename/sort columns
merge_tables.py	Merge two tables by key columns (VLOOKUP-style, left/inner/outer)
transpose_excel.py	Transpose rows and columns
template_fill.py	Fill {{column name}} placeholders in a template row by row using a data table
rename_sheets.py	Rename worksheets (original name:new name, index:new name, prefix/suffix)
vlookup_multi.py	Multi-table VLOOKUP: left-join the main table with multiple lookup tables in sequence
format_conditional.py	Format conditionally (gt/lt/between/duplicate values/color scale)
format_columns_as_text.py	Set specified columns to text format to avoid pure numbers being displayed in scientific notation

For usage, see each script's --help or reference.md。

More details

• For detailed API and parameters, see reference.md。
• For more scenario examples, see examples.md。