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

  • Статья

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

Объект Workbook — это точка входа для вашей надстройки для взаимодействия с Excel. Он поддерживает коллекции листов, таблиц, сводных таблиц и других элементов, через которые выполняется доступ и изменение данных Excel. Объект WorksheetCollection предоставляет надстройке доступ ко всем данным книги с помощью отдельных листов. В частности, он позволяет надстройке добавлять листы, перемещаться между ними и назначать обработчиков событий листа. В статье Работа с листами с использованием API JavaScript для Excel описывается способ доступа к листам и их изменение.

Получение активной ячейки или выделенного диапазона

Объект Workbook содержит два метода для получения диапазона ячеек, выделенных пользователем или надстройкой: getActiveCell() и getSelectedRange(). getActiveCell() получает активную ячейку из книги в виде объекта Range. В приведенном ниже примере показан вызов getActiveCell() с последующей печатью адреса ячейки в консоль.

await Excel.run(async (context) => {
    let activeCell = context.workbook.getActiveCell();
    activeCell.load("address");
    await context.sync();

    console.log("The active cell is " + activeCell.address);
});

Метод getSelectedRange() возвращает один диапазон, выделенный в настоящее время. Если выделено несколько диапазонов, возникает ошибка InvalidSelection. В приведенном ниже примере показан вызов метода getSelectedRange(), который затем устанавливает желтый цвет заливки для диапазона.

await Excel.run(async (context) => {
    let range = context.workbook.getSelectedRange();
    range.format.fill.color = "yellow";
    await context.sync();
});

Создание книги

Ваша надстройка может создать новую книгу, отдельную от экземпляра Excel, в котором в настоящее время работает надстройка. Для этой цели в объекте Excel имеется метод createWorkbook. При вызове этого метода сразу открывается и отображается новая книга в новом экземпляре программы Excel. Ваша надстройка остается открытой и запущенной в предыдущей книге.

Excel.createWorkbook();

С помощью метода createWorkbook также можно создать копию существующей книги. Метод принимает в качестве необязательного параметра строковое представление XLSX-файла в кодировке base64. Полученная книга будет копией этого файла, предполагая, что строковый аргумент является допустимым XLSX-файлом.

Текущую книгу надстройки можно получить в виде строки в кодировке Base64 с помощью срезов файлов. Преобразование файла в нужную строку в кодировке base64 можно выполнить с помощью класса FileReader, как показано в приведенном ниже примере.

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (function (event) {
    Excel.run(function (context) {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);

        Excel.createWorkbook(externalWorkbook);
        return context.sync();
    });
});

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

Вставка копии существующей книги в текущую книгу.

В предыдущем примере показана новая книга, которая была создана из существующей книги. Вы также можете скопировать отдельные части или всю существующую книгу целиком в книгу, привязанную в настоящее время к вашей надстройке. Книга имеет insertWorksheetsFromBase64 метод для вставки в нее копий листов целевой книги. Файл другой книги передается как строка в кодировке Base64, как и Excel.createWorkbook вызов.

insertWorksheetsFromBase64(base64File: string, options?: Excel.InsertWorksheetOptions): OfficeExtension.ClientResult<string[]>;

Важно!

Метод insertWorksheetsFromBase64 поддерживается для Excel в Интернете, в Windows и на Mac. Он не поддерживается для iOS. Кроме того, в Excel в Интернете этот метод не поддерживает исходные листы с элементами сводной таблицы, диаграммы, комментария или среза. Если эти объекты присутствуют, insertWorksheetsFromBase64 метод возвращает ошибку UnsupportedFeature в Excel в Интернете.

В следующем примере кода показано, как вставить листы из другой книги в текущую книгу. Этот пример кода сначала обрабатывает файл книги с FileReader объектом и извлекает строку в кодировке base64, а затем вставляет эту строку в кодировке Base64 в текущую книгу. Новые листы вставляются после листа с именем Лист1. Обратите внимание, что [] передается в качестве параметра для свойства InsertWorksheetOptions.sheetNamesToInsert . Это означает, что все листы из целевой книги вставляются в текущую книгу.

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (event) => {
    Excel.run((context) => {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);
            
        // Retrieve the current workbook.
        let workbook = context.workbook;
            
        // Set up the insert options. 
        let options = { 
            sheetNamesToInsert: [], // Insert all the worksheets from the source workbook.
            positionType: Excel.WorksheetPositionType.after, // Insert after the `relativeTo` sheet.
            relativeTo: "Sheet1" // The sheet relative to which the other worksheets will be inserted. Used with `positionType`.
        }; 
            
         // Insert the new worksheets into the current workbook.
         workbook.insertWorksheetsFromBase64(externalWorkbook, options);
         return context.sync();
    });
};

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

Защита структуры книги

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

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

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

Метод protect принимает необязательный строковый параметр. Эта строка представляет пароль, необходимый пользователю для обхода защиты и изменения структуры книги.

Защиту также можно установить на уровне книги, чтобы предотвратить нежелательные изменения данных. Дополнительные сведения см. в разделе Защита данных статьи Работа с листами с использованием API JavaScript для Excel.

Примечание.

Дополнительные сведения о защите книги в Excel см. в статье Защита книги.

Доступ к свойствам документов

Объекты Workbook имеют доступ к метаданным файлов Office, называемым свойствами документов. Свойство объекта properties Workbook — это объект DocumentProperties , содержащий некоторые из этих значений метаданных. В следующем примере показано, как задать author свойство .

await Excel.run(async (context) => {
    let docProperties = context.workbook.properties;
    docProperties.author = "Alex";
    await context.sync();
});

Также можно установить настраиваемые свойства. Объект DocumentProperties содержит свойство custom, представляющее коллекцию пар "ключ-значение" для свойств, определяемых пользователем. Пример настройки пользовательских свойств см. в разделе Пользовательские XML-данные в Excel и Word статьи Сохранение состояния и параметров надстройки.

