格式化数据透视表

数据透视表外观

如何创建数据透视表介绍了如何创建简单的数据透视表。本文描述了如何通过设置各种属性来自定义数据透视表的外观:

• 数据透视表格式选项
• 数据透视字段格式选项
• 数据字段格式选项

如何设置数据透视表格式选项

PivotTable类控制整体数据透视表，可以以多种方式进行格式设置。

如何设置自动格式类型

微软Excel提供多种预设报告格式。Aspose.Cells for Node.js via C++ 同样支持这些格式。要访问它们：

1. 将PivotTable.setIsAutoFormat(value)设置为true。
2. 从PivotTableAutoFormatType枚举中分配一个格式选项。

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
//The path to the documents directory.
var dataDir = RunExamples.GetDataDir(".");
//Load a template file
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls");
var pivotindex = 0;
//Get the first worksheet
var worksheet = workbook.getWorksheets().get(0);
//Accessing the PivotTable
var pivotTable = worksheet.getPivotTables().get(pivotindex);
//Setting the PivotTable report is automatically formatted
pivotTable.setIsAutoFormat(true);
//Setting the PivotTable atuoformat type.
pivotTable.setAutoFormatType(AsposeCells.PivotTableAutoFormatType.Report5);
//Saving the Excel file
workbook.save(dataDir + "output.xls");

如何设置格式选项

下面的代码示例演示了如何格式化数据透视表以显示行和列的总计，以及如何设置报告的字段顺序。它还显示了如何为空值设置自定义字符串。

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
//The path to the documents directory.
var dataDir = RunExamples.GetDataDir(".");
//Load a template file
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls");
//Get the first worksheet
var worksheet = workbook.getWorksheets().get(0);
var pivotindex = 0;
//Accessing the PivotTable
var pivotTable = worksheet.getPivotTables().get(pivotindex);
//Setting the PivotTable report shows grand totals for rows.
pivotTable.setRowGrand(true);
//Setting the PivotTable report shows grand totals for columns.
pivotTable.setColumnGrand(true);
//Setting the PivotTable report displays a custom string in cells that contain null values.
pivotTable.setDisplayNullString(true);
pivotTable.setNullString("null");
//Setting the PivotTable report's layout
pivotTable.setPageFieldOrder(AsposeCells.PrintOrderType.DownThenOver);
//Saving the Excel file
workbook.save(dataDir + "output.xls");

手动设置外观和感觉格式

要手动设置数据透视表报告的外观和感觉，而不是使用预设的报告格式，请使用PivotTable.formatAll(style)和PivotTable.format(row, column, style)方法。为所需的格式创建样式对象，例如：

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
//The path to the documents directory.
var dataDir = RunExamples.GetDataDir(".")
//Load a template file
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls");
//Get the first worksheet
var worksheet = workbook.getWorksheets().get(0);
var pivot = worksheet.getPivotTables().get(0);
pivot.setPivotTableStyleType(AsposeCells.PivotTableStyleType.PivotTableStyleDark1);
var style = workbook.createStyle();
style.getFont().setName("Arial Black");

style.setPattern(AsposeCells.BackgroundType.Solid);
style.setForegroundColor(AsposeCells.Color.Yellow);

pivot.formatAll(style);
//Saving the Excel file
workbook.save(dataDir + "output.xls");

如何设置数据透视表字段格式选项

PivotField类表示数据透视表中的字段，并可以以多种方式进行格式设置。下面的代码示例演示了如何:

• 访问行字段。
• 设置合计。
• 设置自动排序。
• 设置自动显示。

如何设置行/列/页面字段格式

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
//The path to the documents directory.
var dataDir = RunExamples.GetDataDir(".");
//Load a template file
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls");
//Get the first worksheet
var worksheet = workbook.getWorksheets().get(0);
var pivotindex = 0;
//Accessing the PivotTable
var pivotTable = worksheet.getPivotTables().get(pivotindex);
//Setting the PivotTable report shows grand totals for rows.
pivotTable.setRowGrand(true);
//Accessing the row fields.
var pivotFields = pivotTable.getRowFields();
//Accessing the first row field in the row fields.
var pivotField = pivotFields.get(0);
//Setting Subtotals.
pivotField.setSubtotals(AsposeCells.PivotFieldSubtotalType.Sum, true);
pivotField.setSubtotals(AsposeCells.PivotFieldSubtotalType.Count, true);
//Setting autosort options.
//Setting the field auto sort.
pivotField.setIsAutoSort(true);
//Setting the field auto sort ascend.
pivotField.setIsAscendSort(true);
//Setting the field auto sort using the field itself.
pivotField.setAutoSortField(-5);
//Setting autoShow options.
//Setting the field auto show.
pivotField.setIsAutoShow(true);
//Setting the field auto show ascend.
pivotField.setIsAscendShow(false);
//Setting the auto show using field(data field).
pivotField.setAutoShowField(0);
//Saving the Excel file
workbook.save(dataDir + "output.xls");

如何设置数据字段格式

以下代码示例显示如何设置数据字段的显示格式和数字格式。

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
//The path to the documents directory.
var dataDir = RunExamples.GetDataDir(".");
//Load a template file
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls");
//Get the first worksheet
var worksheet = workbook.getWorksheets().get(0);
var pivotindex = 0;
//Accessing the PivotTable
var pivotTable = worksheet.getPivotTables().get(pivotindex);
//Accessing the data fields.
var pivotFields = pivotTable.getDataFields();
//Accessing the first data field in the data fields.
var pivotField = pivotFields.get(0);
//Setting data display format
pivotField.showValuesAs(AsposeCells.PivotFieldDataDisplayFormat.PercentageOf, 1, AsposeCells.PivotItemPositionType.Next, 0);

//Setting number format
pivotField.setNumber(10);
//Saving the Excel file
workbook.save(dataDir + "output.xls");

如何清除数据透视字段

PivotFieldCollection 有一个名为 clear() 的方法，允许您清除数据透视字段。当您想要清除区域中的所有数据透视字段时（例如页、列、行或数据），请使用它。 以下代码示例显示如何清除数据区域中的所有数据透视字段。

const AsposeCells = require("aspose.cells.node");

//For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
//The path to the documents directory.
var dataDir = RunExamples.GetDataDir(".")
//Load a template file
var workbook = new AsposeCells.Workbook(dataDir + "Book1.xls");
//Get the first worksheet
var sheet = workbook.getWorksheets().get(0);
//Get the pivot tables in the sheet
var pivotTables = sheet.getPivotTables();
//Get the first PivotTable
var pivotTable = pivotTables.get(0);
//Clear all the data fields
pivotTable.getDataFields().clear();
//Add new data field
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Betrag Netto FW");
//Set the refresh data flag on
pivotTable.setRefreshDataFlag(true);
//Refresh and calculate the pivot table data
pivotTable.refreshData();
pivotTable.calculateData();
pivotTable.setRefreshDataFlag(false);
//Saving the Excel file
workbook.save(dataDir + "output.xls");