Python办公自动化-Excel处理

最近和同学聊天，有聊到工作中经常会处理一些产品的检测数据，经常都是从一张或者多张Excel表中摘取需要的数据然后放到一张新表中，但是每次都是重复性的工作。如图1要获取数据后处理成图2

• 图1.png
• 图2.png

可不可以写一个程序处理这些工作呢？当然是可以的，Python就可以处理。Python中处理Excel表的库有很多。比如：xlrd，openpyxl，xlwings等如图3。

• image.png

这次我们就选用openpyxl库来处理
首先我们新建工程建原始数据导入工程

• 图3.png

我们的原始数据文件就是“analyzedata.xlsx”
新建一个.py文件，“analyze.py”
然后导入库

from openpyxl import Workbook  //主要是用来新建文件
from openpyxl import load_workbook  //加载文件

首先我们在原来的Excel上新建一个sheet，并且命名为“Result”，代码如下：

# 新建一个新表用于存放结果数据
def new_sheet():
    # 加载Excel文件
    workbook = load_workbook(filename="analyzedata.xlsx")
    # 定义一个新表名
    new_sheetname = "Result"
    # 判断Excel中是否已经存在“Result”名称的这样一个表
    if new_sheetname not in workbook.sheetnames:
        # 如果不存在我们就新建一个
        workbook.create_sheet(new_sheetname)
        # print(workbook.sheetnames)
        workbook.save(filename="analyzedata.xlsx")
    # 如果已经存在，我们就把sheet里的数据删除
    else:
        sheet = workbook[new_sheetname]
        # 打印出表格有数据的范围，观察看看
        # print(sheet.dimensions) # A1:E27
        # 或有数据的每一行，然后执行删除
        for row in sheet.iter_rows():
            # print(row)
            sheet.delete_rows(idx=1)
            workbook.save(filename="analyzedata.xlsx")

注意每次我们退Excel表进行了表的删减，对数据进行了更改，都需要对文件使用“.save()”函数保存。如下

workbook.save(filename="analyzedata.xlsx")

注意：我们对于已经存在的表，最好进行一次数据的清空再加入新数据，防止有其他无用的数据混入。
比如我做的是删除每一行的操作。这样也可以做到在加入新数据前清空所有数据。

  sheet = workbook[new_sheetname]
  # 打印出表格有数据的范围，观察看看
  # print(sheet.dimensions) # A1:E27
  # 或有数据的每一行，然后执行删除
  for row in sheet.iter_rows():
     # print(row)
      sheet.delete_rows(idx=1)
      workbook.save(filename="analyzedata.xlsx")

通过上面new_sheet()函数我们就新建了一个sheet表。接下来我们获取需要的数据。如下定义一个新函数：

# 到源数据表中找到目标数据
def find_result():
    data_cell_list = []
    workbookT = load_workbook(filename="analyzedata.xlsx")
    # sheetT = workbook.active
    sheetT = workbookT["SourceData"]
    # 获取工作表大小
    sheet_size = sheetT.dimensions
    #  获取工作表内容
    cells = sheetT[sheet_size]
    # print(cells)
    # 定义是三个空列表
    data_cps_h31 = []
    data_cps_h32 = []
    data_poi_tp1 = []

    # cell_row_tuple是每一行为1个元组
    for cell_row_tuple in cells:
        # cell是每一行元组中的每一个小格子
        for cell in cell_row_tuple:
            # print(cell.value)
            if cell.value == "CPS_H31":
                print(cell.row, cell.column)
                cell_Y = sheetT.cell(row=(cell.row + 2), column=(cell.column + 3))
                cell_Z = sheetT.cell(row=(cell.row + 3), column=(cell.column + 3))
                cell_Len = sheetT.cell(row=(cell.row + 4), column=(cell.column + 3))
                cell_WID = sheetT.cell(row=(cell.row + 5), column=(cell.column + 3))
                cell_len_tem = sheetT.cell(row=(cell.row + 4), column=cell.column + 1)
                cell_wid_tem = sheetT.cell(row=(cell.row + 5), column=cell.column + 1)
                data_cps_h31 = [
                    ["Y", cell_Y.value],
                    ["Z", cell_Z.value],
                    [cell_len_tem.value, cell_Len.value],
                    [cell_wid_tem.value, cell_WID.value],
                ]

            if cell.value == "CPS_H32":
                print(cell.row, cell.column)
                cell_Y = sheetT.cell(row=(cell.row + 2), column=(cell.column + 3))
                cell_Z = sheetT.cell(row=(cell.row + 3), column=(cell.column + 3))
                cell_Len = sheetT.cell(row=(cell.row + 4), column=(cell.column + 3))
                cell_WID = sheetT.cell(row=(cell.row + 5), column=(cell.column + 3))
                cell_len_tem = sheetT.cell(row=(cell.row + 4), column=cell.column + 1)
                cell_wid_tem = sheetT.cell(row=(cell.row + 5), column=cell.column + 1)
                data_cps_h32 = [
                    ["Y", cell_Y.value],
                    ["Z", cell_Z.value],
                    [cell_len_tem.value, cell_Len.value],
                    [cell_wid_tem.value, cell_WID.value],
                ]

            if cell.value == "POI_TP1":
                print(cell.row, cell.column)
                cell_X = sheetT.cell(row=(cell.row + 2), column=(cell.column + 2))
                data_poi_tp1 = [
                    ["X", cell_X.value]
                ]

    data_re = [data_cps_h31, data_cps_h32, data_poi_tp1]
    return data_re

思路就是获取到表格中所有内容，也就是获取到所有有数据格子中的数据，然后通过if判断找到所需要的数据。
如下：

    sheet_size = sheetT.dimensions
    #  获取工作表内容
    cells = sheetT[sheet_size]

   # cell_row_tuple是每一行为1个元组
    for cell_row_tuple in cells:
        # cell是每一行元组中的每一个小格子
        for cell in cell_row_tuple:
            # print(cell.value)
            if cell.value == "CPS_H31":
             .......

通过这个函数就可以获得所有需要的数据了。有了数据就可以往新表中添加数据了。添加数据函数如下。

def append_rows(data_result):
    workbook = load_workbook(filename="analyzedata.xlsx")
    sheet = workbook["Result"]
    for row in data_result:
        sheet.append(row)

    workbook.save(filename="analyzedata.xlsx")

这个比较简单就不多说了。对于基础有疑问的可以访问我的简书基础部分。运行代码如下：

• 图4.png

这些数据已经很接近我们结果了，通过分析，我们可以通过插入一列后添加数据达到我们结果。代码如下：

def insert_cols_data():
    workbook = load_workbook(filename="analyzedata.xlsx")
    sheet = workbook["Result"]
    sheet.insert_cols(idx=1)
    cellOne = sheet["A1"]
    cellOne.value = "H31"
    cellTwo = sheet["A5"]
    cellTwo.value = "H32"
    cellThree = sheet["A9"]
    cellThree.value = "H31"
    workbook.save(filename="analyzedata.xlsx")

调用这些函数，然后运行，得到结果：

- 图5.png

注意一个很多人可能遇到的bug，如下图

• 图6.png

这个bug是由于我们ExcelB表格被打开了，没有关闭导致的。如下图

• 图7.png

把表格关闭，运行代码就恢复正常了。本次分享已经完成。

源码地址：关注微信公众号“码农不头秃”后回复“Excel表格处理”将会有源码地址发给您。
对Python感兴趣的朋友可以关注我的简书和公众号。需要Python或者爬虫电子书的朋友们关注微信公众号后台回复“python电子书”。