Filtrar tablas dinámicas por etiqueta o valor

Introducción

Las tablas dinámicas son potentes herramientas de análisis, pero los resúmenes sin procesar a menudo contienen mucha más información de la que necesita presentar. El filtrado es el mecanismo principal para reducir una tabla dinámica a las filas, columnas o valores que importan para un informe específico. Aspose.Cells for Node.js via Java replica las capacidades de filtrado disponibles en Microsoft Excel, exponiéndolas mediante programación para que la generación de informes pueda automatizarse por completo. Las siguientes estrategias de filtrado se tratan en este artículo:

  1. Filtro de etiqueta — filtra los elementos de un campo de fila o columna según sus etiquetas de texto.
  2. Filtro de fecha — filtra campos de fila o columna que solo contienen valores de fecha-hora (o espacios en blanco).
  3. Filtro de valor — filtra elementos según los valores agregados de un campo de datos.
  4. Filtro de los 10 mejores — muestra solo los N elementos superiores o inferiores clasificados por un campo de valor.
  5. Ocultar/Mostrar elementos dinámicos — controla manualmente la visibilidad de cada elemento individual en un campo. Cada enfoque utiliza un método diferente en la clase PivotField o una propiedad en la clase PivotItem. Después de aplicar cualquier filtro, debe llamar a refreshData() y calculateData() en la tabla dinámica para que los datos en caché y los valores calculados reflejen el nuevo estado del filtro.

Filtro de etiqueta

Un filtro de etiqueta le permite filtrar los elementos de un campo de fila o columna comparando sus títulos de texto con un patrón. Esto resulta útil cuando desea mostrar solo los productos cuyos nombres comiencen con una letra específica, contengan una palabra particular o coincidan con algún otro criterio basado en el título. Aspose.Cells expone el filtrado por etiqueta mediante el método PivotField.filterByLabel(PivotFilterType, string). La enumeración PivotFilterType incluye valores como CaptionBeginsWith, CaptionContains, CaptionEndsWith, CaptionDoesNotContain, CaptionIsNotBlank, CaptionIsBlank, entre otros. El segundo argumento proporciona la cadena de etiqueta utilizada para la comparación. El siguiente ejemplo carga un libro que contiene una tabla dinámica existente, aplica un filtro de etiqueta para que solo permanezcan visibles los elementos cuyas etiquetas comiencen con un prefijo especificado, actualiza la tabla dinámica y guarda el resultado.

let fileName = "sample.xlsx";
let prefix = "B";
// Cargar el libro de trabajo existente que contiene una tabla dinámica
let workbook = new AsposeCells.Workbook(fileName);
// Acceder a la hoja de trabajo por índice (primera hoja)
let worksheet = workbook.getWorksheets().get(0);
// Acceder a la tabla dinámica por índice
let pivotTable = worksheet.getPivotTables().get(0);
// Obtener el primer PivotField de fila
let rowField = pivotTable.getRowFields().get(0);
// Aplicar el filtro de etiquetas: mostrar solo los elementos de fila cuyas etiquetas comiencen con el prefijo proporcionado
rowField.filterByLabel(AsposeCells.PivotFilterType.CaptionBeginsWith, prefix, "");
// Actualizar y recalcular los datos de la tabla dinámica para que el filtro surta efecto
pivotTable.getPivotCache().refresh();
// Guardar el libro de trabajo de vuelta en el disco
workbook.save(fileName);

Filtro de fecha

Los filtros de fecha le permiten reducir una tabla dinámica según criterios basados en fechas como hoy, la semana pasada, este mes, el próximo trimestre o un rango de fechas específico. Son filtros especializados que solo funcionan contra campos que almacenan información de fecha-hora.

Aspose.Cells expone el filtrado por fecha mediante el método PivotField.filterByDate(PivotFilterType, params DateTime[] values). La enumeración PivotFilterType contiene valores de fecha dedicados como Today, Yesterday, LastWeek, ThisWeek, NextWeek, LastMonth, ThisMonth, NextMonth, LastQuarter, ThisQuarter, NextQuarter, LastYear, ThisYear, NextYear, y Between. Según el tipo de filtro elegido, pasa uno o dos valores DateTime (para Between, pasa las fechas de inicio y fin). El siguiente ejemplo carga un libro con una tabla dinámica cuya área de fila contiene un campo de fecha, aplica un filtro de fecha que restringe los elementos visibles a un rango de fechas particular, actualiza la tabla dinámica y guarda el libro.

