【python】自动化办公之excel入GP库实战详解(xlwings+psycopg2)

本文主要是介绍【python】自动化办公之excel入GP库实战详解(xlwings+psycopg2)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

python已经不仅是程序员学习使用的工具了，它已经深深融入我们日常工作办公中。本人在工作中每月都会收集大量excel表格文件，做相关汇总处理，还要把清单数据导入数据库中。手动完成几乎浪费了我大半天的时间，作为一个拥有编程思想追求进步的程序媛怎可如此？能用脚本实现的东西决不能重复操作。

本篇结合实际工作内容，主要使用xlwings和psycopg2包，实现excel表格数据导入GP数据库功能。

介绍

py版本：python 3.7

结果表结构：
excel内容：

思路

本篇主要通过xlwings包实现读取excel数据，通过psycopg2包实现数据入库。方法如下：

get_row_col(fileName, sheetName)：获取excel某sheet页有数据的最大行数和列数。参数分别文件路径和sheet页名称。
get_date():批量获取数据，无需值处理。适合数据完整、值中无特殊字符，可直接入库的场景。返回数据为list类型。
trans_data():批量获取数据，并且对值做制表符替换等处理。本篇对指标附和’|| '(||+TAB符号)进行处理，在调试阶段没做处理会出现报错情况。返回数据为list类型。
executemany(data):executemany()方法批量执行sql实现数据入库,data为list类型。此方法需要定义insert语句，优势是操作字段更灵活，缺点是如果操作数据量达到上千条就会十分卡慢。
copy_from(data):copy_from()方法数据入库,data为list类型。直接调用pgload，适合数据量大的入库操作。

最终可根据实际需求组合实现入库，本人采用trans_data+copy_from方法。

脚本

#!/usr/bin/python
#coding=utf-8
import os,reos.chdir(r'D:\summer\svn')
path = os.getcwd()
import xlwings as xw
import psycopg2
from io import StringIO
import pandas as pddef get_row_col(fileName, sheetName):"获取sheet页有数据的最大行数和列数"wb = xw.Book(fileName)ws = wb.sheets(sheetName)info = ws.used_rangenrows = info.last_cell.rowncols = info.last_cell.columnreturn nrows, ncolsdef get_date():"批量获取数据，无需值处理"fileName = path + '\\文档名称.xlsx'sheetName = '清单'row_col = get_row_col(fileName, sheetName)wb_pro = xw.Book(fileName)ws_pro = wb_pro.sheets(sheetName)#数据从A2开始，J列取最大行结束a = 'A2:J' + str(row_col[0])data = ws_pro.range(a)return data.valuedef trans_data():"批量获取数据，并且对值做制表符替换等处理"list = []  # 定义列表用来存放数据fileName = path + '\\文档名称.xlsx'sheetName = '清单'row_col = get_row_col(fileName, sheetName)wb_pro = xw.Book(fileName)ws_pro = wb_pro.sheets(sheetName)#定位从第几行是数据，存在第一二行无用数据的情况A1 = ws_pro.range('A1').valueA2 = ws_pro.range('A2').valueif A1 == '账期':row = 2elif A2 == '账期':row = 3#如果方法不想拆开，也可以直接用注释内容实现# conn = psycopg2.connect(host='***.***.***.**', user='gpadmin', password='***', database='***', port=5432)# cursor = conn.cursor()# sql = "insert into anrpt.project_usage  values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"for i in range(row, row_col[0]+1):print(str(i) + ' : ' + str(row_col[1]))row_data = ws_pro.range('A'+str(i) ,'J'+str(i)).value  # 按行获取excel的值#H列和I列存在制表符和tab等符号需要替换hn = re.sub('[\s+] ', '', str(row_data[7])).replace('||\'	\'', '')In = re.sub('[\s+] ', '', str(row_data[8]))value = [row_data[0], row_data[1], row_data[2], row_data[3], row_data[4], row_data[5], row_data[6],hn , In, row_data[9]]list.append(value)  # 将数据暂存在列表return list# cursor.executemany(sql, list)  # 执行sql语句# conn.commit()# conn.close()#list.clear()  # 清空listdef executemany(data):"executemany()方法批量执行sql实现数据入库,data为list类型"conn = psycopg2.connect(host='***.***.***.**', user='gpadmin', password='***', database='***', port=5432)cur = conn.cursor()sql =  "insert into anrpt.project_usage  values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"cur.executemany(sql, data)conn.commit()conn.close()def copy_from(data):"copy_from()方法数据入库,data为list类型"data1 = pd.DataFrame(data)# dataframe类型转换为IO缓冲区中的str类型output = StringIO()data1.to_csv(output, sep='\t', index=False, header=False)output1 = output.getvalue()conn = psycopg2.connect(host='***.***.***.**', user='gpadmin', password='***', database='***', port=5432)cur = conn.cursor()cur.copy_from(StringIO(output1), 'anrpt.project_usage')conn.commit()conn.close()print('done')if __name__ == '__main__':# data = get_date()data = trans_data()#executemany(data)copy_from(data)