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试一试。感谢您的阅读!

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值