WPS JSA 宏编程(JS):3.了解 Range 对象

下面我们通过一示例,来了解一下最常打交道的 Range 对象:

  1 /*关于[单元格区域(Range)]对象的测试*/
  2 function Range_Test() {
  3     {//1.Range 对象的获取
  4         let rangePathPrinter = rng => Console.log('详细地址:' + 
  5             rng.Worksheet.Name + '/' + rng.Address());
  6         
  7         //1.1.Range 构造器
  8         //通过 new 获取 Range 对象时,默认指向的是 ActiveSheet 表
  9         var rngNew = new Range('I1');
 10         rangePathPrinter(rngNew);
 11         
 12         //1.2.通过 Application.InputBox() 方法
 13         //当用户选取了单元格区域并点击确定时,它返回一个 Range 对象
 14         //当用户未进行任何选取,或者点击了取消时,它返回 false
 15         var rngSelected = Application.InputBox(
 16             '请用鼠标框选单元格区域', '选取单元格区域', undefined, 
 17             undefined, undefined, undefined, undefined, 8);
 18         if (rngSelected === false)
 19             alert('您未选取任何单元格区域!');
 20         else
 21             rangePathPrinter(rngSelected);
 22         
 23         //1.3.通过 Worksheet.Range() 方法获取,它获取的单元格区域
 24         //    是属于该 Worksheet(即工作表对象)的
 25         var rngWorksheet = ThisWorkbook.Worksheets.Item(
 26             ThisWorkbook.Worksheets.Count).Range('A1:A10');
 27         rangePathPrinter(rngWorksheet);
 28         
 29         //1.4.通过 Application.Range() 方法来获取,它也指向
 30         //    ActiveSheet(即当前表)
 31         let rngApplication = Application.Range('A1');
 32         rangePathPrinter(rngApplication);
 33     }
 34         
 35     {//2.Range 的类型原型
 36         //由以下语句的结果可知,Range 函数只是一个工具,它
 37         //在 new 时,将 prototype 重定向了,它并不是真正的原型
 38         Console.log('constructor name : ' + rngNew.constructor.name);
 39         Console.log('is instance of Range? = ' + (rngNew instanceof Range));
 40         Console.log('is prototype same? = ' + 
 41             (rngNew.__proto__ == rngWorksheet.__proto__));
 42         Console.log('is constructor same? = ' + 
 43             (rngNew.constructor == rngWorksheet.constructor));
 44     }
 45     
 46     {//3.查看 Range 对象的所有成员
 47         let memberNames = Object.keys(rngNew);
 48         Console.log('property/method count : ' + memberNames.length);
 49         let counter = 0;
 50         memberNames = memberNames.sort();
 51         for(let name of memberNames) {
 52             let info = '[' + (++counter) + ']' + name;
 53             //不加try...catch...不行,因为为 member 取值可能出错
 54             try {
 55                 let member = rngNew[name];
 56                 if (/*这个条件判断不成功,不知为何*/
 57                     member instanceof Function ||
 58                     /*这个条件却可以,奇怪吧*/
 59                     typeof member == 'function')
 60                     Console.log(info + '(...)');
 61                 else
 62                     Console.log(info);
 63             } catch {
 64                 Console.log(info);
 65             }
 66         }
 67     }
 68     
 69     
 70     {//4.通过 Value()/Value2 快捷读写单元格区域
 71         //4.1.只能通过 Value2 来写数据
 72         let bordersMaker = function(rng, color) {
 73             let indices = [xlEdgeLeft, xlEdgeTop,
 74                 xlEdgeBottom, xlEdgeRight,
 75                 xlInsideHorizontal, xlInsideVertical];
 76             for (let index of indices) {
 77                 (obj=>{
 78                     obj.Weight = xlThin;
 79                     obj.LineStyle = xlContinuous;
 80                     obj.Color = color;
 81                 })(rng.Borders.Item(index));
 82             }
 83         }
 84         //4.1.1.当向包含多个单元格的单元格区域,写入单个值时,
 85         //      每一个单元格都将得到这个值
 86         let rngSingleValueWriteTo = new Range('A1:B2,D1,F1:G3');
 87         let red = 255; //红色
 88         bordersMaker(rngSingleValueWriteTo, red);
 89         rngSingleValueWriteTo.Value2 = '单个值';
 90         
 91         //4.1.2.当向一个单元格区域写入一个一维数组时,它通过遍历将数组
 92         //      中的元素赋值给单元格区域的单元格:
 93         //      1.它总是对应的将第N个元素赋值给每行的第N个单元格;
 94         //      2.如果它的元素个数多于行的单元格个数,多出的元素将被丢弃;
 95         //      3.如果它的元素个数少于行的单元格个数,缺少数据的单元格,将
 96         //        被填充以 "#N/A" 这个错误
 97         let rngSingleDimensionalArrayWriteTo = 
 98             new Range('A4:B6,D4,F5:I6');
 99         let blue = 12611584; //蓝色
100         bordersMaker(rngSingleDimensionalArrayWriteTo, blue);
101         rngSingleDimensionalArrayWriteTo.Value2 = ['a', 'b', 'c'];
102         
103         //4.1.3.当向一个单元格区域写入一个多维数组时,逻辑混乱,
104         //      不要这么调用
105         let rngMultiDimensionalArrayWriteTo =
106             new Range('A8:B10,D8,F8:I9');
107         let black = 0; //黑色
108         bordersMaker(rngMultiDimensionalArrayWriteTo, black);
109         rngMultiDimensionalArrayWriteTo.Value2 = 
110             [['a', 'b', 'c'], [1, 2, 3], [false, true, true]];
111             
112         //4.2.读取全部单元格区域数据
113         //可以一次性取得单元格区域对象的所有单元格的值,它们会被
114         //组织成一个数组返回,这个数组按照先列后行的数据读存单元格
115         //区域中所有单元格的值;
116         let rngData = new Range('A12:C15');
117         rngData.Rows.Item(1).Value2 = ['A', 'B', 'C'];
118         rngData.Rows.Item(2).Value2 = ['D', 'E', 'F'];
119         rngData.Rows.Item(3).Value2 = ['G', 'H', 'I'];
120         //读取全部
121         let vsData = rngData.Value2;
122         Console.log(JSON.stringify(vsData));
123         //少读一列
124         let vsDataPart = rngData.Cells.Item(1)
125             .Resize(3, 2).Value2;
126         Console.log(JSON.stringify(vsDataPart))
127         //读单行数据
128         let vsDataRow = rngData.Rows.Item(1).Value2;
129         Console.log(JSON.stringify(vsDataRow));
130         //读单列数据
131         let vsDataColumn = rngData.Columns.Item(1).Value2;
132         Console.log(JSON.stringify(vsDataColumn));
133         //由以上例子的输出可知,只有单行单元格区域的数据,
134         //可以一次无误地将所有数据读取到一个数组中;
135         //而且只要你的单元格区域包含多个单元格,读取到的
136         //一定会是一个二维数组
137         //综上所述,请在快捷读取单元格区域数据时,只按行单行
138         //单行的读取
139     }
140     
141     {/*5.通过 Value()/Value2 读写特殊值:
142         //5.1.Date 类型
143         后者将 Date 与 Currency 类型的数据以 Double 类型返回
144         VBA 在这方面处理得比较好,因为它本身支持的 Date/Currency 类型是与 Excel
145         的同名数据类型是对等的;而 JSA 的 Date 类型与 Excel 的 Date 类型完全不对
146         等,至于 Currency 类型,JSA 则根本就不支持*/
147         let now = new Date(Date.now());
148         let cell = rngNew;
149         cell.Clear();
150         //通过赋值加设置数据格式的方式,可以向单元格输入日期值
151         cell.Value2 = now;
152         cell.NumberFormatLocal = 'yyyy/mm/dd hh:MM:ss;@';
153         Console.log(cell.Value2.constructor.name);//Number
154         Console.log(cell.Value().constructor.name);//Date
155         let dtValue = cell.Value();
156         //JSA日期是带时区的,Excel 的不带时区
157         Console.log(now);
158         Console.log(now.toLocaleString());
159         Console.log(dtValue);
160         Console.log(dtValue.toLocaleString());
161         //设置到单元格的日期数据会丢失毫秒的精度
162         Console.log(now.valueOf());
163         Console.log(dtValue.valueOf());
164         Console.log(now - dtValue == now.getMilliseconds());
165         //所以在 JSA 与 Excel 有日期时间数据交换时,要注意时区与毫秒精度方面的影响
166     }
167 }

其输出如下:

详细地址:Sheet2/$I$1
详细地址:Sheet1/$H$32
详细地址:Sheet2/$A$1:$A$10
详细地址:Sheet2/$A$1
constructor name : Range
is instance of Range? = false
is prototype same? = true
is constructor same? = true
property/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]]
Number
Date
2021/8/22 17:03:34
2021/8/23 上午1:03:34
2021/8/22 17:03:34
2021/8/23 上午1:03:34
1629651814310
1629651814000
true
posted @ 2021-08-26 15:11  nutix  阅读(7602)  评论(0编辑  收藏  举报