选择Excel形状或查找其topLeftCell

Question

在office-js中，Shape对象没有选择或激活方法。为了模仿这个动作，我想使用类似于VBA TopLeftCell属性的东西，它也不存在。所以我想，让我写一个函数来得到这个范围:

async function GetTopLeftOfShape(sht: Excel.Worksheet, shp: Excel.Shape) {
shp.load("top, left");
sht.getUsedRangeOrNullObject();
await shp.context.sync();
const shpTop = shp.top;
const shpLeft = shp.left;
let topLeftCell = sht.getRange("A1");
let cellTop: number = 0;
let cellLeft: number = 0;
do {
topLeftCell = topLeftCell.getOffsetRange(0, 1);
topLeftCell.load("left");
await sht.context.sync();
cellLeft = topLeftCell.left.valueOf();
} while (cellLeft < shpLeft);
do {
topLeftCell = topLeftCell.getOffsetRange(1, 0);
topLeftCell.load("top");
await sht.context.sync();
cellTop = topLeftCell.top.valueOf();
} while (cellTop < shpTop);
topLeftCell = topLeftCell.getOffsetRange(-1, -1);
await sht.context.sync();
return topLeftCell;
}

更新2023-1-17 ###代码不能在我的office-js项目中工作，但它在Scriptlab中工作得很好。发生了什么事?

更新2023-1-18 # # # # # #

我像这样调用上面的函数(shp和sht被定义并设置为有问题的形状和它的父工作表在这段代码的其他地方):

let shpTopLeftCell = await GetTopLeftOfShape(sht, shp);
shpTopLeftCell.select();
await sht.context.sync();

但是什么也没发生;单元格选择没有改变，也没有任何错误。

Answer 1

看起来是我自己破解的。这段代码可以工作(尽管我已经更改了函数，只返回单元格的地址):

async function GetTopLeftOfShape(sht: Excel.Worksheet, shp: Excel.Shape) {
shp.load("top, left");
await shp.context.sync();
const shpTop: number = shp.top.valueOf();
const shpLeft: number = shp.left.valueOf();
let topLeftCell = sht.getRange("A1");
let cellTop: number = 0;
let cellLeft: number = 0;
let rowCt: number = 1;
let colCt: number = 1;
do {
topLeftCell = sht.getRange("A1").getOffsetRange(rowCt, colCt);
topLeftCell.load("left,top");
await sht.context.sync();
cellLeft = topLeftCell.left.valueOf();
cellTop = topLeftCell.top.valueOf();
if (cellLeft < shpLeft) {
colCt++;
}
if (cellTop < shpTop) {
rowCt++;
}
} while (cellLeft < shpLeft || cellTop < shpTop);
topLeftCell = sht.getRange("A1").getOffsetRange(rowCt - 1, colCt - 1);
topLeftCell.load("address");
await topLeftCell.context.sync();
return topLeftCell.address.valueOf();
}