Python读取Excel文件

早上刚到公司,另一个实习生问如何把 Excel 中保存的测试数据写到 INSERT 语句中,这的确是个很常见的问题,想想之前学 Python 时用过的 openpyxl 模块就是用来操作 Excel 表格的,而且自己当时还做过很多例子,于是就花了几分钟查了官网API,撸了个非常简单的脚本用于把 Excel 中的数据放到 INSERT 语句中

首先 Excel 中的数据格式是这样的(因为数据太多,所以只使用部分数据):

mark

调用 openpyxl.utils.column_index_from_string() 发现有 100 多个字段

Python 代码如下(非常简单,不做解释):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#! python3
import openpyxl
import openpyxl.utils as u
wb = openpyxl.load_workbook('D:\\WeChat\\WeChat Files\\NARUTO1976841230\\Files\\Test.xlsx')
sheet = wb['Sheet1']
s = ''
for row in range(2, sheet.max_row + 1):
s = 'INSERT INTO PM_VAT_TX VALUES ('
for col in range(1, sheet.max_column + 1):
value = str(sheet[u.get_column_letter(col) + str(row)].value)
# print(sheet[u.get_column_letter(col) + str(row)].value)
s += '\''
if value != 'None':
s += str.strip(value)
else:
value = ''
s += value
s += '\''
if col != sheet.max_column:
s += ','
s += ');'
print(s)

