使用openpyxl批量提取Excel单元格内容

背景

今天晚上收到了朋友发的微信，他手里有一份医院提供的PDF版员工体检报告，每个员工一页表格，一共700多页。他的主管要求他把PDF中的每位员工的姓名、身份证号、部门、体检结果汇总成一个excel表格。

我粗略的看了一下PDF文件，每一页都是一个固定的表格，且PDF中是嵌入的文字而非栅格化后的图片。或许操作起来还是很简单的。

折腾

处理PDF

PDF这种让你好看好打印的文件格式，虽然可能用ReportLab模块也能处理，但是我也就用过它生成PDF。总体考虑下来，还是转成我稍微熟悉点的Excel再处理吧。

PDF转Excel这一部分我用的是PDF-XChange Editor。当然，可能有其他免费或者合乎隐私要求的工具，但我电脑上目前也只有这个。

为了后续更方便的处理，在转换时，设置了“整个文档输出为一个工作表”和“将每段设置为工作表的一行”。

处理Excel

打开转换后的Excel表，发现第一个姓名在C3和C4这个合并单元格中，第二个姓名出现在了C23和C24这个合并单元格中。那么显然，后续只要沿着这个公差20的等差数列找下去就可以了

以此类推，找到了所有需要的信息。

下面使用openpyxl处理这个Excel

import openpyxl

workbook = openpyxl.load_workbook('体检表.xlsx')
sheet = workbook['Sheet1']

i = 0
while i < 800:
    # 姓名
    a = 'C' + str(3 + 20 * i)
    print(str(sheet[a].value))
    i = i + 1

这里需要提到一个坑，PDF-XChange Editor转换得到的Excel表格内容位置是相对稳定的，但是某些单元格文字的数量变化会影响其他信息所在的单元格
就我的Excel举例：姓名小于4个字的，部门在G2:J2这个合并单元格中，但当姓名为4个字时，部门在‘E2:J2’这个合并单元格。
这里为了防止拿不到信息或者拿到的信息不全，最简单粗暴的方法是获取所有有可能有数据的单元格的内容，后期再把空的删掉。

def get_value(location):
    return str(sheet[location].value) + ';'

i = 0
while i < 800:
        # 姓名
        a = 'C' + str(3 + 20 * i)
        b = 'D' + str(3 + 20 * i)
        # 身份证号
        c = 'C' + str(4 + 20 * i)
        d = 'D' + str(4 + 20 * i)
        # 工号
        e = 'K' + str(4 + 20 * i)
        f = 'L' + str(4 + 20 * i)
        # 部门
        g = 'E' + str(2 + 20 * i)
        h = 'F' + str(2 + 20 * i)
        j = 'G' + str(2 + 20 * i)
        k = 'H' + str(2 + 20 * i)
        print(get_value(a) + get_value(b) + get_value(c) + get_value(d) + get_value(e) + get_value(f) + get_value(g) + get_value(h) + get_value(j) + get_value(k))

这样输出的数据可以保证每一行都有相同数量的部分，并且中间以;隔开，可以很方便的导入Excel删除无用空值。

输出没问题的话，使用with open保存的txt

with open('output.txt', 'a') as file:
	file.write(get_value(a) + get_value(b) + get_value(c) + get_value(d) + get_value(e) + get_value(f) + get_value(g) + get_value(h) + get_value(j) + get_value(k) + '\n')

最后只要把txt导入Excel表，删除None值就可以了~

后续的优化

g = 'E' + str(2 + 20 * i)
h = 'F' + str(2 + 20 * i)
j = 'G' + str(2 + 20 * i)
k = 'H' + str(2 + 20 * i)

这样的写法还是不够美观的，下面说明一种写法
事实上，如果要获取C3:G3这些单元格中的内容，最先想到的应该是

cell_range = sheet['C3:G3']

检查它的类型，是tuple。那么我们可以使用下面的代码获取这个tuple中有什么

print(cell_range[0])

结果是

(<Cell 'Sheet1'.C3>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.E3>, <Cell 'Sheet1'.F3>, <Cell 'Sheet1'.G3>)

从而，我们可以使用循环得到C3:G3中的内容

for i in cell_range[0]:
    print(i.value)

所以代码可以修改为

def get_value(location):
    cell_value = ''
    for cell in sheet[location][0]:
        cell_value = str(cell.value) + ';'
		return cell_value

i = 0
with open('output.txt', 'a') as file:
    while i < 800:
        # 姓名
        a = 'C' + str(3 + 20 * i) + ':' + 'D' + str(3 + 20 * i)
        # 身份证号
        b = 'C' + str(4 + 20 * i) + ':' + 'D' + str(4 + 20 * i)
        # 工号
        c = 'K' + str(4 + 20 * i) + ':' + 'L' + str(4 + 20 * i)
        # 部门
        d = 'E' + str(2 + 20 * i) + ':' + 'H' + str(2 + 20 * i)
        file.write(get_value(a) + get_value(b) + get_value(c) + get_value(d) + get_value(e) + '\n')
        i = i + 1

同理，对于二元表格，可以使用

def get_value(location):
	for row in sheet[location]:
		for cell in row:
			cell_value = str(cell.value) + ';'
			return cell_value