Объединение нескольких книг в одну

Статья
11/29/2023

В этом примере показано, как извлечь данные из нескольких книг в одну централизованную книгу. Он использует два сценария: один для получения сведений из книги, а другой для создания новых листов с этой информацией. Он объединяет скрипты в потоке Power Automate, который действует во всей папке OneDrive.

Важно!

Этот пример копирует только значения из других книг. Он не сохраняет форматирование, диаграммы, таблицы и другие объекты.

Решение

Создайте файл Excel в OneDrive. В этом примере используется имя файла "Combination.xlsx".
Создайте и сохраните два скрипта из этого примера.
Создайте папку в OneDrive и добавьте в нее одну или несколько книг с данными. В этом примере используется имя папки output.
Создайте поток (как описано в разделе потока Power Automate этой статьи), чтобы выполнить следующие действия:
1. Выведите список всех файлов в папке output.
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 для бизнеса. В поле Папка используйте средство выбора файлов, чтобы выбрать папку output.
Добавьте действие для выполнения скрипта данных возвращаемого листа , чтобы получить все данные из каждой книги. Выберите действие Выполнить скрипт соединителя Excel Online (бизнес). Используйте следующие значения для действия. Обратите внимание, что при добавлении идентификатора для файла Power Automate заключит действие в элемент управления For each , поэтому действие будет выполняться для каждого файла.
- Расположение: OneDrive для бизнеса
- Библиотека документов: OneDrive
- File: Id (динамическое содержимое из списка файлов в папке)
- Скрипт: возврат данных листа
Добавьте действие для запуска скрипта Добавить листы в созданный файл Excel. При этом будут добавлены данные из всех остальных книг. После предыдущего действия Выполнить скрипт и внутри элемента управления For each добавьте действие, которое использует действие Выполнить скрипт соединителя Excel Online (бизнес). Используйте следующие значения для действия.
- Расположение: OneDrive для бизнеса
- Библиотека документов: OneDrive
- Файл: "Combination.xlsx" (ваш файл, выбранный с помощью средства выбора файлов)
- Скрипт: добавление листов
- workbookName: Имя (динамическое содержимое из списка файлов в папке)
- worksheetInformation (см. примечание после следующего изображения): result (динамическое содержимое из скрипта запуска)
Примечание.

Нажмите кнопку Переключиться на ввод всего массива , чтобы добавить объект массива напрямую вместо отдельных элементов массива. Сделайте это перед вводом результата.
Сохраните поток. Конструктор потоков должен выглядеть так, как показано на следующем рисунке.
Нажмите кнопку Тестировать на странице редактора потоков или запустите поток через вкладку Мои потоки . Обязательно разрешите доступ при появлении запроса.
Теперь файл "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") и действие инкремент переменной между двумя действиями Выполнить скрипт . Затем вместо того, чтобы использовать Имя в качестве имени книги в "Выполнить скрипт 1", используйте выражение substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?['Name']),20)) и динамическое содержимое из переменной. Это сокращает имена книг до 20 символов и добавляет текущий номер книги к строке, передаваемой в скрипт.

Примечание.

Вместо того, чтобы усложнять поток и скрипт, проще гарантировать, что имена файлов и листов будут достаточно короткими.

Поделиться через