Форматирование сводной таблицы

Внешний вид сводной таблицы

Как создать сводную таблицу показывает, как создать простую сводную таблицу. В этой статье пойдет дальше и обсудит, как настроить внешний вид сводной таблицы, устанавливая свойства.

Настройка параметров формата сводной таблицы

Класс PivotTable позволяет устанавливать различные параметры форматирования сводной таблицы.

Установка типов автоформата и стиля сводной таблицы

Приведенный ниже пример кода показывает, как установить тип автоформата и тип стиля сводной таблицы с использованием свойств AutoFormatType и PivotTableStyleType.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SetAutoFormatandPivotTableStyleTypes.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");
int pivotindex = 0;
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(pivotindex);
// Accessing the PivotTable
PivotTable pivotTable = worksheet.getPivotTables().get(pivotindex);
		
//Setting the PivotTable report is automatically formatted for Excel 2003 formats
pivotTable.setAutoFormat(true);
//Setting the PivotTable atuoformat type.
pivotTable.setAutoFormatType(PivotTableAutoFormatType.CLASSIC);

//Setting the PivotTable's Styles for Excel 2007/2010 formats e.g XLSX.
pivotTable.setPivotTableStyleType(PivotTableStyleType.PIVOT_TABLE_STYLE_LIGHT_1);

Настройка параметров формата

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingFormatOptions.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");

// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Dragging the third field to the data area.
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);

// Show grand totals for rows.
pivotTable.setRowGrand(true);

// Show grand totals for columns.
pivotTable.setColumnGrand(true);

// Display a custom string in cells that contain null values.
pivotTable.setDisplayNullString(true);
pivotTable.setNullString("null");

// Setting the layout
pivotTable.setPageFieldOrder(PrintOrderType.DOWN_THEN_OVER);

Настройка параметров форматирования полей сводной таблицы

Помимо управления форматированием общей сводной таблицы, Aspose.Cells for Java позволяет тонко настраивать форматирование для строковых полей, столбцовых полей и полей страниц.

Установка формата для полей строк, столбцов и страниц

Приведенный ниже пример кода показывает, как получить доступ к полям строк, к определенной строке, установить итоги, применить автоматическую сортировку и использовать опцию автоматического отображения.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SetRowColumnPageFieldsFormat.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");

// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Accessing the row fields.
PivotFieldCollection pivotFields = pivotTable.getRowFields();

// Accessing the first row field in the row fields.
PivotField pivotField = pivotFields.get(0);

// Setting Subtotals.
pivotField.setSubtotals(PivotFieldSubtotalType.SUM, true);
pivotField.setSubtotals(PivotFieldSubtotalType.COUNT, true);

// Setting autosort options. Setting the field auto sort.
pivotField.setAutoSort(true);

// Setting the field auto sort ascend.
pivotField.setAscendSort(true);

// Setting the field auto sort using the field itself.
pivotField.setAutoSortField(-1);

// Setting autoShow options. Setting the field auto show.
pivotField.setAutoShow(true);

// Setting the field auto show ascend.
pivotField.setAscendShow(false);

// Setting the auto show using field(data field).
pivotField.setAutoShowField(0);

Установка формата полей данных

Нижеприведенные строки кода иллюстрируют, как форматировать поля данных.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingDataFieldFormat.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");

// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
// Accessing the data fields.
PivotFieldCollection pivotFields = pivotTable.getDataFields();

// Accessing the first data field in the data fields.
PivotField pivotField = pivotFields.get(0);

// Setting data display format
pivotField.setDataDisplayFormat(PivotFieldDataDisplayFormat.PERCENTAGE_OF);

// Setting the base field.
pivotField.setBaseFieldIndex(1);

// Setting the base item.
pivotField.setBaseItemPosition(PivotItemPosition.NEXT);

// Setting number format
pivotField.setNumber(10);

Изменение быстрого стиля сводной таблицы

