赞
踩
JS宏 / JSA的全称是JavaScript for Application & WPS,是内置在WPS Office的应用程序对象,通过JavaScript语言进行访问操作,支持部分ES6语法,因此需要一定的 JavaScript 基础,对于前端程序员的你来说非常简单
本篇介绍 JavaScript 操作 Excel Application 对象的常用方法,助你从VBA切换至JS,WPS Office建议更新到最新版本
Range.Resize(行数,列数),以某个单元格为起点扩展区域
user为二维数组,其中user.length表示一维长度,user[0].length表示二维长度
Env对象主要用于取系统环境基本信息,这个对象目前提供了取用户目录、临时目录等相关信息,此对象需要WPS 2024版本,老版本不支持
Env.GetDesktopPath(),获取用户桌面目录
-
- function writeData() {
- const user = [
- ['name', 'sex', 'age'],
- ['清风', '男', 25],
- ['若雨', '男', 22],
- ['猫猫', '女', 18],
- ['童颜', '女', 22],
- ['茶季', '女', 21]
- ]
-
- //A1:B6区域写入数据 Range('A1').Resize(6, 3).Value2 = user
- Range('A1').Resize(user.length, user[0].length).Value2 = user
-
- //let path = Env.GetDesktopPath() + '\\user' //获取桌面目录,Env对象仅支持WPS2024
- let path = Application.DefaultFilePath + '\\user'
-
- ActiveSheet.Copy() //复制工作表,如果Copy方法没有参数则默认新建一个工作簿
- ActiveWorkbook.SaveAs(path, 20) //另存为txt文件,XlFileFormat枚举文件格式:xlTextWindows
- ActiveWorkbook.SaveAs(path, 51) //另存为xlsx文件,XlFileFormat枚举文件格式:xlWorkbookDefault
-
- //js字符串模板
- MsgBox(`文件路径在: \n${path}.txt \n${path}.xlsx`)
- }
获取A1:C6单元格区域的值,并将数据写入E1:G6单元格区域
-
- //读取A1、A2单元格的值,两种方法
- let str1 = Range('A1').Value()
- let str2 = Range('A2').Value2
- console.log(str1) //输出name
- console.log(str2) //输出清风
-
- //写入A1单元格
- Range('A1').Value2 = '姓名'
-
- //读取A1:B6单元格区域的值,获取的数据类型为二维数组
- let arr = Range('A1:C6').Value2
-
- //写入D1:E6单元格区域
- Range('E1:G6').Value2 = arr
Range.CurrentRegion属性返回一个Range对象,该对象表示当前区域。当前区域是空白行和空白列的任何组合所限定的区域。此为只读属性。效果相当于快捷键 Ctrl + A 选定有效的数据区域。
- let user = Range('A1').CurrentRegion.Select()
- Selection.Copy(Range('A8'))
- let user = Range('A1').CurrentRegion.Select()
- Selection.CopyPicture()
- Range('A15').PasteSpecial()
注意直接在代码写文件路径,要转义反斜杠\
例 D:\test.xlsx 要写成 D:\\test.xlsx 或者 D:/test.xlsx
-
- //打开工作簿,需要完整路径,返回一个代表打开的工作簿的Workbook对象
- let wb1 = Workbooks.Open('D:\\user.xlsx')
-
- //获取已经打开的工作簿对象,工作簿名称或全路径都可以
- let wb2 = Workbooks('user.xlsx')
- let wb3 = Workbooks('D:\\user.xlsx')
-
- //导入txt文本文件,如何分列数据请参考官方文档的Workbooks.OpenText方法
- Workbooks.OpenText('D:\\user.txt')
-
- function traverse_files() {
- let folder = 'D:\\*.xlsx' //遍历D盘下的xlsx文件
- let files = Dir(folder)
- while (files) {
- console.log(files)
- files = Dir()
- }
- }
-
- let wb = ThisWorkbook //获取当前代码所在的工作簿对象
- let sht1 = wb.ActiveSheet //获取当前显示的工作表(活动工作表)
- let sht2 = wb.Sheets('Sheet1') //获取当前名为Sheet1的工作表
Fetch方法仅支持WPS2024,2023以前的版本不可用,需要了解 JavaScript 回调函数、异步、Promise、Async
- function sendhttp() {
- let wb = Workbooks.Add()
- wb.SaveAs(`${Application.DefaultFilePath}/video-${Date.now()}`)
-
- let url = "https://api.bilibili.com/x/v3/fav/resource/list?media_id=2809168080&pn=1&ps=20&order=mtime&type=0&tid=0&platform=web"
- fetch(url)
- .then(res => res.json()) //res.json()是一个Promise对象
- .then(d => { //拿到数据
- //获取视频列表总数量
- let cot = d.data.info.media_count
- let page = []
- if (cot > 20) {
- //获取页数
- let limit = Math.ceil(cot / 20)
- //获取当前页码
- let pn = Number(url.match(/(?<=pn=)\d+/)[0])
- for (let i = 1; i <= limit; i++) {
- if (i != pn) {
- //拼接每页的url
- page.push(url.replace(/(?<=pn=)\d+/, i))
- }
- }
- }
-
- //设置行高列宽
- Rows("1:" + cot).RowHeight = 60
- Columns('A:B').ColumnWidth = 15;
- Columns('C:E').ColumnWidth = 32;
-
- //写入数据
- writedata(d.data.medias, 0)
-
- //获取剩下的页
- page.forEach((url, i) => {
- fetch(url)
- .then(req => req.json())
- .then(d => writedata(d.data.medias, i + 1))
- })
-
- })
- }
-
- function writedata(list, index) {
- //处理需要的数据
- let arr = []
- 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
- ]))
-
- //写入数据
- index = index * 20 + 1
- Range('A' + index).Resize(arr.length, 5).Value2 = arr
- Range(`A${index}:A${index + arr.length - 1}`).Value2 = ""
-
- //插入视频封面图片
- arr.forEach((i, o) => {
- let link = i[0]
- let top = (index + o - 1) * 60 + 3
- ActiveSheet.Shapes.AddPicture(link, true, true, 4, top, 86, 54)
- })
- }
Shapes.AddPicture(链接或路径, 是否链接文件, 是否与文档保存, Left, Top, 图片宽度, 图片高度)
以生成条形码图片为例,这里用bwip-js在线api演示效果
-
- let sht = ThisWorkbook.ActiveSheet
- let link = 'https://bwipjs-api.metafloor.com/?bcid=code128&text=200534231&includetext=true&textsize=12&height=9'
- sht.Shapes.AddPicture(link, true, true, 150, 20, 120, 50)
-
- function inputbox_test() {
- let tip = ['请输入名字', '请选择行', '请选择列', '请选择数据区域']
- let myName = InputBox(tip[0])
- alert('你输入的名字是 ' + myName)
-
- let ros = Application.InputBox(tip[1], undefined, undefined, undefined, undefined, undefined, undefined, 8).Row
- alert('你选择的行号是 ' + ros)
-
- //Application.InputBox参数用对象的方式传入,代码就不用写这么长了
- let col = Application.InputBox({Prompt: tip[2], Type: 8}).Column
- alert('你选择的列号是 ' + col)
-
- let arr = Application.InputBox({Prompt: tip[3], Type: 8}).Value2
- if (!arr) return alert('未能识别数据区域') //!逻辑非
-
- const data = arr.map(i => i.join(',')) //map方法数组迭代
- alert('你选择的数据是\n' + data.join('\n'))
- }
-
- let yesno = MsgBox('Yes or No?', jsYesNo) //用户选择是,返回的值是6
-
- let sht = ThisWorkbook.ActiveSheet
- let ron = sht.Range('A1').CurrentRegion.Value2
-
- let wb = Workbooks.Add()
-
- function save_workbook() {
- let sht = ThisWorkbook.ActiveSheet
- , data = sht.Range('A1').CurrentRegion.Value2
- , th = data[0] //获取表头
- , path = (ThisWorkbook.Path || Application.DefaultFilePath) + '\\'
-
- //forEach数组迭代方法
- data.forEach((item, i) => {
- if (i == 0) return //忽略第一行数据
- let wb = Workbooks.Add()
- , sht = wb.ActiveSheet
- , filePath = path + item[0].replace(/[\\/:*?\"<>|]/g, '-') //文件名称不得有\/:*?"<>|字符,用正则将特殊字符替换成-
-
- sht.Range('A1:B2').Value2 = [th, item]
- wb.SaveAs(filePath, 51)
- wb.Close() //关闭工作簿
- })
- alert('完成,文件路径在\n' + path)
- }
或者可以把代码复制到模块里逐句执行查看效果
-
- function testJS() {
- const arr = [['名字', '年龄'], ['清风', 18], ['若雨', 22], ['猫的树', 27], ['童颜', 25], ['茶季', 19]]
-
- //获取当前代码工作簿的活动工作表名称
- let sht = ThisWorkbook.ActiveSheet
- console.log(sht.Name)
-
- //向A1:B6单元格区域写入二维数组数据
- sht.Range('A1').Resize(6, 2).Value2 = arr
-
- //获取A1单元格的值
- let rng = sht.Range('A1').Value2
- console.log(rng)
-
- //获取A1:B6单元格区域的值,并将数据写入D1:E6区域
- let arrData = sht.Range('A1:B6').Value2
- sht.Range('D1:E6').Value2 = arrData
-
- //选中以空行与空列的组合为边界的单元格区域,选中A1:B6区域的值复制到G1单元格
- sht.Range('A1').CurrentRegion.Select()
- Selection.Copy(sht.Range('G1'))
-
- //链接转图片,以生成条形码图片为例
- let link = 'https://bwipjs-api.metafloor.com/?bcid=code128&text=200534231&includetext=true&textsize=12&height=9'
- sht.Shapes.AddPicture(link, true, true, 20, 100, 120, 50)
-
- //InputBox对象
- let tip = ['请输入名字', '请选择行', '请选择列', '请选择数据区域']
- let myName = InputBox(tip[0])
- console.log('你输入的名字是 ' + myName)
-
- let ros = Application.InputBox({Prompt: tip[1], Type: 8}).Row
- console.log('你选择的行号是 ' + ros)
-
- let col = Application.InputBox({Prompt: tip[2], Type: 8}).Column
- console.log('你选择的列号是 ' + col)
-
- let selectData = Application.InputBox({Prompt: tip[3], Type: 8}).Value2
- if (selectData) {
- const mydata = selectData.map(i => i.join(','))
- console.log('你选择的数据是\n' + mydata.join('\n'))
- } else {
- console.log('未能识别数据区域')
- }
-
- //弹出让用户选择:‘是’或‘否’的窗口
- let yesno = MsgBox('Yes or No?', jsYesNo) //用户选择是,返回的值是6
- console.log(yesno)
-
- //获取以空行与空列的组合为边界的区域并转化为数组
- let data = sht.Range('A1').CurrentRegion.Value2
- console.log(data.join())
-
- //获取表头
- let th = data[0]
- console.log(th.join())
-
- //设置文件保存路径
- //console.log(Env.GetDesktopPath()) //打印用户桌面目录,Env对象要更新WPS版本,版本太低用不了
- let path = (ThisWorkbook.Path || Application.DefaultFilePath) + '\\'
- console.log(path)
-
- //forEach数组迭代
- data.forEach((item, i) => {
- if (i == 0) return //忽略第一行数据
- let wb = Workbooks.Add()
- , sht = wb.ActiveSheet
- , filePath = path + item[0].replace(/[\\/:*?\"<>|]/g, '-') //文件名称不得有\/:*?"<>|字符,用正则将特殊字符替换成-
-
- console.log(filePath)
-
- sht.Range('A1:B2').Value2 = [th, item]
- wb.SaveAs(filePath, 51)
- wb.Close() //关闭工作簿
- })
- console.log('完成,文件路径在\n' + path)
-
- //遍历文件
- let folder = path + '*.xlsx'
- let files = Dir(folder)
- while (files) {
- console.log(files)
- files = Dir()
- }
- }
最后学习JavaScript 语言推荐用w3school,打好基础才能更好利用JS宏摸鱼
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。