C# 操作Excel数据透视表详解 – 创建、操作和删除

数据透视表是Excel中可以进行数据分类汇总和分析的一个强大工具，很多报表形式都可以通过数据透视表来实现。它的一项重要功能是能够重新排列字段项，使用户可以从不同的角度对数据进行分析，并且无需进行复杂的操作，只需简单的拖拉即可实现。可以说数据透视表的应用在我们现在的日常工作中已经非常广泛了。

今天这篇文章将介绍如何使用C#和​​Spire.XLS​​组件在Excel文档中创建数据透视表，对数据透视表进行多种设置，以及删除现有数据透视表。

文中所使用的示例文档如下：

一、创建数据透视表

数据透视表不是直接使用工作表中的数据作为数据源，而是使用数据的缓存，因此我们需要在添加透视表之前，为数据创建缓存。以下代码创建了一个包含行字段和值字段的简单数据透视表。

//加载文档
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"Input.xlsx");
 
//获取第一张工作表sheet1
Worksheet sheet1 = workbook.Worksheets[0];
 
//添加一个新的工作表sheet2并命名为“工资报表”（用于添加数据透视表）
Worksheet sheet2 = workbook.Worksheets.Add("工资报表");
 
//为sheet1的数据"A1:E14"创建缓存
CellRange dataRange = sheet1.Range["A1:E14"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
 
//使用缓存创建数据透视表，并指定透视表的名称以及位置（这里将透视表插入到了第二张工作表sheet2）
PivotTable pivotTable = sheet2.PivotTables.Add("PivotTable",sheet2.Range["A1"], cache);
 
//添加行字段
var r1 = pivotTable.PivotFields["部门"];
r1.Axis = AxisTypes.Row;
 
//添加行字段
var r2 = pivotTable.PivotFields["姓名"];
r2.Axis = AxisTypes.Row;
 
//设置行字段的标题
pivotTable.Options.RowHeaderCaption = "部门";
 
//添加值字段
pivotTable.DataFields.Add(pivotTable.PivotFields["工资"], "Sum of 工资", SubtotalTypes.Sum);
pivotTable.DataFields.Add(pivotTable.PivotFields["奖金"], "Sum of 奖金", SubtotalTypes.Sum);
 
//设置数据透视表的样式（共支持80余种Excel自带的样式）
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
 
//保存文档
workbook.SaveToFile("数据透视表.xlsx",ExcelVersion.Version2010);

数据透视表除了可以包含行字段以外，还可以包含列字段。给透视表添加列字段的代码和行字段的类似：

var r2 = pivotTable.PivotFields["字段名"];
r2.Axis = AxisTypes.Column; 
pivotTable.Options.ColumnHeaderCaption = "列字段标题";

二、数据透视表设置

样式设置 

在Excel中（以Excel 2013为例）内置的数据透视表样式大约有85种。Spire.XLS组件也支持这些样式，它们可以在PivotBuiltInStyles枚举中查看。

pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark11;

添加行字段标题和列字段标题 (Row And Column Header Caption)

pivotTable.Options.RowHeaderCaption = "行字段标题";
pivotTable.Options.ColumnHeaderCaption = "列字段标题";

报表筛选

当一个数据透视表中包含多条数据，而我们需要对其中一小部分数据进行深入分析时，可以对数据进行筛选。下面的代码给一个数据透视表添加了报表筛选。 

//加载文档
Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
//获取数据透视表
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
 
//添加报表筛选
PivotReportFilter filter = new PivotReportFilter("部门", true);
pivotTable.ReportFilters.Add(filter);
 
//保存文档
workbook.SaveToFile("筛选.xlsx",ExcelVersion.Version2013);

排序

通过SortType属性，我们可以对数据透视表中的字段设置升序、降序和手动三种排序方式。

//加载文档
Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
//获取数据透视表
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
 
//对指定字段进行降序排序
pivotTable.PivotFields[2].SortType = PivotFieldSortType.Descending;
 
//保存文档
workbook.SaveToFile("排序.xlsx",ExcelVersion.Version2013);

折叠/展开行

当我们不需要对数据透视表中某一类数据进行分析的时候，可以将它折叠起来，在需要进行分析的时候，也可以将它展开。下面的代码将”部门”字段下“开发部”的详细信息折叠了起来。

//加载文档
Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
//获取数据透视表
XlsPivotTable pivotTable =workbook.Worksheets[1].PivotTables[0] as XlsPivotTable;
 
//计算数据
pivotTable.CalculateData();
 
//折叠”部门”字段下“开发部”的详细信息
 (pivotTable.PivotFields["部门"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("开发部", true);
 
//保存文档
workbook.SaveToFile("折叠行.xlsx",ExcelVersion.Version2013);

隐藏/显示字段列表

通常我们点击数据透视表，右侧会出现一个字段列表，它展示了我们的数据透视表中包含了哪些字段，哪些是行字段，哪些是列字段等等。当不需要这个列表的时候，我们可以将它隐藏起来。

//加载文档
Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
XlsPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0] as XlsPivotTable;
 
pivotTable.Options.ShowFieldList = false;
 
workbook.SaveToFile("隐藏字段列表.xlsx",ExcelVersion.Version2013);

 

除了可以隐藏字段列表以外，行列总计、“+/-”按钮以及字段标题等都可以被隐藏。

隐藏/显示行总计或列总计

Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
XlsPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0] as XlsPivotTable;
 
pivotTable.ShowColumnGrand = false;
 
//pivotTable.ShowRowGrand = true;
 
workbook.SaveToFile("隐藏列总计.xlsx", ExcelVersion.Version2013);

隐藏/显示“+/-”按钮

//加载文档
Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");

//获取数据透视表
XlsPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0] as XlsPivotTable;

