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


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: 0    Average: 0/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.

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.