Python use case – Save each worksheet as a separate excel workbook 1


In this post “Python use case – Save each worksheet as a separate excel workbook“, we are going to learn that how we can create a separate workbook for each worksheet of a given excel file. We will be copying data, values, formatting and all other settings of the sheet in the newly created workbook. Assume that we have an excel workbook having five sheets named “Sheet1“, “Sheet2“, “Sheet3“, “Sheet4“, and “Sheet5” which is as this.

Sample excel file

Sample excel file

Now, we want to create 5 different excel workbooks (one for each worksheet).

Output

Output

We are going to use Excel application to copy data, values, formatting and other settings of each sheet as a new workbook in a python script. Below is the python code.

def create_wb_from_ws():
    try:
        filepath = 'D:\Test\Excel\Sample Excel.xlsx'
        
        from win32com.client import DispatchEx
        excel = DispatchEx("Excel.Application")

        if excel == None:
            print('-' * 100)
            print('Error: Excel is not found on this machine. Existing!')
            print('-' * 100)
            return
        else:
            print('-' * 100)
            print('Message: Excel version {0} is available.'.format(excel.version))
            print('-' * 100)
        
        if int(float(excel.version)) < 12:
            fileext = '.xls'
        else:
            fileext = '.xlsx'

        import os
        if not os.path.exists(filepath):
            print('The entered file path does not exists. Existing!')
            return

        filedir = os.path.join(os.path.dirname(filepath), os.path.splitext(os.path.basename(filepath))[0])
        if not os.path.exists(filedir):
            os.mkdir(filedir)

        excel.Visible = False
        excel.DisplayAlerts = False
        wb = excel.Workbooks.Open(Filename = filepath)
        wb.Application.Visible = False

        for sheet in wb.Worksheets:
            filename = os.path.join(filedir, sheet.name + fileext)
            wbnew = excel.Workbooks.Add()
            wbnew.Application.Visible = False
            sheet.Copy(Before = wbnew.Worksheets(1))

            for s in wbnew.Worksheets:
                if s.name != sheet.name:
                    wbnew.Worksheets(s.name).Delete()

            wbnew.SaveAs(filename)
            print('Saved sheet name "{0}" as a new excel file at {1}'.format(sheet.name, filename))
            wbnew.Close(SaveChanges = 1)

        wb.Close(True)
        excel.Quit()
    except:
        print('-' * 100)
        print('Error occurred')
        print('-' * 100)
        raise

if __name__ == "__main__":
    create_wb_from_ws()

To execute the above python script, we can call this .py file using command prompt window as this.

python d:\Code\ExcelCreator.py

The output will be as this:

Excel creator output

Excel creator output

Thanks for the reading. Please share your input in comment section.

Rate This
[Total: 4    Average: 4/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal is a passionate Data Engineer and Data Analyst. He has implemented many end to end solutions using Big Data, Machine Learning, OLAP, OLTP, and cloud technologies. He loves to share his experience at https://www.sqlrelease.com/. Connect with Gopal on LinkedIn at https://www.linkedin.com/in/ergkranjan/.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One thought on “Python use case – Save each worksheet as a separate excel workbook

  • Avatar
    Andrea Catalina Fajardo

    That worked!!! Thanks a lot!! Just wondering if I need to be done for several excel files how I am supposed to be done?
    I tried:
    import os
    def create_wb_from_ws():
    try:
    directory = ‘H:\Documents\Andrea\SimuID\soil\soil_horizons’
    with os.scandir(directory) as it:
    for file in it:
    filepath = os.fsdecode(file)
    But it did not work!