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

Excel效率人

已于 2024-02-29 07:42:05 修改

阅读量3.4w

点赞数 48

分类专栏： Excel JS 文章标签： wps javascript excel

于 2023-02-05 19:34:29 首次发布

本文链接：https://blog.csdn.net/huaermeier/article/details/128891646

版权

Excel JS 专栏收录该内容

11 篇文章

订阅专栏

JS宏 / JSA的全称是JavaScript for Application & WPS，是内置在WPS Office的应用程序对象，通过JavaScript语言进行访问操作，支持部分ES6语法，因此需要一定的 JavaScript 基础，对于前端程序员的你来说非常简单

本篇介绍 JavaScript 操作 Excel Application 对象的常用方法，助你从VBA切换至JS，WPS Office建议更新到最新版本

向单元格写入二维数组数据、另存为xlsx工作簿文件、另存为txt文本文件

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

选中以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()

打开指定路径的工作簿、导入txt文本文件

注意直接在代码写文件路径，要转义反斜杠\

例 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的工作表

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

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

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)

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

弹出YesNo选择窗口


let yesno = MsgBox('Yes or No?', jsYesNo) //用户选择是，返回的值是6

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


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

创建一个新的工作簿


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

或者可以把代码复制到模块里逐句执行查看效果


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宏摸鱼