Trabajar con libros mediante la API de JavaScript de Excel
En este artículo se ofrecen ejemplos de código que muestran cómo realizar tareas comunes con libros mediante la API de JavaScript de Excel. Para obtener la lista completa de propiedades y métodos que admite el Workbook objeto, vea Objeto Workbook (API de JavaScript para Excel). En este artículo también se describen las acciones a nivel de libro realizadas por el objeto Application.
El objeto Workbook es el punto de entrada para que el complemento interactúe con Excel. Mantiene colecciones de hojas de cálculo, tablas, tablas dinámicas y más, por las que se accede a los datos de Excel y se realizan cambios. El objeto WorksheetCollection permite que el complemento tenga acceso a todos los datos del libro mediante hojas de cálculo individuales. En concreto, permite que el complemento agregue hojas de cálculo, navegue por ellos y asigne controladores a eventos de la hoja de cálculo. En el artículo Trabajar con hojas de cálculo mediante la API de JavaScript de Excel se describe cómo acceder a hojas de cálculo y editarlas.
Obtener la celda activa o el rango seleccionado
El objeto Workbook contiene dos métodos que obtienen un rango de celdas que el usuario o el complemento han seleccionado: getActiveCell() y getSelectedRange(). getActiveCell() obtiene la celda activa desde el libro como un objeto Range. En el ejemplo siguiente se muestra una llamada a getActiveCell(), seguida por la impresión de la dirección de la celda en la consola.
await Excel.run(async (context) => {
let activeCell = context.workbook.getActiveCell();
activeCell.load("address");
await context.sync();
console.log("The active cell is " + activeCell.address);
});
El método getSelectedRange() devuelve el rango único seleccionado actualmente. Si hay varios rangos seleccionados, se produce un error InvalidSelection. En el siguiente ejemplo se muestra una llamada a getSelectedRange() que luego establece el color de relleno del rango a amarillo.
await Excel.run(async (context) => {
let range = context.workbook.getSelectedRange();
range.format.fill.color = "yellow";
await context.sync();
});
Crear un libro
El complemento puede crear un libro nuevo, independiente de la instancia de Excel en la que el complemento se está ejecutando actualmente. El objeto de Excel tiene el método createWorkbook para este propósito. Cuando se llama a este método, el nuevo libro se abre inmediatamente y se muestra en una nueva instancia de Excel. El complemento permanece abierto y en ejecución con el libro anterior.
Excel.createWorkbook();
El método createWorkbook también puede crear una copia de un libro existente. El método acepta una representación de cadena con codificación base64 de un archivo .xlsx como un parámetro opcional. El libro resultante será una copia de ese archivo, si el argumento de la cadena es un archivo .xlsx válido.
Puede obtener el libro actual del complemento como una cadena codificada en base64 mediante la segmentación de archivos. La clase FileReader puede usarse para convertir un archivo en la cadena con codificación base64 necesaria, como se muestra en el ejemplo siguiente.
// 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]);
Insertar una copia de un libro existente en el libro actual
El ejemplo anterior muestra la creación de un libro a partir de un libro existente. También puede copiar un libro existente entero o una parte en el que está asociado actualmente con su complemento. Un libro tiene el insertWorksheetsFromBase64 método para insertar copias de las hojas de cálculo del libro de destino en sí mismo. El archivo del otro libro se pasa como una cadena codificada en base64, al igual que la Excel.createWorkbook llamada.
insertWorksheetsFromBase64(base64File: string, options?: Excel.InsertWorksheetOptions): OfficeExtension.ClientResult<string[]>;
Importante
El insertWorksheetsFromBase64 método se admite para Excel en la Web, en Windows y en Mac. No se admite para iOS. Además, en Excel en la Web, este método no admite hojas de cálculo de origen con elementos PivotTable, Chart, Comment o Slicer. Si esos objetos están presentes, el insertWorksheetsFromBase64 método devuelve el UnsupportedFeature error en Excel en la Web.
En el ejemplo de código siguiente se muestra cómo insertar hojas de cálculo de otro libro en el libro actual. Este ejemplo de código procesa primero un archivo de libro con un FileReader objeto y extrae una cadena codificada en base64 y, a continuación, inserta esta cadena codificada en base64 en el libro actual. Las nuevas hojas de cálculo se insertan después de la hoja de cálculo denominada Sheet1. Tenga en cuenta que [] se pasa como parámetro de la propiedad InsertWorksheetOptions.sheetNamesToInsert . Esto significa que todas las hojas de cálculo del libro de destino se insertan en el libro actual.
// 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]);
Proteger la estructura del libro
El complemento puede controlar la capacidad del usuario para editar la estructura del libro. La propiedad protection del objeto Workbook es un objeto WorkbookProtection con un método protect(). En el ejemplo siguiente se muestra un escenario básico que alterna la protección de la estructura del libro.
await Excel.run(async (context) => {
let workbook = context.workbook;
workbook.load("protection/protected");
await context.sync();
if (!workbook.protection.protected) {
workbook.protection.protect();
}
});
El método protect acepta un parámetro de cadena opcional. Esta cadena representa la contraseña necesaria para que un usuario omita la protección y cambie la estructura del libro.
La protección también puede establecerse a nivel de hoja de cálculo para evitar la edición no deseada de datos. Para obtener más información, consulte la sección sobre la Protección de datos del artículo Trabajar con hojas de cálculo con la API de JavaScript de Excel.
Nota:
Para más información sobre la protección de libros de Excel, consulte el artículo Proteger un libro.
Obtener acceso a las propiedades del documento
Los objetos Workbook tienen acceso a los metadatos de archivo de Office, que se conocen como las propiedades del documento. La propiedad del properties objeto Workbook es un objeto DocumentProperties que contiene algunos de estos valores de metadatos. En el ejemplo siguiente se muestra cómo establecer la author propiedad .
await Excel.run(async (context) => {
let docProperties = context.workbook.properties;
docProperties.author = "Alex";
await context.sync();
});
También puede definir propiedades personalizadas. El objeto DocumentProperties contiene una propiedad custom que representa una colección de pares de clave-valor para propiedades definidas por el usuario. Para obtener un ejemplo de cómo establecer propiedades personalizadas, vea la sección Datos XML personalizados en Excel y Word del artículo Persist add-in state and settings (Conservar estado y configuración del complemento).
Acceder a la configuración del documento
La configuración de un libro es similar a la colección de propiedades personalizadas. La diferencia es que la configuración es única para un solo archivo de Excel y un emparejamiento de complementos, mientras que las propiedades solo están conectadas al archivo. En el ejemplo siguiente se muestra cómo crear y acceder a una configuración.
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);
});
Acceso a la configuración de la referencia cultural de la aplicación
Un libro tiene una configuración de idioma y referencia cultural que afecta al modo en que se muestran determinados datos. Esta configuración puede ayudar a localizar los datos cuando los usuarios del complemento comparten libros en diferentes idiomas y referencias culturales. El complemento puede usar el análisis de cadenas para localizar el formato de números, fechas y horas en función de la configuración de referencia cultural del sistema para que cada usuario vea los datos en el formato de su propia referencia cultural.
Application.cultureInfo define la configuración de la referencia cultural del sistema como un objeto CultureInfo . Contiene valores como el separador decimal numérico o el formato de fecha.
Algunos valores de configuración de referencia cultural se pueden cambiar a través de la interfaz de usuario de Excel. La configuración del sistema se conserva en el CultureInfo objeto . Los cambios locales se mantienen como propiedades de nivel de aplicación, como Application.decimalSeparator.
En el ejemplo siguiente se cambia el carácter separador decimal de una cadena numérica de '', al carácter usado por la configuración del sistema.
// 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();
});
Controlar el comportamiento de cálculo
Establecer modo de cálculo
De forma predeterminada, Excel vuelve a calcular los resultados de la fórmula cuando se cambia una referencia de celda. El rendimiento del complemento puede beneficiarse de los ajustes a este comportamiento de cálculo. El objeto Application tiene una propiedad calculationMode de tipo CalculationMode. Se puede establecer en los siguientes valores.
automatic: El comportamiento predeterminado de recálculo en el que Excel calcula nuevos resultados de la fórmula cada vez que se cambian datos relevantes.automaticExceptTables: Igual queautomatic, pero los cambios realizados en los valores de las tablas se pasan por alto.manual: Los cálculos solo se ejecutan cuando el usuario o el complemento los solicitan.
Establecer tipo de cálculo
El objeto Application proporciona un método para forzar un recálculo inmediato. Application.calculate(calculationType) inicia un recálculo manual en función del calculationType especificado. Se pueden especificar los siguientes valores.
full: Recalcular todas las fórmulas de todos los libros abiertos, independientemente de si han cambiado desde el último recálculo.fullRebuild: Comprobar formulas dependientes, y luego recalcular todas las fórmulas de todos los libros abiertos, independientemente de si han cambiado desde el último recálculo.recalculate: Recalcular las fórmulas que han cambiado (o se han marcado mediante programación para volver a calcularlas) desde el último cálculo y las fórmulas que dependen de ellas en todos los libros activos.
Nota:
Para obtener más información sobre el recálculo, consulte el artículo Cambiar recálculo, iteración o precisión de fórmulas.
Suspender temporalmente los cálculos
La API de Excel también permite que los complementos desactiven cálculos hasta que se llame a RequestContext.sync(). Esto se realiza mediante suspendApiCalculationUntilNextSync(). Use este método si el complemento está editando rangos grandes sin necesidad de tener acceso a los datos entre las ediciones.
context.application.suspendApiCalculationUntilNextSync();
Detección de la activación del libro
El complemento puede detectar cuándo se activa un libro. Un libro se vuelve inactivo cuando el usuario cambia el foco a otro libro, a otra aplicación o (en Excel en la Web) a otra pestaña del explorador web. Un libro se activa cuando el usuario devuelve el foco al libro. La activación del libro puede desencadenar funciones de devolución de llamada en el complemento, como actualizar los datos del libro.
Para detectar cuándo se activa un libro, registre un controlador de eventos para el evento onActivated de un libro. Los controladores de eventos del onActivated evento reciben un objeto WorkbookActivatedEventArgs cuando se desencadena el evento.
Importante
El onActivated evento no detecta cuándo se abre un libro. Este evento solo detecta cuándo un usuario vuelve a cambiar el foco a un libro ya abierto.
En el ejemplo de código siguiente se muestra cómo registrar el controlador de onActivated eventos y configurar una función de devolución de llamada.
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.`);
});
}
Guardar el libro
Workbook.save guarda el libro en el almacenamiento persistente. El save método toma un único parámetro opcional saveBehavior que puede ser uno de los siguientes valores.
Excel.SaveBehavior.save(predeterminado): el archivo se guarda sin preguntar al usuario que especifique el nombre de archivo y la ubicación de almacenamiento. Si no ha guardado el archivo anteriormente, se guarda en la ubicación predeterminada. Si ha guardado el archivo anteriormente, se guarda en la misma ubicación.Excel.SaveBehavior.prompt: Si el archivo no se ha guardado anteriormente, se le pedirá al usuario que especifique el nombre de archivo y la ubicación de almacenamiento. Si ha guardado el archivo anteriormente, se guardará en la misma ubicación y no se preguntará al usuario.
Precaución
Si se le solicita al usuario que guarde y cancele la operación, save inicia una excepción.
context.workbook.save(Excel.SaveBehavior.prompt);
Cerrar el libro
Workbook.close cierra el libro, junto con los complementos que están asociados con el libro (la aplicación Excel permanece abierta). El close método toma un único parámetro opcional closeBehavior que puede ser uno de los siguientes valores.
Excel.CloseBehavior.save(predeterminado): el archivo se guarda antes de cerrar. Si no ha guardado el archivo anteriormente, se le pedirá el usuario que especifique el nombre del archivo y la ubicación de almacenamiento.Excel.CloseBehavior.skipSave: el archivo se cierra inmediatamente, sin guardar. Se perderán los cambios no guardados.
context.workbook.close(Excel.CloseBehavior.save);