excel-sheet-splitter

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Excel工作表拆分工具

Excel Worksheet Splitting Tool

任务目标

Task Objectives

本Skill用于：将Excel工作簿(.xlsx, .xlsm)按工作表拆分为独立的Excel文件
能力包含：保留所有单元格值和公式、保持原始格式、维护列宽行高、保留合并单元格
触发条件：用户需要将多工作表Excel文件拆分为独立文件时

This Skill is used to: Split Excel workbooks (.xlsx, .xlsm) into separate Excel files by worksheet
Capabilities include: Retain all cell values and formulas, preserve original formatting, maintain column widths and row heights, keep merged cells
Trigger condition: When users need to split multi-worksheet Excel files into separate files

前置准备

Preparations

依赖说明：scripts脚本所需的Python包
```
openpyxl>=3.0.0
```
Python版本：3.7或更高版本

Dependency description: Python packages required by the scripts
```
openpyxl>=3.0.0
```
Python version: 3.7 or higher

操作步骤

Operation Steps

第一步：准备拆分

Step 1: Prepare for Splitting

将需要拆分的Excel文件准备好，确保文件格式为.xlsx或.xlsm。

Prepare the Excel file to be split, ensuring the file format is .xlsx or .xlsm.

第二步：执行拆分

Step 2: Execute Splitting

基本用法

Basic Usage

使用默认设置拆分Excel文件（输出到相同目录）：

bash

python scripts/split_excel_sheets.py input_file.xlsx

这将创建名为

input_file_Sheet1.xlsx

、

input_file_Sheet2.xlsx

等的文件。

Split Excel files with default settings (output to the same directory):

bash

python scripts/split_excel_sheets.py input_file.xlsx

This will create files named

input_file_Sheet1.xlsx

input_file_Sheet2.xlsx

, etc.

高级选项

Advanced Options

指定输出目录：

bash

python scripts/split_excel_sheets.py input_file.xlsx -o ./output_folder

自定义文件名前缀：

bash

python scripts/split_excel_sheets.py data.xlsx -p "2024年报表"

生成：

2024年报表_Sheet1.xlsx

、

2024年报表_Sheet2.xlsx

等。

JSON格式输出（用于程序化调用）：

bash

python scripts/split_excel_sheets.py input_file.xlsx --json

返回包含文件路径和状态的结构化JSON数据。

Specify output directory:

bash

python scripts/split_excel_sheets.py input_file.xlsx -o ./output_folder

Custom filename prefix:

bash

python scripts/split_excel_sheets.py data.xlsx -p "2024 Report"

Generates:

2024 Report_Sheet1.xlsx

2024 Report_Sheet2.xlsx

, etc.

JSON format output (for programmatic calls):

bash

python scripts/split_excel_sheets.py input_file.xlsx --json

Returns structured JSON data containing file paths and status.

第三步：查看结果

Step 3: View Results

拆分完成后，在输出目录查看生成的Excel文件。每个工作表都会生成一个独立的Excel文件。

After splitting is completed, check the generated Excel files in the output directory. Each worksheet will generate an independent Excel file.

Python API调用

Python API Call

可以直接在Python代码中导入并使用函数：

python

from scripts.split_excel_sheets import split_excel_sheets

result = split_excel_sheets(
    input_file='data.xlsx',
    output_dir='./output',
    prefix='自定义前缀'
)

print(f"状态: {result['status']}")
print(f"创建了 {len(result['files'])} 个文件")

You can directly import and use the function in Python code:

python

from scripts.split_excel_sheets import split_excel_sheets

result = split_excel_sheets(
    input_file='data.xlsx',
    output_dir='./output',
    prefix='Custom Prefix'
)

print(f"Status: {result['status']}")
print(f"Created {len(result['files'])} files")

输出格式说明

Output Format Description

脚本返回一个字典，包含以下字段：

```
status
```
: 状态码 ('success'成功, 'partial_success'部分成功, 'error'错误)
```
message
```
: 人类可读的摘要信息
```
input_file
```
: 原始文件路径
```
output_directory
```
: 文件创建位置
```
files
```
: 创建的文件路径列表
```
errors
```
: 错误信息列表（如有）
```
total_sheets
```
: 原始文件中的工作表数量
```
successful
```
: 成功创建的文件数量
```
failed
```
: 失败的工作表数量

The script returns a dictionary containing the following fields:

```
status
```
: Status code ('success' for success, 'partial_success' for partial success, 'error' for error)
```
message
```
: Human-readable summary information
```
input_file
```
: Original file path
```
output_directory
```
: File creation location
```
files
```
: List of created file paths
```
errors
```
: List of error messages (if any)
```
total_sheets
```
: Number of worksheets in the original file
```
successful
```
: Number of successfully created files
```
failed
```
: Number of failed worksheets

文件命名规则

File Naming Rules

输出文件命名格式：

{前缀}_{工作表名}.xlsx

默认前缀：输入文件名（不含扩展名）
工作表名会被清理（仅保留字母数字、空格、连字符、下划线）
文件系统不兼容的字符（如
```
/
```
、
```
\
```
、
```
*
```
、
```
?
```
）会被移除

Output file naming format:

