Запись большого набора данных
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
Для этого примера необходимо выполнить следующие действия.
- Создайте книгу в OneDrive с именем SampleData.xlsx.
- Создайте вТорую книгу в OneDrive с именемTargetWorkbook.xlsx.
- Откройте SampleData.xlsx в Excel.
- Добавьте пример данных. Для создания этих данных можно использовать скрипт из раздела Запись большого набора данных в пакетах .
- Создайте и сохраните оба следующих скрипта. Используйте команду Автоматизировать>новый скрипт , чтобы вставить код и сохранить скрипты с предложенными именами.
- Выполните действия, описанные в разделе Поток 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: чтение и запись данных в цикле
Войдите в Power Automate и создайте мгновенный облачный поток.
Выберите Вручную активировать поток и нажмите кнопку Создать.
Создайте переменную для отслеживания текущей строки, считываемой и записываемой. В построителе потоков нажмите кнопку + и добавьте действие. Выберите действие Инициализировать переменную и присвойте ему следующие значения.
- Имя: currentRow
- Тип: Целое число
- Значение: 0
Добавьте действие, чтобы задать количество строк для чтения в одном пакете. В зависимости от количества столбцов это значение может быть меньше, чтобы избежать ограничений на передачу данных. Создайте новое действие Инициализация переменной со следующими значениями.
- Имя: batchSize
- Тип: Целое число
- Значение: 10000
Добавьте элемент управления Do until . Поток будет считывать фрагменты данных до тех пор, пока все они не будут скопированы. Вы будете использовать значение -1 , чтобы указать, что конец данных достигнут. Присвойте элементу управления следующие значения.
- Выберите значениеcurrentRow (динамическое содержимое)
- равно (из раскрывающегося списка)
- Выберите значение: -1
Остальные шаги добавляются в элемент управления Do . Затем вызовите скрипт для чтения данных. Добавьте действие Выполнить скрипт соединителя Excel Online (бизнес). Переименуйте его в Чтение данных. Используйте следующие значения для действия.
- Расположение: OneDrive для бизнеса
- Библиотека документов: OneDrive
- Файл: "SampleData.xlsx" (выбранный в средства выбора файлов)
- Скрипт: чтение выбранных строк
- startRow: currentRow (динамическое содержимое)
- batchSize: batchSize (динамическое содержимое)
Вызовите скрипт для записи данных. Добавьте второе действие Выполнить скрипт . Переименуйте его в Запись данных. Используйте следующие значения для действия.
- Расположение: OneDrive для бизнеса
- Библиотека документов: OneDrive
- Файл: "TargetWorkbook.xlsx" (выбранный в средства выбора файлов)
- Скрипт. Запись данных в строке
- data: result (динамическое содержимое из данных чтения)
- Сначала нажмите переключить входные данные для всего массива .
- startRow: currentRow (динамическое содержимое)
- batchSize: batchSize (динамическое содержимое)
Обновите текущую строку, чтобы она отражала, что пакет данных был прочитан и записан. Добавьте действие переменной increment со следующими значениями.
- Имя: currentRow
- Значение: batchSize (динамическое содержимое)
Добавьте элемент управления Условие для проверка, если скрипты считывают все. Скрипт "Запись данных по расположению строк" возвращает значение true, если в нем записано меньше строк, чем позволяет размер пакета. Это означает, что он находится в конце набора данных. Создайте действие элемента управления Условие со следующими значениями.
- Выберите значение: result (динамическое содержимое из записи данных)
- равно (из раскрывающегося списка)
- Выберите значение: true (выражение)
В разделе True элемента управления Условие задайте для переменной currentRowзначение -1. Добавьте действие Задать переменную со следующими значениями.
- Имя: currentRow
- Значение: -1
Сохраните поток. Конструктор потоков должен выглядеть так, как показано на следующем рисунке.
Нажмите кнопку Тестировать на странице редактора потоков или запустите поток через вкладку Мои потоки . Обязательно разрешите доступ при появлении запроса.
Теперь файл "TargetWorkbook.xlsx" должен содержать данные из "SampleData.xlsx".
Office Scripts