Добавить поля строк и столбцов сводной таблицы в Aspose.Cells для .NET

Добавление поля в область строк или столбцов

Метод PivotTable.addFieldToArea(int fieldType, String fieldName) перемещает базовое поле из исходных данных в одну из четырёх областей сводной таблицы. Аргумент fieldType принимает одно из следующих значений PivotFieldType.

ROW — поля, размещаемые вертикально слева
COLUMN — поля, размещаемые горизонтально в верхней части
DATA — поля, значения которых агрегируются
PAGE — поля, используемые в качестве фильтров отчёта

После добавления полей к ним можно обращаться через свойства PivotTable.getRowFields() и PivotTable.getColumnFields(). Каждое свойство возвращает PivotFieldCollection. Поле с индексом 0 в RowFields является самым внешним полем строки, а последующие индексы представляют поля, вложенные внутрь него. То же правило индексации применяется к ColumnFields.

Порядок вложенности полей имеет значение. Добавление Category в область строк первым, а затем Item создаёт сводную таблицу, в которой внешняя группировка — Category, а внутренняя — Item. Изменение порядка на противоположный меняет иерархию.

Промежуточные итоги полей сводной таблицы

Метод PivotField.setSubtotals(int subtotalType, boolean shown) управляет тем, какие строки промежуточных итогов отображаются для поля сводной таблицы. Каждый вызов независимо переключает один тип промежуточного итога. Передача shown = true отображает промежуточный итог, а shown = false скрывает его. Поскольку каждый вызов затрагивает только один тип, многократный вызов метода с разными значениями subtotalType формирует настраиваемое подмножество промежуточных итогов.

Перечисление PivotFieldSubtotalType определяет доступные виды промежуточных итогов.

AUTOMATIC — Aspose.Cells выбирает набор по умолчанию (как правило, SUM для числовых полей)
NONE — подавляет все строки промежуточных итогов
SUM
COUNT
AVERAGE
MAX
MIN
PRODUCT
STD_DEV
STD_DEVP
VAR
VARP

Промежуточные итоги отображаются только тогда, когда в области строк (или в области столбцов) присутствуют два или более полей сводной таблицы. Для одного поля нет ничего осмысленного, для чего можно было бы вычислять промежуточный итог, поэтому вызовы setSubtotals в этом случае не имеют видимого эффекта. Поэтому в этой статье во всех примерах размещаются два поля строки (Category внешнее, Item внутреннее), чтобы граница промежуточного итога между каждой группой Category была видна.

Сценарий 1 — автоматические (по умолчанию) промежуточные итоги

Если setSubtotals не вызывается вообще, Aspose.Cells применяет выбор AUTOMATIC для числовых полей. Следующий пример явно подтверждает это поведение, вызывая setSubtotals(PivotFieldSubtotalType.AUTOMATIC, true) для внешнего поля строки Category.

import com.aspose.cells.*;

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Data");

worksheet.getCells().get(0, 0).putValue("Category");
worksheet.getCells().get(0, 1).putValue("Item");
worksheet.getCells().get(0, 2).putValue("Year");
worksheet.getCells().get(0, 3).putValue("Amount");

worksheet.getCells().get(1, 0).putValue("Fruit");
worksheet.getCells().get(1, 1).putValue("Apple");
worksheet.getCells().get(1, 2).putValue(2020);
worksheet.getCells().get(1, 3).putValue(100);

worksheet.getCells().get(2, 0).putValue("Fruit");
worksheet.getCells().get(2, 1).putValue("Apple");
worksheet.getCells().get(2, 2).putValue(2021);
worksheet.getCells().get(2, 3).putValue(150);

worksheet.getCells().get(3, 0).putValue("Fruit");
worksheet.getCells().get(3, 1).putValue("Banana");
worksheet.getCells().get(3, 2).putValue(2020);
worksheet.getCells().get(3, 3).putValue(80);

