请求网络接口数据一般用WinHttpRequest对象,相较于VBA,JS处理JSON字符串有绝对优势,但是在WPS JS宏编辑器不能直接引用。
WPS官方文档Application.Run方法可以执行 DLL 或 XLL 中的函数,返回被调用函数返回的值,说明可以调用COM组件扩展WPS功能。
网上找了很多资料才找到解决办法:下载这位大佬封装好的XLL文件添加到加载项,通过Application.Run方法调用Eval函数传递JS代码字符串即可
下载地址:扩展wps js宏对com对象的操作 - fan2006 - 博客园 (cnblogs.com)
例一:利用B站视频收藏夹的网络接口,通过GET请求把响应的JSON数据写入到Excel表格
步骤一:下载好大佬封装好的XLL加载项文件引入到WPS
步骤二:打开WPS宏编辑器写代码,本质还是调用VBA的WinHttpRequest对象请求网页数据
演示代码
function getVideoList() {
//设置请求信息,如果要发送数据,向options对象设置data属性即可,注意data属性数据类型必须为JSON
const options = {
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',
method: 'GET', //请求方式:GET / POST
headers: { //设置请求头
'accept': 'application/json, text/plain, */*',
'origin': 'https://space.bilibili.com',
'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'
}
}
let res = request_JSON(options) //调用request_JSON自定义函数获取数据
let obj = JSON.parse(res) //将JSON字符串解析为JS可操作的数组、对象
let list = obj.data.medias //视频信息在medias属性
let arr = [['视频封面', 'up主', '视频标题', '视频简介', '视频链接']] //声明二维数组,设置表头
//ES5数组迭代方法
list.forEach(i => arr.push([
i.cover + '@320w_200h_1c_!web-space-favlist-video.webp',
i.upper.name,
i.title,
i.intro,
'https://www.bilibili.com/video/' + i.bvid
]))
//写入数据
let ros = arr.length
Range('A1').Resize(ros, arr[0].length).Value2 = arr
Range('A2:A' + ros).Value2 = ''
//设置工作表样式
Rows(1).RowHeight = 30
Rows('2:' + ros).RowHeight = 60
Columns('A:B').ColumnWidth = 15
Columns('C:E').ColumnWidth = 32
//插入视频封面图片
arr.slice(1).forEach((i, o) => {
let top = (o + 1) * 60 - 26
ActiveSheet.Shapes.AddPicture(i[0], true, true, 4, top, 86, 54)
})
}
//获取JSON字符串数据,参数数据类型:string | object,封装函数便于以后多次调用
function request_JSON(opt) {
let code = ''
if (typeof(opt) == 'string') {
code = `
var http = new ActiveXObject('WinHttp.WinHttpRequest.5.1');
http.Open('GET', '${opt}', true);
http.Send();
http.WaitForResponse();
var data = http.ResponseText;data`
} else {
let header = '' //设置请求头
let data = opt.data ? JSON.stringify(opt.data) : '' //设置post请求发送的数据
if (opt.headers) {
for (let i in opt.headers) {
header += `http.SetRequestHeader('${i}', '${opt.headers[i]}');`
}
};
code = `
var http = new ActiveXObject('WinHttp.WinHttpRequest.5.1');
http.Open('${opt.method}', '${opt.url}', true);
${header}
http.Send('${data}');
http.WaitForResponse();
var data = http.ResponseText;data`
};
return Application.Run('Eval', code) //调用Eval函数
}
WPS最新版本更新了JavaScript Fetch API,不需要再引用任何XLL文件了,代码更加简化
function fetchTEST() {
let 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'
//JavaScript Fetch API网络请求示例
fetch(url).then(data => data.json()).then(obj => {
let list = obj.data.medias
let arr = [['视频封面', 'up主', '视频标题', '视频简介', '视频链接']]
list.forEach(i => arr.push([
i.cover + '@320w_200h_1c_!web-space-favlist-video.webp',
i.upper.name,
i.title,
i.intro,
'https://www.bilibili.com/video/' + i.bvid
]))
let ros = arr.length
Range('A1').Resize(ros, arr[0].length).Value2 = arr
Range('A2:A' + ros).Value2 = ''
Rows(1).RowHeight = 30
Rows('2:' + ros).RowHeight = 60
Columns('A:B').ColumnWidth = 15
Columns('C:E').ColumnWidth = 32
arr.slice(1).forEach((i, o) => {
let top = (o + 1) * 60 - 26
ActiveSheet.Shapes.AddPicture(i[0], true, true, 4, top, 86, 54)
})
})
}
例二:发送带token的请求,抓取回复我的信息