I. Preface
The company's colleagues have recently done excel-related work; today to seek help from the knowledge seeker to merge multiple excel for a workbook, the original java operation poi is too painful, unwieldy, memory consumption is serious, the knowledge seeker to use python less than 40 lines of code to complete the work of more than 60 excel workbook merged into one; python is really fragrant!
With the bragging rights out of the way, readers who have read the Knowledge Seekers series will know that previously Knowledge Seekers posted a
python topics using openpyxl to manipulate excelThis article is not openpyx library, the use of the library is xlrd, xlwt library, although the function of these two libraries can not be root openpyx compared, but can operate xls end of the old version of excel and openpyx does not support;
II Code
The general idea is as follows
- Iterate to get all excel files in the root directory
- Match by excel name to get a certain type of excel
- Creating a workbook for writing the copied data
- Each excel has a Sheet1, loop through the cells to write to the created workbook
Detailed process is written inside the code; readers can refer to the idea and use, the follow-up knowledge seekers have time to come out of the use of this 2 libraries of basic series of articles;
# -*- coding: utf-8 -*-
import xlrd
import xlwt
import os
import re
""" Write in workbook """
def write_excel(path, write_sheet):
# Loading workbooks
book = xlrd.open_workbook(path)
# Get form
read_sheet = book.sheet_by_name('Sheet1')
# Traverse
for row in range(read_sheet.nrows):
for col in range(read_sheet.ncols):
write_sheet.write(row, col, read_sheet.cell_value(row,col))
# Get the names of all files in the root directory
def walk(path):
for root,dirs,names in (path):
list = []
for filename in names:
path = (root, filename)
(path)
return list
if __name__ == "__main__":
# Create workbooks
write_book = ()
# Root directory
root = r'C:\mydata\generator\excel'
path_list = walk(root)
for path in path_list:
val = ("This specialty")
if val!=-1:
# Regular Match
ser = ('.*20200403(.*?).xls',path)
name = (1)
# Create sheets
write_sheet = write_book.add_sheet(name)
# Write
write_excel(path, write_sheet)
# Save
write_book.save(r'ThisPolytechnic.xls')
to this article on the python less than 50 lines of code to complete the implementation of multiple excel merger example of the article is introduced to this, more related python multiple excel merger content, please search for my previous posts or continue to browse the following related articles I hope that you will support me in the future more!