Python that merges a lot of excel into one excel

Since 30 setting files are excel and all are carefully sent as separate files. I thought that opening each one would be a violation of basic human rights, so I asked python to do it.

Strategy

Put all the excel in one folder, make one python in the same folder, Describe all the contents on the first sheet of the file called all.xlsx.

Practice

File placement

.
├── all.xlsx         #Excel finally completed
├── excel_merge.py   #Python to make this time
├── site-packages    #Contains the package to use. Only numpy is used this time.
│   ├── bin
│   ├── numpy
│   └── numpy-1.18.5.dist-info
├── hogehoge1.xlsx   #A large amount of excel sent politely
├── hogehoge2.xlsx
・ ・ ・
└── hogehoge30.xlsx

install numpy

pip install numpy -t site-packages/

If you specify the folder name with -t, the file will be expanded and installed on the spot.

Create excel_merge.py

Let's write and save the source like this

import os, sys, glob
sys.path.append(os.path.join(os.path.dirname(__file__), 'site-packages'))
import openpyxl as px

NEW_FILE = "all.xlsx"

#Get all excel files
files = glob.glob("./*.xls*")

all_data = []
for f in files:
    #Ignore any open excel. all.Ignore xlsx even if it exists.
    if f.startswith('./~')  or f == NEW_FILE:
        continue
    #Please open excel
    wb=px.load_workbook(f, data_only=True)
    #Please open the sheet
    ws = wb.worksheets[0]
    #Read the sheet and get all the rows
    for row in ws.iter_rows(min_row=2):
        #If there is an unnecessary line, skip it.
        if row[0].value is None or \
            not str(row[0].value).strip() or \
            row[1].value is None or \
            row[0].value == 'nanika zyogai sitai mozi':
            continue
        values = []
        #Get data by licking all cells
        for col in row:
            values.append(col.value)
        #Save all cell data in one array
        all_data.append(values)

#From here all.Work to make xlsx
# print(all_data)
wb = px.Workbook()
ws = wb.worksheets[0]
start_row = 2
start_col = 3
#Write all cell data in order
for y, row in enumerate(all_data):
    for x, cell in enumerate(row):
        ws.cell(row=start_row + y,
                    column=start_col + x,
                    value=all_data[y][x])

#save as
wb.save(NEW_FILE)

Recommended Posts

Python that merges a lot of excel into one excel
Connect a lot of Python or and and
[Python] Combine multiple Excel sheets into one
Python code that removes contiguous spaces into one
Simple comparison of Python libraries that operate Excel
[Python] A program that counts the number of valleys
One liner that outputs 1000000 digits of pi in Python
Python + selenium to GW a lot of e-mail addresses
A collection of Excel operations often used in Python
[Python] A program that compares the positions of kangaroos.
A Python program that converts ical data into text
A script that combines multiple pages of a PDF file into one page without margins
A memo that implements the job of loading a GCS file into BigQuery in Python
A script that combines your favorite python modules and binaries into one Lambda Layer
A function that divides iterable into N pieces in Python
I want to start a lot of processes from python
[Blender x Python] Let's arrange a lot of Susanne neatly !!
Divide a set of integers into cosets in one shot
A set of script files that do wordcloud in Python3
A Python script that compares the contents of two directories
[Python] A story that seemed to fall into a rounding trap
[Python] Creating a GUI tool that automatically processes CSV of temperature rise data in Excel
A record of patching a python package
A good description of Python decorators
A story that struggled to handle the Python package of PocketSphinx
[Python] A memorandum of beautiful soup4
A brief summary of Python collections
From a book that programmers can learn ... (Python): Review of arrays
I made a lot of files for RDP connection with Python
A function that measures the processing time of a method in python
This and that of python properties
[Python] A program that compares each element of list one by one and wins or loses. zip ()
[python] A note that started to understand the behavior of matplotlib.pyplot
The story of making a module that skips mail with python
[Python] A program that rotates the contents of the list to the left
I did a lot of research on how Python is executed
[Python] A program that calculates the number of chocolate segments that meet the conditions
[Python] A program that calculates the number of socks to be paired
A summary of Python e-books that are useful for free-to-read data analysis
A python program that resizes a video and turns it into an image
I want to color a part of an Excel string in Python
Code reading of faker, a library that generates test data in Python
From a book that makes the programmer's way of thinking interesting (Python)
[Python] Note: A self-made function that finds the area of the normal distribution
[Python] A program that creates stairs with #
Display a list of alphabets in Python 3
Make a relation diagram of Python module
[python] Get a list of instance variables
[python] [meta] Is the type of python a type?
[Python] Create a LineBot that runs regularly
A typed world that begins with Python
The story of blackjack A processing (python)
A program that plays rock-paper-scissors using Python
Combine python function arguments into one variable
Introduce graphviz into a portable python environment
Combine multiple python files into one python file
[Python] Get a list of folders only
[Python] A program that rounds the score
A memorandum of python string deletion process
I generated a lot of images like Google Calendar favicon with Python and incorporated it into Vue's project
A class for PYTHON that can be operated without being aware of LDAP