Работа с листами с использованием API JavaScript для Excel

  • Статья

В этой статье приведены примеры кода, в которых показано, как выполнять стандартные задачи для листов с использованием API JavaScript для Excel. Полный список свойств и методов, поддерживаемых объектами и, см. в Worksheet разделах Объект листа (API JavaScript для Excel) и Объект WorksheetCollection (API JavaScript для Excel).WorksheetCollection

Примечание.

Сведения в этой статье применимы только к обычным листам, а не к листам диаграмм или макросов.

Получение листов

В примере кода ниже показано, как возвратить коллекцию листов, загрузить свойство name каждого листа и записать сообщение в консоль.

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items/name");

    await context.sync();
    
    if (sheets.items.length > 1) {
        console.log(`There are ${sheets.items.length} worksheets in the workbook:`);
    } else {
        console.log(`There is one worksheet in the workbook:`);
    }

    sheets.items.forEach(function (sheet) {
        console.log(sheet.name);
    });
});

Примечание.

Свойство id листа однозначно идентифицирует лист в данной книге, и его значение останется неизменным даже при переименовании или перемещении листа. При удалении листа из книги в Excel на Mac id удаленный лист может быть переназначен новому листу, который будет создан впоследствии.

Получение активного листа

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

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.load("name");

    await context.sync();
    console.log(`The active worksheet is "${sheet.name}"`);
});

Задание активного листа

В следующем примере кода активному листу присваивается лист с именем Sample, загружается его name свойство и записывается сообщение в консоль. Если лист с таким именем отсутствует, activate() метод выдает ошибку ItemNotFound .

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.activate();
    sheet.load("name");

    await context.sync();
    console.log(`The active worksheet is "${sheet.name}"`);
});

Ссылка на листы по их относительным положениям

В примерах ниже показано, как ссылаться на лист по его относительному положению.

Получение первого листа

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

await Excel.run(async (context) => {
    let firstSheet = context.workbook.worksheets.getFirst();
    firstSheet.load("name");

    await context.sync();
    console.log(`The name of the first worksheet is "${firstSheet.name}"`);
});

Получение последнего листа

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

await Excel.run(async (context) => {
    let lastSheet = context.workbook.worksheets.getLast();
    lastSheet.load("name");

    await context.sync();
    console.log(`The name of the last worksheet is "${lastSheet.name}"`);
});

Получение следующего листа

Следующий пример кода возвращает лист, который следует за активным листом в книге, загружает его name свойство и записывает сообщение в консоль. Если после активного листа нет, getNext() метод выдает ошибку ItemNotFound .

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    let nextSheet = currentSheet.getNext();
    nextSheet.load("name");

    await context.sync();
    console.log(`The name of the sheet that follows the active worksheet is "${nextSheet.name}"`);
});

Получение предыдущего листа

Следующий пример кода получает лист, который предшествует активному листу в книге, загружает его name свойство и записывает сообщение в консоль. Если перед активным листом нет, getPrevious() метод выдает ошибку ItemNotFound .

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    let previousSheet = currentSheet.getPrevious();
    previousSheet.load("name");

    await context.sync();
    console.log(`The name of the sheet that precedes the active worksheet is "${previousSheet.name}"`);
});

Добавление листа

Следующий пример кода добавляет в книгу новый лист с именем Sample , загружает его name свойства и position , а затем записывает сообщение в консоль. Новый лист будет следовать за всеми остальными.

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;

    let sheet = sheets.add("Sample");
    sheet.load("name, position");

    await context.sync();
    console.log(`Added worksheet named "${sheet.name}" in position ${sheet.position}`);
});

Копирование существующего листа

Worksheet.copy добавляет новый лист, являющийся копией существующего листа. Имя нового листа будет содержать номер в конце по аналогии с копированием листов в пользовательском интерфейсе Excel (например, МойЛист (2)). Worksheet.copy может принимать два необязательных параметра:

  • positionType — перечисление WorksheetPositionType, указывающее, где в книге нужно добавить новый лист.
  • relativeTo — если параметру positionType присвоено значение Before или After, требуется указать лист, относительно которого нужно добавить новый лист (этот параметр отвечает на вопрос "До или после чего?").

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

