splitting an Excel workbook into multiple excel files

user_12885738 0
Viewed 7889

i have an excel workbook with 29 different sheets. i used the following code to save each sheet as an individual excel file:

from xlrd import open_workbook
from xlwt import Workbook

rb = open_workbook('c:\\original file.xls',formatting_info=True)

for a in range(5): #for example there're only 5 tabs/sheets
    rs = rb.sheet_by_index(a)

    new_book = Workbook()
    new_sheet = new_book.add_sheet('Sheet 1')

    for row in range(rs.nrows):
        for col in range(rs.ncols):
            new_sheet.write(row, col, rs.cell(row, col).value)

    new_book.save("c:\\" + str(a) + ".xls")

i got this code from: stackoverflow.com/questions/28873252/python-splitting-an-excel-workbook. it worked well but is there a way i could save the workbooks by sheet name. so the sheet name should be what the file is called. i tried replacing 

new_book.save("c:\\" + str(a) + ".xls")

with 

new_book.save(sheet.names + str(a) + ".xls")

But it didnt work

pandas python
1 Answers

IIUC,

you can use pandas with pd.ExcelFile and read the whole workbook as a dictionary.

import pandas as pd

xl = pd.ExcelFile('c:\\original file.xls')


for sheet in xl.sheet_names:
    df = pd.read_excel(xl,sheet_name=sheet)
    df.to_excel(f"{sheet}.xls",index=False)
user_93751020
Related