Trabajar con tablas mediante la API de JavaScript de Excel

Este artículo ofrece ejemplos que muestran cómo realizar tareas comunes con tablas mediante la API de JavaScript de Excel. Para obtener la lista completa de propiedades y métodos compatibles con los objetos and, vea Table Object (API de Table TableCollection JavaScript para Excel) y TableCollection Object (API de JavaScript para Excel).

Crear una tabla

El ejemplo de código siguiente crea una tabla en la hoja de cálculo denominada Ejemplo. La tabla tiene encabezados y contiene cuatro columnas y siete filas de datos. Si la aplicación Excel donde se ejecuta el código admite el conjunto de requisitos ExcelApi 1.2, el ancho de las columnas y el alto de las filas se establecen para que se ajusten mejor a los datos actuales de la tabla.

Nota

Para especificar un nombre para una tabla, primero debe crear la tabla y, a continuación, establecer su propiedad, como se name muestra en el ejemplo siguiente.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";

    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    expensesTable.rows.add(null /*add rows to the end of the table*/, [
        ["1/1/2017", "The Phone Company", "Communications", "$120"],
        ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
        ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
        ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
        ["1/11/2017", "Bellows College", "Education", "$350"],
        ["1/15/2017", "Trey Research", "Other", "$135"],
        ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    return context.sync();
}).catch(errorHandlerFunction);

Nueva tabla

Nueva tabla en Excel.

Agregar filas a una tabla

En el ejemplo de código siguiente se agregan siete filas nuevas a la tabla denominada ExpensesTable dentro de la hoja de cálculo denominada Ejemplo. Las nuevas filas se agregan al final de la tabla. Si la aplicación Excel donde se ejecuta el código admite el conjunto de requisitos ExcelApi 1.2, el ancho de las columnas y el alto de las filas se establecen para que se ajusten mejor a los datos actuales de la tabla.

Nota

La index propiedad de un objeto TableRow indica el número de índice de la fila dentro de la colección rows de la tabla. Un TableRow objeto no contiene una propiedad que se puede usar como clave única para identificar la id fila.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.rows.add(null /*add rows to the end of the table*/, [
        ["1/16/2017", "THE PHONE COMPANY", "Communications", "$120"],
        ["1/20/2017", "NORTHWIND ELECTRIC CARS", "Transportation", "$142"],
        ["1/20/2017", "BEST FOR YOU ORGANICS COMPANY", "Groceries", "$27"],
        ["1/21/2017", "COHO VINEYARD", "Restaurant", "$33"],
        ["1/25/2017", "BELLOWS COLLEGE", "Education", "$350"],
        ["1/28/2017", "TREY RESEARCH", "Other", "$135"],
        ["1/31/2017", "BEST FOR YOU ORGANICS COMPANY", "Groceries", "$97"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    return context.sync();
}).catch(errorHandlerFunction);

Tabla con nuevas filas

Tabla con nuevas filas en Excel.

Agregar una columna a una tabla

Estos ejemplos muestran cómo agregar una columna a una tabla. En el primer ejemplo se rellena la columna nueva con valores estáticos; en el segundo ejemplo se rellena la columna nueva con fórmulas.

Nota

La propiedad index de un objeto TableColumn indica el número de índice de la columna dentro de la colección de columnas de la tabla. La propiedad id de un objeto TableColumn contiene una clave única que identifica la columna.

Agregar una columna que contiene valores estáticos

El ejemplo de código siguiente agrega una nueva columna a la tabla denominada TablaGastos dentro de la hoja de cálculo denominada Ejemplo. La nueva columna se agrega después de las columnas de la tabla existentes y contiene un encabezado ("Day of the week"), así como los datos para rellenar las celdas de la columna. Si la aplicación Excel donde se ejecuta el código admite el conjunto de requisitos ExcelApi 1.2, el ancho de las columnas y el alto de las filas se establecen para que se ajusten mejor a los datos actuales de la tabla.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.columns.add(null /*add columns to the end of the table*/, [
        ["Day of the Week"],
        ["Saturday"],
        ["Friday"],
        ["Monday"],
        ["Thursday"],
        ["Sunday"],
        ["Saturday"],
        ["Monday"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    return context.sync();
}).catch(errorHandlerFunction);

Tabla con la nueva columna

Tabla con nueva columna en Excel.

Agregar una columna que contenga fórmulas

El ejemplo de código siguiente agrega una nueva columna a la tabla denominada TablaGastos dentro de la hoja de cálculo denominada Ejemplo. La nueva columna se agrega al final de la tabla contiene un encabezado ("Type of the Day") y usa una fórmula para rellenar cada celda de datos de la columna. Si la aplicación Excel donde se ejecuta el código admite el conjunto de requisitos ExcelApi 1.2, el ancho de las columnas y el alto de las filas se establecen para que se ajusten mejor a los datos actuales de la tabla.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.columns.add(null /*add columns to the end of the table*/, [
        ["Type of the Day"],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")']
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    return context.sync();
}).catch(errorHandlerFunction);

Tabla con la nueva columna calculada

Tabla con nueva columna calculada en Excel.

Cambiar el tamaño de una tabla

El complemento puede cambiar el tamaño de una tabla sin agregar datos a la tabla ni cambiar los valores de celda. Para cambiar el tamaño de una tabla, use el método Table.resize. En el ejemplo de código siguiente se muestra cómo cambiar el tamaño de una tabla. En este ejemplo de código se usa ExpensesTable de la sección Crear una tabla de este artículo y se establece el nuevo intervalo de la tabla en A1:D20.

Excel.run(function (context) {
    // Retrieve the worksheet and a table on that worksheet.
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    // Resize the table.
    expensesTable.resize("A1:D20");

    return context.sync();
}).catch(errorHandlerFunction);

Importante

El nuevo intervalo de la tabla debe superponerse con el intervalo original y los encabezados (o la parte superior de la tabla) deben estar en la misma fila.

Tabla después del cambio de tamaño

Tabla con varias filas vacías en Excel.

Actualizar el nombre de la columna

En el ejemplo de código siguiente se actualiza el nombre de la primera columna de la tabla a Fecha de compra. Si la aplicación Excel donde se ejecuta el código admite el conjunto de requisitos ExcelApi 1.2, el ancho de las columnas y el alto de las filas se establecen para que se ajusten mejor a los datos actuales de la tabla.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    var expensesTable = sheet.tables.getItem("ExpensesTable");
    expensesTable.columns.load("items");

    return context.sync()
        .then(function () {
            expensesTable.columns.items[0].name = "Purchase date";

            if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
                sheet.getUsedRange().format.autofitColumns();
                sheet.getUsedRange().format.autofitRows();
            }

            return context.sync();
        });
}).catch(errorHandlerFunction);

