Ejercicio: Trabajar con tablas y gráficos en complementos de Excel

  • 17 minutos

En este ejercicio, aprenderá a trabajar con tablas y gráficos mediante la API de JavaScript de Excel de Office.js. Después de crear una tabla, aprenderá a aplicar acciones de ordenación y filtrado en los datos de la tabla. También aprenderá a crear y editar gráficos en el libro con el complemento personalizado de Excel.

Requisitos previos

Para desarrollar complementos de Office para Microsoft Excel se necesita Excel 2016, versión 1711 (compilación 8730.1000 Hacer clic y ejecutar) o posterior. Puede que necesite ser participante de Office Insider para obtener esta versión. Para más información, vea Participar en Office Insider.

Usará Node.js para crear el complemento de Excel personalizado en este módulo. En los ejercicios de este módulo se presupone que tiene las herramientas siguientes instaladas en su estación de trabajo del desarrollador.

Importante

En la mayoría de los casos, instalar la última versión de las siguientes herramientas es la mejor opción. Las versiones enumeradas aquí fueron usadas la última vez que se publicó y se probó este módulo.

Crear el proyecto de complemento

Ejecute el siguiente comando para crear un proyecto de complemento con el generador de Yeoman:

yo office

Nota:

Cuando ejecute el comando yo office, es posible que reciba mensajes sobre las directivas de recopilación de datos de Yeoman y las herramientas de la CLI de complementos de Office. Use la información adecuada que se proporciona para responder a los mensajes.

Cuando se le pida, proporcione la información siguiente para crear el proyecto de complemento:

  • Elija un tipo de proyecto: proyecto de Panel de tareas de complemento de Office
  • Elija un tipo de script: JavaScript
  • ¿Qué nombre quiere asignar al complemento? Mi complemento de Office
  • ¿Qué aplicación cliente de Office quiere admitir? Excel

Captura de pantalla de las preguntas y respuestas del generador de Yeoman.

Después de completar el asistente, cambie a la carpeta del proyecto creada por el generador (Complemento de Mi Office) e instale los módulos npm mediante la ejecución de npm install.

Sugerencia

Al instalar dependencias, puede ignorar las advertencias o errores, y las instrucciones Enhorabuena. Se ha creado el complemento. Los siguientes pasos: que muestra el generador de Yeoman. En el resto de esta unidad se incluyen todos los pasos que deberá seguir.

Crear una tabla

En esta sección, probará mediante programación que el complemento es compatible con la versión de Excel del usuario actual, agregará una tabla a una hoja de cálculo, la rellenará con datos y le dará formato.

Programe el complemento

  1. Abra el proyecto en el editor de código.

  2. Abra el archivo ./src/taskpane/taskpane.html. El archivo contiene el formato HTML para el panel de tareas.

  3. Busque el elemento <main> y elimine todas las líneas que aparecen detrás de la etiqueta de apertura <main> y antes de la etiqueta de cierre </main>.

  4. Agregue el siguiente marcado inmediatamente después de la etiqueta de apertura <main>:

    <button class="ms-Button" id="create-table">Create Table</button><br/><br/>

  5. Abra el archivo ./src/taskpane/taskpane.js. Este archivo contiene el código de la API de JavaScript de Office que facilita la interacción entre el panel de tareas y la aplicación host de Office.

  6. Para quitar todas las referencias al botón ejecutar y a la función run(), siga estos pasos:

    • Busque y elimine la línea document.getElementById("run").onclick = run;.
    • Encuentre y elimine la función run() completa.

  7. En la llamada al método Office.onReady(), busque la línea if (info.host === Office.HostType.Excel) { y agregue el código siguiente inmediatamente después de esa línea:

    // Determine if the user's version of Office supports all the Office.js APIs that are used in the tutorial.
if (!Office.context.requirements.isSetSupported('ExcelApi', '1.7')) {
  console.log('Sorry. The tutorial add-in uses Excel.js APIs that are not available in your version of Office.');
}

// Assign event handlers and other initialization logic.
document.getElementById("create-table").onclick = createTable;

    Nota:

    • La primera parte de este código determina si la versión del usuario de Excel es compatible con una versión de Excel.js que incluya todas las API que usará esta serie de tutoriales. En un complemento de producción, use el cuerpo del bloque condicional para ocultar o deshabilitar la interfaz de usuario que llamaría a la API no compatible. Esto permitirá que el usuario siga utilizando las partes del complemento que son compatibles con su versión de Excel.
    • En la segunda parte de este código se agrega un controlador de eventos para el botón create-table.

  8. Agregue la función siguiente al final del archivo:

    async function createTable() {
  await Excel.run(async (context) => {

    // TODO1: Queue table creation logic here.

    // TODO2: Queue commands to populate the table with data.

    // TODO3: Queue commands to format the table.

    await context.sync();
  })
  .catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
      console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
  });
}

    Nota:

    • La lógica de negocios de Excel.js se agregará a la función que se pasa a Excel.run(). Esta lógica no se ejecuta inmediatamente. En su lugar, se agrega a una cola de comandos pendientes.
    • El método context.sync() envía todos los comandos en cola a Excel para su ejecución.
    • El Excel.run() va seguido de un bloque catch. Este es el procedimiento recomendado que debe seguir siempre.

  9. En la función createTable(), reemplace TODO1 con el código siguiente:

    const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
