WPS JS宏利用Fetch API实现网页数据抓取与Excel自动化处理

1. 为什么需要网页数据抓取与Excel自动化在日常办公中我们经常需要从各种网站获取数据并整理到Excel表格中。比如市场人员需要抓取竞品价格、财务人员需要获取汇率数据、运营人员需要统计社交媒体互动情况。传统做法是手动复制粘贴不仅效率低下还容易出错。WPS Office最新版本内置的JS宏环境配合JavaScript原生的Fetch API可以轻松实现网页数据抓取和Excel自动化处理。相比之前需要通过XLL插件调用WinHttpRequest的方式现在代码更加简洁直观不需要任何第三方依赖。我最近在做一个B站视频数据分析项目时就深刻体会到了这种方式的便利性。原本需要半小时手动整理的数据现在只需要运行一个宏10秒就能自动获取并格式化到表格中。2. WPS JS宏环境准备2.1 启用开发者工具首先确保你使用的是最新版WPS Office建议2023及以上版本。打开WPS表格点击顶部菜单栏的开发工具→宏编辑器。如果没有看到这个选项需要到文件→选项→自定义功能区中勾选开发工具。2.2 了解JS宏基础WPS的宏编辑器支持两种语言VBA和JavaScript。我们选择JS环境因为原生支持JSON处理语法更现代简洁可以直接使用Fetch API等Web API新建一个宏默认会生成如下模板function Macro1() { // 在这里编写你的代码 }2.3 检查Fetch API支持在宏编辑器中输入以下测试代码并运行function testFetch() { console.log(typeof fetch); // 应该输出function console.log(typeof Promise); // 应该输出function }如果都能正确输出说明环境支持现代JavaScript特性。3. 使用Fetch API获取网页数据3.1 基本GET请求Fetch API的使用非常简单下面是一个获取公开API数据的例子function getPublicData() { const apiUrl https://jsonplaceholder.typicode.com/posts/1; fetch(apiUrl) .then(response { if (!response.ok) { throw new Error(网络响应不正常); } return response.json(); }) .then(data { console.log(获取到的数据:, data); // 这里可以调用写入Excel的函数 writeToExcel(data); }) .catch(error { console.error(请求出错:, error); }); }3.2 处理需要认证的请求很多API需要携带认证信息比如Token。下面示例展示如何设置请求头function getPrivateData() { const apiUrl https://api.example.com/protected-data; const token your_auth_token_here; fetch(apiUrl, { headers: { Authorization: Bearer ${token}, Content-Type: application/json } }) .then(response response.json()) .then(data processData(data)); }3.3 POST请求示例除了获取数据我们也可以提交数据function postData() { const apiUrl https://api.example.com/submit; const postData { name: 测试数据, value: 123 }; fetch(apiUrl, { method: POST, headers: { Content-Type: application/json, }, body: JSON.stringify(postData) }) .then(response response.json()) .then(data { console.log(提交成功:, data); }); }4. 将数据写入Excel表格4.1 基本数据写入获取到数据后我们需要将其写入Excel。WPS JS宏提供了与VBA类似的Range对象function writeToExcel(data) { // 假设data是一个对象数组 const headers Object.keys(data[0]); const values data.map(item Object.values(item)); // 写入表头 Range(A1).Resize(1, headers.length).Value2 [headers]; // 写入数据 Range(A2).Resize(values.length, values[0].length).Value2 values; // 自动调整列宽 Columns(A:Z).AutoFit(); }4.2 处理复杂数据结构有时候数据可能包含嵌套对象或数组需要先展平function processComplexData(data) { return data.map(item ({ id: item.id, title: item.title, author: item.user.name, tags: item.tags.join(, ) })); }4.3 添加图片等富文本内容B站示例中的视频封面是图片URL我们可以直接插入function insertImages(imageUrls) { imageUrls.forEach((url, index) { const row index 2; // 从第二行开始 const top (row - 1) * 60 10; // 计算垂直位置 try { ActiveSheet.Shapes.AddPicture(url, true, true, 4, top, 86, 54); } catch (e) { console.error(无法加载图片 ${url}:, e); } }); }5. 完整实战案例B站视频数据抓取5.1 分析API接口以B站收藏夹为例通过浏览器开发者工具可以找到对应的API接口https://api.bilibili.com/x/v3/fav/resource/list?media_id收藏夹IDpn页码ps每页数量5.2 编写完整函数结合前面所学完整实现如下function fetchBilibiliFavorites() { const mediaId 1017007045; // 替换为你的收藏夹ID const url https://api.bilibili.com/x/v3/fav/resource/list?media_id${mediaId}pn1ps20; // 设置请求头 const headers { accept: application/json, user-agent: Mozilla/5.0 }; fetch(url, { headers }) .then(response response.json()) .then(data { if (data.code ! 0) { throw new Error(API错误: ${data.message}); } const videos data.data.medias; const tableData videos.map(video ({ 封面: video.cover, UP主: video.upper.name, 标题: video.title, 简介: video.intro, 链接: https://www.bilibili.com/video/${video.bvid}, 播放量: video.cnt_info.play, 收藏量: video.cnt_info.collect })); writeVideoData(tableData); }) .catch(error { console.error(获取数据失败:, error); Range(A1).Value2 错误: error.message; }); } function writeVideoData(data) { // 清空现有数据 Cells.Clear(); // 写入表头 const headers Object.keys(data[0]); Range(A1).Resize(1, headers.length).Value2 [headers]; // 写入数据 const values data.map(item Object.values(item)); Range(A2).Resize(values.length, values[0].length).Value2 values; // 设置样式 Rows(1).Font.Bold true; Columns.AutoFit(); // 特殊处理图片列 const imageUrls data.map(item item.封面); insertImages(imageUrls); }5.3 错误处理与重试机制网络请求可能会失败添加重试逻辑function fetchWithRetry(url, options, retries 3) { return fetch(url, options) .catch(err { if (retries 0) throw err; console.log(请求失败剩余重试次数: ${retries}); return new Promise(resolve setTimeout(resolve, 1000) ).then(() fetchWithRetry(url, options, retries - 1) ); }); }6. 进阶技巧与优化6.1 分页获取所有数据很多API采用分页设计我们需要循环获取所有页面async function fetchAllPages(baseUrl, pageSize 20) { let allData []; let page 1; let hasMore true; while (hasMore) { const url ${baseUrl}pn${page}ps${pageSize}; const response await fetch(url); const data await response.json(); if (data.data.medias.length 0) { hasMore false; } else { allData allData.concat(data.data.medias); page; // 避免请求太快被限制 await new Promise(resolve setTimeout(resolve, 500)); } } return allData; }6.2 数据缓存与更新为了避免重复请求相同数据可以添加本地缓存function getCachedData(key, ttl 3600) { const now new Date().getTime(); const cached Application.Run(GetSetting, DataCache, key); if (cached) { const { timestamp, data } JSON.parse(cached); if (now - timestamp ttl * 1000) { return Promise.resolve(data); } } return null; } function setCachedData(key, data) { const cache { timestamp: new Date().getTime(), data: data }; Application.Run(SaveSetting, DataCache, key, JSON.stringify(cache)); }6.3 定时自动刷新结合WPS的OnTime方法可以实现定时自动刷新数据let refreshTimer null; function startAutoRefresh(interval 300) { // 5分钟 stopAutoRefresh(); refreshTimer Application.Run(OnTime, new Date(new Date().getTime() interval * 1000), refreshData ); } function stopAutoRefresh() { if (refreshTimer) { Application.Run(OnTime, refreshTimer, Disable); refreshTimer null; } } function refreshData() { fetchBilibiliFavorites(); startAutoRefresh(); }7. 常见问题排查7.1 跨域请求问题由于安全限制某些API可能会阻止跨域请求。解决方法确保请求头中包含正确的Origin和Referer尝试添加mode: cors选项如果API支持可以设置credentials: includefetch(url, { mode: cors, credentials: include, headers: { Origin: https://www.bilibili.com, Referer: https://www.bilibili.com/ } });7.2 数据量过大处理当处理大量数据时可能会遇到性能问题。优化建议分批获取和写入数据关闭屏幕更新加速处理Application.ScreenUpdating false; // 大数据处理... Application.ScreenUpdating true;7.3 图片加载失败网络图片可能无法加载添加备用方案function safeAddPicture(url, cell) { try { const shape ActiveSheet.Shapes.AddPicture(url, true, true, cell.Left 2, cell.Top 2, cell.Width - 4, cell.Height - 4 ); shape.Placement 1; // 随单元格移动 return true; } catch (e) { cell.Value2 图片加载失败; return false; } }8. 安全注意事项不要在代码中硬编码敏感信息如API密钥可以考虑从单元格读取const apiKey Range(Config!B1).Value2;对用户输入进行验证防止注入攻击function sanitizeInput(input) { return input.replace(/[^\w\-]/g, ); }使用HTTPS协议确保数据传输安全。遵守目标网站的robots.txt和使用条款不要过度请求造成服务器负担。在实际项目中我通常会创建一个专门的配置表存放API端点、认证信息等敏感内容这样既方便管理也避免了将敏感信息直接写在代码中。同时建议为宏添加简单的UI界面让使用者可以输入必要的参数而不是直接修改代码。