Excel、PDF数据处理

Excel、PDF数据处理

安装对应的包

处理Excel数据，需要先安装第三方xlrd/x、xlwt包，通常我们用pip安装，在当前程序环境下执行命令：

pip install xlrd
pip install xlwt

通过正则匹配excel对应列或者行元素值，将对应列行的数据匹配成功后插入对应的excel表的行列中去，整体代码如下：

# coding=utf-8
import re
from xlwt import Workbook
import xlrd
import sys
import os


def excel_process(path):
    _data = xlrd.open_workbook(path)
    table = _data.sheet_by_index(0)
    # 依据下标获取特定sheet表 table.nrows # 获取该sheet表行数 table.ncols获取该sheet表列
    ncols = table.ncols  # 获取列表的有效列数
    print ncols
    # print table.cell(1, 5).value
    book = Workbook(encoding='utf-8')
    sheet = book.add_sheet('Sheet1')  # 创建一个sheet
    for i in range(table.nrows):
        # k = list(table.row_values(1,1))  # 获取行数的数据
        # res = str(table.cell(i, 5).value)
        res = table.cell_value(i, 5)
        res2 = table.cell_value(i, 6)
        # res = table.row(i)[5].value
        if res:
            if type(res) == float:
                res = int(res)
                res = unicode(res)
            result = re.findall("1[0-9]{10}", res)
            result = ''.join(result)
        else:
            result = ''
        if res2:
            if type(res2) == float:
                res2 = int(res2)
                res2 = unicode(res2)
            result2 = re.findall("1[0-9]{10}", res2)
            result2 = ''.join(result2)
        else:
            result2 = ''
        print i,result
        if i:
            sheet.write(i, 1, label=result)  # 向第1行第1列写入获取到的值
            sheet.write(i, 2, label=result2)  # 向第1行第1列写入获取到的值
    book.save("20.xls")
    return "ok"

if __name__ == '__main__':
    path = "2020.xlsx"
    excel_process(path)

python中正则表达式的使用(提取Excel内容)：https://www.runoob.com/python/python-reg-expressions.html

读取PDF文件转为txt文件，首先需安装处理PDF的第三方包 1 pip install pdfminer/ 2 pip install pdfminer3k/ 3 pip install pdfminer.six(推荐)，具体代码如下：

# coding=utf-8
import os, re
from pdfminer.pdfinterp import PDFResourceManager, PDFPageInterpreter
from pdfminer.pdfpage import PDFPage
from pdfminer.converter import TextConverter
from pdfminer.layout import LAParams


# 将一个PDF转换成txt文件
def pdfTotxt(filepath, outpath):
    try:
        fp = open(filepath, 'rb')  # 读数据
        outfp = open(outpath, 'w')  # 写数据
        # 创建一个PDF资源管理器对象来存储共享资源,caching = False不缓存
        rsrcmgr = PDFResourceManager(caching=False)
        # 创建PDF设备对象
        laparams = LAParams()
        device = TextConverter(rsrcmgr, outfp, codec='utf-8', laparams=laparams, imagewriter=None)
        # 创建PDF解析器对象
        interpreter = PDFPageInterpreter(rsrcmgr, device)
        for page in PDFPage.get_pages(fp, pagenos=set(), maxpages=0,
                                      password='', caching=False, check_extractable=True):
            page.rotate = page.rotate % 360
            interpreter.process_page(page)
        # 关闭输入流
        fp.close()
        # 关闭输出流
        device.close()
        outfp.flush()
        outfp.close()
    except Exception as e:
        print("Exception:%s", e)


# 一个文件夹下的所有pdf文档转换成txt
def fileTotxt(fileDir):
    files = os.listdir(fileDir)
    tarDir = fileDir + 'txt'
    if not os.path.exists(tarDir):
        os.mkdir(tarDir)
    replace = re.compile(r'\.pdf', re.I)
    for file in files:
        filePath = fileDir + '\\' + file
        outPath = tarDir + '\\' + re.sub(replace, '', file) + '.txt'
        pdfTotxt(filePath, outPath)
        print("Saved " + outPath)


if __name__ == '__main__':
    pdfTotxt(u'212.pdf', 'test.txt')
    # fileTotxt('path')  # PDF文件夹路径

批量将数据库查询结果集导出excel

写入方法，使用调用即可

import openpyxl as xl
import os
import time
def write_excel_file(data, f_name, folder_path='F:\\cxhf\\XCHF\\insurance\\app\\api'):
    result_path = os.path.join(folder_path, f_name)
    # print('***** 开始写入excel文件 ' + result_path + ' ***** \n')
    if os.path.exists(result_path):
        #   print('***** excel已存在，在表后添加数据 ' + result_path + ' ***** \n')
        workbook = xl.load_workbook(result_path)
        sheet = workbook.active
    else:
        #  print('***** excel不存在，创建excel ' + result_path + ' ***** \n')
        workbook = xl.Workbook()
        workbook.save(result_path)
        sheet = workbook.active
        headers = ["姓名__", "手机号__", "__", "__", "__", "__", "__",
                   "__", "__", "__", "__"]   #设置文件列
        sheet.append(headers)
    sheet.append(data)
    workbook.save(result_path)
    return result_path


file_name = str(int(time.time() * 1000)) + '.xlsx'
para = "select * from table " # 数据库查询数据集
write_excel_file(para, file_name, folder_path='/root/temp')
url = 'https://xxxxxxxx.oss-cn-hangzhou.aliyuncs.com/' + \
                  upload_img.award_file('/root/temp' + '/' + file_name, file_name) # 返回上传至oss存储地址供下载