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.
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.
.
├── 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
pip install numpy -t site-packages/
If you specify the folder name with -t, the file will be expanded and installed on the spot.
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