wps宏代码学习

原创已于 2025-09-02 12:29:32 修改 · 6.2k 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#wps #学习 #宏

于 2024-10-30 21:16:48 首次发布

电脑&网络专栏收录该内容

63 篇文章

订阅专栏

参考资料

推荐学习视频：合集·WPS JS宏从入门到放弃（JSA）教程

JavaScript 教程

在这里插入图片描述

【JS宏教程】WPS表格JS宏快速入门必备最基础知识

wps js宏获取工作簿、工作表、单元格的值、链接转图片、单元格区域数据转二维数组、保存工作簿的常用Excel操作

通过选项卡“帮助”（F1）
在这里插入图片描述

通过WPS开放平台
在WPS开放平台（https://open.wps.cn/）中“开发文档”中的“客户端开发”进入到WPS JS宏帮助中心。
在这里插入图片描述
https://open.wps.cn/previous/docs/client/wpsLoad

基础知识

打开宏编辑器和JS代码调试

工具-》开发工具-》WPS宏编辑器
在这里插入图片描述
左边是工程区，当打开多个excel时会有多个，要注意不要把代码写到其他工作簿去了
右边是代码区

宏是js语言，因此变量或者方法可以网上搜索。比如搜索js变量、js数组方法等。

在语句行号前面点击，可以打断点。
在这里插入图片描述
使用Console.log(“你好”)打印文字、变量值等

代码可以使用F11可以逐语句运行，使用F10可以逐过程运行，跳出等
也可以监视变量值

数据类型

数值分为整数和浮点数，都是number类型

var a=Math.trunc(5.984)  //截取整数
var b=Math.random()  //生成随机小数
var c=Math.pow(5,2)  //乘幂
var d=Math.max(3,5)  //求最大值
var d=Math.min(3,5)  //求最小值

字符串是string类型

	var a="3.14"
	var b="hello\nworld"
	var c=a+b  //字符串相加，会变成字符串连接

字符串转数值

	var a="3"
	var b=5
	b=Number(a)

数值转字符串

	var a=3.14
	var b=String(a)

进制转换

	var a=12
	var b=a.toString(2)  //转换成二进制，结果为字符串类型
	var b=a.toString(16)  //转换成十六进制，结果为字符串类型

变量申明和赋值
变量有三种声明变量的方式const、var、let
const 常量
let 变量申明（作用域更小）
var 变量申明（作用域更大）
var a；//申明单个变量
var b，c，d；//申明多个变量
var e=1,f=2,g=3.2//申明多个变量并赋值数据类型

单元格读写、数组

 Value（）  方法                           //只读
 Value2     属性   只读取值                //可读写
 Formula    属性 单元格有公式则读取公式     //可读写

获取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

function 删除(){
	Range("A6").Select();
	Selection.Delete();  //删除数据
}

向单元格写入二维数组数据、另存为xlsx工作簿文件、另存为txt文本文件
Range.Resize(行数,列数)，以某个单元格为起点扩展区域
user为二维数组，其中user.length表示一维长度，user[0].length表示二维长度

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单元格为起点的连续非空白区域，复制到A8单元格
Range.CurrentRegion属性返回一个Range对象,该对象表示当前区域。当前区域是空白行和空白列的任何组合所限定的区域。此为只读属性。效果相当于快捷键 Ctrl + A 选定有效的数据区域。

let user = Range('A1').CurrentRegion.Select()
Selection.Copy(Range('A8'))

将选定的单元格区域复制为图片，粘贴到A15单元格

let user = Range('A1').CurrentRegion.Select()
Selection.CopyPicture()
Range('A15').PasteSpecial()

将以A1单元格为起点的连续非空白区域转二维数组（类似VBA的CurrentRegion属性）

let sht = ThisWorkbook.ActiveSheet
let ron = sht.Range('A1').CurrentRegion.Value2

打印数组内容

	var arr=Range("a2:b5"). Value2
	Console.log(JSON. stringify(arr))

插入单元格、区域、行、列

Range("H1").Insert(xlShiftToRight)  // 在H1处插入一个单元格，原来的单元格及其同行的右方的单元格，全部右移一格。即在单元格左边插入相应的单元格/区域。
Range("H1").Insert(xlShiftDown)  // 在H1处插入一个单元格，原来的单元格及其列行的下方的单元格，全部下移一格。即在单元格上方插入相应的单元格/区域。

Range("H1:I3").Insert(xlShiftToRight)  // 在H1到I2这个区域的左侧插入同样大小的区域（在这个例子中就是3x2的单元格）
	