//隐藏“+/-”按钮
pivotTable.ShowDrillIndicators = false;

//保存文档
workbook.SaveToFile("隐藏加减按钮.xlsx", ExcelVersion.Version2013);

 

隐藏/显示字段标题

Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
XlsPivotTable pivotTable =workbook.Worksheets[1].PivotTables[0] as XlsPivotTable;
 
pivotTable.DisplayFieldCaptions = false;
 
workbook.SaveToFile("隐藏字段标题.xlsx", ExcelVersion.Version2013);

设置值显示方式

“值显示方式”功能可以更加灵活地显示数据，在下面的代码中，我们将第一个值字段的值显示方式设置为了列汇总的百分比。

Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
XlsPivotTable pivotTable =workbook.Worksheets[1].PivotTables[0] as XlsPivotTable;
 
PivotDataField pivotDataField =pivotTable.DataFields[0];
 
pivotDataField.ShowDataAs = PivotFieldFormatType.PercentageOfColumn;
 
workbook.SaveToFile("值显示方式.xlsx",ExcelVersion.Version2013);

三、删除数据透视表

在删除数据透视表时，可以通过名称删除，也可以通过索引（index）删除。

//加载文档
Workbook workbook = new Workbook();
workbook.LoadFromFile("数据透视表.xlsx");
 
//删除第二张工作表上名称为“PivotTable”的数据透视表
workbook.Worksheets[1].PivotTables.Remove("PivotTable");
 
//删除第二张工作表上索引为0即第一个数据透视表
//workbook.Worksheets[1].PivotTables.RemoveAt(0);
 
//保存文档
workbook.SaveToFile("删除数据透视表.xlsx", ExcelVersion.Version2013);

从下图我们可以看到，第二张工作表中的数据透视表已经被删除：

 

 

由于篇幅问题，这篇文章只介绍了Spire.XLS组件数据透视表的其中一部分功能，如果对更多功能感兴趣，可以下载​​Spire.XLS​​试一试。感谢您的阅读！