await Excel.run(async (context) => {
    let myWorkbook = context.workbook;
    let sampleSheet = myWorkbook.worksheets.getActiveWorksheet();
    let copiedSheet = sampleSheet.copy(Excel.WorksheetPositionType.after, sampleSheet);
    await context.sync();
});

Удаление листа

В примере кода ниже показано, как удалить последний лист в книге (если это не единственный лист в книге) и записать сообщение в консоль.

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items/name");

    await context.sync();
    if (sheets.items.length === 1) {
        console.log("Unable to delete the only worksheet in the workbook");
    } else {
        let lastSheet = sheets.items[sheets.items.length - 1];

        console.log(`Deleting worksheet named "${lastSheet.name}"`);
        lastSheet.delete();

        await context.sync();
    }
});

Примечание.

Лист с уровнем скрытия "надежно скрыт" невозможно удалить с помощью метода delete. Чтобы удалить лист, нужно сперва изменить его уровень скрытия.

Переименование листа

В примере ниже показано, как изменить имя активного листа на New Name (Новое имя).

await Excel.run(async (context) => {
    let currentSheet = context.workbook.worksheets.getActiveWorksheet();
    currentSheet.name = "New Name";

    await context.sync();
});

Перемещение листа

В примере ниже показано, как переместить лист из последней позиции в книге на первую.

await Excel.run(async (context) => {
    let sheets = context.workbook.worksheets;
    sheets.load("items");
    await context.sync();

    let lastSheet = sheets.items[sheets.items.length - 1];
    lastSheet.position = 0;
    await context.sync();
});

Настройка видимости листа

В примерах ниже показано, как настроить видимость листа.

Скрытие листа

В примере кода ниже показано, как сделать лист Sample (Пример) скрытым, загрузить его свойство name и записать сообщение в консоль.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.visibility = Excel.SheetVisibility.hidden;
    sheet.load("name");

    await context.sync();
    console.log(`Worksheet with name "${sheet.name}" is hidden`);
});

Отмена скрытия листа

В примере кода ниже показано, как сделать лист Sample (Пример) видимым, загрузить его свойство name и записать сообщение в консоль.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.visibility = Excel.SheetVisibility.visible;
    sheet.load("name");

    await context.sync();
    console.log(`Worksheet with name "${sheet.name}" is visible`);
});

Получение одной ячейки листа

В примере кода ниже показано, как получить ячейку, расположенную в строке 2 и столбце 5 листа Sample (Пример), загрузить его свойства address и values и записать сообщение в консоль. Значения, передаваемые в метод getCell(row: number, column:number), представляют собой индексируемые с нуля номера строк и столбцов получаемой ячейки.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let cell = sheet.getCell(1, 4);
    cell.load("address, values");

    await context.sync();
    console.log(`The value of the cell in row 2, column 5 is "${cell.values[0][0]}" and the address of that cell is "${cell.address}"`);
});

Обнаружение изменений данных

Возможно, надстройке потребуется реагировать на изменения пользователями данных в листе. Чтобы обнаружить эти изменения, можно зарегистрировать обработчик событий для события onChanged листа. Обработчики события onChanged получают объект WorksheetChangedEventArgs при возникновении события.

Объект WorksheetChangedEventArgs предоставляет сведения об изменениях и источнике. Так как событие onChanged возникает при изменении формата или значения данных, может быть полезно, чтобы надстройка проверяла, действительно ли значения изменились. Свойство details объединяет эти сведения в виде интерфейса ChangedEventDetail. В следующем примере кода показано, как отобразить значения и типы измененной ячейки до и после изменения.

