Ejemplos de tabla

Estos ejemplos muestran interacciones comunes con tablas de Excel.

Creación de una tabla ordenada

En este ejemplo se crea una tabla a partir del rango usado de la hoja de cálculo actual y, a continuación, se ordena en función de la primera columna.

function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  const selectedSheet = workbook.getActiveWorksheet();

  // Create a table with the used cells.
  const usedRange = selectedSheet.getUsedRange();
  const newTable = selectedSheet.addTable(usedRange, true);

  // Sort the table using the first column.
  newTable.getSort().apply([{ key: 0, ascending: true }]);
}

Filtrar una tabla

Este ejemplo filtra una tabla existente utilizando los valores de una de las columnas.

function main(workbook: ExcelScript.Workbook) {
  // Get the table in the workbook named "StationTable".
  const table = workbook.getTable("StationTable");

  // Get the "Station" table column for the filter.
  const stationColumn = table.getColumnByName("Station");

  // Apply a filter to the table that will only show rows 
  // with a value of "Station-1" in the "Station" column.
  stationColumn.getFilter().applyValuesFilter(["Station-1"]);
}

Sugerencia

Copie la información filtrada en el libro mediante Range.copyFrom. Agregue la siguiente línea al final del script para crear una nueva hoja de cálculo con los datos filtrados.

  workbook.addWorksheet().getRange("A1").copyFrom(table.getRange());

Filtrar un valor

El ejemplo anterior filtra una tabla en función de una lista de valores incluidos. Para excluir un valor determinado de la tabla, debe proporcionar la lista de todos los demás valores de la columna. En este ejemplo se usa una función columnToSet para convertir una columna en un conjunto de valores únicos. A continuación, ese conjunto quita el valor excluido ("Station-1").

function main(workbook: ExcelScript.Workbook) {
  // Get the table in the workbook named "StationTable".
  const table = workbook.getTable("StationTable");

  // Get the "Station" table column for the filter.
  const stationColumn = table.getColumnByName("Station");

  // Get a list of unique values in the station column.
  const stationSet = columnToSet(stationColumn);

  // Apply a filter to the table that will only show rows
  // that don't have a value of "Station-1" in the "Station" column. 
  stationColumn.getFilter().applyValuesFilter(stationSet.filter((value) => {
      return value !== "Station-1";
  }));
}

/**
 * Convert a column into a set so it only contains unique values.
 */
function columnToSet(column: ExcelScript.TableColumn): string[] {
    const range = column.getRangeBetweenHeaderAndTotal().getValues() as string[][];
    const columnSet: string[] = [];
    range.forEach((value) => {
        if (!columnSet.includes(value[0])) {
            columnSet.push(value[0]);
        }
    });

    return columnSet;
}

Quitar filtros de columna de tabla

En este ejemplo se quitan los filtros de una columna de tabla, en función de la ubicación de celda activa. El script detecta si la celda forma parte de una tabla, determina la columna de tabla y borra los filtros que se aplican a ella.

Descargue table-with-filter.xlsx de un libro listo para usar. Agregue el siguiente script para probar el ejemplo usted mismo.

function main(workbook: ExcelScript.Workbook) {
  // Get the active cell.
  const cell = workbook.getActiveCell();

  // Get the tables associated with that cell.
  // Since tables can't overlap, this will be one table at most.
  const currentTable = cell.getTables()[0];

  // If there's no table on the selection, end the script.
  if (!currentTable) {
    console.log("The selection is not in a table.");
    return;
  }

  // Get the table header above the current cell by referencing its column.
  const entireColumn = cell.getEntireColumn();
  const intersect = entireColumn.getIntersection(currentTable.getRange());
  const headerCellValue = intersect.getCell(0, 0).getValue() as string;

  // Get the TableColumn object matching that header.
  const tableColumn = currentTable.getColumnByName(headerCellValue);

  // Clear the filters on that table column.
  tableColumn.getFilter().clear();
}

Antes de borrar el filtro de columna (observe la celda activa)

Después de borrar el filtro de columna

Sugerencia

Si desea obtener más información sobre cómo guardar el filtro antes de borrarlo (y volver a aplicarlo más adelante), consulte Mover filas entre tablas guardando filtros, un ejemplo más avanzado.

Referencia dinámica a los valores de tabla

Este script usa la sintaxis "@COLUMN_NAME" para establecer fórmulas en una columna de tabla. Los nombres de columna de la tabla se pueden cambiar sin cambiar este script.

function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  const table = workbook.getTable("Profits");

  // Get the column names for columns 2 and 3.
  // Note that these are 1-based indices.
  const nameOfColumn2 = table.getColumn(2).getName();
  const nameOfColumn3 = table.getColumn(3).getName();

  // Set the formula of the fourth column to be the product of the values found
  // in that row's second and third columns.
  const combinedColumn = table.getColumn(4).getRangeBetweenHeaderAndTotal();
  combinedColumn.setFormula(`=[@[${nameOfColumn2}]]*[@[${nameOfColumn3}]]`);
}

Antes del script

Mes	Precio	Unidades vendidas	Total
Jan	45	5
Febrero	45	3
Marzo	45	6

Después del script

Mes	Precio	Unidades vendidas	Total
Jan	45	5	225
Febrero	45	3	135
Marzo	45	6	270