Доступ к параметрам документа

Параметры книги похожи на коллекцию настраиваемых свойств. Различие заключается в том, что параметры уникальны для одного файла Excel и соединения надстройки, а свойства связаны только с файлом. В приведенном ниже примере показано, как создать параметр и получить к нему доступ.

await Excel.run(async (context) => {
    let settings = context.workbook.settings;
    settings.add("NeedsReview", true);
    let needsReview = settings.getItem("NeedsReview");
    needsReview.load("value");

    await context.sync();
    console.log("Workbook needs review : " + needsReview.value);
});

Доступ к параметрам языка и региональных параметров приложения

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

Application.cultureInfo определяет параметры системного языка и региональных параметров в виде объекта CultureInfo . Он содержит такие параметры, как числовой разделитель десятичных разрядов или формат даты.

Некоторые параметры и региональные параметры можно изменить с помощью пользовательского интерфейса Excel. Системные параметры сохраняются в объекте CultureInfo . Все локальные изменения сохраняются как свойства уровня приложения, например Application.decimalSeparator.

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

// This will convert a number like "14,37" to "14.37"
// (assuming the system decimal separator is ".").
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let decimalSource = sheet.getRange("B2");

    decimalSource.load("values");
    context.application.cultureInfo.numberFormat.load("numberDecimalSeparator");
    await context.sync();

    let systemDecimalSeparator =
        context.application.cultureInfo.numberFormat.numberDecimalSeparator;
    let oldDecimalString = decimalSource.values[0][0];

    // This assumes the input column is standardized to use "," as the decimal separator.
    let newDecimalString = oldDecimalString.replace(",", systemDecimalSeparator);

    let resultRange = sheet.getRange("C2");
    resultRange.values = [[newDecimalString]];
    resultRange.format.autofitColumns();
    await context.sync();
});

Управление режимом вычислений

Установка режима вычислений

По умолчанию Excel пересчитывает результаты формул при каждом изменении ячейки из ссылки. Производительность вашей надстройки можно улучшить путем изменения режима вычислений. У объекта Application есть свойство calculationMode типа CalculationMode. Для него можно задать следующие значения.

  • automatic: режим пересчета по умолчанию, при котором Excel вычисляет новые результаты формулы при каждом изменении соответствующих данных.
  • automaticExceptTables: аналогично automatic, за исключением того, что игнорируются любые изменения значений таблиц.
  • manual: вычисления выполняются только в том случае, если пользователь или надстройка запрашивает их.

Установка типа вычислений

Объект Application предоставляет метод применения немедленного пересчета. Метод Application.calculate(calculationType) запускает ручной пересчет с учетом указанного типа calculationType. Можно указать следующие значения.

  • full: пересчет всех формул во всех открытых книгах независимо от их изменения с прошлого пересчета.
  • fullRebuild: проверка зависимых формул с последующим пересчетом всех формул во всех открытых книгах независимо от их изменения с прошлого пересчета.
  • recalculate: пересчет формул, которые были изменены (или помечены программным путем для пересчета) с момента последнего вычисления, и зависимых от них формул во всех активных книгах.

Примечание.

Дополнительные сведения о пересчете см. в статье Изменение пересчета, итерации или точности формулы.

Временная приостановка вычисления

API Excel также позволяет надстройкам отключить вычисления до вызова RequestContext.sync(). Для этого используется suspendApiCalculationUntilNextSync(). Используйте этот метод, если ваша надстройка изменяет большие диапазоны без необходимости доступа к данным между изменениями.

context.application.suspendApiCalculationUntilNextSync();

Обнаружение активации книги

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

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

Важно!

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

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

async function run() {
    await Excel.run(async (context) => {
        // Retrieve the workbook.
        let workbook = context.workbook;
    
        // Register the workbook activated event handler.
        workbook.onActivated.add(workbookActivated);
        await context.sync();
    });
}

async function workbookActivated(event) {
    await Excel.run(async (context) => {
        // Retrieve the workbook and load the name.
        let workbook = context.workbook;
        workbook.load("name");        
        await context.sync();

        // Callback function for when the workbook is activated.
        console.log(`The workbook ${workbook.name} was activated.`);
    });
}

Сохраните книгу.

Workbook.save сохраняет книгу в постоянное хранилище. Метод save принимает один необязательный saveBehavior параметр, который может быть одним из следующих значений.

  • Excel.SaveBehavior.save (по умолчанию): файл будет сохранен без предварительного запроса имени файла, а также место для сохранения. Если файл не был сохранен ранее, он будет сохранен в папке по умолчанию. Если файл уже был сохранен ранее, он будет сохранен в той же папке.
  • Excel.SaveBehavior.prompt: если файл не был сохранен ранее, будет предложено ввести имя файла и место для сохранения. Если файл уже был сохранен ранее, он будет сохраняться в той же папке, и никаких дополнительных действий не потребуется.

Предостережение

Если пользователь при запрос на сохранение отменяет операцию, save выдает исключение.

context.workbook.save(Excel.SaveBehavior.prompt);

Закрытие книги.

Workbook.close закрывает книгу, а также надстройки, связанные с книгой, (приложение Excel остается открытым). Метод close принимает один необязательный closeBehavior параметр, который может быть одним из следующих значений.

  • Excel.CloseBehavior.save (по умолчанию): файл будет сохранен до закрытия. Если файл не был сохранен ранее, будет предложено ввести имя файла и место для сохранения.
  • Excel.CloseBehavior.skipSave: файл будет немедленно закрыт без сохранения. Все несохраненные изменения будут потеряны.
context.workbook.close(Excel.CloseBehavior.save);

См. также