JSA实现多工作簿多工作表数据查询SQL（附件带JSA链接MySQL）

imnnoii

这篇帖子是JSA如何多工作簿多工作表查询
思路来源VBA。不要和VBA比较了，我认输。
搬砖仔还在学习JS中~~~~~希望JS大佬多多指正问题。


如图：如果需要在这么多工作簿中找一个叫张三的档案~~~~
依次打开查询明显不符合我我摸鱼的风格。

如果你需要在很多规范的表中查找一个数据，且你是WPS用户，那么这篇文章可能对你有用。
为了方便演示，采用了站内的资源。并且参考了其中的VBA代码。这个多工作簿多表查询算是我学VBA的入门帖子了，给大家推荐下。
VBA原帖入口：[Excel 程序开发] 【83期】VBA多工作簿多工作表数据查询[已小结]

采用了ODBC（所以需要驱动，特别是MySQL需要自行安装）。过程也不是很优雅，只能算能用。

关于如何连接，代码写的比较清楚了。如果你自己使用建立DSN也是可以的
       SQL 语句---代码SQL数组部分和最后组成的sqlstr
关于代码的一些基本说明：
代码运行后选择文件夹（当然也可以改成多选文件），其中所有xls表格都将作为数据源。

是的，如图，目前对源数据的要求比较高。
源数据的表格，格式需要保持高度一致。
下载附件： JSA连接数据库-WPSPUB.zip (453.76 KB, 下载次数: 211)

2022-3-30 11:03 上传

点击文件名下载附件

用WPS 打开，宏编辑器可直接查看源码
你可能能在代码中学习到以下基本方法你也可以写的更优雅
代码中的基本方法：
Application.FileDialog ----打开文件选择
Application.FileSearch ----遍历文件（真的好用，dir方法可能需要try 抛出错误）
Sheets.QueryTables   ----链接数据源


速度方面差了一点

sobili

JSA for WPS这个版块知道的人太少了。为你的努力点赞。

imnnoii

sobili 发表于 2022-3-30 17:39
JSA for WPS这个版块知道的人太少了。为你的努力点赞。

zpy2

能不能把代码贴出来，方便手机党学习一下。

imnnoii