let inputPath = "sample.xlsx";
let outputPath = "output_filtered.xlsx";
if (!fs.existsSync(inputPath))
{
    throw new Error("Source workbook not found. Path: " + inputPath);
}
// Cargar el libro de trabajo existente que contiene la tabla dinámica
var workbook = new AsposeCells.Workbook(inputPath);
// Acceder a la hoja de cálculo que contiene la tabla dinámica (por índice)
var worksheet = workbook.getWorksheets().get(0);
// Acceder a la tabla dinámica por índice
var pivotTable = worksheet.getPivotTables().get(0);
// Obtener el PivotField de fecha del área de filas
// (El filtro de fecha solo funciona cuando el área de filas/columnas contiene solo celdas de fecha-hora o espacios en blanco)
let dateField = pivotTable.getRowFields().get(0);
// Definir el criterio de fecha para el filtro Entre
let startDate = new Date(2020, 0, 1);
let endDate = new Date(2020, 11, 31);
// Aplicar el filtro de fecha en el campo dinámico
dateField.filterByDate(AsposeCells.PivotFilterType.DateBetween, startDate, endDate);
// Actualizar y recalcular la tabla dinámica para que el filtro surta efecto
pivotTable.getPivotCache().refresh();
// Guardar el libro de trabajo
workbook.save(outputPath);

Filtro de valor

Los filtros de valor operan sobre los valores agregados que una tabla dinámica calcula en su área de datos. En lugar de coincidir con etiquetas de texto, comparan totales numéricos contra un umbral. Los casos de uso típicos incluyen mostrar solo productos cuya suma de ventas supere una cantidad objetivo o solo regiones cuyo recuento de transacciones esté dentro de un rango. Aspose.Cells expone el filtrado por valor mediante el método PivotField.filterByValue(PivotField valueField, PivotFilterType filterType, params object[] values). El parámetro filterType utiliza valores como ValueGreaterThan, ValueLessThan, ValueBetween, ValueEqual, ValueNotEqual, ValueGreaterThanOrEqual, y ValueLessThanOrEqual. El parámetro valueField especifica qué campo de datos debe evaluarse, y los argumentos finales proporcionan los valores umbral. El siguiente ejemplo carga un libro con una tabla dinámica, aplica un filtro de valor que mantiene solo los elementos cuyas ventas agregadas superen un umbral numérico, actualiza la tabla dinámica y guarda el libro.

var workbook = new AsposeCells.Workbook("sample.xlsx");
var worksheet = workbook.getWorksheets().get(0);
var pivotTable = worksheet.getPivotTables().get(0);
var rowField = pivotTable.getRowFields().get(0);
var dataField = pivotTable.getDataFields().get(0);
// Encontrar el índice del campo de datos manualmente ya que PivotFieldCollection no tiene IndexOf
var dataFieldIndex = -1;
for (var i = 0; i < pivotTable.getDataFields().getCount(); i++)
{
    if (pivotTable.getDataFields().get(i) == dataField)
    {
        dataFieldIndex = i;
        break;
    }
}
if (dataFieldIndex >= 0)
{
    rowField.filterByValue(dataFieldIndex, AsposeCells.Pivot.PivotFilterType.ValueGreaterThan, 5000, Number.MAX_VALUE);
}
pivotTable.getPivotCache().refresh();
workbook.save("output.xlsx");

Filtro de los 10 mejores

El filtro de los 10 mejores es una forma especializada de filtro de valor que retiene solo los N elementos superiores o inferiores según un campo de valor elegido. Se usa comúnmente para informes de clasificación como “los 10 mejores productos por ingresos” o “las 5 regiones inferiores por recuento de ventas”.

Aspose.Cells expone el filtrado de los 10 mejores mediante el método PivotField.filterTop10(int itemCount, bool isTop, PivotField valueField, PivotFilterType filterType). El parámetro itemCount define cuántos elementos retener, isTop indica si se mantienen los elementos superiores (true) o los inferiores (false), valueField hace referencia al campo de datos utilizado para la clasificación, y filterType controla cómo se calcula el valor (normalmente Sum, pero también Count y Percent). El siguiente ejemplo carga un libro con una tabla dinámica que contiene un campo de valor, aplica un filtro de los 10 mejores para mantener solo los 10 elementos superiores por la suma de ventas, actualiza la tabla dinámica y guarda el libro.