Tabla con el nombre de la columna nueva

Tabla con nuevo nombre de columna en Excel.

Obtener datos de una tabla

En el ejemplo de código siguiente se leen los datos de una tabla denominada ExpensesTable en la hoja de cálculo denominada Sample y, después, se envían esos datos debajo de la tabla en la misma hoja de cálculo.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    // Get data from the header row
    var headerRange = expensesTable.getHeaderRowRange().load("values");

    // Get data from the table
    var bodyRange = expensesTable.getDataBodyRange().load("values");

    // Get data from a single column
    var columnRange = expensesTable.columns.getItem("Merchant").getDataBodyRange().load("values");

    // Get data from a single row
    var rowRange = expensesTable.rows.getItemAt(1).load("values");

    // Sync to populate proxy objects with data from Excel
    return context.sync()
        .then(function () {
            var headerValues = headerRange.values;
            var bodyValues = bodyRange.values;
            var merchantColumnValues = columnRange.values;
            var secondRowValues = rowRange.values;

            // Write data from table back to the sheet
            sheet.getRange("A11:A11").values = [["Results"]];
            sheet.getRange("A13:D13").values = headerValues;
            sheet.getRange("A14:D20").values = bodyValues;
            sheet.getRange("B23:B29").values = merchantColumnValues;
            sheet.getRange("A32:D32").values = secondRowValues;

            // Sync to update the sheet in Excel
            return context.sync();
        });
}).catch(errorHandlerFunction);

Salida de tabla y de datos

Tabla de datos en Excel.

Detectar cambios en los datos

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

El objeto TableChangedEventArgs 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 Table.onChanged event.
function onTableChanged(eventArgs) {
    Excel.run(function (context) {
        var details = eventArgs.details;
        var 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();
    });
}

Ordenar los datos de una tabla

En el ejemplo de código siguiente se ordenan los datos de la tabla en orden descendente según los valores de la cuarta columna de la tabla.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    // Queue a command to sort data by the fourth column of the table (descending)
    var sortRange = expensesTable.getDataBodyRange();
    sortRange.sort.apply([
        {
            key: 3,
            ascending: false,
        },
    ]);

    // Sync to run the queued command in Excel
    return context.sync();
}).catch(errorHandlerFunction);

Datos de la tabla ordenados por importe (descendente)

Datos de tabla ordenados en Excel.

Cuando se ordenan datos en una hoja de cálculo, se activa una notificación de evento. Para obtener más información acerca de los eventos relacionados con la organización y de cómo el complemento puede registrar a los gestores de eventos para responder a dichos eventos, consulte Controlar la organización de eventos.

Aplicar filtros a una tabla

En el siguiente ejemplo de código se aplican filtros a la columna Importe y la columna Categoría dentro de una tabla. Como resultado de los filtros, solo se muestran las filas en las que Categoría es uno de los valores especificados y el Importe es inferior al valor promedio para todas las filas.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    // Queue a command to apply a filter on the Category column
    filter = expensesTable.columns.getItem("Category").filter;
    filter.apply({
        filterOn: Excel.FilterOn.values,
        values: ["Restaurant", "Groceries"]
    });

    // Queue a command to apply a filter on the Amount column
    var filter = expensesTable.columns.getItem("Amount").filter;
    filter.apply({
        filterOn: Excel.FilterOn.dynamic,
        dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
    });

    // Sync to run the queued commands in Excel
    return context.sync();
}).catch(errorHandlerFunction);

Datos de la tabla con los filtros aplicados a Categoría e Importe

