1200字范文 > 提取指定的PDF表格保存到Excel

提取指定的PDF表格保存到Excel

时间：2019-10-26 02:14:56

相关推荐

提取指定的PDF表格保存到Excel

摘要：本文介绍一个提取PDF中的表格内容的程序。首先，程序给出使用示例，最后给出代码开发思路及细节。

0.需求说明

PDF中存在大量表格，需要从表格中提取出指定类型的表格，这些表格主要通过表头和表中的关键字来确定。

1.PDF示例

样例PDF下载地址：样本一、样本二、样本三

2.提取规则

提取规则通过Excel指定，如下示例：

3.提取结果示例

提取的结果保存在Excel中，结果如下：

4.使用方法

首先准备好Demo.xlsx文件（下载），同时下载PDFparser.exe程序（下载），将二者放在同一个目录下，然后将PDF文件准备好放在任意文件夹xxx中，将xxx文件夹和以上两个文件放在同一目录下，双击运行程序即可。

5.代码说明

程序使用pdfplumber模块进行PDF解析以获取表格和文本程序使用xlwt模块和xlrd模块进行Excel的读写程序使用多进程+多线程模式加快速度程序使用re模块来使用Python正则表达式

6.代码细节

PDF解析

# 该类用来实现PDF表格和文字内容的提取class Extractor(object):def __init__(self, file_path, rules):''':param file_path:PDF file path:param rules: extract rules'''self.file_path = file_pathself.rules = rules

# 加载PDF文件def parse_pages(self):try:pages = []pdf = pdfplumber.open(self.file_path)print('parse file:{} page num:{}'.format(os.path.basename(self.file_path), len(pdf.pages)))for index, page in enumerate(pdf.pages):tables = page.extract_tables()if len(tables) < 1:continuepages.append({'text': page.extract_text(), 'tables': tables, 'page': index + 1})return pagesexcept Exception as e:print(e)return None# 提取特定类型表头的表格，规则有rules参数指定def extract_table_with_specific_header(self, pages):if pages is None or len(pages) < 1:# print('no-page...')returntarget_tables = []# 遍历所有页面for index, page in enumerate(pages):text = page['text']tables = page['tables']page_id = page['page'] - 1lines = re.split(r'\n+', text)# 遍历当前页面的所有行for ind, line in enumerate(lines):# 判定表头符合规则的表格if sum([1 for rule in self.rules['in-header'] if re.search(rule, line)]) > 0 and \sum([1 for rule in self.rules['not-in-header'] if re.search(rule, line)]) == 0:if ind >= len(lines) - 1:breakcnt = ind + 1next = lines[ind + 1]if ind < len(lines) - 2 and re.search(r'单位[：:]', next):next = lines[ind + 2]cnt += 1for ti, table in enumerate(tables):if not table:continuefirst = ''.join([word for word in table[0] if word is not None])# 表格是完整的情况if first == re.sub(r'\s+', '', next):tables[ti] = Falseif index + 1 < len(pages) and len(pages[index + 1]['tables']) > 0:table_next = pages[index + 1]['tables'][0]fi = ''.join([item for item in table_next[0] if item is not None])if re.sub(r'\s+', '', fi) in re.sub(r'\s+', '', pages[index + 1]['text'][1]):table += table_nexttarget_tables.append({'page': page_id + 1, 'method': 'exact', 'table': table,'table-id': str(page_id + 1) + str(ti + 1)})# 表格可能不完整的情况elif first in re.sub(r'\s+', '', next):tables[ti] = Falseif index + 1 < len(pages) and len(pages[index + 1]['tables']) > 0:table_next = pages[index + 1]['tables'][0]fi = ''.join([item for item in table_next[0] if item is not None])if re.sub(r'\s+', '', fi) in re.sub(r'\s+', '', pages[index + 1]['text'][1]):table += table_nexttarget_tables.append({'page': page_id + 1, 'method': 'guess', 'table': table,'table-id': str(page_id + 1) + '-' + str(ti + 1)})return target_tables# 提取表格中存在指定类型信息的表格，规则由参数rules指定def extract_table_with_specific_info(self, pages):if pages is None or len(pages) < 1:# print('no-page...')returntarget_tables = []for index, page in enumerate(pages):tables = page['tables']page_id = page['page'] - 1for ti, table in enumerate(tables):st = str(table)if len(self.rules['in-table']) == sum([1 for in_tab in self.rules['in-table'] if in_tab in st]):if len(self.rules['not-in-table']) == sum([1 for not_tab in self.rules['not-in-table'] if not not_tab in st]):target_tables.append({'page': page_id + 1, 'method': 'content-in-table', 'table': table,'table-id': str(page_id + 1) + str(ti + 1)})return target_tables# 提取存在指定关键词的页面，关键词有rules指定def extract_specific_page(self, pages):if pages is None or len(pages) < 1:# print('no-page...')returntarget_pages = []for index, page in enumerate(pages):text = page['text']page_id = page['page']if len(self.rules['in-page']) == sum([1 for rule in self.rules['in-page'] if re.search(rule, text)]):target_pages.append({'page': page_id, 'text': text})return target_pages# 执行以上所有过程，返回提取结果def run(self):pages = self.parse_pages()if pages is None or len(pages) < 1:print('parse pdf error:', os.path.basename(self.file_path))returntry:target_1 = self.extract_table_with_specific_header(pages)target_2 = self.extract_table_with_specific_info(pages)target_3 = self.extract_specific_page(pages)tables = []s = []for table in target_1:if table['table-id'] not in s:s.append(table['table-id'])tables.append(table)for table in target_2:if table['table-id'] not in s:s.append(table['table-id'])tables.append(table)return tables, target_3except Exception as e:print(e)

