将 Excel 中多组数据对应的数据库数据保存到文件中
例题描述和简单分析
有数据库表 EMPLOYEE，部分数据如下：
EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
SALARY |
1 |
Rebecca |
Moore |
F |
California |
1974-11-20 |
2005-03-11 |
R&D |
7000 |
2 |
Ashley |
Wilson |
F |
New York |
1980-07-19 |
2008-03-16 |
Finance |
11000 |
3 |
Rachel |
Johnson |
F |
New Mexico |
1970-12-17 |
2010-12-01 |
Sales |
9000 |
4 |
Emily |
Smith |
F |
Texas |
1985-03-07 |
2006-08-15 |
HR |
7000 |
5 |
Ashley |
Smith |
F |
Texas |
1975-05-13 |
2004-07-30 |
R&D |
16000 |
6 |
Matthew |
Johnson |
M |
California |
1984-07-07 |
2005-07-07 |
Sales |
11000 |
7 |
Alexis |
Smith |
F |
Illinois |
1972-08-16 |
2002-08-16 |
Sales |
9000 |
8 |
Megan |
Wilson |
F |
California |
1979-04-19 |
1984-04-19 |
Marketing |
11000 |
9 |
Victoria |
Davis |
F |
Texas |
1983-12-07 |
2009-12-07 |
HR |
3000 |
… |
… |
… |
… |
… |
… |
… |
… |
… |
另有 Excel 文件 args.xlsx，如下：
dept |
gender |
R&D |
M |
HR |
F |
需要将 Excel 中的多组 dept,gender 对应的数据库表 EMPLOYEE 数据保存到文件中，结果如下：
R&D_M.xlsx：
EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
SALARY |
10 |
Ryan |
Johnson |
M |
Pennsylvania |
1976-03-12 |
2006-03-12 |
R&D |
13000 |
22 |
Jacob |
Davis |
M |
Texas |
1985-05-07 |
2001-05-07 |
R&D |
16000 |
33 |
Matthew |
Martinez |
M |
Pennsylvania |
1980-07-19 |
2000-07-19 |
R&D |
11000 |
35 |
Justin |
Smith |
M |
Texas |
1978-08-20 |
2008-08-20 |
R&D |
7000 |
38 |
Matthew |
Johnson |
M |
New York |
1972-11-20 |
2002-11-20 |
R&D |
6000 |
187 |
Nicholas |
Smith |
M |
Pennsylvania |
1986-11-13 |
2005-12-01 |
R&D |
5000 |
189 |
Antony |
Williams |
M |
North Carolina |
1978-07-26 |
2007-11-01 |
R&D |
7000 |
190 |
Robert |
Johnson |
M |
Minnesota |
1976-01-12 |
2008-12-01 |
R&D |
10000 |
191 |
David |
Johnson |
M |
Florida |
1969-01-31 |
2007-01-01 |
R&D |
5000 |
192 |
Christian |
Martin |
M |
Ohio |
1974-12-09 |
2006-11-01 |
R&D |
8000 |
195 |
Michael |
Smith |
M |
South Carolina |
1975-05-10 |
2011-01-01 |
R&D |
6500 |
198 |
Christopher |
Robinson |
M |
Arkansas |
1978-10-03 |
2004-04-01 |
R&D |
8000 |
200 |
Zachary |
Williams |
M |
Michigan |
1983-06-17 |
2001-04-01 |
R&D |
7000 |
202 |
Nicholas |
Miller |
M |
Florida |
1987-04-27 |
2006-05-01 |
R&D |
6500 |
211 |
William |
Thomas |
M |
Pennsylvania |
1969-05-10 |
2004-11-01 |
R&D |
5000 |
212 |
Ryan |
Smith |
M |
Washington |
1981-09-14 |
2009-05-01 |
R&D |
8000 |
214 |
James |
Williams |
M |
Texas |
1976-04-11 |
2000-07-01 |
R&D |
5000 |
215 |
Ryan |
Williams |
M |
California |
1971-01-13 |
2009-10-01 |
R&D |
6500 |
HR_F.xlsx：
EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
SALARY |
4 |
Emily |
Smith |
F |
Texas |
1985-03-07 |
2006-08-15 |
HR |
7000 |
9 |
Victoria |
Davis |
F |
Texas |
1983-12-07 |
2009-12-07 |
HR |
3000 |
163 |
Ashley |
Smith |
F |
Kentucky |
1979-12-26 |
2010-12-01 |
HR |
5000 |
165 |
Hannah |
Smith |
F |
Texas |
1979-06-26 |
2010-07-01 |
HR |
6500 |
171 |
Megan |
Jones |
F |
California |
1978-07-24 |
2010-09-01 |
HR |
8000 |
175 |
Jasmine |
Smith |
F |
Pennsylvania |
1976-03-23 |
2005-07-01 |
HR |
7000 |
177 |
Megan |
Johnson |
F |
Missouri |
1978-03-11 |
2009-07-01 |
HR |
5000 |
179 |
Olivia |
Jones |
F |
North Carolina |
1972-08-17 |
2007-05-01 |
HR |
10000 |
180 |
Abigail |
Smith |
F |
New York |
1972-09-19 |
2007-05-01 |
HR |
5000 |
181 |
Alyssa |
Johnson |
F |
Illinois |
1982-04-30 |
2002-08-01 |
HR |
6500 |
解法及简要说明
在集算器中编写脚本 p1.dfx，如下所示：
A |
|
1 |
=file("args.xlsx").xlsimport@t() |
2 |
=connect("demo") |
3 |
=A1.(A2.query("SELECT * FROM EMPLOYEE WHERE DEPT=? AND GENDER=?",dept,gender)) |
4 |
>A2.close() |
5 |
>A1.(file(dept/"_"/gender/".xlsx").xlsexport@t(A3(#))) |
简要说明：
A1 读取 args.xlsx 数据
A2 连接数据源名称为 demo 的数据库
A3 循环 A1，依次在数据库中执行 SQL 结果返回成序表，这里有两个参数，分别对应当前 A1 的 dept 和 gender
A4 关闭数据库连接
A5 循环 A1，依次将 A3 的数据导出到 Excel 中，Excel 文件名为当前 A1 的 dept_gender.xlsx
JAVA 集成这段代码的方法可参考：《Java 如何调用 SPL 脚本》。
英文版