I made a tool in Python that right-clicks an Excel file and divides it into files for each sheet.

background purpose

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.

Installation of external libraries

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

code

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()

Create bat file

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 to Sendto folder

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.

How to Use

Right-click the Excel file → Send → click excel_split.bat to create a file for each sheet in the Excel file. 無題.png

Supplement: If you want to make an EXE and distribute it to users

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

I made a tool in Python that right-clicks an Excel file and divides it into files for each sheet.
I made a Discord bot in Python that translates when it reacts
A python program that resizes a video and turns it into an image
I made a python dictionary file for Neocomplete
Divide each PowerPoint slide into a JPG file and output it with python
[Python] I made a script that automatically cuts and pastes files on a local PC to an external SSD.
I made a VM that runs OpenCV for Python
A function that divides iterable into N pieces in Python
I made an automated program for pasting screenshots in Excel
I made a toolsver that spits out OS, Python, modules and tool versions to Markdown
I made a tool that makes it a little easier to create and install a public key.
I made a script in Python to convert a text file for JSON (for vscode user snippet)
I made a tool that makes it convenient to set parameters for machine learning models.
[Python3] I made a decorator that declares undefined functions and methods.
What I learned and coded for a function that opens a special Windows folder in Python3 ctypes
Python> I made a test code for my own external file
I made a lot of files for RDP connection with Python
Read an Excel sheet and loop it line by line Python VBA
I made a scaffolding tool for the Python web framework Bottle
I made a library that adds docstring to a Python stub file.
Get the formula in an excel file as a string in Python
[python] I made a class that can write a file tree quickly
I wrote a tri-tree that can be used for high-speed dictionary implementation in D language and Python.
I made a POST script to create an issue on Github and register it in the Project
A script that retrieves tweets with Python, saves them in an external file, and performs morphological analysis.
I made a web application in Python that converts Markdown to HTML
The one that divides the csv file, reads it, and processes it in parallel
I made a CLI tool to convert images in each directory to PDF
I want to color a part of an Excel string in Python
I made a script in python to convert .md files to Scrapbox format
Open an Excel file in Python and color the map of Japan
I made a tool that makes decompression a little easier with CLI (Python3)
[IOS] I made a widget that displays Qiita trends in Pythonista3. [Python]
I made a program to check the size of a file in Python
I made a payroll program in Python!
I created a password tool in Python.
I made a configuration file with Python
[Python] I created an app that automatically downloads the audio file of each word used for the English study app.
How to save the feature point information of an image in a file and use it for matching
I made a program in Python that reads CSV data of FX and creates a large amount of chart images
I made a system that automatically decides whether to run tomorrow with Python and adds it to Google Calendar.
I made a tool to notify Slack of Connpass events and made it Terraform
[Python, PyPDF2] A script that divides a spread PDF into two left and right
I want to write an element to a file with numpy and check it.
[Python] Concatenate a List containing numbers and write it to an output file.
Note that I understand the least squares algorithm. And I wrote it in Python.
[Python] I made a LINE Bot that detects faces and performs mosaic processing.
I made a familiar function that can be used in statistics with Python
I made an in-house peer bonus tool (aggregation only) using Python and BigQuery
In Python, I made a LINE Bot that sends pollen information from location information.
A memo that I wrote a quicksort in Python
I made a useful tool for Digital Ocean
I wrote a class in Python3 and Java
I made a Caesar cryptographic program in Python.
I made a Python program for Raspberry Pi that operates Omron's environmental sensor in the mode with data storage
A Python script that crawls RSS in Azure Status and posts it to Hipchat
I made a program to convert images into ASCII art with Python and OpenCV
I made a Docker Image that reads RSS and automatically tweets regularly and released it.
Create an exe file that works in a Windows environment without Python with PyInstaller
A memo that implements the job of loading a GCS file into BigQuery in Python
I made a web application that maps IT event information with Vue and Flask