Skip to content

Instantly share code, notes, and snippets.

@aschleg
Last active June 1, 2022 18:11
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save aschleg/62bcdc6cef2831c0c035 to your computer and use it in GitHub Desktop.
Save aschleg/62bcdc6cef2831c0c035 to your computer and use it in GitHub Desktop.
Alternative method to split Excel worksheet into multiple worksheets based on column name
from xlwings import Workbook, Range, Sheet
import pandas as pd
import os
# Alternative method to split an Excel worksheet into multiple sheets based on a column name.
# The script will prompt four questions to enter in the required information. The workbook will then open and
# split the prompted worksheet into separate worksheets based on the desired column name.
# To run, open the command prompt and enter the command python Split_Excel_Worksheet_v2.py
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Script
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
path = raw_input('Enter folder path of workbook containing worksheet to split: ')
worksheet = raw_input('Enter workbook name with extension, e.g. example.xlsx: ')
sheet = raw_input('Enter worksheet name to split: ')
column = raw_input('Enter column name to split worksheet data on: ')
workbook = os.path.join(path, worksheet)
wb = Workbook(workbook)
data = pd.DataFrame(pd.read_excel(workbook, sheet, index_col=None, na_values=[0]))
data.sort(column, axis = 0, inplace = True)
split = data.groupby(column)
for i in split.groups:
Sheet.add()
Range('A1', index = False).value = split.get_group(i)
@ajin9581
Copy link

Can I get the same for Linux

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment