Trabajar con hojas de cálculo mediante la API de JavaScript de Excel

Este artículo ofrece ejemplos que muestran cómo realizar tareas comunes con hojas de cálculo mediante la API de JavaScript de Excel. Para obtener la lista completa de propiedades y métodos que admiten los Worksheet objetos y WorksheetCollection , vea Objeto Worksheet (API de JavaScript para Excel) y Objeto WorksheetCollection (API de JavaScript para Excel).

Obtener hojas de cálculo

En el siguiente código de ejemplo obtiene la colección de hojas de cálculo, carga la propiedad name de cada hoja de cálculo y escribe un mensaje en la consola.

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);
    });
});

Obtener la hoja de cálculo activa

El siguiente código de ejemplo obtiene la hoja de trabajo activa, carga su propiedad name y escribe un mensaje en la consola.

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}"`);
});

Configurar la hoja de cálculo activa

El ejemplo de código siguiente establece la hoja de cálculo activa en la hoja de cálculo denominada Sample, carga su name propiedad y escribe un mensaje en la consola. Si no hay ninguna hoja de cálculo con ese nombre, el activate() método produce un ItemNotFound error.

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}"`);
});

Hacer referencia a hojas de cálculo por su posición relativa

Estos ejemplos muestran cómo hacer referencia a una hoja de cálculo por su posición relativa.

Obtener la primera hoja de cálculo

El siguiente código de ejemplo obtiene la primera hoja de cálculo del libro, carga su propiedad name y escribe un mensaje en la consola.

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}"`);
});

Obtener la última hoja de cálculo

El siguiente código de ejemplo obtiene la última hoja de cálculo del libro, carga su propiedad name y escribe un mensaje en la consola.

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}"`);
});

Obtener la siguiente hoja de cálculo

El ejemplo de código siguiente obtiene la hoja de cálculo que sigue a la hoja de cálculo activa del libro, carga su name propiedad y escribe un mensaje en la consola. Si no hay ninguna hoja de cálculo después de la hoja de cálculo activa, el getNext() método produce un ItemNotFound error.

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}"`);
});

Obtener la hoja de cálculo anterior

El ejemplo de código siguiente obtiene la hoja de cálculo que precede a la hoja de cálculo activa en el libro, carga su name propiedad y escribe un mensaje en la consola. Si no hay ninguna hoja de cálculo antes de la hoja de cálculo activa, el getPrevious() método produce un ItemNotFound error.

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}"`);
});

Agregar una hoja de cálculo

En el ejemplo de código siguiente se agrega una nueva hoja de cálculo denominada Sample al libro, se cargan sus name propiedades y position y se escribe un mensaje en la consola. La nueva hoja de cálculo se agrega después de las demás hojas de cálculo existentes.

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}`);
});

Copiar una hoja de cálculo existente

Worksheet.copy agrega una nueva hoja de cálculo que es una copia de una hoja de cálculo existente. El nombre de la nueva hoja de cálculo tendrá un número agregado al final, de forma coherente con la copia de una hoja de cálculo en la interfaz de usuario de Excel (por ejemplo, MySheet (2)). Worksheet.copy puede tomar dos parámetros, ambos opcionales:

• positionType: una lista WorksheetPositionType que especifica en qué parte del libro de cálculo se debe agregar la nueva hoja de trabajo.
• relativeTo: Si la positionType es Before o After, debe especificar una hoja de cálculo relativa a la cual se debe añadir la nueva hoja (este parámetro responde a la pregunta "¿Antes o después de qué?)

El ejemplo de código siguiente copia la hoja de cálculo actual e inserta la nueva hoja directamente después de la hoja de cálculo actual.

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();
});

Eliminar una hoja de cálculo

En el ejemplo de código siguiente se elimina la última hoja del libro (siempre que no sea la única hoja) y se escribe un mensaje en la consola.

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();
    }
});

Cambiar el nombre de una hoja de cálculo

En el ejemplo de código siguiente se cambia el nombre de la hoja de cálculo activa a Nuevo nombre.

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

    await context.sync();
});

Mover una hoja de cálculo

En el ejemplo de código siguiente se mueve una hoja de cálculo desde la última a la primera posición en el libro.

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();
});

Configurar la visibilidad de la hoja de cálculo

Estos ejemplos muestran cómo configurar la visibilidad de una hoja de cálculo.

Ocultar una hoja de cálculo

El siguiente código de ejemplo configura la visibilidad de la hoja de cálculo denominada Ejemplo para ocultarla, carga su propiedad name y escribe un mensaje en la consola.

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`);
});

Mostrar una hoja de cálculo

El siguiente código de ejemplo configura la visibilidad de la hoja de cálculo denominada Ejemplo para mostrarla, carga su propiedad name y escribe un mensaje en la consola.

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`);
});

Obtener una única celda de una hoja de cálculo

En el ejemplo de código siguiente se obtiene la celda que se encuentra en la fila 2, columna 5 de la hoja de cálculo denominada Ejemplo, se cargan sus propiedades address y values y se escribe un mensaje en la consola. Los valores que se pasan al método getCell(row: number, column:number) son el número de fila con índice cero y el número de columna de la celda que se están recuperando.

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}"`);
});

Detectar cambios en los datos

Quizás necesite que el complemento reaccione a los cambios que realicen los usuarios en los datos de una hoja de cálculo. Con el fin de detectar dichos cambios, puede registrar un controlador de eventos para el evento onChanged de una hoja de cálculo. Los controladores de eventos para el evento onChanged reciben un objeto WorksheetChangedEventArgs cuando se produce el evento.

El objeto WorksheetChangedEventArgs proporciona información sobre los cambios y el origen. Dado que el evento onChanged se produce cuando cambia el formato o el valor de los datos, puede resultar útil que el complemento compruebe si realmente han cambiado los valores. La propiedad details encapsula esta información como un ChangedEventDetail. En el ejemplo siguiente, se muestra cómo visualizar los tipos y valores previos y posteriores de una celda que ha sido modificada.

// 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();
    });
}

Detección de cambios de fórmula

El complemento puede realizar un seguimiento de los cambios en las fórmulas de una hoja de cálculo. Esto resulta útil cuando una hoja de cálculo está conectada a una base de datos externa. Cuando la fórmula cambia en la hoja de cálculo, el evento de este escenario desencadena las actualizaciones correspondientes en la base de datos externa.

Para detectar cambios en las fórmulas, registre un controlador de eventos para el evento onFormulaChanged de una hoja de cálculo. Los controladores de eventos del onFormulaChanged evento reciben un objeto WorksheetFormulaChangedEventArgs cuando se desencadena el evento.

En el ejemplo de código siguiente se muestra cómo registrar el onFormulaChanged controlador de eventos, usar el WorksheetFormulaChangedEventArgs objeto para recuperar la matriz formulaDetails de la fórmula modificada y, a continuación, imprimir detalles sobre la fórmula modificada con las propiedades 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}.`
        );         
    });
}

Gestionar eventos de clasificación

Los eventos onColumnSorted y onRowSorted indican cuándo se ordenan los datos de la hoja de cálculo. Estos eventos están conectados a objetos Worksheet individuales y a WorkbookCollection del libro. Se activan si la ordenación se realiza mediante programación o de forma manual mediante la interfaz de usuario de Excel.

Los eventos onColumnSorted y onRowSorted proporcionan sus devoluciones de llamada con WorksheetColumnSortedEventArgs o WorksheetRowSortedEventArgs, respectivamente. Estos proporcionan más detalles sobre el evento. En particular, ambos EventArgs tienen una propiedad address que representa las filas o columnas que se movieron como resultado de la operación de ordenación. Se incluye cualquier celda con contenido ordenado, incluso si el valor de esa celda no formaba parte de los criterios de ordenación.

Las siguientes imágenes muestran los intervalos devueltos por la propiedad address para ordenar eventos. En primer lugar, aquí se muestran los datos de ejemplo antes de ordenar:

Si se realiza una ordenación de arriba a abajo en "Q1" (los valores de "B"), se devuelven las siguientes filas resaltadas por WorksheetRowSortedEventArgs.address.

Si se realiza una ordenación de izquierda a derecha en "Quinces" (los valores de "4") en los datos originales, las siguientes columnas resaltadas las devuelve WorksheetColumnsSortedEventArgs.address.

El siguiente ejemplo de código muestra cómo registrar un controlador de eventos para el evento Worksheet.onRowSorted. La devolución de llamada del controlador elimina el color de relleno del rango y, después, rellena las celdas de las filas que se movieron.

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();
});

Buscar todas las celdas con texto coincidente

El objeto Worksheet cuenta con un método findAll para buscar una cadena especificada en la hoja de cálculo. Devuelve un objeto RangeAreas, que es una colección de objetos Range que se pueden editar todos a la vez.

En el ejemplo siguiente se buscan todas las celdas con valores de la cadena Completado y les pone en verde. Tenga en cuenta que findAll produce un ItemNotFound error si la cadena especificada no existe en la hoja de cálculo. Si no está seguro de si la cadena especificada existe en la hoja de cálculo, use el método findAllOrNullObject para controlar correctamente ese escenario.

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";
});

Filtrar datos

Un AutoFilter aplica filtros de datos en un rango de la hoja de cálculo. Se crea con Worksheet.autoFilter.apply, que tiene los parámetros siguientes.

• range: el rango al que se aplica el filtro, especificado como una cadena o un objeto Range.
• columnIndex: el índice de columna basado en cero con el que se evalúan los criterios de filtro.
• criteria: un objeto FilterCriteria que determina las filas que se deben filtrar en función de la celda de la columna.

El primer ejemplo de código muestra cómo agregar un filtro al rango utilizado de la hoja de cálculo. Este filtro ocultará las entradas que no estén en el 25 % superior, en función de los valores de la columna 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();
});

El siguiente ejemplo muestra cómo actualizar el filtro automático con el método reapply. Esto se debe hacer cuando cambian los datos del rango.

// 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();
});

En el ejemplo de código siguiente se muestra cómo usar el clearColumnCriteria método para borrar el filtro automático de una sola columna, al tiempo que se deja el filtro activo en otras columnas.

// 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();
});

El ejemplo final de filtro automático muestra cómo quitar el filtrado automático de la hoja de cálculo con el método 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();
});

Un AutoFilter también puede aplicarse a tablas individuales. Vea Trabajar con tablas mediante la API de JavaScript de Excel para obtener más información.

Protección de datos

El complemento puede controlar la capacidad del usuario para editar datos en una hoja de cálculo. La propiedad protection de la hoja de cálculo es un objeto WorksheetProtection con un método protect(). En el ejemplo siguiente se muestra un escenario básico que alterna la protección completa del libro activo.

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();
    }
});

El método protect tiene dos parámetros opcionales:

• options: un objeto WorksheetProtectionOptions que define las restricciones de edición específicas del objeto.
• password: una cadena que representa la contraseña necesaria para que un usuario pueda saltarse la protección y editar la hoja de cálculo.

El artículo Proteger una hoja de cálculo tiene más información acerca de la protección de hojas de cálculo y cómo cambiarla a través de la interfaz de usuario de Excel.

Detección de cambios en el estado de protección de la hoja de cálculo

Un complemento o la interfaz de usuario de Excel pueden cambiar el estado de protección de una hoja de cálculo. Para detectar cambios en el estado de protección, registre un controlador de eventos para el onProtectionChanged evento de una hoja de cálculo. Los controladores de eventos para el onProtectionChanged evento reciben un WorksheetProtectionChangedEventArgs objeto cuando se desencadena el evento.

En el ejemplo de código siguiente se muestra cómo registrar el onProtectionChanged controlador de eventos y cómo usar el WorksheetProtectionChangedEventArgs objeto para recuperar las isProtectedpropiedades , worksheetIdy source del evento.

// 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);    
    });
}

Diseño de página y configuración de impresión

Los complementos tienen acceso a la configuración de diseño de página en un nivel de hoja de cálculo. Estos controlan cómo se imprime la hoja. Un objeto Worksheet tiene tres propiedades relacionadas con el diseño: horizontalPageBreaks, verticalPageBreaks, pageLayout.

Worksheet.horizontalPageBreaks y Worksheet.verticalPageBreaks son PageBreakCollections. Estas son colecciones de PageBreaks, que especifican los rangos donde se insertan saltos de página manuales. El siguiente ejemplo de código agrega un salto de página horizontal encima de la fila 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 es un objeto PageLayout. Este objeto contiene la configuración de impresión y diseño que no depende de una implementación específica de la impresora. Estas opciones incluyen los márgenes, orientación, numeración, filas de título y el área de impresión.

El siguiente código de ejemplo centra la página (vertical y horizontalmente), establece una fila de título que se va a imprimir en la parte superior de cada página y establece el área impresa a una subsección de la hoja de cálculo.

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();
});

Ver también

• Modelo de objetos de JavaScript en Excel para Complementos de Office