const expensesTable = currentWorksheet.tables.add("A1:D1", true /*hasHeaders*/);
expensesTable.name = "ExpensesTable";

    Nota:

    • El código crea una tabla mediante add() el método de la colección de tablas de una hoja de cálculo, que siempre existe aunque esté vacía. Esta es la forma estándar de creación de los objetos Excel.js. No hay API de constructor de clase, y nunca usa un operador new para crear un objeto de Excel. En su lugar, agregue un objeto de la colección primaria.
    • El primer parámetro del método add() es el rango de solamente la fila superior de la tabla, no toda la fila que la tabla utilizará finalmente. Esto es porque al complemento rellena las filas de datos (en el paso siguiente), se agregarán nuevas filas a la tabla en lugar de escribir los valores en las celdas de las filas existentes. Se trata de un patrón muy común, porque el número de filas que tendrá la tabla con frecuencia no se conoce cuando se crea la tabla.
    • Los nombres de tabla deben ser únicos en todo el libro, no solo en la hoja de cálculo.

  10. En la función createTable(), reemplace TODO2 con el código siguiente:

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

expensesTable.rows.add(null /*add at the end*/, [
  ["1/1/2017", "The Phone Company", "Communications", "120"],
  ["1/2/2017", "Northwind Electric Cars", "Transportation", "142.33"],
  ["1/5/2017", "Best For You Organics Company", "Groceries", "27.9"],
  ["1/10/2017", "Coho Vineyard", "Restaurant", "33"],
  ["1/11/2017", "Bellows College", "Education", "350.1"],
  ["1/15/2017", "Trey Research", "Other", "135"],
  ["1/15/2017", "Best For You Organics Company", "Groceries", "97.88"]
]);

    Nota:

    • Se establecen los valores de celda de un rango con una matriz de matrices.
    • Se crean nuevas filas en una tabla mediante una llamada al método add() de la colección de filas de la tabla. Puede agregar varias filas en una sola llamada de add() añadiendo varias matrices de valores de celda de la matriz principal que se pasa como segundo parámetro.

  11. En la función createTable(), reemplace TODO3 con el código siguiente:

    expensesTable.columns.getItemAt(3).getRange().numberFormat = [['\u20AC#,##0.00']];
expensesTable.getRange().format.autofitColumns();
expensesTable.getRange().format.autofitRows();

    Nota:

    • El código obtiene una referencia a la columna Cantidad pasando el índice basado en cero al método getItemAt() del conjunto de columnas de la tabla.
    • Los objetos de colección de Excel.js, como TableCollection, WorksheetCollection y TableColumnCollection, tienen un propiedad items que es una matriz de los tipos de objetos secundarios, como Table, Worksheet o TableColumn, pero un objeto *Collection no es una matriz en sí mismo.
    • Después, el código da formato al rango de la columna Cantidad como euros al segundo decimal.
    • Por último, se asegura de que el ancho de las columnas y el alto de las filas son lo suficientemente grandes como para aceptar el elemento de datos más largo (o más alto). Tenga en cuenta que el código debe obtener objetos Range para dar formato. Los objetos TableColumn y TableRow no cuentan con propiedades de formato.

  12. Compruebe que guardó todos los cambios que realizó en el proyecto.

