Запись большого набора данных

Range.setValues() API помещает данные в диапазон. Этот API имеет ограничения в зависимости от различных факторов, таких как размер данных и параметры сети. Это означает, что если вы попытаетесь записать большой объем информации в книгу в виде одной операции, вам потребуется записать данные небольшими пакетами, чтобы надежно обновить большой диапазон.

В первой части примера показано, как создать большой набор данных в Excel. Во второй части пример расширяется, чтобы он был частью потока Power Automate. Это необходимо, если выполнение скрипта занимает больше времени, чем время ожидания действия Power Automate.

Основные сведения о производительности в сценариях Office см. в статье Повышение производительности сценариев Office.

Пример 1. Создание большого набора данных пакетами

Этот скрипт записывает строки диапазона в меньшие части. Он выбирает 1000 ячеек для записи за раз. Запустите скрипт на пустом листе, чтобы увидеть пакеты обновлений в действии. Выходные данные консоли дают дополнительные сведения о том, что происходит.

Примечание.

Количество записываемых строк можно изменить, изменив значение SAMPLE_ROWS. Число ячеек, записываемых как одно действие, можно изменить, изменив значение CELLS_IN_BATCH.

function main(workbook: ExcelScript.Workbook) {
  const SAMPLE_ROWS = 100000;
  const CELLS_IN_BATCH = 10000;

  // Get the current worksheet.
  const sheet = workbook.getActiveWorksheet();

  console.log(`Generating data...`)
  let data: (string | number | boolean)[][] = [];
  // Generate six columns of random data per row. 
  for (let i = 0; i < SAMPLE_ROWS; i++) {
    data.push([i, ...[getRandomString(5), getRandomString(20), getRandomString(10), Math.random()], "Sample data"]);
  }

  console.log(`Calling update range function...`);
  const updated = updateRangeInBatches(sheet.getRange("B2"), data, CELLS_IN_BATCH);
  if (!updated) {
    console.log(`Update did not take place or complete. Check and run again.`);
  }
}

function updateRangeInBatches(
  startCell: ExcelScript.Range,
  values: (string | boolean | number)[][],
  cellsInBatch: number
): boolean {

  const startTime = new Date().getTime();
  console.log(`Cells per batch setting: ${cellsInBatch}`);

  // Determine the total number of cells to write.
  const totalCells = values.length * values[0].length;
  console.log(`Total cells to update in the target range: ${totalCells}`);
  if (totalCells <= cellsInBatch) {
    console.log(`No need to batch -- updating directly`);
    updateTargetRange(startCell, values);
    return true;
  }

  // Determine how many rows to write at once.
  const rowsPerBatch = Math.floor(cellsInBatch / values[0].length);
  console.log("Rows per batch: " + rowsPerBatch);
  let rowCount = 0;
  let totalRowsUpdated = 0;
  let batchCount = 0;

  // Write each batch of rows.
  for (let i = 0; i < values.length; i++) {
    rowCount++;
    if (rowCount === rowsPerBatch) {
      batchCount++;
      console.log(`Calling update next batch function. Batch#: ${batchCount}`);
      updateNextBatch(startCell, values, rowsPerBatch, totalRowsUpdated);

      // Write a completion percentage to help the user understand the progress.
      rowCount = 0;
      totalRowsUpdated += rowsPerBatch;
      console.log(`${((totalRowsUpdated / values.length) * 100).toFixed(1)}% Done`);
    }
  }
  
  console.log(`Updating remaining rows -- last batch: ${rowCount}`)
  if (rowCount > 0) {
    updateNextBatch(startCell, values, rowCount, totalRowsUpdated);
  }

  let endTime = new Date().getTime();
  console.log(`Completed ${totalCells} cells update. It took: ${((endTime - startTime) / 1000).toFixed(6)} seconds to complete. ${((((endTime  - startTime) / 1000)) / cellsInBatch).toFixed(8)} seconds per ${cellsInBatch} cells-batch.`);

  return true;
}

