Excel Splitter

Purpose

Split a single Excel .xlsx workbook into many smaller .xlsx workbooks, one for
each distinct value found in a chosen column. The bundled script performs the split
deterministically and safely (handles illegal filename characters, empty groups, and
name collisions).

When to use

• The user wants to split an Excel file by column (e.g., "按列拆分", "按地区拆分成多个表").
• The user wants to split all sheets in an Excel file into separate files/attachments (e.g., "拆分工作表", "把工作表拆成单独文件").
• The user wants to merge multiple Excel files row-wise (e.g., "合并 Excel", "把多个附件合成一个表").
• The user wants to merge multiple sheets inside one Excel file row-wise (e.g., "合并 Sheet", "把多个Sheet合成一个表").

Prerequisites (Python environment)

The script requires pandas and openpyxl. A requirements.txt is bundled with the
skill; install it into a Python 3.10+ environment before running (one-time, needs
internet). Installation is explicit and auditable — the script does not modify the
environment on its own.

1. Use a Python 3.10+ interpreter. The managed Python runtime bundled with WorkBuddy is
   preferred; its path includes the current user's home directory:
   - Windows: C:\Users\<USER>\.workbuddy\binaries\python\versions\<version>\python.exe
   - macOS / Linux: ~/.workbuddy/binaries/python/versions/<version>/python.exe
   If that path cannot be found, fall back to python3 (3.10+) on PATH.
2. (Optional, recommended) Run inside a dedicated virtual environment so packages stay
   isolated from the system:
   - Windows: <managed_python> -m venv <home>\.workbuddy\binaries\python\envs\default
   - macOS / Linux: <managed_python> -m venv <home>/.workbuddy/binaries/python/envs/default
3. Install dependencies from the bundled requirements file (run once):
   - Windows: <venv_dir>\Scripts\pip install -r requirements.txt
   - macOS / Linux: <venv_dir>/bin/pip install -r requirements.txt

If the dependencies are missing when the script runs, it stops with a clear error telling
you to run the install command above. This keeps dependency installation transparent
rather than silently altering the environment.

Usage

Run the bundled script scripts/excel_splitter.py with the venv interpreter.

Arguments

• -m, --mode (default split_by_col): operation mode:
• split_by_col: split a sheet by column values.
• split_sheets: split a workbook into separate files, one per sheet.
• merge_files: concatenate multiple files (row-wise) into a single workbook.
• merge_sheets: concatenate multiple sheets of a single workbook (row-wise) into one sheet.
• -f, --file (required): path to the source .xlsx (or wildcard/directory/comma-separated files for merge_files).
• -c, --column (default 0): column name or 0-based index (only for split_by_col).
• -o, --output (optional): output folder path (for split modes) or output file path (for merge modes).
• -s, --sheet (default 0): worksheet name or index (for split_by_col and merge_files).
• --drop-split-column (optional flag): remove the split column in output sub-tables (only for split_by_col).

Examples

1. Split by the column named "地区" (split_by_col):

...venv\Scripts\python.exe scripts/excel_splitter.py -f "C:/path/销售表.xlsx" -c "地区"

2. Split sheets in a workbook to separate files (split_sheets):

...venv\Scripts\python.exe scripts/excel_splitter.py -m split_sheets -f "C:/path/多表.xlsx" -o "C:/path/拆分结果"

3. Merge multiple files in a folder into one table (merge_files):

...venv\Scripts\python.exe scripts/excel_splitter.py -m merge_files -f "C:/path/拆分结果/*.xlsx" -o "C:/path/合并表.xlsx"

4. Merge all sheets inside a workbook into one table (merge_sheets):

...venv\Scripts\python.exe scripts/excel_splitter.py -m merge_sheets -f "C:/path/多表.xlsx" -o "C:/path/合并工作表.xlsx"

Behavior & edge cases

• Each unique value in the split column becomes one output file: <value>.xlsx.
• NaN / empty group values are written to 未分类_空值.xlsx.
• Filenames are sanitized: Windows-illegal characters (\ / : * ? " < > |) become _,
  and trailing dots/spaces are stripped. Empty names fall back to 未命名.
• If two group values sanitize to the same filename, a numeric suffix is appended
  (name_1.xlsx, name_2.xlsx, …).
• The original file is never modified; all outputs go to a separate folder.

Output

The script prints a summary: split column, output directory, file count, total rows,
and a per-file row count. Relay this summary back to the user after running, and point
them to the output folder.