Excel Worksheet Splitting Tool
Task Objectives
- 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
- Dependency description: Python packages required by the scripts
- Python version: 3.7 or higher
Operation Steps
Step 1: Prepare for Splitting
Prepare the Excel file to be split, ensuring the file format is .xlsx or .xlsm.
Step 2: Execute Splitting
Basic Usage
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
,
, etc.
Advanced Options
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"
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
After splitting is completed, check the generated Excel files in the output directory. Each worksheet will generate an independent Excel file.
Python API Call
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
The script returns a dictionary containing the following fields:
- : Status code ('success' for success, 'partial_success' for partial success, 'error' for error)
- : Human-readable summary information
- : Original file path
- : File creation location
- : List of created file paths
- : List of error messages (if any)
- : Number of worksheets in the original file
- : Number of successfully created files
- : Number of failed worksheets
File Naming Rules
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
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:
- : Success or partial success
- : Complete failure
Limitations Description
- 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
Example 1: Split Reports for Distribution
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
Example 2: Extract All Worksheets for Separate Processing
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
Example 3: Programmatic Batch Processing
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
Before processing important files, it is recommended to test with a sample workbook:
bash
# 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
Resource Index
- Core script: See scripts/split_excel_sheets.py (Excel Worksheet Splitting Tool)
Notes
- 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",