1. function Main() {
   
2. //选择文件夹方式查询
   
3. //将查询选择的文件夹下面的所有xls表格
   
4. //对数据源格式要求
   
5. //访问WPS.PUB查看代码详解
   
6.         Application.ScreenUpdating = true;
   
7.         Application.ScreenUpdating = false;
   
8.         const Driver = "{Microsoft Excel Driver (*.xls)}"        //ODBC驱动，所以如果你没有驱动，该程序运行将失败
   
9.         let mypath = Application.FileDialog(msoFileDialogFolderPicker);
   
10.         let files
    
11.         let T = 0;
    
12.         let QTnum1 = ThisWorkbook.Connections.Count
    
13.         mypath.Filters.Clear();
    
14.         mypath.Filters.Add("All files", "*.*");
    
15.         if(mypath.Show() == -1) {
    
16.                 mypath = mypath.SelectedItems.Item(1);
    
17.                 mypath = mypath.replace("\","\\\");
    
18.                 (obj=>{
    
19.                         obj.LookIn = mypath;
    
20.                         obj.SearchSubFolders = false;
    
21.                         obj.FileName = "*.xls";
    
22.                         obj.LastModified=msoLastModifiedAnyTime
    
23.                         obj.Execute(msoSortByFileName,msoSortOrderAscending);
    
24.                         files = obj.FoundFiles;
    
25.                 })(Application.FileSearch)
    
26.                 let r = Range("A65536").End(xlUp).Offset(1).Row;
    
27.                 Rows("2:"+r).Delete()
    
28.                 //计时开始
    
29.                 let ST =new Date().getTime();
    
30.                 //暂定每个表格中都用同一个格式（1部门，2部门，三部门
    
31.                 let TableDB = Array("1部门","2部门","3部门");
    
32.                 //后续需要更改
    
33.                 let querystr = ThisWorkbook.Sheets(1).Range("B1").Value2
    
34.                 let connstring="";                //连接字符
    
35.                 let SQL = [];                        //存储SQL语句
    
36.                 let WBDBS = ""                        //跨表字符串"[EXCEL 8.0;DataBase=xxxx.xls;]."
    
37.                 let N = 1;                                //计数。1-49(总49)
    
38.                 let QTadd = ""                        //查询
    
39.                 for(let i=1;i<files.Count+1;i++){
    
40.                         let filePath = files(i).replace(/\\/g,"\\\");
    
41.                         let filenames = files(i).split("\");
    
42.                         let filename = filenames[filenames.length-1].split(".")[0]
    
43.                         //粗糙的做法。不能识别xxx.xxx.xls
    
44.                         if(N==1){
    
45.                                 connstring = "ODBC;Driver="+Driver+";DBQ="+filePath+";ReadOnly=0;FIL=excel 8.0;";
    
46.                         }
    
47.                         for(let v of TableDB){
    
48.                                 WBDBS = N<=TableDB.length?"":"[EXCEL 8.0;DataBase="+filePath+";]."
    
49.                                 SQL[N-1] = "SELECT [姓名],[客户号],[档案编号],'"+v+"' as [部门],'"+filename+"' as [文件名] FROM "+WBDBS+"["+v+"$A3:F100] WHERE [姓名]='"+querystr+"'"       
    
50.                                 N++;
    
51.                         }
    
52.                         if(N%49==0){
    
53.                                 //执行查询，重置计数器
    
54.                                 sqlstr = SQL.join(" UNION ALL ");
    
55.                                 r = Range("A65536").End(xlUp).Offset(1).Row;
    
56.                                 QTadd = ThisWorkbook.Sheets.Item(1).QueryTables.Add(connstring,Range("A65536").End(xlUp).Offset(1),sqlstr);
    
57.                                 QTadd.Refresh();
    
58.                                 //ThisWorkbook.Connections.Item(QTnum+1).Delete()
    
59.                                 if(r>2){Rows(r).Delete()}
    
60.                                 N=1;
    
61.                                 SQL = [];
    
62.                                 sqlstr = "";
    
63.                                 WBDBS="";
    
64.                                 QTadd = "";
    
65.                         }
    
66.                 }//遍历所有文件
    
67.                 if(SQL.length>0){
    
68.                 //执行查询
    
69.                         sqlstr = SQL.join(" UNION ALL ");
    
70.                         r = Range("A65536").End(xlUp).Offset(1).Row;
    
71.                         QTadd = ThisWorkbook.Sheets.Item(1).QueryTables.Add(connstring,Range("A65536").End(xlUp).Offset(1),sqlstr);
    
72.                         QTadd.Refresh();
    
73.                         //ThisWorkbook.Connections.Item(QTnum+1).Delete()
    
74.                         //已知问题。会导致保存失败WPS崩溃
    
75.                         if(r>2){Rows(r).Delete()}
    
76.                         N=1;
    
77.                         SQL = [];
    
78.                         sqlstr = "";
    
79.                         WBDBS="";
    
80.                         QTadd = "";
    
81.                        
    
82.                 }
    
83.                 T = new Date().getTime()-ST;
    
84.         }
    
85.         MsgBox("查询完毕!\n用时："+T+"ms",jsOKOnly,"JSA多表联查-WPSPUB")
    
86.         Application.ScreenUpdating = true;
    
87.         let QTnum2 = ThisWorkbook.Connections.Count
    
88.         //该循环避免了下次打开时提示外部数据方法。但在Excel中打开依然存在无效
    
89.         //该循环是已知问题的修复办法
    
90.         for(let i=QTnum2;i>QTnum1;i--){
    
91.                 ThisWorkbook.Connections.Item(i).Delete()
    
92.         }
    
93. }
    
94.        

复制代码

方便手机用户观看代码

gangyouquan

赞
zsbdzsbd

紫色海水

好贴，学习了

ares_sheng

这个好，MYSQL数据库都解决了。

goldowl2011

Thanks a lot.

孙森峰

感谢分享！想知道MariaDB的连接方法