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

左手的ㄟ右手 2023-10-17 17:42 21阅读 0赞

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

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

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

![20180122113204697][]

**一、创建数据透视表**

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

//加载文档
    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);

![20180115155640453][]

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

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);

![20180122111825213][]

**排序**

通过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);

![20180115155942914][]

**折叠/展开行**

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

//加载文档
    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);

![20180115160016507][]

**隐藏/显示字段列表**

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

//加载文档
    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);

![20180115160101990][]

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

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

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);

![20180115160218361][]

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

//加载文档
    Workbook workbook = new Workbook();
    workbook.LoadFromFile("数据透视表.xlsx");
    
    //获取数据透视表
    XlsPivotTable pivotTable = workbook.Worksheets[1].PivotTables[0] as XlsPivotTable;
    
    //隐藏“+/-”按钮
    pivotTable.ShowDrillIndicators = false;
    
    //保存文档
    workbook.SaveToFile("隐藏加减按钮.xlsx", ExcelVersion.Version2013);

![20180115160309039][]

**隐藏/显示字段标题**

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

![20180115160334973][]

**设置值显示方式**

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

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);

![20180115160139574][]

**三、删除数据透视表**

在删除数据透视表时，可以通过名称删除，也可以通过索引（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);

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

![20180115160413186][]

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

[Spire.XLS]: http://e-iceblue.cn/Downloads/Spire-XLS-NET.html
[20180122113204697]: https://img-blog.csdn.net/20180122113204697
[20180115155640453]: https://img-blog.csdn.net/20180115155640453
[20180122111825213]: https://img-blog.csdn.net/20180122111825213
[20180115155942914]: https://img-blog.csdn.net/20180115155942914
[20180115160016507]: https://img-blog.csdn.net/20180115160016507
[20180115160101990]: https://img-blog.csdn.net/20180115160101990
[20180115160218361]: https://img-blog.csdn.net/20180115160218361
[20180115160309039]: https://img-blog.csdn.net/20180115160309039
[20180115160334973]: https://img-blog.csdn.net/20180115160334973
[20180115160139574]: https://img-blog.csdn.net/20180115160139574
[20180115160413186]: https://img-blog.csdn.net/20180115160413186