let inputPath = "input.xlsx";
let outputPath = "output.xlsx";
let workbook = new AsposeCells.Workbook(inputPath);
// Acceder a la hoja de cálculo que contiene la tabla dinámica (índice 0)
let worksheet = workbook.getWorksheets().get(0);
// Acceder a la tabla dinámica por índice
let pivotTable = worksheet.getPivotTables().get(0);
// Confirmar que hay al menos un PivotField de valor en el área de datos
if (pivotTable.getDataFields().getCount() == 0)
{
    throw new Error("Pivot table has no value (data) PivotField.");
}
let valueField = pivotTable.getDataFields().get(0);
// Obtener el PivotField de fila objetivo (el campo al que queremos aplicar Top 10)
let rowField = pivotTable.getRowFields().get(0);
// El primer (y único) campo de datos está en el índice 0; Top 10 clasifica por él.
let valueFieldIndex = 0;
// Aplicar el filtro Top 10 en el campo de fila:
//   - itemCount   = 10
//   - filterType  = PivotFilterType.Sum
//   - isTop       = true (N superiores; false significaría N inferiores)
//   - valueFieldIndex = el índice del campo de datos utilizado para clasificar los elementos
rowField.filterTop10(10, AsposeCells.PivotFilterType.Sum, true, valueFieldIndex);
// Actualizar los datos de la tabla dinámica y recalcularla para que el filtro surta efecto
pivotTable.getPivotCache().refresh();
// Guardar el libro
workbook.save(outputPath);

Filtrar ocultando o mostrando elementos dinámicos

Además de las API de filtros estructurados, Aspose.Cells le permite controlar directamente la visibilidad de cada elemento dinámico individual. Al iterar a través de la colección PivotItems de un PivotField y alternar la propiedad IsHidden, puede suprimir selectivamente elementos específicos sin aplicar un filtro basado en fórmulas. Establecer IsHidden = true oculta el elemento de la tabla dinámica; establecer IsHidden = false lo muestra nuevamente y lo hace visible de nuevo. Este enfoque es útil cuando la regla de filtrado es irregular o específica de un elemento, como ocultar un pequeño número de categorías con nombre que no deben aparecer en un informe particular. El siguiente ejemplo carga una tabla dinámica, oculta un elemento específico por nombre, demuestra cómo mostrarlo nuevamente, actualiza la tabla dinámica y guarda el libro.

let workbook = new AsposeCells.Workbook("pivot_table_sample.xlsx");
// Access the first worksheet which contains the pivot table
let sheet = workbook.getWorksheets().get(0);
// Access the pivot table by index (the first pivot table on the sheet)
let pivotTable = sheet.getPivotTables().get(0);
// Retrieve the target PivotField (the first row label field that we'll hide/unhide items in)
let pivotField = pivotTable.getRowFields().get(0);
// Iterate through the PivotItems collection of the selected PivotField
let itemCount = pivotField.getPivotItems().getCount();
for (let i = 0; i < itemCount; i++) {
    let item = pivotField.getPivotItems().get(i);
    // Hide pivot items that match a specific name/criterion
    if (item.getName() == "Item1" || item.getName() == "Item2") {
        item.setIsHidden(true);
    }
    // Demonstrate unhiding: re-show a previously hidden pivot item
    if (item.getName() == "Item3") {
        item.setIsHidden(false);
    }
}
// Refresh and recalculate the pivot table so changes take effect
pivotTable.getPivotCache().refreshData();
// Save the workbook — hidden items stay in the underlying data
// but are excluded from the displayed pivot table output
workbook.save("output_pivot_filtered.xlsx");

Resumen

Aspose.Cells for Node.js via Java proporciona un conjunto completo de capacidades de filtrado de tablas dinámicas que coinciden con las que se encuentran en Microsoft Excel. Los filtros de etiqueta, fecha y valor cubren los escenarios analíticos más comunes, mientras que el filtro de los 10 mejores maneja los informes de clasificación. Cuando la regla de filtrado es irregular, la propiedad PivotItem.IsHidden ofrece una alternativa flexible a nivel de elemento. Combinar estas estrategias —por ejemplo, aplicar un filtro de etiqueta y luego ocultar elementos específicos— le permite construir informes de tablas dinámicas específicamente dirigidos completamente desde código.