{prefix}_{worksheet_name}.xlsx

Default prefix: Input file name (without extension)
Worksheet names will be cleaned (only alphanumeric characters, spaces, hyphens, underscores are retained)
File system-incompatible characters (such as
```
/
```
,
```
\\
```
,
```
*
```
,
```
?
```
) will be removed

保留内容说明

Retained Content Description

拆分后的每个文件将保留：

所有单元格值和公式
原始格式（字体、颜色、边框、填充、数字格式）
列宽和行高
合并单元格
工作表结构

Each split file will retain:

All cell values and formulas
Original formatting (font, color, borders, fill, number format)
Column widths and row heights
Merged cells
Worksheet structure

错误处理

Error Handling

脚本能处理常见问题：

文件不存在：报告文件未找到
无工作表：报告工作簿为空
工作表处理错误：继续处理其余工作表，逐个报告错误
工作表名中的非法字符：自动清理为有效文件名

退出代码：

```
0
```
: 成功或部分成功
```
1
```
: 完全失败

The script can handle common issues:

File not found: Reports that the file was not found
No worksheets: Reports that the workbook is empty
Worksheet processing error: Continues processing remaining worksheets and reports errors one by one
Illegal characters in worksheet names: Automatically cleans to valid file names

Exit codes:

```
0
```
: Success or partial success
```
1
```
: Complete failure

局限性说明

Limitations Description

外部引用公式：如果公式引用其他工作表的单元格，在独立文件中可能显示错误
工作表间依赖：引用其他工作表的图表或数据验证可能无法正常工作
宏代码：.xlsm文件的VBA代码会被保留，但引用其他工作表的宏可能失败

External reference formulas: If formulas reference cells in other worksheets, they may display errors in independent files
Inter-worksheet dependencies: Charts or data validations referencing other worksheets may not work properly
Macro code: VBA code in .xlsm files will be retained, but macros referencing other worksheets may fail

使用示例

Usage Examples

示例1：拆分报表用于分发

Example 1: Split Reports for Distribution

功能：将月度报表按部门工作表拆分 执行方式：脚本执行

bash

python scripts/split_excel_sheets.py monthly_report.xlsx -o ./team_reports -p "2024年1月"

输出：在team_reports目录生成以"2024年1月"为前缀的独立Excel文件

Function: Split monthly reports by department worksheets Execution method: Script execution

bash

python scripts/split_excel_sheets.py monthly_report.xlsx -o ./team_reports -p "January 2024"

Output: Generates independent Excel files prefixed with "January 2024" in the team_reports directory

示例2：提取所有工作表用于单独处理

Example 2: Extract All Worksheets for Separate Processing

功能：将合并数据文件的每个工作表提取为独立文件 执行方式：脚本执行

bash

python scripts/split_excel_sheets.py consolidated_data.xlsx

输出：每个工作表成为独立的Excel文件，可用于单独分析

Function: Extract each worksheet from a consolidated data file into an independent file Execution method: Script execution

bash

python scripts/split_excel_sheets.py consolidated_data.xlsx

Output: Each worksheet becomes an independent Excel file, which can be used for separate analysis

示例3：程序化批量处理

Example 3: Programmatic Batch Processing

功能：批量处理多个Excel文件 执行方式：Python代码

python

import glob
from scripts.split_excel_sheets import split_excel_sheets

for file in glob.glob('*.xlsx'):
    result = split_excel_sheets(file, output_dir='./split_files')
    if result['status'] != 'success':
        print(f"{file}处理遇到问题: {result['message']}")

输出：所有Excel文件的工作表都被拆分到split_files目录

Function: Batch process multiple Excel files Execution method: Python code

python

import glob
from scripts.split_excel_sheets import split_excel_sheets

for file in glob.glob('*.xlsx'):
    result = split_excel_sheets(file, output_dir='./split_files')
    if result['status'] != 'success':
        print(f"Problem processing {file}: {result['message']}")

Output: All worksheets from Excel files are split into the split_files directory

测试建议

Testing Recommendations

在处理重要文件之前，建议使用样本工作簿进行测试：

bash

undefined

Before processing important files, it is recommended to test with a sample workbook:

bash

undefined

使用测试文件进行拆分

Split using test file

python scripts/split_excel_sheets.py test_file.xlsx -o ./test_output

验证所有工作表都被正确拆分并保留格式

Verify that all worksheets are correctly split and formatting is preserved

undefined

undefined

资源索引

Resource Index

核心脚本：见 scripts/split_excel_sheets.py（Excel工作表拆分工具）

Core script: See scripts/split_excel_sheets.py (Excel Worksheet Splitting Tool)

注意事项

Notes

确保Excel文件路径正确，文件格式为.xlsx或.xlsm
拆分后的文件中，跨工作表引用的公式可能需要手动调整
工作表名中的特殊字符会被自动清理为文件系统兼容的字符
建议先用测试文件验证功能，再处理重要数据

Ensure the Excel file path is correct and the file format is .xlsx or .xlsm
In split files, formulas with cross-worksheet references may need manual adjustment
Special characters in worksheet names will be automatically cleaned to file system-compatible characters
It is recommended to verify the function with a test file before processing important data",