JS宏本质是调用Office提供的各种对象的过程,由于WPS宏编辑器支持大部分ES6语法,如果有JavaScript基础、并且对ES5、ES6语法特性有一定了解,上手会非常简单。本篇介绍JS操作Excel表格对象的常用方法,助你从VBA转战JS,这里用的是WPS Office 2022 12980版本
向单元格写入二维数组数据、另存为xlsx工作簿文件、另存为txt文本文件
Resize(行数,列数),以某个单元格为起点扩展区域
arr变量的数据类型为二维数组,其中arr.length表示一维长度,arr[0].length表示二维长度
function export_file() {
const arr = [
['名字', '年龄']
, ['清风', 18]
, ['若雨', 22]
, ['猫的树', 27]
, ['童颜', 25]
, ['茶季', 19]
]
Range('A1').Resize(arr.length, arr[0].length).Value2 = arr //向A1:B6单元格区域写入二维数组数据
let path = (ThisWorkbook.Path || Application.DefaultFilePath) + '\\test' //设置文件保存路径
ActiveSheet.Copy() //复制工作表,如果Copy方法没有参数则默认新建一个工作簿
Application.ActiveWorkbook.SaveAs(path, 20) //另存为txt文件,XlFileFormat枚举文件格式:xlTextWindows的值为20
Application.ActiveWorkbook.SaveAs(path, 51) //另存为xlsx文件,XlFileFormat枚举文件格式:xlWorkbookDefault的值为51
alert(`文件路径在 \n${path}.txt \n${path}.xlsx`) //js模板字符串用法
}
打开指定路径的工作簿、导入txt文本文件
注意直接在代码写文件路径,要转义反斜杠\
例 D:\test.xlsx 要写成 D:\\test.xlsx 或者 D:/test.xlsx
//打开工作簿,需要完整路径,返回一个代表打开的工作簿的Workbook对象
let wb1 = Workbooks.Open('D:\\test.xlsx')
//获取已经打开的工作簿对象,工作簿名称或全路径都可以
let wb2 = Workbooks('test.xlsx')
let wb3 = Workbooks('D:\\test.xlsx')
//导入txt文本文件,如何分列数据请参考官方文档的Workbooks.OpenText方法
Workbooks.OpenText('D:\\test.txt')
遍历指定文件夹下的文件
function traverse_files() {
let folder = 'D:\\test\\' + '*.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的工作表
获取单元格的值
//直接用Range对象取值要尤其注意工作表指向,新手很容易出错
let val1 = Range('A1').Value2
let val2 = Range('A1').Value()
//最好指定工作表对象,不容易出错
let wb = ThisWorkbook
let sht = wb.ActiveSheet
let val3 = sht.Range('A1').Value2
获取A1:B6单元格区域的值,并将数据写入D1:E6单元格区域
let sht = ThisWorkbook.ActiveSheet
let arr = sht.Range('A1:B6').Value2 //获取A1:B6单元格区域的值,获取的数据类型为二维数组
sht.Range('D1:E6').Value2 = arr //将值写入D1:E6单元格区域
选中以A1单元格为起点的连续非空白区域,复制到D1单元格
let sht = ThisWorkbook.ActiveSheet
sht.Range('A1').CurrentRegion.Select() //选中A1:B6单元格区域
Selection.Copy(sht.Range('D1')) //将选中的区域复制到D1单元格
链接转图片方法、生成条形码图片
express.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'
let pic = 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)
let col = Application.InputBox(tip[2], undefined, undefined, undefined, undefined, undefined, undefined, 8).Column
alert('你选择的列号是 ' + col)
let arr = Application.InputBox(tip[3], undefined, undefined, undefined, undefined, undefined, undefined, 8).Value2
if (!arr) return alert('未能识别数据区域') //!逻辑非
const data = arr.map(i => i.join(',')) //ES5数组迭代方法
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) + '\\'
//ES5语法的数组迭代方法
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'
let pic = 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(tip[1], undefined, undefined, undefined, undefined, undefined, undefined, 8).Row
console.log('你选择的行号是 ' + ros)
let col = Application.InputBox(tip[2], undefined, undefined, undefined, undefined, undefined, undefined, 8).Column
console.log('你选择的列号是 ' + col)
let selectData = Application.InputBox(tip[3], undefined, undefined, undefined, undefined, undefined, undefined, 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())
//设置文件保存路径
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宏摸鱼