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 Table objetos y TableCollection , vea Objeto Table (API de JavaScript para Excel) y Objeto TableCollection (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 de Excel donde se ejecuta el código admite el conjunto de requisitosExcelApi 1.2, el ancho de las columnas y el alto de las filas se establecen para ajustarse 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 name propiedad, como se muestra en el ejemplo siguiente.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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();
await context.sync();
});
Nueva tabla
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. El index parámetro del add método se establece nullen , que especifica que las filas se agreguen después de las filas existentes en la tabla. El alwaysInsert parámetro se establece en true, lo que indica que las nuevas filas se insertan en la tabla, no debajo de la tabla. El ancho de las columnas y el alto de las filas se establecen para ajustarse 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 id propiedad que se pueda usar como clave única para identificar la fila.
// This code sample shows how to add rows to a table that already exists
// on a worksheet named Sample.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.rows.add(
null, // index, Adds 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"]
],
true, // alwaysInsert, Specifies that the new rows be inserted into the table.
);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Tabla con las nuevas filas
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. El ancho de las columnas y el alto de las filas se establecen para ajustarse mejor a los datos actuales de la tabla.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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"]
]);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Tabla con la nueva columna
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. El ancho de las columnas y el alto de las filas se establecen para ajustarse mejor a los datos actuales de la tabla.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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")']
]);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Tabla con la nueva columna calculada
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 anteriormente en este artículo y se establece el nuevo intervalo de la tabla en A1:D20.
await Excel.run(async (context) => {
// Retrieve the worksheet and a table on that worksheet.
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Resize the table.
expensesTable.resize("A1:D20");
await context.sync();
});
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
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. El ancho de las columnas y el alto de las filas se establecen para ajustarse mejor a los datos actuales de la tabla.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.columns.load("items");
await context.sync();
expensesTable.columns.items[0].name = "Purchase date";
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
Tabla con el nombre de la columna nueva
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.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Get data from the header row.
let headerRange = expensesTable.getHeaderRowRange().load("values");
// Get data from the table.
let bodyRange = expensesTable.getDataBodyRange().load("values");
// Get data from a single column.
let columnRange = expensesTable.columns.getItem("Merchant").getDataBodyRange().load("values");
// Get data from a single row.
let rowRange = expensesTable.rows.getItemAt(1).load("values");
// Sync to populate proxy objects with data from Excel.
await context.sync();
let headerValues = headerRange.values;
let bodyValues = bodyRange.values;
let merchantColumnValues = columnRange.values;
let 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.
await context.sync();
});
Tabla y salida de datos
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.
async function onTableChanged(eventArgs) {
await Excel.run(async (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})`);
await 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.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Queue a command to sort data by the fourth column of the table (descending).
let sortRange = expensesTable.getDataBodyRange();
sortRange.sort.apply([
{
key: 3,
ascending: false,
},
]);
// Sync to run the queued command in Excel.
await context.sync();
});
Datos de la tabla ordenados por importe (descendente)
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.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
// Queue a command to apply a filter on the Category column.
let categoryFilter = expensesTable.columns.getItem("Category").filter;
categoryFilter.apply({
filterOn: Excel.FilterOn.values,
values: ["Restaurant", "Groceries"]
});
// Queue a command to apply a filter on the Amount column.
let amountFilter = expensesTable.columns.getItem("Amount").filter;
amountFilter.apply({
filterOn: Excel.FilterOn.dynamic,
dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
});
// Sync to run the queued commands in Excel.
await context.sync();
});
Datos de la tabla con los filtros aplicados a Categoría e Importe
Borrar los filtros de la tabla
En el siguiente ejemplo de código se borran todos los filtros aplicados actualmente a la tabla.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.clearFilters();
await context.sync();
});
Datos de la tabla sin filtros aplicados
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 getVisibleView() método como se muestra a continuación para obtener el contenido visible de una tabla cada vez que se hayan aplicado filtros de columna.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.getItem("ExpensesTable");
let visibleRange = expensesTable.getDataBodyRange().getVisibleView();
visibleRange.load("values");
await context.sync();
console.log(visibleRange.values);
});
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.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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
});
await context.sync();
});
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).
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let 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";
await context.sync();
});
Tabla después de darle formato
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. El ancho de las columnas y el alto de las filas se establecen para ajustarse mejor a los datos actuales de la tabla.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
// Define values for the range.
let 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.
let range = sheet.getRange("A1:E7");
range.values = values;
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
sheet.activate();
// Convert the range to a table.
let expensesTable = sheet.tables.add('A1:E7', true);
expensesTable.name = "ExpensesTable";
await context.sync();
});
Datos en el rango (antes de que el intervalo se convierta en una tabla)
Datos en la tabla (después de que el intervalo se convierta en una tabla)
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. El ancho de las columnas y el alto de las filas se establecen para ajustarse mejor a los datos actuales de la tabla.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
expensesTable.name = "ExpensesTable";
expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];
let transactions = [
{
"DATE": "1/1/2017",
"MERCHANT": "The Phone Company",
"CATEGORY": "Communications",
"AMOUNT": "$120"
},
{
"DATE": "1/1/2017",
"MERCHANT": "Southridge Video",
"CATEGORY": "Entertainment",
"AMOUNT": "$40"
}
];
let newData = transactions.map(item =>
[item.DATE, item.MERCHANT, item.CATEGORY, item.AMOUNT]);
expensesTable.rows.add(null, newData);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
sheet.activate();
await context.sync();
});
Nueva tabla
