linq.js 是一个开源的包,我下载自 https://github.com/mihaifm/linq,大家如果需要可由此地下载
    只需要将它的代码粘贴到 WPS 宏编辑器中一个新建的模块,即可使用。
    下面是我用 linq.js 以及自定义的 OfficeCollectionIterator 迭代器类,进行的一个查询测试(数据是由前面的 XLSExample 测试例子制备的)(因为使用了全局表达式,请将【工具】》【选项】》【编译】》【禁止全局作用域表达式】取消勾选):

    1. /*将 Office 集合对象封装成可迭代对象*/
    2. class OfficeCollectionIterator {
    3. constructor(collection) {
    4. if (collection == null ||
    5. collection == undefined ||
    6. typeof collection.Count != 'number' ||
    7. typeof collection.Item != 'function')
    8. throw new TypeError('参数 collection 必须是一个 Office 集合对象');
    9. this.collection = collection;
    10. this.index = 1;
    11. }
    12. [Symbol.iterator]() {
    13. return this;
    14. }
    15. next() {
    16. if (this.index <= this.collection.Count)
    17. return { done : false,
    18. value : this.collection.Item(this.index++)
    19. };
    20. else
    21. return { done : true };
    22. }
    23. /*重置迭代器,以方便重新迭代*/
    24. reset() {
    25. this.index = 1;
    26. }
    27. /*是否已经迭代结束*/
    28. get isEnd() {
    29. return this.index > this.collection.Count;
    30. }
    31. }
    32. //linq.js
    33. //来自:https://github.com/mihaifm/linq
    34. //功能:可以对任何可迭代对象进行查询
    35. //源型:C# 或说 .Net 的 Linq(语言集成查询)
    36. class linq_js_Test{
    37. static getInfo(keepSlient = true) {
    38. if (linq_js_Test.__info != undefined)
    39. return linq_js_Test.__info;
    40. let info = {};
    41. let rangeItor = new OfficeCollectionIterator(
    42. ActiveSheet.UsedRange.Cells);
    43. //(1)定位标题单元格
    44. info.TitleCell = Enumerable.from(rangeItor)
    45. .where(cell => cell.Font.Bold == true)
    46. .where(cell => cell.MergeCells == true)
    47. .where(cell => cell.HorizontalAlignment == xlCenter)
    48. .first();
    49. info.Title = info.TitleCell.Text;
    50. if(!keepSlient)
    51. Console.log('表的标题单元格是:' + info.TitleCell.Address());
    52. //(2)取得表的左上单元格
    53. info.LeftTopCell = info.TitleCell.Offset(1, 0);
    54. if(!keepSlient)
    55. Console.log('表的左上单元格是:' + info.LeftTopCell.Address());
    56. //(3)定位表的右下单元格,其特征是:
    57. //单元格的左上边框是常规的,右下边框是加粗的
    58. //新的查询之前,重置迭代器
    59. rangeItor.reset();
    60. info.RightBottomCell = Enumerable.from(rangeItor)
    61. //查询条件1:单元格的左上边框线型是连续的,线宽是细
    62. .where(cell => [xlEdgeLeft, xlEdgeTop]
    63. .map(bdrIndex => cell.Borders.Item(bdrIndex))
    64. .every(bdr => { return bdr.Weight == xlThin &&
    65. bdr.LineStyle == xlContinuous; }))
    66. .where(cell => [xlEdgeRight, xlEdgeBottom]
    67. .map(bdrIndex => cell.Borders.Item(bdrIndex))
    68. .every(bdr => { return bdr.Weight == xlMedium &&
    69. bdr.LineStyle == xlContinuous; }))
    70. .first();
    71. if(!keepSlient)
    72. Console.log('表的右下单元格是:' + info.RightBottomCell.Address());
    73. //(4)表所在的单元格区域
    74. let tableAddress = info.LeftTopCell.Address() + ':' +
    75. info.RightBottomCell.Address();
    76. info.TableRange = Range(tableAddress);
    77. if(!keepSlient)
    78. Console.log('表所在单元格区域是:' + info.TableRange.Address());
    79. //(5)表头所在单元格区域
    80. info.HeaderRange = info.TableRange.Rows.Item(1);
    81. if(!keepSlient)
    82. Console.log('表头所在单元格区域是:' + info.HeaderRange.Address());
    83. //(6)内容单元格区域
    84. let contentAddress = info.LeftTopCell.Offset(1, 0).Address() +
    85. ':' + info.RightBottomCell.Address();
    86. info.ContentRange = Range(contentAddress);
    87. if(!keepSlient)
    88. Console.log('表内容所在单元格区域是:' + info.ContentRange.Address());
    89. //(7)表头信息
    90. let headerRangeItor = new OfficeCollectionIterator(
    91. info.HeaderRange.Cells);
    92. info.Headers = Enumerable.from(headerRangeItor)
    93. .select(cell => cell.Text)
    94. .toArray();
    95. if(!keepSlient)
    96. Console.log('表头有:' + info.Headers.join('、'));
    97. linq_js_Test.__info = info;
    98. return info;
    99. }
    100. //使用 distinct 去重,取得性别的种类
    101. static 取得性别种类() {
    102. let info = linq_js_Test.getInfo();
    103. let innerSexColumnIndex = info.Headers.indexOf('性别') + 1;
    104. let sexColumnRange = info.ContentRange.Columns
    105. .Item(innerSexColumnIndex);
    106. let rangeItor = new OfficeCollectionIterator(
    107. sexColumnRange.Cells);
    108. //新的查询之前,重置迭代器
    109. rangeItor.reset();
    110. let sexCellFormatChecker = cell =>
    111. [xlEdgeLeft, xlEdgeTop, xlEdgeRight]
    112. .map(borderIndex => cell.Borders.Item(borderIndex))
    113. .every(bdr => { return bdr.Weight == xlThin &&
    114. bdr.LineStyle == xlContinuous; });
    115. //取得性别单元格
    116. let sexes = Enumerable.from(rangeItor)
    117. .select(cell => cell.Text)
    118. .distinct()
    119. .toArray();
    120. Console.log('性别有:' + sexes.join('、'));
    121. }
    122. //使用 orderByDescending 倒序排序
    123. static 取得总分第3至第5名的名单() {
    124. let info = linq_js_Test.getInfo();
    125. let rangeItor = new OfficeCollectionIterator(
    126. info.ContentRange.Rows);
    127. let innerNameColumnIndex = info.Headers.indexOf('姓名') + 1;
    128. let innerLanguageColumnIndex = info.Headers.indexOf('语文') + 1;
    129. let innerMathColumnIndex = info.Headers.indexOf('数学') + 1;
    130. let innerEnglishColumnIndex = info.Headers.indexOf('外语') + 1;
    131. let names = Enumerable.from(rangeItor)
    132. //获取姓名与总分信息
    133. .select(row => {
    134. let name = row.Cells.Item(1,
    135. innerNameColumnIndex).Text;
    136. let languageScore = row.Cells.Item(1,
    137. innerLanguageColumnIndex).Value();
    138. let mathScore = row.Cells.Item(1,
    139. innerMathColumnIndex).Value();
    140. let englishScore = row.Cells.Item(1,
    141. innerEnglishColumnIndex).Value();
    142. let totalScore = languageScore +
    143. mathScore + englishScore;
    144. return { 'Name' : name, 'Score' : totalScore };
    145. })
    146. //按总分倒序排序
    147. .orderByDescending(obj => obj.Score)
    148. .skip(2).take(3)
    149. .select(obj => obj.Name)
    150. .toArray();
    151. Console.log('总分第3至5名是:' + names.join(','));
    152. }
    153. //使用 forEach 执行操作
    154. static 将各科都及格的行高亮显示() {
    155. let info = linq_js_Test.getInfo();
    156. let rangeItor = new OfficeCollectionIterator(
    157. info.ContentRange.Rows);
    158. let innerLanguageColumnIndex = info.Headers.indexOf('语文') + 1;
    159. let innerMathColumnIndex = info.Headers.indexOf('数学') + 1;
    160. let innerEnglishColumnIndex = info.Headers.indexOf('外语') + 1;
    161. let green = 65280;
    162. let names = Enumerable.from(rangeItor)
    163. //获取平均分及格了的行
    164. .where(row => [
    165. row.Cells.Item(1, innerLanguageColumnIndex).Value(),
    166. row.Cells.Item(1, innerMathColumnIndex).Value(),
    167. row.Cells.Item(1, innerEnglishColumnIndex).Value()
    168. ].every(score => score >= 60))
    169. //将得到的行高亮显示
    170. .forEach(row => row.Interior.Color = green);
    171. }
    172. }

    数据表如下:
    image.png
    数据文件:
    linq_js_Test.xlsx
    测试代码如下:

    1. function TTTTTesTTTTT() {
    2. Console.clear();
    3. linq_js_Test.getInfo(false);
    4. linq_js_Test.取得性别种类();
    5. linq_js_Test.取得总分第3至第5名的名单();
    6. linq_js_Test.将各科都及格的行高亮显示();
    7. }

    其输出如下:

    1. 表的标题单元格是:$B$2
    2. 表的左上单元格是:$B$3
    3. 表的右下单元格是:$G$13
    4. 表所在单元格区域是:$B$3:$G$13
    5. 表头所在单元格区域是:$B$3:$G$3
    6. 表内容所在单元格区域是:$B$4:$G$13
    7. 表头有:姓名、性别、年龄、语文、数学、外语
    8. 性别有:女、男、僧、阉
    9. 总分第35名是:貂蝉,李白,罗麽

    image.png
    因为宏编程其实就是在以编程的方式,对应用程序宿主公布给宏代码的对象以及集合打交道,操作无外乎增删改查。
    有了 linq 这个库,我们就可以摆脱 var/for/if 这种散乱的拼凑的代码,而以 linq 的语义化的、直观的形式来编写我们想要的查询逻辑。