WPS表格 JSA 学习笔记
自定义公式
function 加括号(区域, 左括号, 右括号){
return `${左括号}${区域}${右括号||左括号}`;
}
单元格拼接JSON
function jsonStr(rng){
return `{"descp":"${rng.Cells.Item(1,1).Value2}","name":"${rng.Cells.Item(1,2).Value2}","key":"${rng.Cells.Item(1,3).Value2}"},`;
}
单元格内文字颜色
function test(){
var len = Range("A1").Value2.length;
for(let i = 1; i<=len; i++){
Range("A1").Characters(i,1).Font.ColorIndex = i+7;
}
}
引用系统自带公式
注意:这里是代码中引用系统自带公式,是直接拿公式结果来用。如果想在单元格里设置公式看设置公式,别搞混淆了。
Transpose
WorksheetFunction.函数名(参数...);
function test1(rng){
return rng.Value2.join();
}
function test2(rng){
var arr = WorksheetFunction.Transpose(rng); // 通过转置函数,得到按行读的数据
return arr.join();
}
VLookup
注意参数类型,VLookup
的前两个参数,传的都是单元格引用。
function 引用公式(){
Range('D1').Value2 = WorksheetFunction.VLookup(Range("C1"), Range("A:B"), 2, false);
}
Sheet 工作表
创建工作表
/** 创建新表放在开头 */
function addFirst(sheetName = '工作表'){
Sheets.Add(Worksheets.Item(1)).Name = `${sheetName}${Worksheets.Count}`;
}
/** 创建新表放在末尾 */
function addLast(sheetName = '工作表'){
Sheets.Add(undefined, Worksheets.Item(Worksheets.Count)).Name = `${sheetName}${Worksheets.Count}`;
}
/** 在当前表左侧添加 */
function addPrev(sheetName = '工作表'){
Sheets.Add().Name = `${sheetName}${Worksheets.Count}`;
}
/** 在当前表右侧追加 */
function addNext(sheetName = '工作表'){
Sheets.Add(undefined, ActiveSheet).Name = `${sheetName}${Worksheets.Count}`;
}
function main(){
addPrev(); Application.Worksheets.Item("Sheet1").Select();
addNext('摸鱼表');
addFirst('加班表');
addLast('下班表');
alert("确认后将删除刚才创建的表。");
let sheetsName = Array.from(Sheets).filter(sh=>sh.Name!="Sheet1").map(sh=>sh.Name);
Application.Sheets.Item(sheetsName).Delete();
}
工作表引用
function tes1t(){
Application.Worksheets.Item(1).Cells.Item(1, 1).Value2 = "按索引取工作表s1";
Range("A2").Value2 = "当前活动区域赋值s1";
Application.Worksheets.Item("Sheet1").Cells.Item(1, 2).Value2 = "按名称取工作表s1";
Cells.Item(2, 2).Value2 = "当前活动单元格赋值s1";
}
Cells 单元格
单元格引用
Cells(行序号, 列序号)
获取第1行第1列,并选中
Cells.Item(1, 1).Select();
获取第3行第3列,并选中。Item
是VBA
思想,去掉也是可以的。
Cells(3, 3).Select();
Range 范围
单元格引用
function tes1t(){
Application.Worksheets.Item("Sheet2").Activate();
Range("A1").Value2 = "当前活动区域赋值s2";
Cells.Item(1, 2).Value2 = "当前活动单元格赋值s2";
}
- 选中
A1:H8
范围
Range("A1:H8").Select();
- 选中第
1行
第1列
到第3行
第3列
,也就是范围:A1:C3
Range(Cells(1, 1),Cells(3, 3)).Select();
填写公式
A1:H8
范围内,每个单元格都填充公式Rand()
function test(){
Application.Worksheets.Item("Sheet1").Activate();
Application.Range("A1:H8").Formula = "=Rand()";
}
单元格赋值
注意:使用Value2
function test(){
Application.Range("A1:H1").Value2 = "666";
}
A1:E1
区域赋值
function test(){
Range("A1:E1").Value2 = [1,2,3,4,5] ;
}
遍历
遍历 Range 打印值
function test(){
var rng = Range("A1:A20");
for(v of rng){
Console.log(v.Value2)
};
}
按目标单元格背景颜色对指定 Range 求和
这是个自定义公式,可以直接用了
/**
* 按目标单元格背景颜色对指定 Range 求和
* @param rng 求和区域
* @param target 目标单元格
*/
function sumByBgColor(rng, target){
let tc = target.Interior.Color; // 取目标单元格颜色。
return Array.from(rng) // rng转标准数组。这里的 range 应该只是个类数组对象
.filter( c => c.Interior.Color == tc) // 筛选颜色相同单元格
.reduce((a, c, i) => a + c.Value2 , 0); // 合计
}
遍历Range处理单元格内容
给 A1:A20
的内容加个括号
function 遍历Range加括号(){
var rng = Range("A1:A20");
rng.Value2 = rng.Value2.map(v => {
return `【${v}】`;
});
}
for(v of rng){
遍历Range处理单元格颜色
给 A1:A20
改背景色
function 遍历Range改背景色(){
var rng = Range("A1:A20");
Array.from(rng).forEach(v => {
(obj=>{
obj.Color = 5296274;
})(v.Interior);
});
}
遍历Range加批注
function 遍历Range加批注(){
var rng = Range("A1:A20");
let len = rng.Count;
for(let i=1; i<=len; i++){
let obj = rng.Cells.Item(i,1);
obj.ClearComments();
obj.AddComment(Date.now().toString());
}
}
遍历Range加批注(带背景图)
function 遍历Range加带背景图的批注(){
var rng = Range("A1:A5");
let len = rng.Count;
for(let i=1; i<=len; i++){
let obj = rng.Cells.Item(i,1);
obj.ClearComments();
obj.AddComment(Date.now().toString()); // 批注内容为时间戳
obj.Comment.Shape.Fill.UserPicture(`${Application.ActiveWorkbook.Path}\\img\\WPS-${i}.jpg`); // 批注添加背景图片 .\img\WPS-*.jpg
}
}
批量删除行
/**
* 拼Range
*/
function 批量删除()
{
var rng = Range("A1:A50");
let unionRng = null;
// 将所有符合条件的行拼在一起
for(r of rng){
if(/\D+/.test(r.Value2)){ // 删除条件:内容非数字
if(unionRng == null){
unionRng = r.EntireRow;
}else{
unionRng = Application.Union(unionRng, r.EntireRow);
}
}
};
// unionRng.Select();
unionRng.Delete(xlShiftUp); // 一次性删除
}
/**
* 拼地址
*/
function 批量删除(){
var rng = Range("A1:A50");
// 将所有符合条件的行拼在一起
let rn = [...rng].filter(r=>/\D+/.test(r.Value2)) // 删除条件:内容非数字
.map(r=>r.Row+':'+r.Row);
// 一次性删除
Range(rn.join(',')).Delete(xlShiftUp);
}
数组转置
目前WPS有点反人类。取的时候按列,存的时候按行。。。精神分裂。。。
function test(){
let arr = Range("A1:C3").Value2; //取出 range
Console.log(JSON.stringify(arr)); // [[1,4,7],[2,5,8],[3,6,9]]
arr = arr[0].map((_, colIndex) => arr.map(row => row[colIndex])); // 数组转置
Console.log(JSON.stringify(arr)); // [[1,2,3],[4,5,6],[7,8,9]]
}
- 使用例子:
function getArr4Range(rng){
let arr = rng.Value2; //取出 range
return arr[0].map((_, colIndex) => arr.map(row => row[colIndex])); // 数组转置
}
function setArr4Range(rng, arr){
arr.forEach( (v,i) => rng.Rows.Item(i+1).Value2 = v );
}
function test(){
let rngData = getArr4Range(Range("A1:C3"));
Console.log(JSON.stringify(rngData));
let arr = [[1,2,3],[4,5,6],[7,8,9]];
setArr4Range(Range("E1:G3"), arr);
}
Range(Cells(1, 1),Cells(3, 3)).Select();
Selection 选区
function test(){
Range("A1:E5").Select(); // 选中 A1:E5 区域
var arr = [...Selection.Value2]; // 获取当前区域的值,并解构成数组
Console.log(JSON.stringify(arr,null,2));
}
得到:
[
["a1","b1","c1","d1","e1"],
["a2","b2","c2","d2","e2"],
["a3","b3","c3","d3","e3"],
["a4","b4","c4","d4","e4"],
["a5","b5","c5","d5","e5"]
]
Columns 列
删除所有隐藏列
function deleteHiddenColumns(){
[...Columns]
.map(col => col.EntireColumn)
.filter(col => col.EntireColumn.Hidden)
.forEach(col => col.Delete(xlShiftToLeft));
}
遍历单元格
Sheet 范围内遍历
嵌套循环输出乘法口诀表
function test(){
Application.Worksheets.Item("Sheet1").Activate()
for(let i = 1; i <= 9; i++){
for(let j = 1; j <= 9; j++){
Application.Cells.Item(i, j).Value2 = i*j;
}
}
}
Range 范围内遍历
嵌套循环输出乘法口诀表
这里的i
,j
表示range
范围内的偏移量
function test(){
Application.Worksheets.Item("Sheet1").Activate()
let rng = Range("C5:P20");
for(let i = 1; i <= 9; i++){
for(let j = 1; j <= 9; j++){
rng.Cells.Item(i, j).Value2 = i*j;
}
}
}
设置公式
在C2
单元格创建一个公式:在A
列查找a2
并返回B
列对应的值。
Range('C2').Formula = '=VLookup("a2",A:B,2,false)';