Range("H1:I3").Insert(xlShiftDown)  // 在H1到I2这个区域的上方插入同样大小的区域（在这个例子中就是3x2的单元格）

Range("H:H").Insert(xlShiftDown) // 当Range选择的区域是整个列时，Insert的参数无论是哪个，都只会是xlShiftToRight，即左侧插入一列。毕竟不可能在整列的上方插入整列。
	
Range("15:15").Insert(xlShiftToRight) // 同理，当Range选择的区域是整个行时，Insert的参数无论是哪个，都只会是xlShiftDown，即在上方插入一行

数组

在这里插入图片描述

arr=Range('A1:C5').Value2

就把上面选中单元格的内容保存到数组arr中了，表格下标从0开始，即arr[0][0]保存的时A1单元格内容

function test2(){
	const arr=ThisWorkbook.Sheets('测试表1').Range('A5').CurrentRegion.Value2
	arr.reverse()
	Range('F5').Resize(arr.length,arr[0].length).Value2=arr
}

CurrentRegion是当前单元格挨着的有内容的单元格全部选中，类似于选中A5单元格然后按alt+A
在这里插入图片描述
代码第一行就把上面选中的所有单元格内容保存到数组arr中了
arr.reverse()是把数组内容倒过来
Resize(行数,列数) 是指定单元格行数和列数。
arr.length就是数组的行数。arr[0].length是指的数组第一行的列数
代码第3行就是把数组赋值给F5打头的单元格区域
在这里插入图片描述

function submitData(){
	if(checkData()==true){ //自己写个函数来检查数据是否异常   ==true可以省略，通常只写if(checkData())
		const arr=Range('C6').CurrentRegion.Value2		
		const Data=[] //准备一个数组来装数据		
		const targetRow=Sheets('操作记录').Range('A65535').End(3).Row+1  //先取得数据表的最大非空行位并+1作为要写入数据的行位
		let r=0//等下要用r来记录是第几行数量，方便写金额公式的时候判断行位
		for(i=4;i<=11;i++){
			if(arr[i][1]){  //如果单元格有内容
				//金额写成公式字符串，这里使用反引号，在键盘左上角数字1的左侧，反引号里变量可以用${}标出
				let strAmount=`=D${targetRow+r}*F${targetRow+r}`  //假如r=0，targetRow=2，这里就是'D2*F2'
				r++//遇到数量非空行r自增1假如有三行数据，到第三行时r=2，公式就是 D4*F4
				//接下来按数据表的列 构建一个数组
				//人员 领货日期 领货型号 数量 xxx单价 金额 备注
				let rowItem=[arr[1][1],arr[0][1],arr[2][1],arr[i][1],arr[2][3],arr[i][2],strAmount,arr[i][3]]
				//利用push方法把上面的数组一行一行塞进Data构成二维数组
				Data.push(rowItem)
			}
		}
		Sheets('操作记录').Range(`A${targetRow}`).Resize(Data.length,Data[0].length).Value2=Data 
		Sheets('测试表1').Range('D7：D8').ClearContents()
		Sheets('测试表1').Range('D7').Select()

	}else{
		alert('信息不全')
	}
}

工作簿、工作表操作

 
//打开工作簿，需要完整路径，返回一个代表打开的工作簿的Workbook对象
let wb1 = Workbooks.Open('D:\\user.xlsx')
 
//获取已经打开的工作簿对象，工作簿名称或全路径都可以
let wb2 = Workbooks('user.xlsx')

//导入txt文本文件，如何分列数据请参考官方文档的Workbooks.OpenText方法
Workbooks.OpenText('D:\\user.txt')

 
let wb = ThisWorkbook //获取当前代码所在的工作簿对象
let sht1 = wb.ActiveSheet //获取当前显示的工作表（活动工作表）
let sht2 = wb.Sheets('Sheet1') //获取当前名为Sheet1的工作表

 ThisWorkbook（当前工作簿） ThisWorkbook.Name； //（当前工作薄的名字）
 ActiveWorkbook（活动工作簿）ActiveWorkbook.Name; //（活动工作簿的名字）
 Workbooks（"String"）(叫做string的工作簿)  Workbooks（"String"）相当于Workbooks.Item（"String"）
 Workbooks（number）(第几个工作簿)  Workbooks(1) 相当于Workbooks.Item(1) //第一个工作簿

ThisWorkbook.Name; //(工作簿名字) ActiveWorkbook.Name
ThisWorkbook.Path; //(工作簿路径)
ThisWorkbook.FullName; //(工作簿路径加名字)