Datos de tabla filtrados en Excel.

Borrar los filtros de la tabla

En el siguiente ejemplo de código se borran todos los filtros aplicados actualmente a la tabla.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.clearFilters();

    return context.sync();
}).catch(errorHandlerFunction);

Datos de la tabla sin filtros aplicados

Tabla de datos no filtrados en Excel.

Obtener el rango visible de una tabla filtrada

En el ejemplo de código siguiente se obtiene un rango que contiene los datos solo de las celdas que están visibles actualmente en la tabla especificada y, después, escribe los valores de dicho rango en la consola. Puede usar el método como se muestra a continuación para obtener el contenido visible de una tabla siempre que se hayan aplicado getVisibleView() filtros de columna.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    var visibleRange = expensesTable.getDataBodyRange().getVisibleView();
    visibleRange.load("values");

    return context.sync()
        .then(function() {
            console.log(visibleRange.values);
        });
}).catch(errorHandlerFunction);

AutoFilter

Un complemento puede usar el objeto Autofiltro de la tabla para filtrar los datos. Un objeto AutoFilter es la estructura de filtro completa de una tabla o rango. Todas las operaciones de filtro descritas anteriormente en este artículo son compatibles con el filtrado automático. El punto de acceso único hace que sea más fácil acceder a varios filtros y administrarlos.

El ejemplo de código siguiente muestra el mismo filtrado de datos que el anterior ejemplo de código, pero hecho completamente mediante el filtrado automático.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.autoFilter.apply(expensesTable.getRange(), 2, {
        filterOn: Excel.FilterOn.values,
        values: ["Restaurant", "Groceries"]
    });
    expensesTable.autoFilter.apply(expensesTable.getRange(), 3, {
        filterOn: Excel.FilterOn.dynamic,
        dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
    });

    return context.sync();
}).catch(errorHandlerFunction);

Un AutoFilter también puede aplicarse a un rango en el nivel de hoja de cálculo. Vea Trabajar con hojas de cálculo mediante la API de JavaScript de Excel para obtener más información.

Aplicar formato a una tabla

En el ejemplo de código siguiente se aplica formato a una tabla. Especifica colores de relleno diferentes para la fila de encabezado de la tabla, el cuerpo de la tabla, la segunda fila de la tabla y la primera columna de la tabla. Para obtener información sobre las propiedades que puede usar para especificar el formato, vea Objeto RangeFormat (API de JavaScript para Excel).

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    var expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.getHeaderRowRange().format.fill.color = "#C70039";
    expensesTable.getDataBodyRange().format.fill.color = "#DAF7A6";
    expensesTable.rows.getItemAt(1).getRange().format.fill.color = "#FFC300";
    expensesTable.columns.getItemAt(0).getDataBodyRange().format.fill.color = "#FFA07A";

    return context.sync();
}).catch(errorHandlerFunction);

Tabla después de darle formato

Tabla después de aplicar formato en Excel.

Convertir un rango en una tabla

En el ejemplo de código siguiente se crea un rango de datos y, después, se convierte ese rango en una tabla.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    // Define values for the range
    var values = [["Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4"],
    ["Frames", 5000, 7000, 6544, 4377],
    ["Saddles", 400, 323, 276, 651],
    ["Brake levers", 12000, 8766, 8456, 9812],
    ["Chains", 1550, 1088, 692, 853],
    ["Mirrors", 225, 600, 923, 544],
    ["Spokes", 6005, 7634, 4589, 8765]];

    // Create the range
    var range = sheet.getRange("A1:E7");
    range.values = values;

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    // Convert the range to a table
    var expensesTable = sheet.tables.add('A1:E7', true);
    expensesTable.name = "ExpensesTable";

    return context.sync();
}).catch(errorHandlerFunction);

Datos en el rango (antes de que el intervalo se convierta en una tabla)

Datos en intervalo en Excel.

Datos en la tabla (después de que el intervalo se convierta en una tabla)

Datos de la tabla en Excel.

Importar datos JSON en una tabla

En el ejemplo de código siguiente se crea una tabla en la hoja de cálculo denominada Ejemplo y, después, rellena la tabla usando un objeto JSON que define dos filas de datos. Si la aplicación Excel donde se ejecuta el código admite el conjunto de requisitos ExcelApi 1.2, el ancho de las columnas y el alto de las filas se establecen para que se ajusten mejor a los datos actuales de la tabla.

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");

    var expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";
    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    var transactions = [
      {
        "DATE": "1/1/2017",
        "MERCHANT": "The Phone Company",
        "CATEGORY": "Communications",
        "AMOUNT": "$120"
      },
      {
        "DATE": "1/1/2017",
        "MERCHANT": "Southridge Video",
        "CATEGORY": "Entertainment",
        "AMOUNT": "$40"
      }
    ];

    var newData = transactions.map(item =>
        [item.DATE, item.MERCHANT, item.CATEGORY, item.AMOUNT]);

    expensesTable.rows.add(null, newData);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    return context.sync();
}).catch(errorHandlerFunction);

Nueva tabla

Nueva tabla de datos JSON importados en Excel.

Vea también