// This function would be used as an event handler for the Worksheet.onChanged event.
function onWorksheetChanged(eventArgs) {
    Excel.run(function (context) {
        let details = eventArgs.details;
        let address = eventArgs.address;

        // Print the before and after types and values to the console.
        console.log(`Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),`
            + ` now is ${details.valueAfter}(${details.valueTypeAfter})`);
        return context.sync();
    });
}

Обнаружение изменений формул

Надстройка может отслеживать изменения формул на листе. Это полезно, если лист подключен к внешней базе данных. При изменении формулы на листе событие в этом сценарии активирует соответствующие обновления во внешней базе данных.

Чтобы обнаружить изменения формул, зарегистрируйте обработчик событий для события onFormulaChanged листа. Обработчики событий для onFormulaChanged события получают объект WorksheetFormulaChangedEventArgs при срабатывании события.

Важно!

Событие onFormulaChanged обнаруживает, когда изменяется сама формула, а не значение данных, полученное в результате вычисления формулы.

В следующем примере кода показано, как зарегистрировать onFormulaChanged обработчик событий, использовать WorksheetFormulaChangedEventArgs объект для получения массива formulaDetails измененной формулы, а затем вывести сведения об измененной формуле с помощью свойств FormulaChangedEventDetail .

Примечание.

Этот пример кода работает только при изменении одной формулы.

async function run() {
    await Excel.run(async (context) => {
        // Retrieve the worksheet named "Sample".
        let sheet = context.workbook.worksheets.getItem("Sample");
    
        // Register the formula changed event handler for this worksheet.
        sheet.onFormulaChanged.add(formulaChangeHandler);
    
        await context.sync();
    });
}

async function formulaChangeHandler(event) {
    await Excel.run(async (context) => {
        // Retrieve details about the formula change event.
        // Note: This method assumes only a single formula is changed at a time. 
        let cellAddress = event.formulaDetails[0].cellAddress;
        let previousFormula = event.formulaDetails[0].previousFormula;
        let source = event.source;
    
        // Print out the change event details.
        console.log(
          `The formula in cell ${cellAddress} changed. 
          The previous formula was: ${previousFormula}. 
          The source of the change was: ${source}.`
        );         
    });
}

Обработка событий сортировки

События onColumnSorted и onRowSorted указывают на сортировку любых данных на листе. Эти события связаны с индивидуальными объектами Worksheet и с WorkbookCollection книги. Они срабатывают при выполнении сортировки (программным образом или вручную с помощью пользовательского интерфейса Excel).

Примечание.

Событие onColumnSorted срабатывает при сортировке столбцов в результате операции сортировки слева направо. Событие onRowSorted срабатывает при сортировке строк в результате операции сортировки сверху вниз. Сортировка таблицы с помощью раскрывающегося меню в заголовке столбца приводит к срабатыванию события onRowSorted. Событие соответствует перемещаемым данным, а не критериям сортировки.

События onColumnSorted и onRowSorted реализуют функции обратного вызова соответственно с помощью объектов WorksheetColumnSortedEventArgs и WorksheetRowSortedEventArgs. Эти объекты предоставляют более подробную информацию о событии. В частности, оба EventArgs обладают свойством address, которое представляет строки или столбцы, перемещенные в результате операции сортировки. Включаются все ячейки, содержимое которых было отсортировано, даже если значение ячейки не входит в состав критериев сортировки.

На приведенных ниже рисунках показаны диапазоны, возвращенные свойством address для событий сортировки. Вот образец данных до сортировки:

Данные таблицы в Excel перед сортировкой.

Если сортировка сверху вниз выполняется для "Q1" (значения в "B"), следующие выделенные строки возвращаются WorksheetRowSortedEventArgs.address.

Данные таблицы в Excel после сортировки сверху вниз. Перемещенные строки выделены.

Если сортировка слева направо выполняется для "Quinces" (значения в "4") исходных данных, следующие выделенные столбцы возвращаются WorksheetColumnsSortedEventArgs.address.