worksheet.getCells().get(4, 0).putValue("Fruit");
worksheet.getCells().get(4, 1).putValue("Banana");
worksheet.getCells().get(4, 2).putValue(2021);
worksheet.getCells().get(4, 3).putValue(90);

worksheet.getCells().get(5, 0).putValue("Vegetable");
worksheet.getCells().get(5, 1).putValue("Carrot");
worksheet.getCells().get(5, 2).putValue(2020);
worksheet.getCells().get(5, 3).putValue(50);

worksheet.getCells().get(6, 0).putValue("Vegetable");
worksheet.getCells().get(6, 1).putValue("Carrot");
worksheet.getCells().get(6, 2).putValue(2021);
worksheet.getCells().get(6, 3).putValue(60);

worksheet.getCells().get(7, 0).putValue("Vegetable");
worksheet.getCells().get(7, 1).putValue("Daikon");
worksheet.getCells().get(7, 2).putValue(2020);
worksheet.getCells().get(7, 3).putValue(40);

worksheet.getCells().get(8, 0).putValue("Vegetable");
worksheet.getCells().get(8, 1).putValue("Daikon");
worksheet.getCells().get(8, 2).putValue(2021);
worksheet.getCells().get(8, 3).putValue(45);

int pivotIndex = worksheet.getPivotTables().add("A1:D9", "F3", "PivotTable1");
PivotTable pivotTable = worksheet.getPivotTables().get(pivotIndex);

pivotTable.addFieldToArea(PivotFieldType.ROW, "Category");
pivotTable.addFieldToArea(PivotFieldType.ROW, "Item");
pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Year");
pivotTable.addFieldToArea(PivotFieldType.DATA, "Amount");

PivotField categoryField = pivotTable.getRowFields().get(0);
categoryField.setSubtotals(PivotFieldSubtotalType.AUTOMATIC, true);

pivotTable.calculateData();

workbook.save("output_automatic.xlsx");

Сценарий 2 — подавление всех промежуточных итогов (None)

Вызов setSubtotals(PivotFieldSubtotalType.NONE, true) удаляет все строки промежуточных итогов из сводной таблицы, оставляя только строки полей и общий итог внизу. Это полезно, когда требуются только сгруппированные необработанные данные без каких-либо итоговых строк.

import com.aspose.cells.*;

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Data");

String[] headers = { "Category", "Item", "Year", "Amount" };
for (int j = 0; j < headers.length; j++)
{
    worksheet.getCells().get(0, j).putValue(headers[j]);
}

Object[][] data = {
    { "Fruit",     "Apple",  2020, 100 },
    { "Fruit",     "Apple",  2021, 150 },
    { "Fruit",     "Banana", 2020, 80  },
    { "Fruit",     "Banana", 2021, 90  },
    { "Vegetable", "Carrot", 2020, 50  },
    { "Vegetable", "Carrot", 2021, 60  },
    { "Vegetable", "Daikon", 2020, 40  },
    { "Vegetable", "Daikon", 2021, 45  }
};

for (int i = 0; i < data.length; i++)
{
    for (int j = 0; j < data[i].length; j++)
    {
        worksheet.getCells().get(i + 1, j).putValue(data[i][j]);
    }
}

int pivotIndex = worksheet.getPivotTables().add("A1:D9", "F3", "PivotTable1");
PivotTable pivotTable = worksheet.getPivotTables().get(pivotIndex);

pivotTable.addFieldToArea(PivotFieldType.ROW, "Category");
pivotTable.addFieldToArea(PivotFieldType.ROW, "Item");
pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Year");
pivotTable.addFieldToArea(PivotFieldType.DATA, "Amount");

PivotField categoryField = pivotTable.getRowFields().get(0);
categoryField.setSubtotals(PivotFieldSubtotalType.NONE, true);
pivotTable.calculateData();

workbook.save("output_none.xlsx");

Сценарий 3 — настраиваемое подмножество промежуточных итогов (Sum + Average)

