Работа с листами с использованием 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);
    });
});

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

В примере кода ниже показано, как получить активный лист, загрузить его свойство 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 (например, MySheet (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();
    }
});

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

В примере ниже показано, как изменить имя активного листа на 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 обработчик событий, использовать 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 реализуют функции обратного вызова соответственно с помощью объектов WorksheetColumnSortedEventArgs и WorksheetRowSortedEventArgs. Эти объекты предоставляют дополнительные сведения о событии. В частности, оба EventArgs обладают свойством address, которое представляет строки или столбцы, перемещенные в результате операции сортировки. Свойство включает любую ячейку с отсортированным содержимым, даже если значение этой ячейки не входит в критерии сортировки.

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

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

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

В приведенном ниже примере кода показано, как зарегистрировать обработчик событий для события 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 можно работать как группа.

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

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let foundRanges = sheet.findAll("Complete", {
        completeMatch: true, // Match the whole cell value.
        matchCase: false // Case-insensitive search.
    });

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

Дополнительные параметры поиска, включая поиск с первым совпадением, частичным совпадением и с учетом регистра, см. в разделе Поиск строк на листе Excel.

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

Объект 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);    
    });
}

См. также

• Объектная модель JavaScript для Excel в надстройках Office