Приведенные ниже примеры кода показывают, как изменить быстрый стиль, примененный к сводной таблице.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ModifyPivotTableQuickStyle.class) + "PivotTables/";
// Open the template file containing the pivot table.
Workbook wb = new Workbook(dataDir + "sample1.xlsx");
// Add Pivot Table style
Style style1 = wb.createStyle();
com.aspose.cells.Font font1 = style1.getFont();
font1.setColor(Color.getRed());
Style style2 = wb.createStyle();
com.aspose.cells.Font font2 = style2.getFont();
font2.setColor(Color.getBlue());
int i = wb.getWorksheets().getTableStyles().addPivotTableStyle("tt");
// Get and Set the table style for different categories
TableStyle ts = wb.getWorksheets().getTableStyles().get(i);
int index = ts.getTableStyleElements().add(TableStyleElementType.FIRST_COLUMN);
TableStyleElement e = ts.getTableStyleElements().get(index);
e.setElementStyle(style1);
index = ts.getTableStyleElements().add(TableStyleElementType.GRAND_TOTAL_ROW);
e = ts.getTableStyleElements().get(index);
e.setElementStyle(style2);

// Set Pivot Table style name
PivotTable pt = wb.getWorksheets().get(0).getPivotTables().get(0);
pt.setPivotTableStyleName("tt");

// Save the file.
wb.save(dataDir + "ModifyPivotTableQuickStyle_out.xlsx");

Очистка полей сводной таблицы

PivotFieldCollection имеет метод с именем clear(), который очищает поля сводной таблицы. Используйте его для очистки полей сводной таблицы во всех областях, например странице, столбце, строке или данных. Приведенный ниже образец кода показывает, как очистить все поля сводной таблицы в области данных.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ClearPivotFields.class) + "PivotTables/";
// Load a template file
Workbook workbook = new Workbook(dataDir + "PivotTable.xls");

// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

// Get the pivot tables in the sheet
PivotTableCollection pivotTables = sheet.getPivotTables();

// Get the first PivotTable
PivotTable pivotTable = pivotTables.get(0);

// Clear all the data fields
pivotTable.getDataFields().clear();

// Add new data field
pivotTable.addFieldToArea(PivotFieldType.DATA, "Betrag Netto FW");

// Set the refresh data flag on
pivotTable.setRefreshDataFlag(false);

// Refresh and calculate the pivot table data
pivotTable.refreshData();
pivotTable.calculateData();

// Save the Excel file
workbook.save(dataDir + "ClearPivotFields_out.xlsx");

Функция консолидации

Применение функции консолидации к данным полей сводной таблицы

Aspose.Cells можно использовать для применения функции консолидации к данным полей (или значениям) сводной таблицы. В Microsoft Excel можно щелкнуть правой кнопкой мыши поле значения, затем выбрать опцию Настройки поля значений…, а затем выбрать вкладку Обобщение значений по. Оттуда вы можете выбрать любую функцию консолидации, которая вам нравится, например, Сумма, Количество, Среднее, Максимум, Минимум, Произведение, Уникальное количество и т. д.

Aspose.Cells предоставляет перечисление ConsolidationFunction для поддержки следующих функций консолидации.

• ConsolidationFunction.AVERAGE
• ConsolidationFunction.COUNT
• ConsolidationFunction.COUNT_NUMS
• ConsolidationFunction.DISTINCT_COUNT
• ConsolidationFunction.MAX
• ConsolidationFunction.MIN
• ConsolidationFunction.PRODUCT
• ConsolidationFunction.STD_DEV
• ConsolidationFunction.STD_DEVP
• ConsolidationFunction.SUM
• ConsolidationFunction.VAR
• ConsolidationFunction.VARP

В следующем коде применяется функция консолидации Среднее к первому полю данных (или значению) и функция консолидации Уникальное количество ко второму полю данных (или значению).

Функция консолидации Уникальное количество поддерживается только Microsoft Excel 2013.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ConsolidationFunctions.class) + "PivotTables/";
// Create workbook from source excel file
Workbook workbook = new Workbook(dataDir + "sample1.xlsx");

// Access the first worksheet of the workbook
Worksheet worksheet = workbook.getWorksheets().get(0);

// Access the first pivot table of the worksheet
PivotTable pivotTable = worksheet.getPivotTables().get(0);

// Apply Average consolidation function to first data field
pivotTable.getDataFields().get(0).setFunction(ConsolidationFunction.AVERAGE);

// Apply DistinctCount consolidation function to second data field
pivotTable.getDataFields().get(1).setFunction(ConsolidationFunction.DISTINCT_COUNT);

// Calculate the data to make changes affect
pivotTable.calculateData();

// Save the workbook
workbook.save(dataDir + "ConsolidationFunctions_out.xlsx");