Power Automate中Excel Script文本转列性能优化求助

一、Excel Script内的批量转换方案（替代逐单元格循环） #

逐单元格读写是Excel Script性能瓶颈的核心原因，改用批量读取-转换-写入的方式，仅需两次API交互，能大幅缩短耗时：

function main(workbook: ExcelScript.Workbook) {
  const targetSheet = workbook.getActiveWorksheet();
  // 配置需要转换的列范围（示例：第2列到第11列，即B-K列，根据实际调整）
  const startColIndex = 2;
  const endColIndex = 11;
  
  // 获取表格有效数据范围（跳过表头，假设表头在第1行）
  const usedRange = targetSheet.getUsedRange();
  const dataRowCount = usedRange.getRowCount() - 1;
  const dataRange = targetSheet.getRange(2, startColIndex, dataRowCount, endColIndex - startColIndex + 1);
  
  // 批量读取所有单元格值
  const rawValues = dataRange.getValues();
  // 批量转换文本为数值（自动跳过非文本内容）
  const convertedValues = rawValues.map(row => 
    row.map(cell => typeof cell === 'string' ? Number(cell) : cell)
  );
  
  // 一次性写入转换后的值
  dataRange.setValues(convertedValues);
  
  // 可选：统一设置数值格式（比如保留两位小数）
  dataRange.setNumberFormat('0.00');
}

该方案耗时通常在几秒内，远低于逐单元格循环的23分钟。

二、前置步骤优化：从JSON转HTML阶段直接指定数值类型 #

既然表格由JSON转HTML再生成Excel，可在生成HTML时给数值列标记格式，让Excel直接识别为数值类型，省去后续转换步骤：

在Power Automate的「Convert JSON to HTML table」动作中，放弃默认模板，自定义HTML模板，给需要识别为数值的<td>标签添加mso-number-format样式：

<tr>
  <td>{{yourNonNumericField}}</td>
  <!-- 数值列添加格式标记 -->
  <td style="mso-number-format:\#\,\#\#0\.00;">{{yourNumericField}}</td>
</tr>

mso-number-format的格式代码可按需调整：

• 整数：mso-number-format:\#\,\#\#0
• 保留两位小数：mso-number-format:\#\,\#\#0\.00
• 百分比：mso-number-format:0%

生成的Excel文件中，对应列会直接以数值类型加载，无需再做格式转换。

补充：原Text to Columns的优化方式 #

如果一定要保留Text to Columns逻辑，不要逐行处理，改为整列批量处理：

function main(workbook: ExcelScript.Workbook) {
  const targetSheet = workbook.getActiveWorksheet();
  const startColIndex = 2;
  const endColIndex = 11;
  const usedRange = targetSheet.getUsedRange();
  const dataRowCount = usedRange.getRowCount() - 1;

  // 遍历目标列，整列执行Text to Columns
  for (let col = startColIndex; col <= endColIndex; col++) {
    const colRange = targetSheet.getRange(2, col, dataRowCount, 1);
    colRange.textToColumns(
      colRange.getCell(0,0),
      ExcelScript.TextToColumnsDelimiter.tab, // 选择不会出现在文本中的分隔符即可
      ExcelScript.TextToColumnsDataType.text
    );
  }
}

整列处理的耗时也会远低于逐行循环。

内容的提问来源于stack exchange，提问作者Facundooz