在 Excelscript for web 中快速迭代范围

发布于 2025-01-14 05:27:10 字数 525 浏览 5 评论 0原文

我想检查一系列单元格是否为空或其中是否有任何值，我使用这个 for 循环 ：

for (let i = 0; i <= namesRange.getCellCount(); i++) {
  if (namesRange.getCell(i,0).getText() == "") 
    {
      break;
    }
  bookedCount += 1;
}

但是这个迭代非常慢（就像使用 Range.getValue< /code>，但控制台警告您使用 .getValue 迭代速度很慢，不会使用 getText 警告您）迭代一个非常短的列表需要几秒钟的时间10 个元素。

有没有办法使用 ExcelScript 快速检查单元格的值？

这是否意味着，即使我使用 office.js 和 Node.js 开发 UDF 或功能区加载项，迭代单元格的速度也会非常慢？

有什么办法可以让它更快吗？

原文

I want to check that a range of cell are empty or has any values in them, I use this for loop :

for (let i = 0; i <= namesRange.getCellCount(); i++) {
  if (namesRange.getCell(i,0).getText() == "") 
    {
      break;
    }
  bookedCount += 1;
}

However this iteration is extremely slow (as is the use of Range.getValue, but the console warns you that iterating with .getValue is slow, does not warn you with getText) It takes several seconds to iterate over a very short list of 10 elements.

Is there any way to check for the values of a cell in a speedy manner using ExcelScripts?

Does this mean that, even if I develop a UDF or a ribbon Add-In with office.js and Node.js it will also be this extremely slow for iterating over cells?

Is there any way to make this faster?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

画▽骨i 2025-01-21 05:27:10

您的代码执行缓慢的原因可能是对 getCell() 和 getText() 的调用成本高昂。您可以尝试不同的方法，而不是每次都在循环中执行这些调用。一种方法是获取单元格值的数组并对其进行迭代。您可以使用 namesRange 变量来获取值数组。您还可以使用它来获取该范围的行数和列数。使用此信息，您应该能够编写嵌套的 for 循环来迭代数组。以下是您可以如何执行此操作的示例：

function main(workbook: ExcelScript.Workbook) {
  let namesRange: ExcelScript.Range = workbook.getActiveWorksheet().getRange("A1");
  let rowCount: number = namesRange.getRowCount();
  let colCount: number = namesRange.getColumnCount();
  let vals: string[][] = namesRange.getValues() as string[][];

  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < colCount; j++) {
      if (vals[i][j] == "") {
        //additional code here
      }
    }
  }
}

The reason your code is likely performing slowly is that the calls to getCell() and getText() are expensive. Instead of performing these calls every time in the loop you can try a different approach. One approach is to get an array of the cell values and iterate over that. You can use your namesRange variable to get the array of values. And you can also use it to get the row count and the column count for the range. Using this information, you should be able to write nested for loops to iterate over the array. Here's an example of how you might do that:

function main(workbook: ExcelScript.Workbook) {
  let namesRange: ExcelScript.Range = workbook.getActiveWorksheet().getRange("A1");
  let rowCount: number = namesRange.getRowCount();
  let colCount: number = namesRange.getColumnCount();
  let vals: string[][] = namesRange.getValues() as string[][];

  for (let i = 0; i < rowCount; i++) {
    for (let j = 0; j < colCount; j++) {
      if (vals[i][j] == "") {
        //additional code here
      }
    }
  }
}

回复收藏 0 原文

可爱暴击 2025-01-21 05:27:10

第一个答案的另一种替代方法是对值范围内的每个单元格使用 forEach 方法。

它可以减少实现所需结果所需的变量数量。

function main(workbook: ExcelScript.Workbook)
{
  let worksheet = workbook.getActiveWorksheet();
  let usedRange = worksheet.getUsedRange().getValues();

  usedRange.forEach(row => {
    row.forEach(cellValue => {
      console.log(cellValue);
    });
  });
}

Another alternative to the first answer is to use the forEach approach for every cell in the range of values.

It can cut down the amount of variables you need to achieve the desired result.

function main(workbook: ExcelScript.Workbook)
{
  let worksheet = workbook.getActiveWorksheet();
  let usedRange = worksheet.getUsedRange().getValues();

  usedRange.forEach(row => {
    row.forEach(cellValue => {
      console.log(cellValue);
    });
  });
}

回复收藏 0 原文

~没有更多了~