WPS JS通过GET/POST请求网抓数据

仙女配凡人

    请求网络接口数据一般用WinHttpRequest对象，相较于VBA，JS处理JSON字符串有绝对优势，但是在WPS JS宏编辑器不能直接引用。WPS官方文档Application.Run方法可以执行 DLL 或 XLL 中的函数，返回被调用函数返回的值，说明可以调用COM组件扩展WPS功能。



    为此网上找了很多资料才找到解决办法：下载这位大佬打包好的XLL文件添加到加载项，需要的时候用Application.Run方法调用Eval函数，参数传递JS代码字符串即可。由于WPS JS本身不支持全局对象，所以没办法用异步回调处理数据。
大佬的文章及XLL文件的下载地址：扩展wps js宏对com对象的操作

根据大佬打包好的XLL加载项文件，我封装了一个请求函数 request_JSON，用于获取JSON字符串数据，方便以后多次调用

1. function request_JSON(opt) {
   
2.   let code = '';
   
3.   if (typeof(opt) == 'string') {
   
4.     code = `
   
5.       var http = new ActiveXObject('WinHttp.WinHttpRequest.5.1');
   
6.       http.Open('GET', '${opt}', true);
   
7.       http.Send();
   
8.       http.WaitForResponse();
   
9.       var data = http.ResponseText;data`
   
10. 
    
11.   } else {
    
12.     let header = '';
    
13.     let data = opt.data ? JSON.stringify(opt.data) : '';
    
14.     if (opt.headers) {
    
15.       for (let i in opt.headers) {
    
16.         header += `http.SetRequestHeader('${i}', '${opt.headers[i]}');`
    
17.       }
    
18.     };
    
19. 
    
20.     code = `
    
21.       var http = new ActiveXObject('WinHttp.WinHttpRequest.5.1');
    
22.       http.Open('${opt.method}', '${opt.url}', true);
    
23.       ${header}
    
24.       http.Send('${data}');
    
25.       http.WaitForResponse();
    
26.       var data = http.ResponseText;data`
    
27.   };
    
28. 
    
29.   return Application.Run('Eval', code)
    
30. 
    
31. }

复制代码

参数数据类型为：string | object
当参数数据类型为string，则传递网络数据接口的url即可，
当参数数据类型为object，则通过声明一个对象变量设置请求头信息：

1. const options = {
   
2.   url: '接口地址'
   
3.     , method: 'GET' //请求方式，GET/POST 都可以
   
4.     , headers: {    //设置请求头
   
5.         'accept': 'application/json, text/plain, */*' //设置请求头的示例
   
6.     }
   
7.     , data: { //要发送的数据
   
8.         '参数': '值' //发送数据的示例
   
9.     }
   
10. }

复制代码

根据前面封装好的函数，做一个案例：
利用B站视频收藏夹的接口，通过GET请求把响应的JSON字符串数据写入到Excel表格

1. function getVideoList() {
   
2.   //设置请求头信息
   
3.   const options = {
   
4.     url: 'https://api.bilibili.com/x/v3/fav/resource/list?media_id=1017007045&pn=1&ps=20&order=mtime&type=0&tid=0&platform=web&jsonp=jsonp',
   
5.     method: 'GET',
   
6.     headers: {
   
7.       'accept': 'application/json, text/plain, */*',
   
8.       'origin': 'https://space.bilibili.com',
   
9.       'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.0.0 Safari/537.36 Edg/112.0.1722.34'
   
10.     }
    
11.   };
    
12. 
    
13.   let res = request_JSON(options), //调用request_JSON自定义函数获取数据
    
14.     obj = JSON.parse(res),             //将JSON字符串解析为JS可操作的数组、对象
    
15.     list = obj.data.medias,              //视频信息在medias属性
    
16.     arr = [['视频封面', 'up主', '视频标题', '视频简介', '视频链接']]; //声明二维数组，设置表头
    
17. 
    
18.   //JavaScript ES5数组迭代方法
    
19.   list.forEach(i => arr.push([
    
20.     i.cover + '@320w_200h_1c_!web-space-favlist-video.webp',
    
21.     i.upper.name,
    
22.     i.title,
    
23.     i.intro,
    
24.     'https://www.bilibili.com/video/' + i.bvid
    
25.   ]));
    
26. 
    
27.   //写入数据
    
28.   let ros = arr.length;
    
29.   Range('A1').Resize(ros, arr[0].length).Value2 = arr;
    
30.   Range('A2:A' + ros).Value2 = '';
    
31. 
    
32.   //设置工作表样式
    
33.   Rows(1).RowHeight = 30;
    
34.   Rows('2:' + ros).RowHeight = 60;
    
35.   Columns('A:B').ColumnWidth = 15;
    
36.   Columns('C:E').ColumnWidth = 32;
    
37. 
    
38.   //插入视频封面图片
    
39.   arr.forEach((i, o) => {
    
40.     if (o == 0) return;
    
41.     let link = i[0];
    
42.     let top = o * 60 - 26;
    
43.     ActiveSheet.Shapes.AddPicture(link, true, true, 4, top, 86, 54)
    
44.   })
    
45. 
    
46. }
    
47. 
    
48. function request_JSON(opt) {
    
49.   let code = '';
    
50.   if (typeof(opt) == 'string') {
    
51.     code = `
    
52.       var http = new ActiveXObject('WinHttp.WinHttpRequest.5.1');
    
53.       http.Open('GET', '${opt}', true);
    
54.       http.Send();
    
55.       http.WaitForResponse();
    
56.       var data = http.ResponseText;data`
    
57. 
    
58.   } else {
    
59.     let header = '';
    
60.     let data = opt.data ? JSON.stringify(opt.data) : '';
    
61.     if (opt.headers) {
    
62.       for (let i in opt.headers) {
    
63.         header += `http.SetRequestHeader('${i}', '${opt.headers[i]}');`
    
64.       }
    
65.     };
    
66. 
    
67.     code = `
    
68.       var http = new ActiveXObject('WinHttp.WinHttpRequest.5.1');
    
69.       http.Open('${opt.method}', '${opt.url}', true);
    
70.       ${header}
    
71.       http.Send('${data}');
    
72.       http.WaitForResponse();
    
73.       var data = http.ResponseText;data`
    
74.   };
    
75. 
    
76.   return Application.Run('Eval', code)
    
77. 
    
78. }

复制代码

动图效果

静听溪流

      感谢分享

LIUZHU

new ActiveXObject，这个对象在WPS的宏编辑器中支持吗？

仙女配凡人

LIUZHU 发表于 2023-4-15 10:26
new ActiveXObject，这个对象在WPS的宏编辑器中支持吗？

不支持，要调用COM对象

LIUZHU

仙女配凡人 发表于 2023-4-15 11:01
不支持，要调用COM对象

这个调用很麻烦，见是见过，期待JS宏的后续开发，解决这个问题吧

sobili

好厉害！一点也看不懂。

LIUZHU

你是老范？

仙女配凡人

sobili 发表于 2023-4-16 17:49
好厉害！一点也看不懂。

没接触过 JavaScript 语言，看不懂是正常的

仙女配凡人

LIUZHU 发表于 2023-4-17 08:32
你是老范？

我不是哦

LIUZHU

仙女配凡人 发表于 2023-4-17 10:11
我不是哦

那个com对象的操作是老范写的吧？