Probar el complemento

  1. Complete los pasos siguientes para iniciar el servidor web local y transferir localmente el complemento.

    Nota:

    Los complementos de Office deben usar HTTPS y no HTTP, incluso cuando está desarrollando. Si le pedirá que instale un certificado después de ejecutar uno de los siguientes comandos, acepte la solicitud para instalar el certificado que proporciona el generador Yeoman.

    Sugerencia

    Si va a probar el complemento en macOS, ejecute el siguiente comando en el directorio raíz del proyecto antes de continuar. Al ejecutar este comando, se iniciará el servidor web local.

    npm run dev-server

    • Para probar el complemento en Excel, ejecute el siguiente comando en el directorio raíz del proyecto. Se iniciará el servidor web local (si todavía no está en ejecución) y se abrirá Excel con el complemento cargado.

      npm start

    • Para probar el complemento en Excel en la web, ejecute el siguiente comando en el directorio raíz del proyecto. Al ejecutar este comando, se iniciará el servidor web local (si todavía no está en ejecución).

      npm run start:web

      Para usar el complemento, abra un nuevo documento en Excel en la Web y, después, transfiera localmente el complemento siguiendo las instrucciones en Transferir localmente complementos de Office en Office en la Web.

  2. En Excel, seleccione la pestaña Inicio y, luego, el botón Mostrar panel de tareas en la cinta para abrir el panel de tareas del complemento.

    Captura de pantalla del botón de complemento de Excel.

  3. En el panel de tareas, seleccione el botón Crear tabla.

    Captura de pantalla de la tabla creada por el tutorial en Excel.

Filtrar y ordenar una tabla

En esta sección, podrá filtrar y ordenar la tabla que ha creado antes.

Filtrar la tabla

  1. Abra el archivo ./src/taskpane/taskpane.html.

  2. Busque el elemento <button>para el botón create-table y agregue el siguiente marcado después de esa línea:

    <button class="ms-Button" id="filter-table">Filter Table</button><br/><br/>

  3. Abra el archivo ./src/taskpane/taskpane.js.

  4. En la llamada al método Office.onReady(), busque la línea siguiente en el método Office.onRead():

    document.getElementById("create-table").onclick = createTable;

    Agregue el código siguiente inmediatamente después de esa línea:

    document.getElementById("filter-table").onclick = filterTable;

  5. Agregue la función siguiente al final del archivo:

    async function filterTable() {
  await Excel.run(async (context) => {

    // TODO1: Queue commands to filter out all expense categories except
    //        Groceries and Education.

    await context.sync();
  })
  .catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
      console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
  });
}

  6. En la función filterTable(), reemplace TODO1 con el siguiente código:

    const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
const expensesTable = currentWorksheet.tables.getItem('ExpensesTable');
const categoryFilter = expensesTable.columns.getItem('Category').filter;
categoryFilter.applyValuesFilter(['Education', 'Groceries']);

    Nota:

    • Primero, el código obtiene una referencia a la columna que necesita filtrarse pasando el nombre de columna al método getItem(), en lugar de pasar el índice al método getItemAt() como hace el método createTable(). Dado que los usuarios pueden mover las columnas de tabla, la columna de un índice determinado podría cambiar cuando se crea la tabla. Es más seguro usar el nombre de columna para obtener una referencia a la columna. Se ha utilizado getItemAt de forma segura en el tutorial anterior, ya que se ha usado en el mismo método que crea la tabla, así que hay ninguna posibilidad de que un usuario haya movido la columna.
    • El método applyValuesFilter() es uno de varios métodos de filtrado en el objeto Filter.

