如何利用Python编程批量处理Excel来提高日常工作效率!

最近的工作遇到一个需求，整理多个相同格式但是不标准的Excel表格，最终汇总成一个Excel表格，并进行数据透视分析。

表格内涉及到合并的单元格，不同表格表头位置有偏差等问题。目标是将所有表格内容相同表头内容统计到一个表内。由于Excel表格内容数据量巨大，如果使用人力的手段，工作量巨大，并且容易出错（预估工作量3天）。所以决定使用Python对Excel进行自动处理，实现一键导出目标Excel的目的。

大致步骤：Python学习交流群：1004391443

1.安装Python3，pip工具环境

2.安装xlrd和xlwt

3.读取所有源Excel文件

4.将读取到的Excel信息组织到新的Excel对象中

5.数据缓存和分析

6.向新Excel对象中追加分析结果

7.将新Excel对象保存为目标输出文件（最终想要的文件）

1.对于Python环境的安装本文不做说明。

2.安装Python扩展包有不同的方式，这里使用pip命令安装xlrd和xlwt。

在Windows下，打开命令提示符（控制台），cd python的安装路径下Scripts文件夹下（在这里有pip命令执行工具）。或者将该路径添加到系统环境变量。

xlrd：读取Excel的扩展包

执行：pip install xlrd

xlwt：写入Excel的扩展包

执行：pip install xlwt

随便写一个py文件写入import xlrd, xlwt 执行该文件不出错说明安装成功。

或者打开python自带的编辑器进行输入测试。

3.具体代码如下

# -*- coding: utf-8 -*-import xlrdimport xlwtimport sys
def read(file_list):
    # 创建新的workbook    out_work_book = xlwt.Workbook(encoding = 'utf-8')    # 为了将多个文件的相同表头的sheet和并到一个sheet内，定义新sheet写入的行号位置    out_work_book_rows = [0,0]
    # 取得传入的文件列表 （第一个参数为脚本名字）    for file_name in file_list[1:]:        # 打开文件        workbook = xlrd.open_workbook(file_name)        # 取得当前文档的sheet列表        sheet_names = workbook.sheet_names()
        for sheet_idx in range(len(sheet_names)):            sheet_name = sheet_names[sheet_idx]                        # sheet = workbook.sheet_by_index(sheet_idx)            sheet = workbook.sheet_by_name(sheet_name)
            # sheet.row_values(n)   获取整行数据            # sheet.col_values(n)   获取整列数据
            # 获取行数            rows_count = sheet.nrows            # 获取列数            cols_count = sheet.ncols            # 存储写入的列数            write_col_count
            # 创建新的sheet            if len(out_work_book_sheet) <= sheet_idx:                out_work_book_sheet[sheet_idx] = out_work_book.add_sheet('sheet' sheet_idx)
            # 写入sheet            for curr_col_num in range(cols_count)                # 当前列的内容                col = sheet.col_values(curr_col_num)                # 遍历当前列的所有选项                for index in range(len(col))                    # 当前列第index的内容                    value = col[index]                    # 在写入的sheet的行号上，加入上一个表写到的位置行号，继续向后追加内容                    out_work_book_sheet[sheet_idx].write(out_work_book_rows[sheet_idx]   index, write_col_count, value)                write_col_count              out_work_book_rows[sheet_idx]  = cols_count    # 输出目标文件    out_work_book.save('out.xls')if __name__ == "__main__":    read(sys.argv)

4.针对合并的单元格，上述代码会出现合并单元格只有一个格子有内容其余为空的情况。这种情形，现将当前sheet表格内所有合并单元格数据保存下来，当遇到属于合并单元格的部分，则默认选取该合并单元格的第一个格子的数值即可。代码如下：

def get_merged_cells_value(sheet, row_index, col_index):
    # 获得当前sheet的所有合并单元格数据    merged = sheet.merged_cells    # 判断给定的单元格，是否属于合并单元格，如果是合并单元格，就返回合并单元格的内容    for (rlow, rhigh, clow, chigh) in merged:        if (row_index >= rlow and row_index < rhigh):            if (col_index >= clow and col_index < chigh):                cell_value = sheet.cell_value(rlow, clow)                return cell_value    return None

5.数据缓存可以利用python中的容器，和对象结构，将Excel中的数据对象化，在读取时候进行实例化然后进行保存。在需要进行数据分析的时候利用。

6.在遇到特殊的表格格式的时候，读取的内容可能在转存的时候出现类型错误，导致无法识别。例如时间类型，正确显示需要按特定格式进行读取并且转换。

表格类型说明: table.ctype

0 empty

1 string

2 number

3 date

4 boolean

5 error

代码如下：

from datetime import datetime,date
def get_merged_cells_value(workbook, sheet, row_index, col_index):
    # 获得当前sheet的所有合并单元格数据    merged = sheet.merged_cells    # 判断给定的单元格，是否属于合并单元格，如果是合并单元格，就返回合并单元格的内容    for (rlow, rhigh, clow, chigh) in merged:        if (row_index >= rlow and row_index < rhigh):            if (col_index >= clow and col_index < chigh):
                if sheet.cell(rlow, clow).ctype == 3:                    date_value = xlrd.xldate_as_tuple(sheet.cell(rlow, clow), workbook.datemode)                    # 转换显示格式为2019/05/05                    cell_value = date(*date_value[:3]).strftime('%Y/%m/%d')                else:                    cell_value = sheet.cell_value(rlow, clow)                                return cell_value    return None

7.最终，我们写一个windows下的bat批处理文件或者在linux下写一个shell，将文档名字作为参数传入执行即可。

到此我利用程序完成了几天的工作，还可以在以后相同的使用环境下更快速的完成工作。