加载Excel和缓存结果

# 该类用来加载Excel，遍历地址获取PDF文件路径及缓存结果class Util():def init(self, folder, out, demo):self.folder = folderself.out = outself.demo = demo

# 加载Demo文件，获取rulesdef load_demo(self):print('load demo Excel:', os.path.basename(self.demo))book = xlrd.open_workbook(self.demo)sheet = book.sheet_by_index(0)in_header = sheet.col_values(0, 2, sheet.nrows)not_in_header = sheet.col_values(1, 2, sheet.nrows)in_table = sheet.col_values(2, 2, sheet.nrows)not_in_table = sheet.col_values(3, 2, sheet.nrows)in_page = sheet.col_values(4, 2, sheet.nrows)rules = {'in-header': in_header, 'not-in-header': not_in_header, 'in-table': in_table,'not-in-table': not_in_table, 'in-page': in_page}for k, v in rules.items():rules[k] = [i for i in v if not re.sub(r'\s+', '', i) == '']return rules# 加载PDF文件，采用迭代遍历def load_folder(self):print('load folder:', self.folder)paths = []for dirpath, dirnames, filenames in os.walk(self.folder):for file in filenames:path = os.path.join(dirpath, file)if os.path.isfile(path) and (os.path.splitext(path)[1] == '.pdf' or os.path.splitext(path)[1] == '.PDF'):paths.append(path)return paths# 缓存结果def save_tmp(self, info, name, code, year):print('save tmp file:', name)if not os.path.isdir('tmp'):os.mkdir('tmp')tables = info[0]pages = info[1]# Excel样式style = xlwt.XFStyle()# background colorpattern = xlwt.Pattern()pattern.pattern = xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour = 5style.pattern = pattern# borderborders = xlwt.Borders()borders.left = xlwt.Borders.THICKborders.right = xlwt.Borders.THICKborders.top = xlwt.Borders.THICKborders.bottom = xlwt.Borders.THICKstyle.borders = borders# fontfont = xlwt.Font()font.name = 'Times New Roman'font.bold = Truefont.underline = Falsefont.italic = Falsestyle.font = font# sheet style-2style2 = xlwt.XFStyle()# background colorpattern = xlwt.Pattern()pattern.pattern = xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour = 22style2.pattern = pattern# borderborders = xlwt.Borders()borders.left = xlwt.Borders.THICKborders.right = xlwt.Borders.THICKborders.top = xlwt.Borders.THICKborders.bottom = xlwt.Borders.THICKstyle2.borders = borders# fontfont = xlwt.Font()font.name = 'Times New Roman'font.bold = Truefont.underline = Falsefont.italic = Falsestyle2.font = font# sheet style-3style3 = xlwt.XFStyle()# background colorpattern2 = xlwt.Pattern()pattern2.pattern = xlwt.Pattern.SOLID_PATTERNpattern2.pattern_fore_colour = 3style3.pattern = pattern2# borderborders = xlwt.Borders()borders.left = xlwt.Borders.THICKborders.right = xlwt.Borders.THICKborders.top = xlwt.Borders.THICKborders.bottom = xlwt.Borders.THICKstyle3.borders = borders# fontfont = xlwt.Font()font.name = 'Times New Roman'font.bold = Truefont.underline = Falsefont.italic = Falsestyle3.font = font# 将数据写如Excelbook = xlwt.Workbook()sheet1 = book.add_sheet('tables')sheet2 = book.add_sheet('pages')for ind, page in enumerate(pages):page_num = page['page']text = page['text']sheet2.write(ind, 0, name)sheet2.write(ind, 1, code)sheet2.write(ind, 2, year)sheet2.write(ind, 3, page_num)sheet2.write(ind, 4, 'search page')sheet2.write(ind, 5, text, style)# save tablei = 0for ti, table in enumerate(tables):page = table['page']method = table['method']table_content = table['table']if method == 'exact':sty = styleelif method == 'guess':sty = style2else:sty = style3for index, row in enumerate(table_content):sheet1.write(i, 0, name)sheet1.write(i, 1, code)sheet1.write(i, 2, year)sheet1.write(i, 3, page)sheet1.write(i, 4, method)for ind, one in enumerate(row):sheet1.write(i, 5 + ind, one if one is not None else '', sty)i += 1i += 1book.save('tmp\\' + name + '.tmp.xls')

