格式化数据透视表

数据透视表外观

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

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

设置数据透视表格式选项

这数据透视表类控制整个数据透视表，可以通过多种方式进行格式化。

设置自动套用格式类型

Microsoft Excel 提供了许多不同的预设报告格式。 Aspose.Cells 也支持这些格式化选项。要访问它们：

1. 放数据透视表.IsAutoFormat到真的.
2. 从数据透视表自动格式类型枚举。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

int pivotindex = 0;

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];

// Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;

// Setting the PivotTable atuoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report5;
            
// Saving the Excel file
workbook.Save(dataDir + "output.xls");

设置格式选项

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotindex = 0;

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];

// Setting the PivotTable report shows grand totals for rows.
pivotTable.RowGrand = true;

// Setting the PivotTable report shows grand totals for columns.
pivotTable.ColumnGrand = true;

// Setting the PivotTable report displays a custom string in cells that contain null values.
pivotTable.DisplayNullString = true;
pivotTable.NullString = "null";

// Setting the PivotTable report's layout
pivotTable.PageFieldOrder = PrintOrderType.DownThenOver;

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

手动格式化外观

要手动格式化数据透视表报告的外观，而不是使用预设的报告格式，请使用数据透视表格式()和数据透视表.FormatAll()方法。为您想要的格式创建一个样式对象，例如：

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
var pivot = workbook.Worksheets[0].PivotTables[0];

pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleDark1;

Style style = workbook.CreateStyle();
style.Font.Name = "Arial Black";
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;

pivot.FormatAll(style);

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

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

这数据透视表类表示数据透视表中的一个字段，可以通过多种方式进行格式化。下面的代码示例显示了如何：

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

设置行/列/页字段格式

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotindex = 0;

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];

// Setting the PivotTable report shows grand totals for rows.
pivotTable.RowGrand = true;

// Accessing the row fields.
Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.RowFields;

// Accessing the first row field in the row fields.
Aspose.Cells.Pivot.PivotField pivotField = pivotFields[0];

// Setting Subtotals.
pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Sum, true);
pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Count, true);

// Setting autosort options.
// Setting the field auto sort.
pivotField.IsAutoSort = true;

// Setting the field auto sort ascend.
pivotField.IsAscendSort = true;

// Setting the field auto sort using the field itself.
pivotField.AutoSortField = -5;

// Setting autoShow options.
// Setting the field auto show.
pivotField.IsAutoShow = true;

// Setting the field auto show ascend.
pivotField.IsAscendShow = false;

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

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

设置数据字段格式

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotindex = 0;

// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotindex];
// Accessing the data fields.
Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.DataFields;

// Accessing the first data field in the data fields.
Aspose.Cells.Pivot.PivotField pivotField = pivotFields[0];

// Setting data display format
pivotField.ShowValuesSetting.CalculationType = PivotFieldDataDisplayFormat.PercentageOf;

// Setting the base field.
pivotField.ShowValuesSetting.BaseFieldIndex = 1;

// Setting the base item.
pivotField.ShowValuesSetting.BaseItemPositionType = Aspose.Cells.Pivot.PivotItemPositionType.Next;

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

清除数据透视字段

这数据透视字段集合有一个名为的方法清除（）这允许您清除数据透视字段。当您要清除区域中的所有数据透视字段时使用它，例如页、列、行或数据。 下面的代码示例显示了如何清除数据区域中的所有数据透视字段。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Load a template file
Workbook workbook = new Workbook(dataDir + "Book1.xls");

// Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

// Get the pivot tables in the sheet
PivotTableCollection pivotTables = sheet.PivotTables;


// Get the first PivotTable
PivotTable pivotTable = pivotTables[0];

// Clear all the data fields
pivotTable.DataFields.Clear();

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

// Set the refresh data flag on
pivotTable.RefreshDataFlag = false;

// Refresh and calculate the pivot table data
pivotTable.RefreshData();
pivotTable.CalculateData();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");