对 Excel 文件中数据进行验证,并将结果写入行末,将错误的单元格填充颜色

在东航项目中,有个需求是从 FTP 中对应的供应商文件夹中,下载账单 Excel 文件到本地(本地也会按照 FTP 中文件对应的位置,对应账单下载到本地中对应的供应商文件夹中,供应商文件夹中,又有以日期命名的子文件夹。既每天的账单文件下载到当天日期的文件夹中)。再从本地中读取 Excel,并对 Excel 文件中数据进行验证,并将结果写入行末,(若该行出现多个错误,须将多个不通过的原因以换行的形式写在同一个单元格中),将错误的单元格填充颜色。

拿到需求后,实现起来还是挺快的。划分的步骤为:
1. 登录 FTP,下载当天的账单;
2. 读取 Excel,验证账单,并写入。

对于第 2 步骤,开始的时候对于验证的思路为:
1. 读取给定路径下,所有子文件夹中的账单文件;
2. 循环读取账单文件,并转换为 key-values 的格式。以第一行的表头作为 key,下面每一行的数据作为对应属性的值。再将得到的字典加入到列表中,这样就能使的验证会从第二行开始。并对每一列进行验证,若不通过,则打开这份账单,写入不通过原因,填充该个单元格为黄色。

写完后,在执行的时候,程序是能解决需求,但是会发现验证一份账单所需的时间很长。一个完整的程序所执行的时间的长度会随着账单文件的增多而变长。若一份账单中,错误数据较多,执行时间就会更长。对于客户来说,甚至流程的设计者来说,这样的设计是无法通过的。于是有了下面的优化:

优化后思路:
1. 在验证的时候记录不通过单元格的所属行及所属列,所属行及不通过原因,并将它们分别加入到两个列表;
部分代码如下:

	log_list = []                                              # 记录需要填充单元格的信息
        log_all = []                                              # 记录需要写入字段的信息
	a = flightlist.index(i) + 1                               # a是记录进入判断时,该数据的行数
        if i['费用发生机场三字码'] not in lv_thnumber:             # lv_thnumber 是一个从机场三字码文件中生成的列表
             print('第%s行,第A列 ' % a + '费用发生机场中,机场三字码有误', file=doc)
             log = "第A列,费用发生机场中,机场三字码有误"
             log_list.append([a, "A"])                          # 此处固定了判断的列,因而知道是A列
             log_all.append({a: [log]})

如上,将验证不通过单元格的 行数与列数,以列表加入到了 log_list 的列表中;行数与验证不通过原因以字典加入 log_all 列表, 且 values 是一个小列表。这样会将一份账单所有的不通过项的属性加入到列表中,在验证完一份账单后,我们再打开这份账单,循环 log_list 与 log_all 列表,执行后续的任务, 部分代码如下:

  if log_all == []:
      pass
  else:
      new_log = []
      for i in log_all:
          for key in i.keys():
              new_log.append(i[key][0])
      f_log = '\n'.join(new_log)
      # print("行数为:", key, "错误为:", f_log)
      iexcel.write_cell(path=file,text=f_log,cell='P%s'%key)

      for j in log_list:
          xw.Range('%s%s' % (j[1], j[0])).color = (255, 255, 0)
		  
      iexcel.write_cell(path=file, text=" ", cell='Q1' )             # 点睛之笔

这里,在写入不通过原因,我用了设计器的方法,从 log_all 列表中取行数与写入的字段作为参数传入方法(写的列固定),实现了写的功能。
在填充颜色,我导入了 xlwings 库,因为目前 Python 支持给 xls 与 xlsx 格式文件填充的库较少,大多仅支持 xls 格式的文件。在使用 xlwing 这个库的时候,由于并不太熟悉,查找资料,它确实有解决给 Excel 填充颜色的这个功能,方法如下:

app = xw.App(visible=True, add_book=False)
app.display_alerts = False
app.screen_updating = False
wb = app.books.open(file)
xw.Range('%s%s' % (col, str(n))).color = (255, 255, 0)
wb.save()
wb.close()
app.quit()

于是,将该方法封装成一个函数,将行数与列数作为参数,后面再循环 log_list 列表的时候,将值传入。就实现了填充的功能。but,从该方法中也可以看出,它会打开与关闭 Excel,因而在执行时,循环一遍,还是会出现一次打开与关闭 Excel。这样的话,在填充这一需求,还是没有达到时间的优化。(辛苦弄了半天,还在原地,受不了,继续)然后查看了设计器对于 Excel 这一功能模块封装的代码,发现设计器中对于 Excel 已经导了 xlwings 库,于是我去掉了这个库在处理填充的大部分代码,只留下了 xw.Range(‘%s%s’ % (col, str(n))).color = (255, 255, 0) ,但是这样填充后不会被保存。会出现总有最后一个填充没有执行,准确的说是最后一个填充每被保存。前面的填充能被保存是因为两次的写入,将中间的这个填充给一起保存了。于是,在代码的最后,我加了一段 iexcel.write_cell(path=file, text=" ", cell=‘Q1’),在无关的位置写了一个空白的字符串,在保存的时候,就将最后的一个填充颜色给保存了。

😄 😄 😄文字太多,估计也看不下去了,还的理清思路,下面直接是总结:

  1. 在处理类似的验证,对比,需要将对比结果或验证结果写入 Excel 时,可以将 错误的行数与列数以及要写入的字段 先放到列表,等一份文件结束后再打开做写的操作;
  2. 在对一个 Excel 文件做多个填充颜色时,(可通过设计器中写的方法打开文件)若考虑 xls 与 xlsx 格式混用,也可将 要填充的行数与列数 放到列表,后循环作为参数传入 xw.Range(‘%s%s’ % (col, str(n))).color = (255, 255, 0),再通过设计器的 iexcel.write_cell(path=file, text=" ", cell=‘不影响数据的单元格’ )写入一个空白字符串,以保存给单元格填充这一功能。

优势:大幅度的减少执行的时间,不影响文件本身的格式。对同一个 Excel 进行多次的写与填充颜色,不需要频繁的打开与关闭文件。(之前一份账单需要 10 分钟左右,配置低的电脑,执行时间更长。现在执行一份账单仅需 30 秒左右。)