JS宏中操控单元格的API（一）

前面介绍过，最基本的API：Range对象，其中的Value2属性支持单元格的读写功能。

一、查阅官方文档

点击编辑器中的帮助进入官方开放平台后点击客户端开发，在搜索框中输入Range后，点解表格API中的Range，如下图所示：

二、Range对象介绍

Range：代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。

Range：是一个全局对象，也存在于工作表对象Sheets下。

示例：

function main(){
    Range("A1").Value2 = 3; //当前活动工作表中的A1单元格写入3
    Sheets(2).Range("A1").Value2 = 5; //当前活动工作簿中第2个sheet表中的A1单元格写入5
    
    let wb = Workbooks.open("D:\\my.xls"); //打开D盘下的my.xls文件
    let a = wb.Sheets(2).Range("A1").Value2; //读取第二个表中A1单元格的内容赋值给a
    console.log(a); //输出a
}

三、Range对象中常用的属性和方法

1、单元格的读取与写入

Value2属性：支持读写，示例如下：

function main(){
    let a = Range("A1").Value2; //读取一个单元格的内容
    console.log(a);
 	//读取一个区域内的内容
    let arr = Range("A1:E6").Value2; //直接读取，输出二维数组
    console.log(JSON.stringify(arr)); //每行是一个子数组
    //依次循环每个单元格来读取内容
    for(let col=1; col<=6; col++){
    	for(let row=1; row<=6; row++){
    		let rg = String.fromCharCode(col + 64) + row; //通过ascii编码获取列标，再拼接行号
    		console.log(Range(rg).Value2); //输出每个单元格的内容
    	}
    }
}

执行效果如下图所示：

Text属性：只读，示例如下：

function main(){
    let a = Range("A1").Text; //读取一个单元格的内容
    console.log(a);
 	
    let arr = Range("A1:E6").Text; //不支持这样写，返回空
    console.log(JSON.stringify(arr)); //输出null
}

执行效果如下图所示：

强行使用Text属性进行写入时会报错

Formula属性：可读写，它返回或设置一个 Variant 值，它代表 A1 样式表示法和宏语言中的对象的公式。示例如下所示：

function main(){
    let a = Range("A1").Formula; //如果单元格里是公式会把公式读取出来
    console.log(a);
 	
    let arr = Range("A1:E6").Formula; //支持读取数组
    console.log(JSON.stringify(arr)); //输出null
    
    //支持写入
    Range("A12").Formula = "=SUM(A1:A6)"; //写入公式
    Range("A14").Value2 = "=SUM(A1:A6)"; //Value2也支持写入公式，因为对于=SUM(A1:A6)的字符串，excel会认为他是公式
}

与Value2不同的是，使用Value2读取时，不会读取公式字符串，而是单元格的值。

执行效果如下图所示：

FormulaArray属性：返回或设置区域的数组公式。返回（或可设置为）单个公式或数组。如果指定区域不包含数组公式，则该属性返回 null。可读写。示例如下：

function main(){
    let a = Range("A1").FormulaArray; //如果单元格里是公式会把公式读取出来
    console.log(a);
 	
    let arr = Range("A1:E6").FormulaArray; //该区域不存在数组公式返回null
    console.log(JSON.stringify(arr)); //输出null
    
    let b = Range("A8:E10").FormulaArray;
    console.log(JSON.stringify(b)); 
    //支持写入
    Range("A12").FormulaArray = "=SUM(A1:A6)"; //支持写入公式
    Range("A14").Value2 = "=SUM(A1:A6)"; //Value2也支持写入公式，因为对于=SUM(A1:A6)的字符串，excel会认为他是公式
    
    Range("A16").FormulaArray = "=FILTER(A1:E6,C1:C6>6)"; //不会自动溢出，带有{}
    Range("A22").Value2 = "=FILTER(A1:E6,C1:C6>6)"; //也支持写入数组公式，不会自动溢出，没有{}
    
    Range("A16:E18").FormulaArray = "=FILTER(A1:E6,C1:C6>6)";  //超出数组部分的单元格填充报错
    Range("A22:E23").Value2 = "=FILTER(A1:E6,C1:C6>6)"; //单元格中全部报错
    
}

执行效果如下图所示：

2、扩展读取与写入

CurrentRegion属性：返回一个 Range 对象，该对象表示当前区域。当前区域是以空行与空列的组合为边界的区域。只读。示例如下：

function main(){
  	let arr = Range("A1").CurrentRegion.Value2; //从A1开始扩展，如果整行/整列为空就停止
    console.log(JSON.stringify(arr)); //第8行以后的数据不会输出
}

执行效果：

Resize（）方法：调整指定区域的大小。返Range对象，该对象代表调整后的区域。示例如下：

function main(){
  	let arr = [[1,2,3],[4,5,6]];
  	Range("A17").Resize(arr.length, arr[0].length).Value2 = arr; 
}

它支持直接写入二维数组。执行效果如下：

四、本章小结

本节中，主要讲述了在JS宏中如何读写单元格中的内容。例如Value2属性，它既可以用于读取单元格的内容，也可以利用它来给单元格赋值。结合CurrentRegion属性一起使用，就可以扩展单元格进行读取；结合Resize方法使用，可以实现扩展写入（即直接写入二维数组）。