Search code examples
pythonexcelpandasxlrd

How to split an Excel workbook by worksheet while preserving grouping

I am doing some excel reports for work and am given a book exported from SSRS daily. The book is nicely set up, with groupings applied to every sheet for an effect similar to pivot tables.

However the book comes with 32 sheets, and I eventually need to send out each sheet individually as a distinct report. Right now I am splitting them up manually, but I am wondering if there is a way to automate this while preserving the grouping.

I previously tried something like:

import xlrd 
import pandas as pd

targetWorkbook = xlrd.open_workbook(r'report.xlsx', on_demand=True)

xlsxDoc = pd.ExcelFile('report.xlsx') 

for sheet in targetWorkbook.sheet_names():
    reportDF = pd.read_excel(xlsxDoc, sheet)

    reportDF.to_excel("report - {}.xlsx".format(sheet))

However since I'm converting each sheet to a pandas datagrams, the grouping is lost.

There are multiple ways to read/interact with excel docs in python, but I can't find a clear way to pick out a sheet and save it as its own document without losing the grouping.

Solution

  • This is my full answer. I have used the Worksheets().Move() method. The main idea is to use win32com.client library.

    This was tested and works on my Windows 10 system with Excel 2013 installed, and Python 3.7. The grouping format was moved intact with the worksheets. I am still working on getting the looping to work. I will revise my answer again when I get the looping to work.

    My example has 3 worksheets, each with different grouping (subtotal) formats.

    #
# Refined .Move() method, save new file using Active Worksheet property.
#
import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb0 = excel.Workbooks.Open(r'C:\python\so\original.xlsx')
excel.Visible = True

# Move sheet1.
wb0.Worksheets(1).Move()
excel.Application.ActiveWorkbook.SaveAs(r'C:\python\so\sheet1.xlsx')

# Move sheet2, which is now the front sheet.
wb0.Worksheets(1).Move()
excel.Application.ActiveWorkbook.SaveAs(r'C:\python\so\sheet2.xlsx')  

# Save single remaining sheet as sheet3.
wb0.SaveAs(r'C:\python\so\sheet3.xlsx')
wb0.Close()
excel.Application.Quit()

    You would also need to install pywin32, which is not a standard library item. https://github.com/mhammond/pywin32

    pip install pywin32