Таблица данных в Excel после сортировки слева направо. Столбцы, которые были перемещены, выделены.

В приведенном ниже примере кода показано, как зарегистрировать обработчик событий для события Worksheet.onRowSorted. Обратный вызов обработчика очищает цвет заливки для диапазона, затем применяет заливку к ячейкам перемещенных строк.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // This will fire whenever a row has been moved as the result of a sort action.
    sheet.onRowSorted.add(async (event) => {
        await Excel.run(async (context) => {
            console.log("Row sorted: " + event.address);
            let sheet = context.workbook.worksheets.getActiveWorksheet();

            // Clear formatting for section, then highlight the sorted area.
            sheet.getRange("A1:E5").format.fill.clear();
            if (event.address !== "") {
                sheet.getRanges(event.address).format.fill.color = "yellow";
            }

            await context.sync();
        });
    });

    await context.sync();
});

Поиск всех ячеек с соответствующим текстом

У объекта Worksheet есть метод findAll для поиска указанной строки в листе. Он возвращает объект RangeAreas, являющийся коллекцией объектов Range, которые можно отредактировать все сразу.

Приведенный ниже пример кода находит все ячейки со значениями, соответствующими строке Complete (Завершено), и окрашивает их зеленым цветом. Обратите внимание, что findAll выдает ошибку ItemNotFound , если указанная строка не существует на листе. Если вы не знаете, существует ли указанная строка на листе, используйте метод findAllOrNullObject для корректной обработки этого сценария.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let foundRanges = sheet.findAll("Complete", {
        completeMatch: true, /* Match the whole cell value, not any part of the text. */
        matchCase: false /* Make the search case-insensitive. */
    });

    await context.sync();
    foundRanges.format.fill.color = "green";
});

Примечание.

В этом разделе описывается поиск ячеек и диапазонов с помощью Worksheet методов объекта. Дополнительные сведения об извлечении диапазонов можно найти в статьях о конкретных объектах.

Фильтрация данных

Объект AutoFilter применяет фильтры данных в диапазоне на листе. Он создается с Worksheet.autoFilter.applyпараметром , который имеет следующие параметры.

  • range: диапазон, к которому применяется фильтр, указанный в виде объекта Range или строки.
  • columnIndex: отсчитываемый от нуля индекс столбца, по которому оценивается условие фильтра.
  • criteria: объект FilterCriteria, определяющий, какие строки следует фильтровать на основе ячейки столбца.

В первом примере кода показано, как добавить фильтр в используемый диапазон на листе. Этот фильтр скрывает записи, не входящие в верхние 25 %, на основе значений в столбце 3.

// This method adds a custom AutoFilter to the active worksheet
// and applies the filter to a column of the used range.
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let farmData = sheet.getUsedRange();

    // This filter will only show the rows with the top 25% of values in column 3.
    sheet.autoFilter.apply(farmData, 3, { criterion1: "25", filterOn: Excel.FilterOn.topPercent });
    await context.sync();
});

В следующем примере кода показано, как обновить автофильтр, используя метод reapply. Это следует выполнять при изменении данных в диапазоне.

// This method refreshes the AutoFilter to ensure that changes are captured.
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.autoFilter.reapply();
    await context.sync();
});

В следующем примере кода показано, как использовать clearColumnCriteria метод для очистки автофильтра только из одного столбца, оставляя фильтр активным для других столбцов.

// This method clears the AutoFilter setting from one column.
await Excel.run(async (context) => {
    // Retrieve the active worksheet.
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // Clear the filter from only column 3.
    sheet.autoFilter.clearColumnCriteria(3);
    await context.sync();
});

В последнем примере кода автофильтра показано, как удалить автофильтр с листа с помощью метода remove.

// This method removes all AutoFilters from the active worksheet.
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.autoFilter.remove();
    await context.sync();
});

