How to implement multiple table merge in excel in python (with code)

Today I will use python to merge multiple excel workbooks into one workbook. I used less than 40 lines of code to merge more than 60 excel workbooks into one. Let’s take a look. Bar.

This article does not use the openpyx library, but uses the xlrd and xlwt libraries. Although the functions of these two libraries cannot be compared with openpyx, they can operate the old version of excel ending in xls and openpyx does not support it

Code

The general idea is as follows

• Traverse to obtain all excel files in the root directory
• According to Match the excel name to obtain a certain type of excel
• Create a workbook for writing copied data
• Each excel has a Sheet1, loop through the cells to write the created workbook

The detailed process is written in the code

# -*- coding: utf-8 -*-
import xlrd
import xlwt
import os
import re
""" 写入工作本 """
def write_excel(path, write_sheet):
    # 加载工作本
    book = xlrd.open_workbook(path)
    # 获取表单
    read_sheet = book.sheet_by_name('Sheet1')
    # 遍历
    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))
            
# 获取根目录下所有文件名
def walk(path):
  for root,dirs,names in os.walk(path):
    list = []
    for filename in names:
        path = os.path.join(root, filename)
        list.append(path)
    return list
if __name__ == "__main__":
    # 创建工作本
    write_book = xlwt.Workbook()
    # 根目录
    root = r'C:\mydata\generator\excel'
    path_list = walk(root)
    for path in path_list:
        val = path.find("本专科")
        if val!=-1:
        # 正则匹配
            ser = re.search('.*20200403(.*?).xls',path)
            name = ser.group(1)
            # 创建sheet
            write_sheet = write_book.add_sheet(name)
            # 写入
            write_excel(path, write_sheet)
    # 保存        
    write_book.save(r'本专科.xls')

Thank you everyone for reading, I hope you will benefit a lot.

This article is reproduced from: https://blog.csdn.net/youku1327/article/details/105300668

Recommended tutorial: "python tutorial"

The above is the detailed content of How to implement multiple table merge in excel in python (with code). For more information, please follow other related articles on the PHP Chinese website!