Trabajar con tablas y gráficos en libros de Excel

14 minutos

La API de JavaScript de Excel controla el texto, las tablas y los gráficos de los libros de Excel mediante programación. En esta unidad, aprenderá a trabajar con tablas, incluidas las opciones de formato, cómo filtrar datos y ordenarlos en la tabla. También aprenderá a agregar gráficos a las hojas de cálculo y personalizarlos.

Información general sobre la plataforma de complementos de Office

La plataforma para desarrolladores de Microsoft 365, que incluye Office, ofrece muchos lienzos para que los desarrolladores inserten personalizaciones y complementos de Office. Los tres lienzos son los documentos, las conversaciones y las páginas.

Microsoft Teams posibilita conversaciones entre los usuarios y permite a los desarrolladores ampliar la experiencia mediante extensiones de mensajería, bots de conversación y otras opciones de personalización.

Los desarrolladores personalizan páginas en SharePoint Server y SharePoint Online con SharePoint Framework.

Los clientes de Office, como Word, Excel, PowerPoint, OneNote y Outlook también se pueden ampliar para implementar paneles de tareas personalizados, acciones y otras personalizaciones mediante complementos.

Diagrama de información general de la plataforma Microsoft 365.

Anatomía del complemento Office.js para Microsoft Excel

Comenzaremos examinando una anatomía de Office.js para Microsoft Excel:

Esquema de la anatomía de un complemento de Office.js.

Todos los complementos de Office deben desactivar el método Office.initialize() cuando una página carga por primera vez el complemento.
Si usa una funcionalidad de Office.js más reciente en el complemento, es importante comprobar si el cliente admite esas extensiones mediante la API de requirements.
Para las API de JavaScript de Excel, usará el método Excel.run() para obtener una instancia del documento actual context.
Una vez que tenga una referencia al context del documento de Excel actual, puede cargar cualquier propiedad en el contexto mediante el método load(). Este método agregará la cola de solicitudes, lo que le permite encadenar varias solicitudes por motivos de rendimiento.
Cuando esté listo para recuperar las propiedades que ha puesto en cola o para realizar cualquier acción en cola, use el método context.sync() para ejecutar el lote de operaciones en cola definidas mediante el método load().
El método context.sync() devuelve una promesa de JavaScript que se puede usar para obtener resultados o una operación anterior y realizar nuevas operaciones.
Como procedimiento recomendado, debe escuchar, detectar y controlar los errores que puedan producirse al trabajar con las API de JavaScript de Excel.

Jerarquía de objetos de Excel

Los desarrolladores de complementos de Excel deben comprender la jerarquía de un libro de Excel y cómo se relaciona con los objetos de Office.js.

Jerarquía de un libro

Office.js proporciona contexto a un libro de Excel a través de Excel.run() y la propiedad context.workbook.

Diagrama que muestra la jerarquía de objetos de Excel.

El libro contiene hojas de cálculo que contienen muchas colecciones. Estas colecciones incluyen elementos como gráficos, tablas y tablas dinámicas.

Se puede tener acceso a muchos objetos de una hoja de cálculo directamente desde el objeto de libro, incluidas las tablas y tablas dinámicas.

Hojas de cálculo

Las hojas de cálculo son conscientes de sus elementos relacionados mediante métodos getNext() y getPrevious().

Puede obtener la hoja de cálculo activa mediante el método workbook.worksheets.getActiveWorksheet() y establecer la hoja de cálculo activa mediante el método worksheet.activate().

Office.js también ofrece muchos eventos de hoja de cálculo, como onActivated, onDeactivatedy onSelectionChanged que los desarrolladores pueden usar en sus complementos personalizados.

Encabezados y estilos

Las tablas son el núcleo de una hoja de cálculo. Excel admite la definición de un rango de datos que constituye la base de una tabla de datos.

Captura de pantalla de ejemplo de libro de Excel.

Rangos

Un rango representa un conjunto de una o más celdas contiguas, como una celda, una fila, una columna, un bloque de celdas, etc.

Puede obtener un objeto de rango con Office.js mediante una hoja de cálculo y una dirección. Por ejemplo, el rango "A1:D4" representa un rango de las celdas superior izquierda a derecha de esta captura de pantalla.

Tablas

Una tabla se establece en función de un intervalo de datos.

