将工作表合并到单个工作簿中

项目
12/06/2023

此示例演示如何将数据从多个工作簿拉取到单个集中式工作簿中。它使用两个脚本：一个用于从工作簿中检索信息，另一个用于创建包含该信息的新工作表。它将脚本合并到一个 Power Automate 流中，该流作用于整个 OneDrive 文件夹。

重要

此示例仅复制其他工作簿中的值。它不保留格式、图表、表或其他对象。

解决方案

在 OneDrive 中创建新的 Excel 文件。此示例使用文件名“Combination.xlsx”。
创建并保存此示例中的两个脚本。
在 OneDrive 中创建一个文件夹，并向其添加一个或多个包含数据的工作簿。此示例使用文件夹名称“output”。
按照本文的 Power Automate 流部分中所述生成流 (，) 执行以下步骤：
1. 列出“输出”文件夹的所有文件。
2. 使用 返回工作表数据 脚本从每个工作簿中的每个工作表获取数据。
3. 使用 “添加工作表 ”脚本在“Combination.xlsx”工作簿中为所有其他文件中的每个工作表创建新工作表。

示例代码：返回工作表数据

/**
 * This script returns the values from the used ranges on each worksheet.
 */
function main(workbook: ExcelScript.Workbook): WorksheetData[] {
  // Create an object to return the data from each worksheet.
  let worksheetInformation: WorksheetData[] = [];

  // Get the data from every worksheet, one at a time.
  workbook.getWorksheets().forEach((sheet) => {
    let values = sheet.getUsedRange()?.getValues();
    worksheetInformation.push({
       name: sheet.getName(),
       data: values as string[][]
    });
  });

  return worksheetInformation;
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

示例代码：添加工作表

/**
 * This script creates a new worksheet in the current workbook for each WorksheetData object provided.
 */
function main(workbook: ExcelScript.Workbook, workbookName: string, worksheetInformation: WorksheetData[]) {
  // Add each new worksheet.
  worksheetInformation.forEach((value) => {
    let sheet = workbook.addWorksheet(`${workbookName}.${value.name}`);

    // If there was any data in the worksheet, add it to a new range.
    if (value.data) {
      let range = sheet.getRangeByIndexes(0, 0, value.data.length, value.data[0].length);
      range.setValues(value.data);
    }
  });
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Power Automate 流：将工作表合并到单个工作簿中

登录到 Power Automate 并创建新的 即时云流。
选择 “手动触发流 ”，然后选择“ 创建”。
从文件夹中获取要合并的所有工作簿。添加操作并选择OneDrive for Business连接器的“在文件夹中列出文件”操作。对于 “文件夹” 字段，请使用文件选取器选择“输出”文件夹。
添加操作以运行 返回工作表数据 脚本，以获取每个工作簿中的所有数据。选择 Excel Online (Business) 连接器的 “运行脚本 ”操作。对操作使用以下值。请注意，添加文件的 ID 时，Power Automate 会将操作包装在 For each 控件中，因此将对每个文件执行该操作。
- 位置：OneDrive for Business
- 文档库：OneDrive
- 文件： id (文件夹) 中列出文件的 动态内容
- 脚本：返回工作表数据
添加操作以在创建的新 Excel 文件上运行 “添加工作表 ”脚本。这将添加所有其他工作簿中的数据。在上一 个“运行”脚本 操作之后，在 “For each ”控件中，添加一个使用 Excel Online (Business) 连接器的 “运行脚本 ”操作的操作。对操作使用以下值。
- 位置：OneDrive for Business
- 文档库：OneDrive
- 文件：“Combination.xlsx” (文件，由文件选取器)
- 脚本：添加工作表
- workbookName：名称 (文件夹) 中列出文件的 动态内容
- worksheetInformation (请参阅下一张图像) 的注释：运行脚本) 的结果 (动态内容
注意

选择“ 切换到输入整个数组 ”按钮，直接添加数组对象，而不是数组的单个项。在输入结果之前执行此操作。
保存流。流设计器应如下图所示。
使用流编辑器页上的“ 测试 ”按钮，或通过“我的流”选项卡运行流。请务必在出现提示时允许访问。
“Combination.xlsx”文件现在应包含新工作表。

疑难解答

已存在具有相同名称或标识符的资源：此错误可能表示“Combination.xlsx”工作簿已具有同名的工作表。如果使用相同的工作簿多次运行流，则会发生这种情况。每次创建一个新工作簿，以存储合并的数据或在“output”文件夹中使用不同的文件名。
参数无效或缺失或格式不正确：此错误可能意味着生成的工作表名称不符合 Excel 的要求。这可能是因为名称太长。如果工作表名称将超过 30 个字符，请将调用 addWorksheet 的“添加工作表”中的代码替换为缩短字符串的内容。由于工作簿名称本身可能太长，因此请在工作表名称的末尾添加递增数字。在循环外部 forEach 声明此数字。
```
let worksheetNumber = 1;
// Add each new worksheet.
worksheetInformation.forEach((value) => {
    let worksheetName = `${workbookName}.${value.name}`;
    let sheet = workbook.addWorksheet(`${worksheetName.substr(0,30)}${worksheetNumber++}`);
```
此外，如果工作簿名称长度超过 30 个字符，则需要在流中缩短它们。首先，必须在流中创建一个变量来跟踪工作簿计数。这将避免将相同的缩短名称传递给脚本。在流 (类型为“Integer”) 之前添加初始化变量操作，并在两个运行脚本操作之间添加递增变量操作。然后，使用表达式substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?['Name']),20))和变量中的动态内容，而不是在“运行脚本 1”中使用 Name 作为 workbookName。这会将工作簿名称缩短到 20 个字符，并将当前工作簿编号追加到要传递给脚本的字符串。

注意

与其使流和脚本更加复杂，不如保证文件和工作表名称足够短。

Share via

将工作表合并到单个工作簿中

解决方案

示例代码：返回工作表数据

示例代码：添加工作表

Power Automate 流：将工作表合并到单个工作簿中

疑难解答

反馈

其他资源