I received a request to divide each sheet in an Excel file as a separate file. Originally I think it is a VBA project, but it is troublesome to open the Excel file and start the macro. Or rather, I don't want to write VBA in the first place. So, I would like to make a tool in Python that right-clicks an Excel file and divides it into files for each sheet.
You will need a library for working with Excel and a library for creating file paths. It is not standard, so install it in advance.
$ pip install win32com.client
$ pip install pathlib
$ pip install openpyxl
excel_split.py
import os
import glob
import win32com.client
import pathlib
import openpyxl
import sys
excel_dir = pathlib.Path(sys.argv[1]).parent #The path of the folder where the target Excel file is saved
input_file = str(sys.argv[1]) #Convert Excel file path to string
#Get a list of sheets(Use openpyxl)
book = openpyxl.load_workbook(input_file)
sheets_list = book.sheetnames
for i in range(len(sheets_list)):
#Start Excel
app = win32com.client.Dispatch("Excel.Application")
app.Visible = False
app.DisplayAlerts = False
#Import workbook in Excel
sheet_no = i + 1 #The excel sheet adds 1 for 1 start
book = app.Workbooks.Open(input_file)
book.WorkSheets(sheet_no).Activate()
new_book = app.Workbooks.Add()
#Save for each sheet
new_file = sheets_list[i] + '.xlsx'
path_obj = pathlib.Path(excel_dir, new_file) #Convert to path object
output_file = path_obj.resolve() #Convert to absolute path
book.Worksheets(sheet_no).Copy(Before=new_book.Worksheets(1)) #Copy the specified sheet to a new Excel file
new_book.SaveAs(str(output_file)) #Save file
#Exit Excel
app.Quit()
The py file is not kicked when placed in the sendto folder. Create a bat file to kick the created excel_split.py. * PowerShell is also acceptable.
excel_split.bat
python \\Where to save the Python file\excel_split.py
Save the created bat file in the sendto folder. The sendto folder can be displayed by pressing the [Win] and [R] keys at the same time and entering "shell: sendto" in the search box.
Right-click the Excel file → Send → click excel_split.bat to create a file for each sheet in the Excel file.
If you want to distribute it to users, you can start it without a bat file by putting the EXE version in the sendto folder as well. The following article introduces the method of EXE conversion.
https://qiita.com/sw1394/items/2123fa9aaecb766657e5
Recommended Posts