La función tables.add() acepta un intervalo de datos con una marca para indicar si la tabla tiene encabezados o no.

Las tablas existentes se pueden recuperar por su identificador o rango con nombre. Los desarrolladores también pueden recorrer en iteración una colección de tablas de la hoja de cálculo.

Una vez agregada la tabla, se pueden agregar encabezados y filas de tabla mediante matrices bidimensionales.

Encabezados

Una tabla creada con una marca de encabezado usará la primera fila del intervalo de datos para sus encabezados.

También puede establecer valores de encabezado mediante la propiedad getHeaderRowRange().values con una matriz bidimensional.

Los fragmentos de código siguientes muestran ejemplos de trabajo de texto y párrafos de trabajo con la API de JavaScript de Excel:

Obtener un rango de datos de una hoja de cálculo:
```
const range = currWorksheet.getRange('A1:D1');
```

Insertar una tabla en la hoja de cálculo basándose en el rango especificado

const table = currWorksheet.tables.add(range, true);
table.name = "ExpensesTable";

Obtener una tabla por nombre de un libro o una hoja de cálculo
```
const table = workbook.tables.getItem("ExpensesTable");
```

Agregar una fila de encabezado a la tabla especificada

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

Inmovilizar la fila de encabezado inmovilizando la fila superior
```
currWorksheet.freezePanes.freezeRows(1);
```

Eliminar la segunda fila de una tabla

const row = context.workbook.tables.getItem("ExpensesTable").rows.getItemAt(1);
row.delete();

Actualizar la segunda fila de una tabla

const row = context.workbook.tables.getItem("ExpensesTable").rows.getItemAt(1);
row.values = [["1/15/2017", "Best For You Organics Company", "Groceries", "97.8"]];

Agregar filas al final de una tabla
```
table.rows.add(null, [
  ["1/1/2017", "The Phone Company", "Communications", "120"]
]);
```
Nota:

El primer parámetro del método add() indica el índice donde se deben agregar los datos. Un valor de null o -1, la fila se agrega al final de la tabla final.
Agregar una fila
```
table.rows.add(0, [
  ["1/10/2017", "Coho Vineyard", "Restaurant", "33"]
]);
```
Nota:

El primer parámetro del método add() indica el índice donde se deben agregar los datos. Valor de 0 agrega los datos al principio del índice.

Filtrar tablas

Cualquier columna de una tabla se puede filtrar mediante Office.js.

Captura de pantalla de Excel con una tabla de datos.

Filtre una columna obteniendo primero una referencia a la columna y, a continuación, use el método applyValuesFilter() para filtrar por valores específicos.

El código siguiente filtra la columna de categoría de por los valores Education y Comestibles:

const categoryFilter = table.columns.getItem('Category').filter;
categoryFilter.applyValuesFilter(["Education", "Groceries"]);

También puede volver a aplicar filtros y borrar filtros de una tabla mediante programación mediante las funciones de tabla reapplyFilters() y clearFilters(), respectivamente.

En el código siguiente se muestra cómo volver a aplicar y borrar filtros:

// re-apply filters
table.reapplyFilters();

// clear filters
table.clearFilters();

Ordenación de tablas

Los desarrolladores pueden ordenar los datos de tabla mediante la API de Office.js desde complementos de Excel. Para ordenar, llame al método table.sort.apply() e incluya el argumento SortFields para especificar los campos por los que ordenar.

El código siguiente define una matriz de campos de ordenación. La propiedad key especifica el índice de columna de la tabla y la propiedad booleana ascending especifica si la ordenación debe ser ascendente o descendente:

const sortFields = [
  { key: 1, ascending: false },
  { key: 2, ascending: true }
];
table.sort.apply(sortFields);

Puede volver a aplicar y borrar las ordenaciones de tabla mediante métodos table.sort.reapply() y table.sort.clear().

// re-apply sort
table.sort.reapply();

// clear filters
table.sort.clear();

Gráficos

Microsoft Excel se ha convertido en un área de juegos para la manipulación y visualización de datos. Por lo tanto, no es ninguna sorpresa que las API de JavaScript de Excel permitan a los desarrolladores agregar y manipular gráficos.

Los gráficos existen dentro de las hojas de cálculo, pero también se puede acceder a ellos directamente desde el objeto de libro.

Captura de pantalla del libro de Excel con un gráfico.

