如何把 Excel 写入数据库
在数据分析业务中，经常要把Excel文件数据结构化解析以后再进行计算或导入关系数据库，但许多Excel文件的格式并不规整，而且文件结构也多种多样，导致编程进行结构化的工作量会比较大，而且很难通用，每次都要针对文件格式进行分析后再进行开发。
先来看个简单格式的例子，现有sales.xlsx文件，如下：
ORDERID |
CLIENT |
SELLERID |
AMOUNT |
ORDERDATE |
1 |
LFRP |
12 |
9268 |
2020-12-07 |
2 |
YG |
10 |
3458 |
2020-12-07 |
3 |
OHY |
3 |
10298 |
2020-12-07 |
4 |
ANZSW |
12 |
7662 |
2020-12-07 |
5 |
QMTP |
6 |
7947 |
2020-12-07 |
现有数据库中的销售数据表（sales），对应的表结构为：
字段名 |
类型 |
ORDERID |
int |
CLIENT |
varchar(10) |
SELLERID |
smallint |
AMOUNT |
double |
ORDERDATE |
date |
需要将excel中数据写入（更新）到数据库中的sales表
借助集算器可以很方便地完成这件事。
集算器安装包可去润乾网站下载，运行时需要一个授权，免费版本就够用。
1. 运行集算器，配置数据库连接并命名，填写的参数和 JDBC 连接时一样。
操作菜单：Tool->Datasource Connection->Datasouce 设置如下：
2. 在集算器中编写脚本sales2db.dfx：
A |
B |
|
1 |
=file("sales.xlsx").xlsimport@t() |
/读取excel文件内容 |
2 |
=connect("mysql") |
/连接数据库 |
3 |
>A2.update(A1,sales,ORDERID,CLIENT,SELLERID,AMOUNT,ORDERDATE) |
/使用db.update函数将 excel数据更新至数据库的sales表 |
4 |
>A2.close() |
/关闭数据库连接 |
因为sales表中数据需要更新，所以直接使用update函数，如果知道excel中数据都是新的，那么可以使用了update@i函数，将只生成INSERT语句，这样速度会快很多，因为不需要判断是否生成UPDATE语句。update函数的详细用法见：db.update()。
3. 执行脚本后，再观察数据库中sales表，可见数据已更新：
当excel中的数据量较多，在运行环境中，不适合或者无法直接加载至内存时。只需要在使用xlsimport函数时添加选项c，即A1改为：=file("sales.xlsx").xlsimport@tc()，就可以完成大数据情况下的excel写入数据库的工作。
复杂格式的情况，例如，现有雇员信息数据，emp.xlsx：
ID: |
1 |
||
Name: |
jack |
Sex: |
F |
location |
LA |
||
Birthday: |
1987/9/13 |
||
phone: |
36527183 |
||
ID: |
2 |
||
Name: |
tom |
Sex: |
M |
location |
CA |
||
Birthday: |
2000/1/1 |
||
phone: |
56253674 |
现有数据库中的销售数据表（emp），对应的表结构为：
字段名 |
类型 |
id |
int |
name |
varchar(10) |
sex |
char(1) |
location |
char(2) |
birthday |
date |
phone |
char(8) |
1. 在集算器中编写脚本emp2db.dfx
A |
B |
C |
|
1 |
=create(id,name,sex,location,birthday,phone) |
||
2 |
=file("emp.xlsx").xlsopen() |
||
3 |
[B,B,D,B,B,B] |
[1,2,2,3,4,5] |
|
4 |
for |
=A3.(~/B3(#)).(eval($[A2.xlscell()/~/")")) |
|
5 |
if len(B4(1))==0 |
break |
|
6 |
>A1.record(B4) |
||
7 |
>B3=B3.(~+6) |
||
8 |
=connect("mysql") |
||
9 |
=A8.update(A1,emp) |
||
10 |
>A8.close() |
A1 创建列名为"id,name,sex,location,birthday,phone"的空序表
A2 打开Excel数据文件
A3 定义雇员信息所在单元格列号序列
B3 定义雇员信息所在单元格行号序列
A4 用for循环读取每个雇员信息
B4 A3.(~/B3(#))先算出当前雇员单元格编号序列,再读出这些单元格值组成雇员信息序列。第一次循环时为[B1,B2,D2,B3,B4,B5]，第二次循环时为[B7,B8,D8,B9,B10,B11]……每次行号加6。$[A2.xlscell(]与"A2.xlscell("相同，都是表示一个字符串，它的好处是在IDE中编写程序时，如果A2单元格的编号发生了变化，$[A2.xlscell(]中的A2会自动变化，比如在A2前插入了一行，这个表达式就会变成$[A3.xlscell(]，而用引号的话，就不会自动变了。
B5 判断雇员ID值是否为空，为空则退出循环，结束读数
B6 将一条雇员信息存入A1序表尾
B7 让雇员信息的行号序列都加上9，读取下一条雇员信息
A8-A10 连接数据库，将雇员信息存入数据库表emp，关闭数据库
2. 执行脚本后，再观察数据库中emp表，可见数据已更新：
当有多个相同格式的excel文件需要写入数据库时，以简单格式为例，比如有格式相同的sales文件（sales.xlsx, sales1.xlsx, sales2.xlsx），脚本可以这样写：
A |
B |
|
1 |
=connect("mysql") |
|
2 |
=directory@p("sales*.xlsx") |
|
3 |
for A2 |
=file(A3).xlsimport@ct() |
4 |
>A1.update(B3,sales,ORDERID,CLIENT,SELLERID,AMOUNT,ORDERDATE) |
|
5 |
>A1.close() |
A1 连接数据库
A2 获取所有满足sales*.xlsx的文件名，返回序列
A3 循环文件名序列
B3 读取当前文件名对应的excel文件内容
B4 使用db.update函数将excel数据更新至数据库的sales表
A5 循环完毕后，执行关闭数据库连接
复杂格式也可以使用这样的办法来完成多个excel文件写入数据库的工作，不再赘述。
英文版