查找表与选择相交 [英] Find table intersect with selection
本文介绍了查找表与选择相交的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在寻找一个相当于Excel VBA Intersect方法的JavaScript。我需要找到活动的细胞表。基本上我用表做的一切都是基于用户当前选择的表。 IIRC，目前还没有办法直接做到这一点。所以我想做的是基本上是这样的：
- 获取所选范围（仍然不完美，因为我真的只想要<
- 获取工作表。
- 循环遍历所有表格在工作表上。
- 查看每张表格，看看所选范围是否在表格范围内。
Excel.run（function（ctx）{
var Selection = ctx.workbook.getSelectedRange（）;
var Tables = ctx.workbook.tables;
var TableNames = ctx.workbook.tables.load（name）;
for（var i = 0; i< TableNames.items.length; i ++）
{
var Table = ctx.workbook.tables.getItem（TableNames.items [i] .name）;
Table.getRange（）。load（'address'）;
var Intersect = Selection.getBoundingRect（Table.getRan GE（）地址）。
if（Intersect！= null）{return ctx.sync（）。then（function（）{
TableNames.items [i] .name;
}）};
}
返回ctx.sync（）;
}）。catch（function（error）{
console.log（error）;
if（error instanceof OfficeExtension.Error）{
console.log（Debug info： + JSON.stringify（error.debugInfo））;
}
}）;
如果API是原生的，这将是API的一大进步。 ;）
感谢
Zack
解决方案
是检查当前选择是否与表相交的几种方法。这段代码演示了其中的两个。
以下两个示例都使用TypeScript 2.1的预览异步/等待语法（或更准确地说，语法已经存在，但是编译为-ES5是新的，仍在预览中）。通过使用等待，第二种方法显得更简单，但两种方法也可以通过常规的承诺链接。
- 预览一个效果更高，但它使用的是预览（即预览版）的API（即仅在Beta CDN上可用，并可以更改/重命名）。它同时进行所有的交叉检查。
- 如果您有数百张表，或者如果您在Excel Online上运行，则Prod版本效率较低。它需要更多的往返服务器，因为它逐一检查每个表交集，并依赖于抛出的错误通知它没有找到交集。
预览方式：
$（'＃check-intersection-preview'）点击（function（）{
//注意：这个函数使用预览 range.getIntersectionOrNull），
//目前只能在Beta CDN上使用，并且可能会更改
//不要依靠这个进行生产，而是使用备用
//（虽然不太整洁）版本
Excel.run（异步函数（上下文）{
var selection = context.workbook.getSelectedRange（）;
var tables = context.workbook.tables.load（name）;
await context.sync（）;
var intersectionections：{[email：string]：Excel.Range} = {} ;
tables.items.forEach（（table）=> {
intersectionections [table.name] = table.getRange（）。
getIntersectionOrNull（selection）.load（address） ;
}）;
await context.sync（ ）;
var found = false;
for（var tableName in intersection）{
var rangeOrNull = intersectionections [tableName];
if（！rangeOrNull.isNull）{
found = true;
console.log（`与{$ {tableName}的交集）`+
`交点范围：$ {rangeOrNull.address}。
}
}
if（！found）{
console.log（选择不与任何表相交）;
}
}）。catch（errorHandler）;
}）;
符合产品的方法：
$（'＃check-intersection-prod'）点击（function（）{
Excel.run （上下文）{
var selection = context.workbook.getSelectedRange（）;
var tables = context.workbook.tables.load（name）;
await context.sync（）;
var found = false;
for（var i = 0; i< tables.items.length; i ++）{
try {
var table = tables .items [i];
var intersectionRange = table.getRange（）
.getIntersection（selection）.load（address）;
await context.sync（）;
//如果达到了同步，这意味着getIntersection
//没有发生错误，所以交集必须有效
found = true;
的console.log （与表$ {table.name}发现的交点。 `+
`交点范围：$ {intersectionRange.address}。
} catch（e）{
var isExpectedError = e instanceof OfficeExtension.Error&&
（< OfficeExtension.Error> e）.code === Excel.ErrorCodes.itemNotFound;
if（！isExpectedError）{
throw e;
}
}
}
if（！found）{
console.log（Selection does not intersect any table）;
}
}）。catch（errorHandler）;
}）;
常见的错误帮助器：
function errorHandler（error）{
console.log（error）;
if（error instanceof OfficeExtension.Error）{
console.log（Debug info：+ JSON.stringify（error.debugInfo））;
}
}
I'm looking for a Javascript equivalent of the Excel VBA Intersect method. I need to find the active cell table. Basically everything I do with tables is based on which table the user currently has selected. IIRC, there is currently there is no way to do this directly. So what I'm trying to do is basically this:
- Get the selected range (still not perfect, as I really only want the ActiveCell, not the Selection).
- Get the worksheet.
- Loop through all tables on the worksheet.
- Check each table and see if the selected range is in the table range.
I've monkey'd around a bit, and this is what I currently have which doesn't work...
Excel.run(function(ctx) {
var Selection = ctx.workbook.getSelectedRange();
var Tables = ctx.workbook.tables;
var TableNames = ctx.workbook.tables.load("name");
for (var i = 0; i < TableNames.items.length; i++)
{
var Table = ctx.workbook.tables.getItem(TableNames.items[i].name);
Table.getRange().load('address');
var Intersect = Selection.getBoundingRect(Table.getRange().address);
if (Intersect != null) {return ctx.sync().then(function() {
TableNames.items[i].name;
})};
}
return ctx.sync();
}).catch(function(error) {
console.log(error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
This would be a major step forward for the API if it was native. ;)
Thanks, Zack
解决方案
There are several ways to check whether the current selection intersects with a table. This snippet demonstrates two of them.
Both of the examples below are written with TypeScript 2.1's preview async/await syntax (or more accurately, the syntax is already there, but the compilation-down-to-ES5 is new and still in preview). The second method is made significantly simpler through the use of "await", but both are possible with just regular promise-chaining as well.
- The Preview one is more efficient, but it uses an API that is in "Preview" (i.e., only available on the Beta CDN, and subject to be changed/renamed). It does all of its intersection-checks simultaneously.
- The Prod version is less efficient if you have hundreds of tables, or if you're running on Excel Online. It requires more roundtrips to the server, as it checks every table intersection one-by-one, and relies on a thrown error to inform it that there is no intersection found.
Preview method:
$('#check-intersection-preview').click(function() {
// Note: this function uses a "Preview" API ("range.getIntersectionOrNull"),
// which is only available on the Beta CDN right now, and is subject to change.
// Do not rely on this for production. Instead, use the alternate
// (albeit less neat) version.
Excel.run(async function(context) {
var selection = context.workbook.getSelectedRange();
var tables = context.workbook.tables.load("name");
await context.sync();
var intersections: { [email: string]: Excel.Range } = { };
tables.items.forEach((table) => {
intersections[table.name] = table.getRange().
getIntersectionOrNull(selection).load("address");
});
await context.sync();
var found = false;
for (var tableName in intersections) {
var rangeOrNull = intersections[tableName];
if (!rangeOrNull.isNull) {
found = true;
console.log(`Intersection found with table "${tableName}". ` +
`Intersection range: "${rangeOrNull.address}".`);
}
}
if (!found) {
console.log("Selection does not intersect any table");
}
}).catch(errorHandler);
});
Prod-compliant method:
$('#check-intersection-prod').click(function() {
Excel.run(async function(context) {
var selection = context.workbook.getSelectedRange();
var tables = context.workbook.tables.load("name");
await context.sync();
var found = false;
for (var i = 0; i < tables.items.length; i++) {
try {
var table = tables.items[i];
var intersectionRange = table.getRange()
.getIntersection(selection).load("address");
await context.sync();
// If reached past the sync, it means that "getIntersection"
// did not throw an error, and so the intersection must be valid.
found = true;
console.log(`Intersection found with table "${table.name}". ` +
`Intersection range: "${intersectionRange.address}".`);
} catch (e) {
var isExpectedError = e instanceof OfficeExtension.Error &&
(<OfficeExtension.Error>e).code === Excel.ErrorCodes.itemNotFound;
if (!isExpectedError) {
throw e;
}
}
}
if (!found) {
console.log("Selection does not intersect any table");
}
}).catch(errorHandler);
});
Common errorHandler helper:
function errorHandler(error) {
console.log(error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
}
这篇关于查找表与选择相交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