Los gráficos tienen muchas propiedades relacionales complejas que se pueden usar para ajustar el aspecto de un gráfico. Estos incluyen títulos, leyendas, ejes, series, etiquetas y formato.

Los usuarios y desarrolladores pueden crear un gráfico basado en un rango de datos y a menudo se crean con tablas.

La función worksheet.charts.add() se usa para crear un gráfico, que acepta un gráfico type, datos range y seriesBy. El argumento seriesBy admite los siguientes valores

Automático
Escalar
Matriz

Excel admite muchos tipos diferentes de gráficos. Los desarrolladores pueden agregar un gráfico a una hoja de cálculo mediante el método worksheet.carts.add("{REPLACE_WITH_CHARTTYPE_ENUM}", range, "{REPLACE_WITH_CHARTSERIESBY_ENUM}").

El argumento ChartType especifica el tipo de gráfico que se va a usar. Consulte el SDK para conocer las opciones disponibles en la enumeración Excel.ChartType.

El argumento ChartSeriesBy especifica si la serie se realiza por filas o por columnas. Consulte el SDK para conocer las opciones disponibles en la enumeración Excel.ChartSeriesBy.

Propiedades del gráfico

El objeto de gráfico de Excel contiene varias propiedades que los desarrolladores pueden usar para personalizar gráficos en hojas de cálculo de Excel. En la tabla siguiente, se enumeran algunas de las propiedades usadas habitualmente en el objeto de gráfico Office.js:

Propiedad	Descripción
`chartType`	Representa el tipo de un gráfico (los valores posibles están en la diapositiva anterior).
`height`	Representa el alto, en puntos, del objeto de gráfico.
`id`	Identificador único del gráfico.
`left`	Distancia, en puntos, desde el lado izquierdo del gráfico hasta el origen de la hoja de cálculo.
`name`	Representa el nombre de un objeto de gráfico.
`showAllFieldButtons`	Indica si se mostrarán todos los botones de campo en un Gráfico dinámico.
`top`	Representa la distancia, en puntos, desde el borde superior del objeto hasta la parte superior de la fila 1 (en una hoja de cálculo) o la parte superior del área del gráfico (en un gráfico).
`width`	Representa el ancho, en puntos, del objeto del gráfico.

Además de las propiedades de la tabla anterior, el objeto de gráfico incluye varias propiedades de relación. Esto marca la diferencia en cómo se muestra el gráfico en Excel:

Relación	Descripción
axes	Representa los ejes del gráfico. Solo lectura.
dataLabels	Representa la clase DataLabels del gráfico. Solo lectura.
format	Encapsula las propiedades de formato del área del gráfico. Solo lectura.
legend	Representa la leyenda del gráfico. Solo lectura.
series	Representa una sola serie o una colección de series del gráfico. Solo lectura.
title	Representa el título del gráfico especificado, incluido el texto, la visibilidad, la posición y el formato del título. Solo lectura.
hoja de cálculo	La hoja de cálculo que contiene el gráfico actual. Solo lectura.

Sugerencia

Consulte la documentación de Office.js de Excel para ver todas las propiedades, métodos y eventos del objeto Excel.Chart.

En los ejemplos de código siguientes se muestra cómo usar la API de Office.js para crear y manipular gráficos en Excel:

Agregar un gráfico de columnas agrupadas mediante datos de tabla

const dataRange = table.getDataBodyRange();
let chart = currWorksheet.charts.add("ColumnClustered", dataRange, "auto");

Obtener gráfico existente por nombre

let chart = workbook.charts.getItem("MyChart");

Modificar las propiedades del gráfico, como la posición, los títulos, los colores y los tamaños de fuente

chart.setPosition("A15", "F30");
chart.title.text = "Expenses";
chart.legend.position = "right"
chart.legend.format.fill.setSolidColor("white");
chart.dataLabels.format.font.size = 15;
chart.dataLabels.format.font.color = "black";
chart.series.getItemAt(0).name = "Value in &euro;";

Resumen

La API de JavaScript de Excel controla el texto, las tablas y los gráficos de los libros de Excel mediante programación. En esta unidad, ha aprendido como trabajar con tablas, incluidas las opciones de formato, cómo filtrar datos y ordenarlos en la tabla. También ha aprendido a agregar gráficos a las hojas de cálculo y personalizarlos.

Continuar