Вы не ограничены одним типом промежуточного итога. Каждый вызов setSubtotals действует независимо для одного типа, поэтому двукратный вызов метода — один раз с SUM и один раз с AVERAGE — формирует настраиваемое подмножество из двух строк промежуточных итогов для каждой группы Category.

import com.aspose.cells.*;

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Data");

worksheet.getCells().get("A1").putValue("Category");
worksheet.getCells().get("B1").putValue("Item");
worksheet.getCells().get("C1").putValue("Year");
worksheet.getCells().get("D1").putValue("Amount");

worksheet.getCells().get(1, 0).putValue("Fruit");
worksheet.getCells().get(1, 1).putValue("Apple");
worksheet.getCells().get(1, 2).putValue(2020);
worksheet.getCells().get(1, 3).putValue(100);

worksheet.getCells().get(2, 0).putValue("Fruit");
worksheet.getCells().get(2, 1).putValue("Apple");
worksheet.getCells().get(2, 2).putValue(2021);
worksheet.getCells().get(2, 3).putValue(150);

worksheet.getCells().get(3, 0).putValue("Fruit");
worksheet.getCells().get(3, 1).putValue("Banana");
worksheet.getCells().get(3, 2).putValue(2020);
worksheet.getCells().get(3, 3).putValue(80);

worksheet.getCells().get(4, 0).putValue("Fruit");
worksheet.getCells().get(4, 1).putValue("Banana");
worksheet.getCells().get(4, 2).putValue(2021);
worksheet.getCells().get(4, 3).putValue(90);

worksheet.getCells().get(5, 0).putValue("Vegetable");
worksheet.getCells().get(5, 1).putValue("Carrot");
worksheet.getCells().get(5, 2).putValue(2020);
worksheet.getCells().get(5, 3).putValue(50);

worksheet.getCells().get(6, 0).putValue("Vegetable");
worksheet.getCells().get(6, 1).putValue("Carrot");
worksheet.getCells().get(6, 2).putValue(2021);
worksheet.getCells().get(6, 3).putValue(60);

worksheet.getCells().get(7, 0).putValue("Vegetable");
worksheet.getCells().get(7, 1).putValue("Daikon");
worksheet.getCells().get(7, 2).putValue(2020);
worksheet.getCells().get(7, 3).putValue(40);

worksheet.getCells().get(8, 0).putValue("Vegetable");
worksheet.getCells().get(8, 1).putValue("Daikon");
worksheet.getCells().get(8, 2).putValue(2021);
worksheet.getCells().get(8, 3).putValue(45);

PivotTableCollection pivotTables = worksheet.getPivotTables();
int pivotIndex = pivotTables.add("A1:D9", "F3", "PivotTable1");
PivotTable pivotTable = pivotTables.get(pivotIndex);

pivotTable.addFieldToArea(PivotFieldType.ROW, "Category");
pivotTable.addFieldToArea(PivotFieldType.ROW, "Item");
pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Year");
pivotTable.addFieldToArea(PivotFieldType.DATA, "Amount");

PivotField categoryField = pivotTable.getRowFields().get(0);
categoryField.setSubtotals(PivotFieldSubtotalType.SUM, true);
categoryField.setSubtotals(PivotFieldSubtotalType.AVERAGE, true);

pivotTable.calculateData();

workbook.save("output_custom.xlsx");

Краткое резюме

Три приведённых выше сценария используют один и тот же набор данных и одинаковую структуру сводной таблицы. Единственное различие между ними — это вызов setSubtotals, применяемый к внешнему полю строки Category. Помните о правиле двух полей: у одного поля в области нечего суммировать между группами, поэтому всегда размещайте как минимум два поля в области строк или столбцов, если хотите, чтобы setSubtotals оказывал видимый эффект.

Применить стили к сводным таблицам в Aspose.Cells для .NET Поля значений в Aspose.Cells for Java