Ordenar la tabla

  1. Abra el archivo ./src/taskpane/taskpane.html.

  2. Busque el elemento <button>para el botón filter-table y agregue el siguiente marcado después de esa línea:

    <button class="ms-Button" id="sort-table">Sort Table</button><br/><br/>

  3. Abra el archivo ./src/taskpane/taskpane.js.

  4. En la llamada al método Office.onReady(), busque la línea siguiente en el método Office.onRead():

    document.getElementById("filter-table").onclick = filterTable;

    Agregue el código siguiente inmediatamente después de esa línea:

    document.getElementById("sort-table").onclick = sortTable;

  5. Agregue la función siguiente al final del archivo:

    async function sortTable() {
  await Excel.run(async (context) => {

    // TODO1: Queue commands to sort the table by Merchant name.

    await context.sync();
  })
  .catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
      console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
  });
}

  6. En la función sortTable(), reemplace TODO1 con el siguiente código:

    const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
const expensesTable = currentWorksheet.tables.getItem('ExpensesTable');
const sortFields = [
  {
    key: 1,            // Merchant column
    ascending: false,
  }
];

expensesTable.sort.apply(sortFields);

    Nota:

    • El código crea una matriz de objetos SortField que tiene un solo miembro, ya que el complemento solo se ordena por la columna Comerciante.
    • La propiedad key de un objeto SortField es el índice de la columna basado en cero para ordenar.
    • El miembro sort de un Table es un objeto TableSort, no es un método. Los SortField se pasan al método TableSort del objetoapply().

  7. Compruebe que guardó todos los cambios que realizó en el proyecto.

Probar el complemento

  1. Si el servidor web local ya está en ejecución y el complemento ya está cargado en Excel, continúe con el paso 2. De lo contrario, inicie el servidor web local y transfiera localmente el complemento:

    • Para probar el complemento en Excel, ejecute el siguiente comando en el directorio raíz del proyecto. Se iniciará el servidor web local (si todavía no está en ejecución) y se abrirá Excel con el complemento cargado.

      npm start

    • Para probar el complemento en Excel en la web, ejecute el siguiente comando en el directorio raíz del proyecto. Al ejecutar este comando, se iniciará el servidor web local (si todavía no está en ejecución).

      npm run start:web

      Para usar el complemento, abra un nuevo documento en Excel en la Web y, después, transfiera localmente el complemento siguiendo las instrucciones en Transferir localmente complementos de Office en Office en la Web.

  2. Si el panel de tareas del complemento aún no está abierto en Excel, vaya a la pestaña Inicio y seleccione el botón Mostrar panel de tareas en la cinta de opciones para abrirlo.

  3. Si la tabla que ha agregado antes en este tutorial no aparece en la hoja de cálculo abierta, seleccione el botón Crear tabla en el panel de tareas.

  4. Seleccione los botones Filtrar tabla y Ordenar tabla, en cualquier orden.

    Captura de pantalla de la tabla filtrada y ordenada por tutorial en Excel.

Crear un gráfico

En esta sección, creará un gráfico con los datos de la tabla que ha creado antes y, después, dará formato al gráfico.

Crear un gráfico con los datos de tabla

  1. Abra el archivo ./src/taskpane/taskpane.html.

  2. Busque el elemento <button> para el botón sort-table y agregue el marcado siguiente después de esa línea:

    <button class="ms-Button" id="create-chart">Create Chart</button><br/><br/>

  3. Abra el archivo ./src/taskpane/taskpane.js.

  4. En la llamada al método Office.onReady(), busque la línea siguiente en el método Office.onRead():

    document.getElementById("sort-table").onclick = sortTable;

    Agregue el código siguiente inmediatamente después de esa línea:

    document.getElementById("create-chart").onclick = createChart;

  5. Agregue la siguiente función al final del archivo:

    async function createChart() {
  await Excel.run(async (context) => {

    // TODO1: Queue commands to get the range of data to be charted.

    // TODO2: Queue command to create the chart and define its type.

    // TODO3: Queue commands to position and format the chart.

    await context.sync();
  })
  .catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
      console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
  });
}

  6. En la función createChart(), reemplace TODO1 con el siguiente código. Para excluir la fila de encabezado, el código usa el método Table.getDataBodyRange() para obtener el rango de datos que quiere representar en el gráfico en lugar del método getRange().

    const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