Объект AutoFilter также можно применять к отдельным таблицам. Дополнительные сведения см. в статье Работа с таблицами с использованием API JavaScript для Excel.

Защита данных

Надстройка может управлять возможностью пользователя по изменению данных на листе. Свойство protection листа является объектом WorksheetProtection с методом protect(). В приведенном ниже примере показан основной сценарий переключения полной защиты активного листа.

await Excel.run(async (context) => {
    let activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.load("protection/protected");
    await context.sync();

    if (!activeSheet.protection.protected) {
        activeSheet.protection.protect();
    }
});

Метод protect содержит два необязательных параметра:

  • options: объект WorksheetProtectionOptions, определяющий конкретные ограничения на редактирование.
  • password: строка, представляющая пароль, необходимый пользователю для обхода защиты и редактирования листа.

В статье Защита листа содержатся дополнительные сведения о защите листа и ее изменении с помощью пользовательского интерфейса Excel.

Обнаружение изменений в состоянии защиты листа

Состояние защиты листа может быть изменено надстройкой или с помощью пользовательского интерфейса Excel. Чтобы обнаружить изменения в состоянии защиты, зарегистрируйте обработчик событий для onProtectionChanged события листа. Обработчики событий для onProtectionChanged события получают объект при WorksheetProtectionChangedEventArgs срабатывании события.

В следующем примере кода показано, как зарегистрировать onProtectionChanged обработчик события и использовать WorksheetProtectionChangedEventArgs объект для получения isProtectedсвойств , worksheetIdи source события.

// This function registers an event handler for the onProtectionChanged event of a worksheet.
async function run() {
    await Excel.run(async (context) => {
        // Retrieve the worksheet named "Sample".
        let sheet = context.workbook.worksheets.getItem("Sample");
    
        // Register the onProtectionChanged event handler.
        sheet.onProtectionChanged.add(checkProtection);
        await context.sync();
    });
}

// This function is an event handler that returns the protection state of a worksheet 
// and information about the changed worksheet.
async function checkProtection(event) {
    await Excel.run(async (context) => {
        // Retrieve the protection, worksheet ID, and source properties of the event.
        let protectionStatus = event.isProtected;
        let worksheetId = event.worksheetId;
        let source = event.source;

        // Print the event properties to the console.
        console.log("Protection status changed. Protection status is now: " + protectionStatus);
        console.log("    ID of changed worksheet: " + worksheetId);
        console.log("    Source of change event: " + source);    
    });
}

Параметры разметки страницы и печати

Надстройкам доступны параметры разметки страницы на уровне листа. Они управляют печатью листа. У объекта Worksheet есть три связанных с разметкой свойства: horizontalPageBreaks, verticalPageBreaks, pageLayout.

Worksheet.horizontalPageBreaks и Worksheet.verticalPageBreaks относятся к PageBreakCollections. Это коллекции объектов PageBreak, указывающих диапазоны вставки разрывов страниц, добавляемых вручную. В следующем примере кода добавляется горизонтальный разрыв страницы над строкой 21.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.horizontalPageBreaks.add("A21:E21"); // The page break is added above this range.
    await context.sync();
});

Worksheet.pageLayout является объектом PageLayout. Этот объект содержит параметры разметки и печати, не зависящие от применения конкретного принтера. Эти параметры включают поля, ориентацию, нумерацию страницы, строки заголовков и область печати.

В следующем примере кода страница выравнивается по центру (по вертикали и горизонтали), устанавливается строка заголовка, которая печатается в верхней части каждой страницы, и задается подраздел листа в качестве области печати.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();

    // Center the page in both directions.
    sheet.pageLayout.centerHorizontally = true;
    sheet.pageLayout.centerVertically = true;

    // Set the first row as the title row for every page.
    sheet.pageLayout.setPrintTitleRows("$1:$1");

    // Limit the area to be printed to the range "A1:D100".
    sheet.pageLayout.setPrintArea("A1:D100");

    await context.sync();
});

См. также