下面我们通过一示例，来了解一下最常打交道的 Range 对象：
/*关于[单元格区域(Range)]对象的测试*/function Range_Test() {{//1.Range 对象的获取let rangePathPrinter = rng => Console.log('详细地址：' +rng.Worksheet.Name + '/' + rng.Address());//1.1.Range 构造器//通过 new 获取 Range 对象时，默认指向的是 ActiveSheet 表var rngNew = new Range('I1');rangePathPrinter(rngNew);//1.2.通过 Application.InputBox() 方法//当用户选取了单元格区域并点击确定时，它返回一个 Range 对象//当用户未进行任何选取，或者点击了取消时，它返回 falsevar rngSelected = Application.InputBox('请用鼠标框选单元格区域', '选取单元格区域', undefined,undefined, undefined, undefined, undefined, 8);if (rngSelected === false)alert('您未选取任何单元格区域!');elserangePathPrinter(rngSelected);//1.3.通过 Worksheet.Range() 方法获取，它获取的单元格区域// 是属于该 Worksheet（即工作表对象）的var rngWorksheet = ThisWorkbook.Worksheets.Item(ThisWorkbook.Worksheets.Count).Range('A1:A10');rangePathPrinter(rngWorksheet);//1.4.通过 Application.Range() 方法来获取，它也指向// ActiveSheet(即当前表)let rngApplication = Application.Range('A1');rangePathPrinter(rngApplication);}{//2.Range 的类型原型//由以下语句的结果可知，Range 函数只是一个工具，它//在 new 时，将 prototype 重定向了，它并不是真正的原型Console.log('constructor name : ' + rngNew.constructor.name);Console.log('is instance of Range? = ' + (rngNew instanceof Range));Console.log('is prototype same? = ' +(rngNew.__proto__ == rngWorksheet.__proto__));Console.log('is constructor same? = ' +(rngNew.constructor == rngWorksheet.constructor));}{//3.查看 Range 对象的所有成员let memberNames = Object.keys(rngNew);Console.log('property/method count : ' + memberNames.length);let counter = 0;memberNames = memberNames.sort();for(let name of memberNames) {let info = '[' + (++counter) + ']' + name;//不加try...catch...不行，因为为 member 取值可能出错try {let member = rngNew[name];if (/*这个条件判断不成功，不知为何*/member instanceof Function ||/*这个条件却可以，奇怪吧*/typeof member == 'function')Console.log(info + '(...)');elseConsole.log(info);} catch {Console.log(info);}}}{//4.通过 Value()/Value2 快捷读写单元格区域//4.1.只能通过 Value2 来写数据let bordersMaker = function(rng, color) {let indices = [xlEdgeLeft, xlEdgeTop,xlEdgeBottom, xlEdgeRight,xlInsideHorizontal, xlInsideVertical];for (let index of indices) {(obj=>{obj.Weight = xlThin;obj.LineStyle = xlContinuous;obj.Color = color;})(rng.Borders.Item(index));}}//4.1.1.当向包含多个单元格的单元格区域，写入单个值时，// 每一个单元格都将得到这个值let rngSingleValueWriteTo = new Range('A1:B2,D1,F1:G3');let red = 255; //红色bordersMaker(rngSingleValueWriteTo, red);rngSingleValueWriteTo.Value2 = '单个值';//4.1.2.当向一个单元格区域写入一个一维数组时，它通过遍历将数组// 中的元素赋值给单元格区域的单元格：// 1.它总是对应的将第N个元素赋值给每行的第N个单元格;// 2.如果它的元素个数多于行的单元格个数，多出的元素将被丢弃；// 3.如果它的元素个数少于行的单元格个数，缺少数据的单元格，将// 被填充以 "#N/A" 这个错误let rngSingleDimensionalArrayWriteTo =new Range('A4:B6,D4,F5:I6');let blue = 12611584; //蓝色bordersMaker(rngSingleDimensionalArrayWriteTo, blue);rngSingleDimensionalArrayWriteTo.Value2 = ['a', 'b', 'c'];//4.1.3.当向一个单元格区域写入一个多维数组时，逻辑混乱，// 不要这么调用let rngMultiDimensionalArrayWriteTo =new Range('A8:B10,D8,F8:I9');let black = 0; //黑色bordersMaker(rngMultiDimensionalArrayWriteTo, black);rngMultiDimensionalArrayWriteTo.Value2 =[['a', 'b', 'c'], [1, 2, 3], [false, true, true]];//4.2.读取全部单元格区域数据//可以一次性取得单元格区域对象的所有单元格的值，它们会被//组织成一个数组返回，这个数组按照先列后行的数据读存单元格//区域中所有单元格的值；let rngData = new Range('A12:C15');rngData.Rows.Item(1).Value2 = ['A', 'B', 'C'];rngData.Rows.Item(2).Value2 = ['D', 'E', 'F'];rngData.Rows.Item(3).Value2 = ['G', 'H', 'I'];//读取全部let vsData = rngData.Value2;Console.log(JSON.stringify(vsData));//少读一列let vsDataPart = rngData.Cells.Item(1).Resize(3, 2).Value2;Console.log(JSON.stringify(vsDataPart))//读单行数据let vsDataRow = rngData.Rows.Item(1).Value2;Console.log(JSON.stringify(vsDataRow));//读单列数据let vsDataColumn = rngData.Columns.Item(1).Value2;Console.log(JSON.stringify(vsDataColumn));//由以上例子的输出可知，只有单行单元格区域的数据，//可以一次无误地将所有数据读取到一个数组中；//而且只要你的单元格区域包含多个单元格，读取到的//一定会是一个二维数组//综上所述，请在快捷读取单元格区域数据时，只按行单行//单行的读取}{/*5.通过 Value()/Value2 读写特殊值：//5.1.Date 类型后者将 Date 与 Currency 类型的数据以 Double 类型返回VBA 在这方面处理得比较好，因为它本身支持的 Date/Currency 类型是与 Excel的同名数据类型是对等的；而 JSA 的 Date 类型与 Excel 的 Date 类型完全不对等，至于 Currency 类型，JSA 则根本就不支持*/let now = new Date(Date.now());let cell = rngNew;cell.Clear();//通过赋值加设置数据格式的方式，可以向单元格输入日期值cell.Value2 = now;cell.NumberFormatLocal = 'yyyy/mm/dd hh:MM:ss;@';Console.log(cell.Value2.constructor.name);//NumberConsole.log(cell.Value().constructor.name);//Datelet dtValue = cell.Value();//JSA日期是带时区的，Excel 的不带时区Console.log(now);Console.log(now.toLocaleString());Console.log(dtValue);Console.log(dtValue.toLocaleString());//设置到单元格的日期数据会丢失毫秒的精度Console.log(now.valueOf());Console.log(dtValue.valueOf());Console.log(now - dtValue == now.getMilliseconds());//所以在 JSA 与 Excel 有日期时间数据交换时，要注意时区与毫秒精度方面的影响}}
其输出如下：
详细地址：Sheet2/$I$1详细地址：Sheet1/$H$32详细地址：Sheet2/$A$1:$A$10详细地址：Sheet2/$A$1constructor name : Rangeis instance of Range? = falseis prototype same? = trueis constructor same? = trueproperty/method count : 187[1]Activate(...)[2]AddComment(...)[3]AddIndent[4]Address(...)[5]AddressLocal(...)[6]AdvancedFilter(...)[7]AllocateChanges(...)[8]AllowEdit[9]Application[10]ApplyNames(...)[11]ApplyOutlineStyles(...)[12]Areas[13]AutoComplete(...)[14]AutoFill(...)[15]AutoFilter(...)[16]AutoFit(...)[17]AutoFormat(...)[18]AutoOutline(...)[19]BorderAround(...)[20]Borders[21]Calculate(...)[22]CalculateRowMajorOrder(...)[23]Cells[24]Characters(...)[25]CheckSpelling(...)[26]Clear(...)[27]ClearComments(...)[28]ClearContents(...)[29]ClearFormats(...)[30]ClearHyperlinks(...)[31]ClearNotes(...)[32]ClearOutline(...)[33]Column[34]ColumnDifferences(...)[35]ColumnWidth[36]Columns[37]Comment[38]Consolidate(...)[39]Copy(...)[40]CopyFromRecordset(...)[41]CopyPicture(...)[42]Count[43]CountLarge[44]CreateNames(...)[45]CreatePublisher(...)[46]Creator[47]CurrentArray[48]CurrentRegion[49]Cut(...)[50]DataSeries(...)[51]Delete(...)[52]Dependents[53]DialogBox(...)[54]DirectDependents[55]DirectPrecedents[56]Dirty(...)[57]DiscardChanges(...)[58]DisplayFormat[59]EditionOptions(...)[60]End(...)[61]EntireColumn[62]EntireRow[63]Errors[64]ExportAsFixedFormat(...)[65]FillDown(...)[66]FillLeft(...)[67]FillRight(...)[68]FillUp(...)[69]Find(...)[70]FindNext(...)[71]FindPrevious(...)[72]FlashFill(...)[73]Font[74]FormatConditions[75]Formula[76]FormulaArray[77]FormulaHidden[78]FormulaLabel[79]FormulaLocal[80]FormulaR1C1[81]FormulaR1C1Local[82]FunctionWizard(...)[83]GetRangeEx(...)[84]GoalSeek(...)[85]Group(...)[86]HasArray[87]HasFormula[88]Height[89]Hidden[90]HorizontalAlignment[91]Hyperlinks[92]ID[93]IndentLevel[94]Insert(...)[95]InsertIndent(...)[96]Interior[97]Item(...)[98]Justify(...)[99]Left[100]ListHeaderRows[101]ListNames(...)[102]ListObject[103]LocationInTable[104]Locked[105]MDX[106]Merge(...)[107]MergeArea[108]MergeCells[109]Name[110]NavigateArrow(...)[111]Next[112]NoteText(...)[113]NumberFormat[114]NumberFormatLocal[115]Offset(...)[116]Orientation[117]OutlineLevel[118]PageBreak[119]Parent[120]Parse(...)[121]PasteSpecial(...)[122]Phonetic[123]Phonetics[124]PivotCell[125]PivotField[126]PivotItem[127]PivotTable[128]Precedents[129]PrefixCharacter[130]Previous[131]PrintOut(...)[132]PrintPreview(...)[133]QueryTable[134]Range(...)[135]RangeEx[136]ReadingOrder[137]RemoveDuplicates(...)[138]RemoveSubtotal(...)[139]Replace(...)[140]Resize(...)[141]Row[142]RowDifferences(...)[143]RowHeight[144]Rows[145]Run(...)[146]Select(...)[147]ServerActions[148]SetPhonetic(...)[149]Show(...)[150]ShowDependents(...)[151]ShowDetail[152]ShowErrors(...)[153]ShowPrecedents(...)[154]ShrinkToFit[155]SmartTags[156]Sort(...)[157]SortSpecial(...)[158]SoundNote[159]SparklineGroups[160]Speak(...)[161]SpecialCells(...)[162]Style[163]SubscribeTo(...)[164]Subtotal(...)[165]Summary[166]Table(...)[167]Text[168]TextToColumns(...)[169]Top[170]UnMerge(...)[171]Ungroup(...)[172]UseStandardHeight[173]UseStandardWidth[174]Validation[175]Value(...)[176]Value2[177]VerticalAlignment[178]Width[179]Worksheet[180]WrapText[181]XPath[182]_BorderAround(...)[183]_Default(...)[184]_NewEnum[185]_PasteSpecial(...)[186]_PrintOut(...)[187]__PrintOut(...)[["A","D","G"],[null,"B","E"],["H",null,"C"],["F","I",null]][["A","D"],["G","B"],["E","H"]][["A","B","C"]][["A"],["D"],["G"],[null]]NumberDate2021/8/22 17:03:342021/8/23 上午1:03:342021/8/22 17:03:342021/8/23 上午1:03:3416296518143101629651814000true
若有收获，就点个赞吧
0 人点赞