执行提取的函数

# 单个文件运行的完整流程，从加载文件到缓存结果的全过程，如果只想使用单线程运行程序，则在主函数中调用该函数即可def run(rules, file, util):extractor = Extractor(file, rules)info = extractor.run()code = re.findall(r’\d{6}’, os.path.basename(file))[0]year = re.findall(r’\d{8}’, os.path.basename(file))[0]if info is None or len(info[0]) < 1:with open(‘noResult.txt’, ‘a’, encoding=‘utf-8’) as fp:fp.write(file + ‘\n’)else:util.save_tmp(info, os.path.basename(file), code, year)

多进程+多线程

# -------------------# 以下两个函数是为了加快执行速度而启用的多线程+多进程模式，计算密集型任务状态下进程越多越好（不多于机器CPU核心数）# -----------------# 多线程：每次会启动跟files数量相对应的线程来执行，但只能执行在一个CPU核心中# multiple threadsdef batch_processor(func, rules, files, util):thread_pool = []for index, file in enumerate(files):th = threading.Thread(target=func, args=(rules, file, util))# print(‘running thread:’, th.name)th.start()thread_pool.append(th)for th in thread_pool:# print(‘waiting for thread:’, th.name)th.join()

# 多进程：启动4个进程执行，每个进程中运行多线程，CPU有几个核心就使用几个进程，一般机器多为双核心四进程，此时4进程可占满CPU运行，效能最大

# multiple processors

def multi_processor_run(func, sub_func, files, rules, util):

pool = multiprocessing.Pool(processes=4)

cnt = 0

batch_size = 5

while cnt < len(files):

rear = cnt + batch_size

if rear > len(files):

rear = len(files)

batch = files[cnt + 0:rear]

pool.apply_async(func, (sub_func, rules, batch, util))

cnt += batch_size

pool.close()

pool.join()

整理结果并保存

# 该函数将缓存在本地目录tmp文件夹下的所有临时Excel文件结果整合到一个Excel中# re-format resultdef re_format(sheet_size):print(‘re-format file…’)files = os.listdir(‘tmp’)paths = []new_book = xlwt.Workbook()for file in files:if os.path.isfile(os.path.join(‘tmp’, file)) and ‘.tmp.xls’ in file:paths.append(os.path.join(‘tmp’, file))

