python之excel多表合并

关于python的应用办公中很常见，尤其是对于数量较多的重复性操作。本节课要做的是将多张excel表中的信息合并到一张excel表中。

 

新建一个文件夹名为【merge】，里面放入三个名为【销售订单信息登记表】的excel表。为了演示只准备了三个exccel表，实际操作中更多数量的excel表也是可以的。

 

 

【销售信息登记表】内部内容如下图所示。也可以自行设置excel中内容，建议保证格式和下图一致，否则需要修改python中的代码。

 

 

 

01

确认目标

 

复制3个excel表中的内容，然后粘贴到一个新建的excel表中汇总。

02

分析过程

 

（1）三个excel表在一个文件中，需要遍历文件夹中的所有文件。这部分之前没学过，需要想办法解决。

 

（2）将第一步遍历的三个excel表中的内容读取，然后保存。可以通过前面讲过的openpyxl模块来实现。

 

（3）新建一个excel表，通过openpyxl模块实现。

 

（4）在新建的excel表中写入表头，写入通过openpyxl模块实现，合并单元格和字体加粗、居中没学过。

 

（5）将第二步中读取的内容写入新建的excel表中，然后保存。

 

根据上述五个步骤，即可完成本次练习。其中有两步涉及到之前未学过的知识，这在编程中很常见，需要自己上网查询解决。受篇幅所限，后面的讲解中不会展示查询的过程。

 

03

代码实现

 

先来实现第一步，想要遍历文件夹中的文件，需要用到os模块。os模块是内置模块，不需要安装。通过【os.listdir('文件夹地址')】函数遍历文件夹中的所有文件，得到文件名称。然后通过for循环，依次获取文件名称。

 

考虑到文件夹中可能存在不需要的文件，比如文件格式或文件名不符合要求的，所以需要将不符合要求的文件过滤。本次的过滤条件为【以'.xlsx'结尾】，通过endswith()函数来判断。对于不符合要求的，返回循环继续执行。代码如下；

import os#遍历文件夹中的所有文件for i in os.listdir(r'C:\Users\GT\Desktop\merge'):#判断文件名是否以'.xlsx'结尾    if i.endswith('.xlsx'):        ......    else:        continue

接下来开始第二步，根据文件名称合成的地址来读取excel文件，获取文件中的工作表【Sheet1】。然后读取表中的内容，并以列表的形式保存。最后将每次循环获得的列表保存在一个新建的空列表中。代码如下：

import os#遍历文件夹中的所有文件for i in os.listdir(r'C:\Users\GT\Desktop\merge'):    #判断文件名是否以'.xlsx'结尾    if i.endswith('.xlsx'):        #读取文件路径        wb = load_workbook(r'C:\Users\GT\Desktop\merge\\'+i)        info = wb['Sheet1']        #获取行数        rows = info.max_row        #按照行数循环读取数据（减去两行标题）        for row in range(rows-2):            # 按行读取每个字段中的值            A = info.cell(3+row,1).value            B = info.cell(3+row,2).value            C = info.cell(3+row,3).value            D = info.cell(3+row,4).value            E = info.cell(3+row,5).value            F = info.cell(3+row,6).value            #判断是否整行都是空值            if A and B and C and D and E and F != None:                # 将读取的值以列表形式保存                list = [A,B,C,D,E,F]                # 将列表list写入data中                data.append(list)            else:            continueelse:    continue

上述代码中，cell(row,col)代表excel中单元格的位置。cell()中的位置是从1开始计算的，例如cell(3,1)代表的是excel中第3行第1列的单元格，也就是A3单元格。【A = info.cell(3+row,1).value】是读取Sheet1中第3+row行第1列的单元格的值存入变量A中。

 

循环结束后，列表data中保存的是遍历所有文件后取得的内容，不包含excel表的表头。

第二步完成后是第三步，通过openpyxl模块创建excel，其中需要用到openpyxl中的Workbook和load_workbook。代码如下：

from openpyxl import Workbook,load_workbook#新建excelwb = Workbook()#新建sheet表sheet = wb.active

第四步中的合并单元格需要用到【merge_cells('单元格范围')】函数来实现，字体和位置需要调用openpyxl.styles模块中的Font和Alignment。代码如下：

from openpyxl.styles import Font,Alignment#merge_cells()函数合并单元格sheet.merge_cells('A1:F1')#确定写入位置title = sheet.cell(1,1)#确定写入内容title.value = '销售订单信息登记表'#字体为粗体的设置font = Font(bold=True)#字体位置水平居中、垂直居中的设置alignment = Alignment(horizontal='center',vertical='center')

上述代码完成了单元格合并、合并单元格内容写入以及字体加粗、居中的设置，还需要对表头的六个标题字段进行写入以及对所有的字体进行加粗、居中设置。代码如下：

#将变量title变为粗体title.font = font#将变量title水平、垂直居中title.alignment = alignment#excel表头字段列表list = ['订单编号','商品名称','单位','订单总额','订单状态','发货状态']for i in range(len(list)):    #通过循环将表头字段写入excel    sheet.cell(2,i+1).value = list[i]    #通过循环将字体加粗    sheet.cell(2,i+1).font = font    #通过循环将内容居中    sheet.cell(2,i+1).alignment = alignment

最后是第五步，利用循环将第二步中存入data列表的数据写入新建的excel中，然后保存excel。代码如下：

#按照data中的列表数量循环for row in range(len(data)):    #将data中的数据写入之前新建的sheet表    sheet.append(data[row])#保存excel表wb.save('total.xlsx')

到这里代码就都完成了，整合上面五步的代码后运行，可以得到一个名为'total.xlsx'的excel表，位于当前编写代码的【.py】文件的同一目录下。