ActiveSheet  //活动工作薄中的活动工作簿，没有成员提示
WorkSheets（“工作表名”）//用工作表名表示，某个工作簿中的所有worksheet对象  相当于Workbooks.Item（"String"）
WorkSheets（number）//用索引号表示，某个工作簿中的所有worksheet对象    相当于Workbooks.Item（number）
Sheets（“工作表名”）//用工作表名表示，包含chart或worksheet对象  相当于Sheets.Item("工作表名")
Sheets（number）//用索引号表示，包含chart或worksheet对象

创建一个新的工作簿

let wb = Workbooks.Add()

将A列名字数据批量另存工作簿

 
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)
}

 
//打开工作簿，需要完整路径，返回一个代表打开的工作簿的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')

如果想使用相对路径可以用ThisWorkbook.Path先获取当前路径

var b=ThisWorkbook.Path + "\\" + "user.xlsx"
let wb2 = Workbooks(b)

let wb = ThisWorkbook //获取当前代码所在的工作簿对象
let sht1 = wb.ActiveSheet //获取当前显示的工作表（活动工作表）
let sht2 = wb.Sheets('Sheet1') //获取当前名为Sheet1的工作表

function traverse_files() {
    let folder = 'D:\\*.xlsx' //遍历D盘下的xlsx文件
    let files = Dir(folder)
    while (files) {
        console.log(files)
        files = Dir()
    }
}

单元格边界获取

得到当前有数据内容的单元格行数
在这里插入图片描述
像上面的表格，
如果选中A1单元格，按CTRL+方向右键就会跳到F1，按CTRL+方向下键就会跳到A9
如果选中A12单元格，按CTRL+方向右键就会跳到F12，按CTRL+方向下键就会跳到A20
就可以转换成代码：

	Range("A1").End(xlToRight).Select();
	Range("A1").End(xlDown).Select();
	
	Range("A12").End(xlToRight).Select();
	Range("A12").End(xlDown).Select();
			
	var a=Range("A1").End(xlToRight).Column;
	var b=Range("A1").End(xlToRight).Row;

自动填写表格例程

在这里插入图片描述

//测试函数
function test(){
	if(Selection.Column==1 && Selection.Count==1){
		Range('D7').Value2=Selection.Value2
	} else if(Selection.Column==2 && Selection.Count==1){
		Range('D8').Value2=Selection.Value2
	}
}

写入上面代码，如果选中第1列并且选中单元格数量是1个，就让D7单元格值等于选中单元格。如果选中第2列并且选中单元格数量是1个，就让D8单元格值等于选中单元格。

做一个按钮来允许宏
在这里插入图片描述
按钮可以输入文字，给按钮指定宏，宏名字就是test。

然后我们选中第一列的一个单元格，再点击按钮，D7就会自动赋值了。
把工作表名字改成“测试表1”

然后加如下代码

function Workbook_SheetSelectionChange(Sh,Target)
{
	if(Sh.Name=='测试表1'){
		test()
	}
}

Workbook_SheetSelectionChange是整个工作簿的回调函数，当选择单元格发生变化的时候就会调用。调用的时候判断当前工作表是不是“测试表1”，如果是就自动运行test宏。
在这里插入图片描述
这样就不需要去点按钮了，每次改变选择的单元格就会自动运行。

字符串

单元格为空检查

const DateNotNull= Range('D6').Value2 !=null

数据输入框、提示框

//	InputBox("请输入分数")  //输入框
//	let num=InputBox("请输入分数","提示",100,100,200)  //第2参数是标题；第3参数是默认值；第4/5参数是窗口坐标
//	alert(num)  //消息提示框
//	MsgBox("你好")
	MsgBox("你好",jsYesNo)  //带是/否按钮的消息框
	MsgBox("你好",jsYesNoCancel)  //带是/否/取消按钮的消息框  MsgBox修改第2参数还支持很多样式

InputBox对象获取用户输入的信息、行号、列号、用户选择的单元格区域数据

 
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'))
}

改变文字效果

function 给范围内的文字改变效果(){
	Range("B5:F10").Select();
	Selection.Font.Size = 20; //字号20
	Selection.Interior.Color = RGB(145, 156, 205); //背景颜色
	Selection.Font.Bold = true; //加粗
}

链接转图片方法、生成条形码图片

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)

在这里插入图片描述

触发事件

Application（工作簿）事件列表
在这里插入图片描述
Workbook （工作表）事件列表

操作实例

抓取网页数据

JavaScript Fetch API 网络请求，抓取网页数据
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)
    })
}