const expensesTable = currentWorksheet.tables.getItem('ExpensesTable');
const dataRange = expensesTable.getDataBodyRange();

  7. En la función createChart(), reemplace TODO2 con el código siguiente:

    const chart = currentWorksheet.charts.add('ColumnClustered', dataRange, 'auto');

    Nota:

    • El primer parámetro para el método add() especifica el tipo de gráfico. Hay varios tipos.
    • El segundo parámetro especifica el rango de datos que se incluirá en el gráfico.
    • El tercer parámetro determina si una serie de puntos de datos de la tabla se debe representar en filas o en columnas. La opción auto pide a Excel que decida el mejor método.

  8. En la función createChart(), reemplace TODO3 con el siguiente código. La mayor parte de este código es fácil de entender:

    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;';

    Nota:

    • Los parámetros del método setPosition() especifican las celdas superior izquierda e inferior derecha del área de hoja de cálculo que debe contener el gráfico. Excel puede ajustar los elementos como el ancho de línea para que el gráfico tenga una buena apariencia en el espacio que se le ha asignado.
    • Una "serie" es un conjunto de puntos de datos de una columna de la tabla. Como hay una única columna que no es de cadena en la tabla, Excel deduce que la columna es la única de puntos de datos en el gráfico. Interpreta a las otras columnas como etiquetas de gráfico. Así que habrá una sola serie en el gráfico y tendrá índice 0. Es la que se etiqueta con "Value in €" (valor en euros).

  9. Compruebe que guardó todos los cambios que realizó en el proyecto.

Probar el complemento

  1. Si el servidor web local ya está en ejecución y el complemento ya está cargado en Excel, continúe con el paso 2. De lo contrario, inicie el servidor web local y transfiera localmente el complemento:

    • Para probar el complemento en Excel, ejecute el siguiente comando en el directorio raíz del proyecto. Se iniciará el servidor web local (si todavía no está en ejecución) y se abrirá Excel con el complemento cargado.

      npm start

    • Para probar el complemento en Excel en la web, ejecute el siguiente comando en el directorio raíz del proyecto. Al ejecutar este comando, se iniciará el servidor web local (si todavía no está en ejecución).

      npm run start:web

      Para usar el complemento, abra un nuevo documento en Excel en la Web y, después, transfiera localmente el complemento siguiendo las instrucciones en Transferir localmente complementos de Office en Office en la Web.

  2. Si el panel de tareas del complemento aún no está abierto en Excel, vaya a la pestaña Inicio y seleccione el botón Mostrar panel de tareas en la cinta de opciones para abrirlo.

  3. Si la tabla que ha agregado antes en este tutorial no aparece en la hoja de cálculo abierta, seleccione el botón Crear tabla y, después, el botón Filtrar tabla y el botón Ordenar tabla, en cualquier orden.

  4. Seleccione el botón Crear gráfico. Se crea un gráfico y se incluyen únicamente los datos de las filas que se han filtrado. Las etiquetas de los puntos de datos en la parte inferior están en el orden del gráfico, es decir, los nombres de los comerciantes en orden alfabético inverso.

Captura de pantalla del gráfico creado por el tutorial en Excel.

Resumen

En este ejercicio, ha aprendido a trabajar con tablas y gráficos mediante la API de JavaScript de Excel de Office.js. Después de crear una tabla, ha aprendido a aplicar acciones de ordenación y filtrado en los datos de la tabla. También ha obtenido información sobre cómo crear y editar gráficos en el libro con el complemento personalizado de Excel.

Comprobar sus conocimientos

1.

¿Cómo se obtiene una instancia del contexto de Excel actual de la API de JavaScript de Excel?

2.

¿Cómo puede un complemento detectar la versión de la API de JavaScript de Excel admitida en el componente actual?