/**
 * A helper function that computes the target range and updates. 
 */
function updateNextBatch(
  startingCell: ExcelScript.Range,
  data: (string | boolean | number)[][],
  rowsPerBatch: number,
  totalRowsUpdated: number
) {
  const newStartCell = startingCell.getOffsetRange(totalRowsUpdated, 0);
  const targetRange = newStartCell.getResizedRange(rowsPerBatch - 1, data[0].length - 1);
  console.log(`Updating batch at range ${targetRange.getAddress()}`);
  const dataToUpdate = data.slice(totalRowsUpdated, totalRowsUpdated + rowsPerBatch);
  try {
    targetRange.setValues(dataToUpdate);
  } catch (e) {
    throw `Error while updating the batch range: ${JSON.stringify(e)}`;
  }
  return;
}

/**
 * A helper function that computes the target range given the target range's starting cell
 * and selected range and updates the values.
 */
function updateTargetRange(
  targetCell: ExcelScript.Range,
  values: (string | boolean | number)[][]
) {
  const targetRange = targetCell.getResizedRange(values.length - 1, values[0].length - 1);
  console.log(`Updating the range: ${targetRange.getAddress()}`);
  try {
    targetRange.setValues(values);
  } catch (e) {
    throw `Error while updating the whole range: ${JSON.stringify(e)}`;
  }
  return;
}

// Credit: https://www.codegrepper.com/code-examples/javascript/random+text+generator+javascript
function getRandomString(length: number): string {
  var randomChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  var result = '';
  for (var i = 0; i < length; i++) {
    result += randomChars.charAt(Math.floor(Math.random() * randomChars.length));
  }
  return result;
}

Обучающее видео: создание большого набора данных

Посмотрите Судхи Рамамурти, пройдите этот пример на YouTube.

Пример 2. Запись данных пакетами из потока Power Automate

Для этого примера необходимо выполнить следующие действия.

1. Создайте книгу в OneDrive с именем SampleData.xlsx.
2. Создайте вТорую книгу в OneDrive с именемTargetWorkbook.xlsx.
3. Откройте SampleData.xlsx в Excel.
4. Добавьте пример данных. Для создания этих данных можно использовать скрипт из раздела Запись большого набора данных в пакетах .
5. Создайте и сохраните оба следующих скрипта. Используйте команду Автоматизировать>новый скрипт , чтобы вставить код и сохранить скрипты с предложенными именами.
6. Выполните действия, описанные в разделе Поток Power Automate: чтение и запись данных в цикле , чтобы создать поток.

Пример кода: чтение выбранных строк

function main(
  workbook: ExcelScript.Workbook, 
  startRow: number, 
  batchSize: number
): string[][] {
  // This script only reads the first worksheet in the workbook.
  const sheet = workbook.getWorksheets()[0];

  // Get the boundaries of the range.
  // Note that we're assuming usedRange is too big to read or write as a single range.
  const usedRange = sheet.getUsedRange();
  const lastColumnIndex = usedRange.getLastColumn().getColumnIndex();
  const lastRowindex = usedRange.getLastRow().getRowIndex();

  // If we're starting past the last row, exit the script.
  if (startRow > lastRowindex) {
      return [[]];
  }

  // Get the next batch or the rest of the rows, whichever is smaller.
  const rowCountToRead = Math.min(batchSize, (lastRowindex - startRow + 1));
  const rangeToRead = sheet.getRangeByIndexes(startRow, 0, rowCountToRead, lastColumnIndex + 1);
  return rangeToRead.getValues() as string[][];
}

Пример кода: запись данных по расположению строки

function main(
  workbook: ExcelScript.Workbook, 
  data: string[][], 
  currentRow: number, 
  batchSize: number
): boolean {
  // Get the first worksheet.
  const sheet = workbook.getWorksheets()[0];

  // Set the given data.
  if (data && data.length > 0) {
    sheet.getRangeByIndexes(currentRow, 0, data.length, data[0].length).setValues(data);
  }

  // If the script wrote less data than the batch size, signal the end of the flow.
  return batchSize > data.length;
}

