linq.js 是一个开源的包,我下载自 https://github.com/mihaifm/linq,大家如果需要可由此地下载
只需要将它的代码粘贴到 WPS 宏编辑器中一个新建的模块,即可使用。
下面是我用 linq.js 以及自定义的 OfficeCollectionIterator 迭代器类,进行的一个查询测试(数据是由前面的 XLSExample 测试例子制备的)(因为使用了全局表达式,请将【工具】》【选项】》【编译】》【禁止全局作用域表达式】取消勾选):
/*将 Office 集合对象封装成可迭代对象*/
class OfficeCollectionIterator {
constructor(collection) {
if (collection == null ||
collection == undefined ||
typeof collection.Count != 'number' ||
typeof collection.Item != 'function')
throw new TypeError('参数 collection 必须是一个 Office 集合对象');
this.collection = collection;
this.index = 1;
}
[Symbol.iterator]() {
return this;
}
next() {
if (this.index <= this.collection.Count)
return { done : false,
value : this.collection.Item(this.index++)
};
else
return { done : true };
}
/*重置迭代器,以方便重新迭代*/
reset() {
this.index = 1;
}
/*是否已经迭代结束*/
get isEnd() {
return this.index > this.collection.Count;
}
}
//linq.js
//来自:https://github.com/mihaifm/linq
//功能:可以对任何可迭代对象进行查询
//源型:C# 或说 .Net 的 Linq(语言集成查询)
class linq_js_Test{
static getInfo(keepSlient = true) {
if (linq_js_Test.__info != undefined)
return linq_js_Test.__info;
let info = {};
let rangeItor = new OfficeCollectionIterator(
ActiveSheet.UsedRange.Cells);
//(1)定位标题单元格
info.TitleCell = Enumerable.from(rangeItor)
.where(cell => cell.Font.Bold == true)
.where(cell => cell.MergeCells == true)
.where(cell => cell.HorizontalAlignment == xlCenter)
.first();
info.Title = info.TitleCell.Text;
if(!keepSlient)
Console.log('表的标题单元格是:' + info.TitleCell.Address());
//(2)取得表的左上单元格
info.LeftTopCell = info.TitleCell.Offset(1, 0);
if(!keepSlient)
Console.log('表的左上单元格是:' + info.LeftTopCell.Address());
//(3)定位表的右下单元格,其特征是:
//单元格的左上边框是常规的,右下边框是加粗的
//新的查询之前,重置迭代器
rangeItor.reset();
info.RightBottomCell = Enumerable.from(rangeItor)
//查询条件1:单元格的左上边框线型是连续的,线宽是细
.where(cell => [xlEdgeLeft, xlEdgeTop]
.map(bdrIndex => cell.Borders.Item(bdrIndex))
.every(bdr => { return bdr.Weight == xlThin &&
bdr.LineStyle == xlContinuous; }))
.where(cell => [xlEdgeRight, xlEdgeBottom]
.map(bdrIndex => cell.Borders.Item(bdrIndex))
.every(bdr => { return bdr.Weight == xlMedium &&
bdr.LineStyle == xlContinuous; }))
.first();
if(!keepSlient)
Console.log('表的右下单元格是:' + info.RightBottomCell.Address());
//(4)表所在的单元格区域
let tableAddress = info.LeftTopCell.Address() + ':' +
info.RightBottomCell.Address();
info.TableRange = Range(tableAddress);
if(!keepSlient)
Console.log('表所在单元格区域是:' + info.TableRange.Address());
//(5)表头所在单元格区域
info.HeaderRange = info.TableRange.Rows.Item(1);
if(!keepSlient)
Console.log('表头所在单元格区域是:' + info.HeaderRange.Address());
//(6)内容单元格区域
let contentAddress = info.LeftTopCell.Offset(1, 0).Address() +
':' + info.RightBottomCell.Address();
info.ContentRange = Range(contentAddress);
if(!keepSlient)
Console.log('表内容所在单元格区域是:' + info.ContentRange.Address());
//(7)表头信息
let headerRangeItor = new OfficeCollectionIterator(
info.HeaderRange.Cells);
info.Headers = Enumerable.from(headerRangeItor)
.select(cell => cell.Text)
.toArray();
if(!keepSlient)
Console.log('表头有:' + info.Headers.join('、'));
linq_js_Test.__info = info;
return info;
}
//使用 distinct 去重,取得性别的种类
static 取得性别种类() {
let info = linq_js_Test.getInfo();
let innerSexColumnIndex = info.Headers.indexOf('性别') + 1;
let sexColumnRange = info.ContentRange.Columns
.Item(innerSexColumnIndex);
let rangeItor = new OfficeCollectionIterator(
sexColumnRange.Cells);
//新的查询之前,重置迭代器
rangeItor.reset();
let sexCellFormatChecker = cell =>
[xlEdgeLeft, xlEdgeTop, xlEdgeRight]
.map(borderIndex => cell.Borders.Item(borderIndex))
.every(bdr => { return bdr.Weight == xlThin &&
bdr.LineStyle == xlContinuous; });
//取得性别单元格
let sexes = Enumerable.from(rangeItor)
.select(cell => cell.Text)
.distinct()
.toArray();
Console.log('性别有:' + sexes.join('、'));
}
//使用 orderByDescending 倒序排序
static 取得总分第3至第5名的名单() {
let info = linq_js_Test.getInfo();
let rangeItor = new OfficeCollectionIterator(
info.ContentRange.Rows);
let innerNameColumnIndex = info.Headers.indexOf('姓名') + 1;
let innerLanguageColumnIndex = info.Headers.indexOf('语文') + 1;
let innerMathColumnIndex = info.Headers.indexOf('数学') + 1;
let innerEnglishColumnIndex = info.Headers.indexOf('外语') + 1;
let names = Enumerable.from(rangeItor)
//获取姓名与总分信息
.select(row => {
let name = row.Cells.Item(1,
innerNameColumnIndex).Text;
let languageScore = row.Cells.Item(1,
innerLanguageColumnIndex).Value();
let mathScore = row.Cells.Item(1,
innerMathColumnIndex).Value();
let englishScore = row.Cells.Item(1,
innerEnglishColumnIndex).Value();
let totalScore = languageScore +
mathScore + englishScore;
return { 'Name' : name, 'Score' : totalScore };
})
//按总分倒序排序
.orderByDescending(obj => obj.Score)
.skip(2).take(3)
.select(obj => obj.Name)
.toArray();
Console.log('总分第3至5名是:' + names.join(','));
}
//使用 forEach 执行操作
static 将各科都及格的行高亮显示() {
let info = linq_js_Test.getInfo();
let rangeItor = new OfficeCollectionIterator(
info.ContentRange.Rows);
let innerLanguageColumnIndex = info.Headers.indexOf('语文') + 1;
let innerMathColumnIndex = info.Headers.indexOf('数学') + 1;
let innerEnglishColumnIndex = info.Headers.indexOf('外语') + 1;
let green = 65280;
let names = Enumerable.from(rangeItor)
//获取平均分及格了的行
.where(row => [
row.Cells.Item(1, innerLanguageColumnIndex).Value(),
row.Cells.Item(1, innerMathColumnIndex).Value(),
row.Cells.Item(1, innerEnglishColumnIndex).Value()
].every(score => score >= 60))
//将得到的行高亮显示
.forEach(row => row.Interior.Color = green);
}
}
数据表如下:
数据文件:
linq_js_Test.xlsx
测试代码如下:
function TTTTTesTTTTT() {
Console.clear();
linq_js_Test.getInfo(false);
linq_js_Test.取得性别种类();
linq_js_Test.取得总分第3至第5名的名单();
linq_js_Test.将各科都及格的行高亮显示();
}
其输出如下:
表的标题单元格是:$B$2
表的左上单元格是:$B$3
表的右下单元格是:$G$13
表所在单元格区域是:$B$3:$G$13
表头所在单元格区域是:$B$3:$G$3
表内容所在单元格区域是:$B$4:$G$13
表头有:姓名、性别、年龄、语文、数学、外语
性别有:女、男、僧、阉
总分第3至5名是:貂蝉,李白,罗麽
因为宏编程其实就是在以编程的方式,对应用程序宿主公布给宏代码的对象以及集合打交道,操作无外乎增删改查。
有了 linq 这个库,我们就可以摆脱 var/for/if 这种散乱的拼凑的代码,而以 linq 的语义化的、直观的形式来编写我们想要的查询逻辑。