SoFunction
Updated on 2025-04-09

Python implements batch Excel splitting function

In daily office work, we often need to split Excel files containing multiple Sheet pages into multiple independent Excel files. For example, when we want to send an Excel form to each department for confirmation, in order to control the scope of information and ensure data confidentiality, each department only needs to view and confirm the Sheet page corresponding to its own department. Manually splitting Excel files is very tedious and time-consuming. In order to improve efficiency, we can write an automation tool in Python to batch split each Sheet page in the Excel file into an independent Excel file.

This article will share how to implement batch Excel splitting using Python and provide detailed code explanations.

1. Requirements Analysis

Maintain the original Sheet page format: During the splitting process, it is necessary to ensure that the content format in each independent Excel file is exactly the same as the corresponding Sheet page in the original Excel file, including font style, cell format, and color.

Color settings, alignment, etc. to avoid the loss of format affecting the viewing and analysis of data.

Output file management: The split independent Excel files are uniformly stored in the specified output folder for easy management and search.

2. Install the dependency library

Before we start writing code, we need to install some necessary Python libraries. Here we will use the pywin32 library to operate Excel files and the tqdm library to display the split progress. You can install it through the following command:

pip install pywin32 tqdm

3. Code implementation

Here is the complete Python code for batch splitting each Sheet page in an Excel file into a separate Excel file. The code contains detailed Chinese comments for everyone to understand.

3.1 

import os
import shutil
import 
from tqdm import tqdm

def sheet2excel(input_excel_path, output_path):
    """
     Split each Sheet page in an Excel file into a separate Excel file

     :param input_excel_path: The path to input Excel file
     :param output_path: output folder path
     """
    # Get the directory where the current script is located    parent = ((__file__))
    
    # Initialize Excel application    excel = ("")
     = False  # Do not display the Excel interface
    # If the output folder exists, delete it and recreate it    output_dir_path = (parent, output_path)
    if (output_dir_path):
        (output_dir_path)
    (output_dir_path)
    
    try:
        # Open Excel file        wb = ((parent, input_excel_path))
        # traverse each Sheet page        for sheet in tqdm(, desc="EXCEL Split"):
            # Copy the current Sheet page to a new workbook            ()
            # Save a new workbook as a standalone Excel file            ((output_dir_path, f"{}.xlsx"))
            # Close new workbook            ()
        # Close the original workbook        ()
    finally:
        # Exit the Excel application        ()

if __name__ == "__main__":
    # Example: Split the files in the current directory and output them to the output folder    sheet2excel("", "output")

3.2 Code Description

1. Function definition and path processing:

The sheet2excel function receives two parameters: input_excel_path (input Excel file path) and output_path (output folder path).

Get the directory where the current script is located by ((__file__)), and then use the function to convert the relative path into an absolute path to ensure that the file and folder paths can be processed correctly on different operating systems.

2. Excel application initialization:

  • (""): Initializes an Excel application object for subsequent operations on Excel files.
  • = False: Set the Excel application to invisible to avoid opening the Excel interface in the background and reducing interference to user operations.

3. Output folder processing:

  • (output_dir_path): If the output folder already exists, delete the folder and all the contents it contains, make sure that the output folder is empty and avoid file conflicts.
  • (output_dir_path): Creates a new output folder for storing separate Excel files after splitting.

4. File splitting operation:

  • wb = (...): Open the specified Excel file.
  • for sheet in tqdm(, desc="EXCEL Split"): Use the tqdm library to traverse each Sheet page in an Excel file and display the split progress.
  • (): Copy the current Sheet page to a new workbook.
  • (...): Save the new workbook as a separate Excel file, named after the original Sheet page.
  • (): Close the new workbook.
  • (): Close the original Excel workbook.

5. Resource release:

(): Exit the Excel application and release relevant resources.

4. Running example

Suppose we have an Excel file called, which contains three Sheet pages: Sheet1, Sheet2, and Sheet3. We can split each Sheet page into a separate Excel file through the following code and save it to the output folder:

if __name__ == "__main__":
    sheet2excel("", "output")

After running the above code, three independent Excel files will be generated in the output folder: , and . During the splitting process, you can see the display of the splitting progress in the console to facilitate understanding of the progress of the splitting work.

5. Things to note

Excel version compatibility: This code operates Excel files based on the pywin32 library. You need to make sure that Microsoft Excel software is installed on your system, and that the code may have compatibility issues on different versions of Excel. It is recommended to test on small-scale data before running the code.

Output folder permissions: Ensure that the user running the code has read, write and delete permissions to the output folder, otherwise it may cause the output folder to be unable to be created or deleted normally.

Data backup: Before performing a split operation, be sure to back up the original Excel file to prevent unexpected situations during the splitting process from losing or corruption of data.

6. Summary

Through the Python code introduced in this article, we can easily implement the function of batch splitting Excel files. This approach not only improves work efficiency, but also reduces the risk of errors caused by manual operations.

This is the end of this article about Python implementing batch Excel splitting function. For more related content on Python Excel splitting, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!