style = xlwt.XFStyle()# background colorpattern = xlwt.Pattern()pattern.pattern = xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour = 5style.pattern = pattern# borderborders = xlwt.Borders()borders.left = xlwt.Borders.THICKborders.right = xlwt.Borders.THICKborders.top = xlwt.Borders.THICKborders.bottom = xlwt.Borders.THICKstyle.borders = borders# fontfont = xlwt.Font()font.name = 'Times New Roman'font.bold = Truefont.underline = Falsefont.italic = Falsestyle.font = font# sheet style-2style2 = xlwt.XFStyle()# background colorpattern = xlwt.Pattern()pattern.pattern = xlwt.Pattern.SOLID_PATTERNpattern.pattern_fore_colour = 22style2.pattern = pattern# borderborders = xlwt.Borders()borders.left = xlwt.Borders.THICKborders.right = xlwt.Borders.THICKborders.top = xlwt.Borders.THICKborders.bottom = xlwt.Borders.THICKstyle2.borders = borders# fontfont = xlwt.Font()font.name = 'Times New Roman'font.bold = Truefont.underline = Falsefont.italic = Falsestyle2.font = font# sheet style-3style3 = xlwt.XFStyle()# background colorpattern2 = xlwt.Pattern()pattern2.pattern = xlwt.Pattern.SOLID_PATTERNpattern2.pattern_fore_colour = 3style3.pattern = pattern2# borderborders = xlwt.Borders()borders.left = xlwt.Borders.THICKborders.right = xlwt.Borders.THICKborders.top = xlwt.Borders.THICKborders.bottom = xlwt.Borders.THICKstyle3.borders = borders# fontfont = xlwt.Font()font.name = 'Times New Roman'font.bold = Truefont.underline = Falsefont.italic = Falsestyle3.font = fonttab_cnt = 1page_cnt = 1tab_rows = 0page_rows = 0sheet2 = new_book.add_sheet('pages-' + str(0))sheet1 = new_book.add_sheet('tables-' + str(0))sheet1.write(0, 0, 'File')sheet1.write(0, 1, 'Code')sheet1.write(0, 2, 'Date')sheet1.write(0, 3, 'Page')sheet1.write(0, 4, 'Method')sheet2.write(0, 0, 'File')sheet2.write(0, 1, 'Code')sheet2.write(0, 2, 'Date')sheet2.write(0, 3, 'Page')sheet2.write(0, 4, 'Method')for index, file in enumerate(paths):book = xlrd.open_workbook(file)sheet = book.sheet_by_name('tables')sheet_pages = book.sheet_by_name('pages')tab_rows += sheet.nrowspage_rows += sheet_pages.nrowsfor row in range(sheet.nrows):if len(sheet.row_values(row)) < 5:sty1 = Noneelif sheet.row_values(row)[4] == 'exact':sty1 = styleelif sheet.row_values(row)[4] == 'guess':sty1 = style2elif sheet.row_values(row)[4] == 'content-in-table':sty1 = style3else:sty1 = Nonefor col, val in enumerate(sheet.row_values(row)):if col > 4:if sty1 is not None:sheet1.write(tab_cnt, col, val, sty1)else:sheet1.write(tab_cnt, col, val)else:sheet1.write(tab_cnt, col, val)tab_cnt += 1tab_cnt += 1for row in range(sheet_pages.nrows):if len(sheet_pages.row_values(row)) < 5:sty2 = Noneelif sheet_pages.row_values(row)[4] == 'exact':sty2 = styleelif sheet_pages.row_values(row)[4] == 'guess':sty2 = style2elif sheet_pages.row_values(row)[4] == 'content-in-table':sty2 = style3else:sty2 = Nonefor col, val in enumerate(sheet_pages.row_values(row)):if col > 4:if sty2 is not None:sheet2.write(page_cnt, col, val, sty2)else:sheet2.write(page_cnt, col, val)else:sheet2.write(page_cnt, col, val)page_cnt += 1page_cnt += 1if tab_rows >= sheet_size:tab_rows = 0tab_cnt = 1sheet1 = new_book.add_sheet('tables-' + str(index))sheet1.write(0, 0, 'File')sheet1.write(0, 1, 'Code')sheet1.write(0, 2, 'Date')sheet1.write(0, 3, 'Page')sheet1.write(0, 4, 'Method')if page_rows >= sheet_size:page_rows = 0page_cnt = 1sheet2 = new_book.add_sheet('pages-' + str(index))sheet2.write(0, 0, 'File')sheet2.write(0, 1, 'Code')sheet2.write(0, 2, 'Date')sheet2.write(0, 3, 'Page')sheet2.write(0, 4, 'Method')new_book.save('tables.xls')

函数入口

# 程序执行入口：主函数if name == ‘main’:# 此命令是为在Windows环境下打包exe时正确引入多进程模块而添加的，在Python解释器中运行代码这一行是不必要的，当然添加之后也无妨multiprocessing.freeze_support()# 程序运行需要的参数# parasbase_dir = r’./’ # 程序工作目录设定为本程序所在的目录out_path = base_dir + r’\result.xls’ # 输出结果文件名称demo = base_dir + r’\Demo.xlsx’ # Demo文件名称

# 新建noResult.txt文件，用来保存没有结果的PDF文件名称with open('noResult.txt', 'w', encoding='utf-8')as fp:fp.write(str(datetime.now()) + '\n')# 初始化Util类util = Util(base_dir + '\\test', out_path, demo)rules = util.load_demo()folder = util.load_folder()# 执行多进程，，但仅执行单线程模式时这里可替换为run函数multi_processor_run(batch_processor, run, folder, rules, util)# 保存结果：5000代表每个Excel的单个sheet最多5000行，超过则会新建sheet# save...re_format(5000)# 移除临时文件，这些临时文件在程序运行过程中会保存在当前目录的tmp文件夹内，其中每个Excel文件保存的是单个PDF文件的结果，最终这些结果将会通过re_format函数整合到一个Excel中，当想要保留这些结果时，可将下面一行代码注释掉shutil.rmtree('tmp')