输出结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO PM_VAT_TX VALUES ('201712200000900473','20171220','TESTCORP','FCY','','001002000000000000000000','','FCYFACC173140002-2708906000PAY','H0001','FACCINIC','FCYFACC173140002-2','20171110','0','708906000','','398.58','376','60','USD','6.6426','FLEXCUBE','Initiation','直接收费金融服务','','','1','376.02','N','1','470581','','FCYFACC173140002','Y','','02','2A','O','2','0','','','SYS','20171220','20171220183738','','','','SYS','20180129','20180129221552','Y','0','22.58','0','ODS_CSV_VAT_O_TX','20171220183729','376.02','0.06','Y','0','','','','','','','Y','','','C','N','913705007062428884','201605010000036604','N','','','470581','','','0','','','','20171110','3.4','60','0','','0','05','','Y','Y','','Operations','FY2017','NOV','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','1');
INSERT INTO PM_VAT_TX VALUES ('201712200000900475','20171220','TESTCORP','FCY','','001002000000000000000000','','FCYFACC173190005-2702906000PAY','H0001','FACCINIC','FCYFACC173190005-2','20171115','0','702906000','','1061.76','999.81','160','USD','6.62375','FLEXCUBE','Initiation','直接收费金融服务','','','1','999.83','N','1','470581','','FCYFACC173190005','Y','','02','2A','O','2','0','','','SYS','20171220','20171220183738','','','','SYS','20180129','20180129221553','Y','0','60.01','0','ODS_CSV_VAT_O_TX','20171220183729','999.83','0.06','Y','0','','','','','','','Y','','','C','N','913705007062428884','201605010000036604','N','','','470581','','','0','','','','20171115','9.06','160','0','','0','05','','Y','Y','','Operations','FY2017','NOV','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','1');
INSERT INTO PM_VAT_TX VALUES ('201712200000900477','20171220','TESTCORP','FCY','','001002000000000000000000','','FCYFACC173190004-2702906000PAY','H0001','FACCINIC','FCYFACC173190004-2','20171115','0','702906000','','15926.4','14997.17','2400','USD','6.62375','FLEXCUBE','Initiation','直接收费金融服务','','','1','14997.18','N','1','470581','','FCYFACC173190004','Y','','02','2A','O','2','0','','','SYS','20171220','20171220183738','','','','SYS','20180129','20180129221554','Y','0','899.84','0','ODS_CSV_VAT_O_TX','20171220183729','14997.18','0.06','Y','0','','','','','','','Y','','','C','N','913705007062428884','201605010000036604','N','','','470581','','','0','','','','20171115','135.85','2400','0','','0','05','','Y','Y','','Operations','FY2017','NOV','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','1');
INSERT INTO PM_VAT_TX VALUES ('201712200000900478','20171220','TESTCORP','RMB','','001003000000000000000000','','RMBFACC172850001-2708906000PAY','H0001','FACCINIC','RMBFACC172850001-2','20171012','0','708906000','','500','471.7','500','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','471.7','N','1','522081','','RMBFACC172850001','Y','','02','2A','O','2','0','','','SYS','20171220','20171220183738','','','','SYS','20180213','20180213221607','Y','0','28.3','0','ODS_CSV_VAT_O_TX','20171220183729','471.7','0.06','Y','0','','','','','','','Y','','','C','N','913100005904380443','201605010000036623','N','','','522081','','','0','','','','20171012','28.3','500','0','','0','05','','Y','Y','','Operations','FY2017','OCT','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.5955');
INSERT INTO PM_VAT_TX VALUES ('201712200000900480','20171220','TESTCORP','RMB','','001003000000000000000000','','RMBFACC173170001-2708906000PAY','H0001','FACCINIC','RMBFACC173170001-2','20171113','0','708906000','','500','471.7','500','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','471.7','N','1','522081','','RMBFACC173170001','Y','','02','2A','O','2','0','','','SYS','20171220','20171220183738','','','','SYS','20180129','20180129221624','Y','0','28.3','0','ODS_CSV_VAT_O_TX','20171220183729','471.7','0.06','Y','0','','','','','','','Y','','','C','N','913100005904380443','201605010000036623','N','','','522081','','','0','','','','20171113','28.3','500','0','','0','05','','Y','Y','','Operations','FY2017','NOV','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.6426');
INSERT INTO PM_VAT_TX VALUES ('201712200000900481','20171220','TESTCORP','RMB','','001003000000000000000000','','RMBFACC173280001-2702908004PAY','H0001','FACCINIC','RMBFACC173280001-2','20171124','0','702908004','','1993350','1880518.87','1993350','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','1880518.86','N','1','470581','','RMBFACC173280001','Y','','02','2A','O','2','0','','','SYS','20171220','20171220183738','','','','SYS','20180129','20180129221625','Y','0','112831.13','0','ODS_CSV_VAT_O_TX','20171220183729','1880518.86','0.06','Y','0','','','','','','','Y','','','C','N','913705007062428884','201605010000036604','N','','','470581','','','0','','','','20171124','112831.13','1993350','0','','0','05','','Y','Y','','Operations','FY2017','NOV','ASV21','PAY','ASV21','Y','Y','I','3060200000000000000','','0','','','SH','N','6.58485');
INSERT INTO PM_VAT_TX VALUES ('201712200000900482','20171220','TESTCORP','RMB','','001003000000000000000000','','RMBFACC173280002-2702908004PAY','H0001','FACCINIC','RMBFACC173280002-2','20171124','0','702908004','','83056.25','78354.95','83056.25','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','78354.95','N','1','470581','','RMBFACC173280002','Y','','02','2A','O','2','0','','','SYS','20171220','20171220183738','','','','SYS','20180129','20180129221625','Y','0','4701.3','0','ODS_CSV_VAT_O_TX','20171220183729','78354.95','0.06','Y','0','','','','','','','Y','','','C','N','913705007062428884','201605010000036604','N','','','470581','','','0','','','','20171124','4701.3','83056.25','0','','0','05','','Y','Y','','Operations','FY2017','NOV','ASV21','PAY','ASV21','Y','Y','I','3060200000000000000','','0','','','SH','N','6.58485');
INSERT INTO PM_VAT_TX VALUES ('201802060000981154','20180206','TESTCORP','RMB','','001003000000000000000000','','RMBFACC180360002-2709904000PAY','H0001','FACCINIC','RMBFACC180360002-2','20180205','0','709904000','','85','85','85','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','85','N','1','441644','','RMBFACC180360002','Y','','02','2A','O','2','0','','','SYS','20180206','20180206003039','','','','SYS','20180206','20180206003120','Y','0','5.1','0','ODS_CSV_VAT_O_TX','20180206003025','85','0.06','Y','0','','','','','','','Y','','','C','N','91330200747358967Q','201605010000036628','N','','','441644','','','0','','','','20180205','5.1','85','0','','0','05','','Y','Y','','Operations','FY2018','FEB','AS2IW','PAY','AS2IW','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.285');
INSERT INTO PM_VAT_TX VALUES ('201802060000981155','20180206','TESTCORP','RMB','','001003000000000000000000','','RMBFACC180360003-2709904000PAY','H0001','FACCINIC','RMBFACC180360003-2','20180205','0','709904000','','85','85','85','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','85','N','1','441644','','RMBFACC180360003','Y','','02','2A','O','2','0','','','SYS','20180206','20180206003039','','','','SYS','20180206','20180206003120','Y','0','5.1','0','ODS_CSV_VAT_O_TX','20180206003025','85','0.06','Y','0','','','','','','','Y','','','C','N','91330200747358967Q','201605010000036628','N','','','441644','','','0','','','','20180205','5.1','85','0','','0','05','','Y','Y','','Operations','FY2018','FEB','AS2IW','PAY','AS2IW','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.285');
INSERT INTO PM_VAT_TX VALUES ('201802080000988820','20180208','TESTCORP','RMB','','001003000000000000000000','','RMBFACC180390001-2709904000PAY','H0001','FACCINIC','RMBFACC180390001-2','20180208','0','709904000','','85','85','85','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','85','N','1','450739','','RMBFACC180390001','Y','','02','2A','O','2','0','','','SYS','20180208','20180208230034','','','','SYS','20180208','20180208230114','Y','0','5.1','0','ODS_CSV_VAT_O_TX','20180208230022','85','0.06','Y','0','','','','','','','Y','','','C','N','110102710928145','201605010000036633','N','','','450739','','','0','','','','20180208','5.1','85','0','','0','05','','Y','Y','','Operations','FY2018','FEB','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.26215');
INSERT INTO PM_VAT_TX VALUES ('201802080000988821','20180208','TESTCORP','RMB','','001003000000000000000000','','RMBFACC180390002-2709904000PAY','H0001','FACCINIC','RMBFACC180390002-2','20180208','0','709904000','','85','85','85','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','85','N','1','470581','','RMBFACC180390002','Y','','02','2A','O','2','0','','','SYS','20180208','20180208230034','','','','SYS','20180208','20180208230115','Y','0','5.1','0','ODS_CSV_VAT_O_TX','20180208230022','85','0.06','Y','0','','','','','','','Y','','','C','N','913705007062428884','201605010000036604','N','','','470581','','','0','','','','20180208','5.1','85','0','','0','05','','Y','Y','','Operations','FY2018','FEB','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.26215');
INSERT INTO PM_VAT_TX VALUES ('201802080000988823','20180208','TESTCORP','RMB','','001003000000000000000000','','RMBFACC180390004-2709904000PAY','H0001','FACCINIC','RMBFACC180390004-2','20180208','0','709904000','','85','85','85','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','85','N','1','522081','','RMBFACC180390004','Y','','02','2A','O','2','0','','','SYS','20180208','20180208230034','','','','SYS','20180208','20180208230115','Y','0','5.1','0','ODS_CSV_VAT_O_TX','20180208230022','85','0.06','Y','0','','','','','','','Y','','','C','N','913100005904380443','201605010000036623','N','','','522081','','','0','','','','20180208','5.1','85','0','','0','05','','Y','Y','','Operations','FY2018','FEB','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.26215');
INSERT INTO PM_VAT_TX VALUES ('201802130000998621','20180213','TESTCORP','RMB','','001003000000000000000000','','RMBFACC180440002-2709904000PAY','H0001','FACCINIC','RMBFACC180440002-2','20180213','0','709904000','','170','170','170','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','170','N','1','656196','','RMBFACC180440002','Y','','02','2A','O','2','0','','','SYS','20180213','20180213225047','','','','SYS','20180213','20180213225138','N','0','10.2','0','ODS_CSV_VAT_O_TX','20180213225033','170','0.06','Y','0','','','','','','','Y','','','C','N','','201605010000036715','N','','','656196','','','2','','','','20180213','10.2','170','0','','0','05','','Y','Y','','Operations','FY2018','FEB','AS24N','PAY','AS24N','Y','Y','I','3060200000000000000','','0','','','SH','N','6.3295');
INSERT INTO PM_VAT_TX VALUES ('201802130000998623','20180213','TESTCORP','RMB','','001003000000000000000000','','RMBFACC180440004-2708906000PAY','H0001','FACCINIC','RMBFACC180440004-2','20180213','0','708906000','','500','500','500','CNY','1','FLEXCUBE','Initiation','直接收费金融服务','','','1','500','N','1','522081','','RMBFACC180440004','Y','','02','2A','O','2','0','','','SYS','20180213','20180213225047','','','','SYS','20180213','20180213225138','Y','0','30','0','ODS_CSV_VAT_O_TX','20180213225033','500','0.06','Y','0','','','','','','','Y','','','C','N','913100005904380443','201605010000036623','N','','','522081','','','0','','','','20180213','30','500','0','','0','05','','Y','Y','','Operations','FY2018','FEB','ASCTG','PAY','ASCTG','Y','Y','I','3060200000000000000','','0','','','SH','Y','6.3295');

需要注意如下几点:

  • 如果数据库字段并不全是字符串类型,比如整数、小数类型等,只要在 Excel 中对应列随便标注一个特定值,然后程序中加个 if 判断,对于这些值不加引号即可
  • openpyxl 模块并不支持老旧的 Excel 格式,这个时候可以新建一张 Excel 文件把数据从旧 Excel 中复制到新建的文件中

这个简单的小例子告诉我们当在处理繁杂、大量重复性的工作时,应该首先思考是否可以通过脚本代替手工操作,因为即便你再熟悉某种编辑器、办公软件等的快捷操作,面对大量无脑重复性的任务往往都是找不到高效解决办法的



羞辱

文章目录
|