Поток Power Automate: чтение и запись данных в цикле

1. Войдите в Power Automate и создайте мгновенный облачный поток.

2. Выберите Вручную активировать поток и нажмите кнопку Создать.

3. Создайте переменную для отслеживания текущей строки, считываемой и записываемой. В построителе потоков нажмите кнопку + и добавьте действие. Выберите действие Инициализировать переменную и присвойте ему следующие значения.

   • Имя: currentRow
   • Тип: Целое число
   • Значение: 0

   

4. Добавьте действие, чтобы задать количество строк для чтения в одном пакете. В зависимости от количества столбцов это значение может быть меньше, чтобы избежать ограничений на передачу данных. Создайте новое действие Инициализация переменной со следующими значениями.

   • Имя: batchSize
   • Тип: Целое число
   • Значение: 10000

   

5. Добавьте элемент управления Do until . Поток будет считывать фрагменты данных до тех пор, пока все они не будут скопированы. Вы будете использовать значение -1 , чтобы указать, что конец данных достигнут. Присвойте элементу управления следующие значения.

   • Выберите значениеcurrentRow (динамическое содержимое)
   • равно (из раскрывающегося списка)
   • Выберите значение: -1

   

6. Остальные шаги добавляются в элемент управления Do . Затем вызовите скрипт для чтения данных. Добавьте действие Выполнить скрипт соединителя Excel Online (бизнес). Переименуйте его в Чтение данных. Используйте следующие значения для действия.

   • Расположение: OneDrive для бизнеса
   • Библиотека документов: OneDrive
   • Файл: "SampleData.xlsx" (выбранный в средства выбора файлов)
   • Скрипт: чтение выбранных строк
   • startRow: currentRow (динамическое содержимое)
   • batchSize: batchSize (динамическое содержимое)

   

7. Вызовите скрипт для записи данных. Добавьте второе действие Выполнить скрипт . Переименуйте его в Запись данных. Используйте следующие значения для действия.

   • Расположение: OneDrive для бизнеса
   • Библиотека документов: OneDrive
   • Файл: "TargetWorkbook.xlsx" (выбранный в средства выбора файлов)
   • Скрипт. Запись данных в строке
   • data: result (динамическое содержимое из данных чтения)
     • Сначала нажмите переключить входные данные для всего массива .
   • startRow: currentRow (динамическое содержимое)
   • batchSize: batchSize (динамическое содержимое)

   

8. Обновите текущую строку, чтобы она отражала, что пакет данных был прочитан и записан. Добавьте действие переменной increment со следующими значениями.

   • Имя: currentRow
   • Значение: batchSize (динамическое содержимое)

   

9. Добавьте элемент управления Условие для проверка, если скрипты считывают все. Скрипт "Запись данных по расположению строк" возвращает значение true, если в нем записано меньше строк, чем позволяет размер пакета. Это означает, что он находится в конце набора данных. Создайте действие элемента управления Условие со следующими значениями.

   • Выберите значение: result (динамическое содержимое из записи данных)
   • равно (из раскрывающегося списка)
   • Выберите значение: true (выражение)

   

10. В разделе True элемента управления Условие задайте для переменной currentRowзначение -1. Добавьте действие Задать переменную со следующими значениями.

    • Имя: currentRow
    • Значение: -1

    

11. Сохраните поток. Конструктор потоков должен выглядеть так, как показано на следующем рисунке.

    

12. Нажмите кнопку Тестировать на странице редактора потоков или запустите поток через вкладку Мои потоки . Обязательно разрешите доступ при появлении запроса.

13. Теперь файл "TargetWorkbook.xlsx" должен содержать данные из "SampleData.xlsx".