C# 创建Excel透视表

1、【C#】

          //创建一个Workbook类实例，并加载Excel文档

            Workbook workbook = new Workbook();

            workbook.LoadFromFile("test.xlsx");

            //获取第一个工作表

            Worksheet sheet = workbook.Worksheets[0];

            //为需要汇总和分析的数据创建缓存

            CellRange dataRange = sheet.Range["A1:D10"];

            PivotCache cache = workbook.PivotCaches.Add(dataRange);

            //使用缓存创建数据透视表，并指定透视表的名称以及在工作表中的位置

            PivotTable pivotTable = sheet.PivotTables.Add("PivotTable", sheet.Range["A12"], cache);

            //添加行字段

            var r1 = pivotTable.PivotFields["月份"];

            r1.Axis = AxisTypes.Row;

            var r2 = pivotTable.PivotFields["厂商"];

            r2.Axis = AxisTypes.Row;

            //设置行字段的标题

            pivotTable.Options.RowHeaderCaption = "月份";

            //添加列字段

            var col1 = pivotTable.PivotFields["产品"];

            col1.Axis = AxisTypes.Column;         

            //设置列字段的标题

            pivotTable.Options.ColumnHeaderCaption = "产品";

            //添加值字段

            pivotTable.DataFields.Add(pivotTable.PivotFields["总产量"], "求和项：总产量", SubtotalTypes.Sum);

            //设置透视表的样式（Spire.XLS共支持80余种Excel内置的数据透视表样式）

            pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark13;

            //保存并打开文档

            workbook.SaveToFile("数据透视表.xlsx", ExcelVersion.Version2013);

            System.Diagnostics.Process.Start("数据透视表.xlsx");

1、【C#】

           //创建Workbook类对象，加载Excel文档 

            Workbook workbook = new Workbook();

            workbook.LoadFromFile("数据透视表.xlsx");

            //获取数据透视表 

            XlsPivotTable pivotTable = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;

            //计算数据 

            pivotTable.CalculateData();

            //展开”月份”字段下“2”的详细信息 

            (pivotTable.PivotFields["月份"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("2", false);

            //折叠”月份”字段下“3”的详细信息

            (pivotTable.PivotFields["月份"] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField).HideItemDetail("3", true);

            //保存并打开文档 

            workbook.SaveToFile("折叠、展开行.xlsx", ExcelVersion.Version2013);

            System.Diagnostics.Process.Start("折叠、展开行.xlsx");

1、【C#】

            //创建一个Workbook类对象，并加载Excel文档 

            Workbook workbook = new Workbook();

            workbook.LoadFromFile("数据透视表.xlsx");

            //获取数据透视表 

            Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets[0].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;

            //对指定字段进行升序排序 

            pivotTable.PivotFields[2].SortType = PivotFieldSortType.Ascending;

            //保存并打开文档 

            workbook.SaveToFile("升序.xlsx", ExcelVersion.Version2013);

            System.Diagnostics.Process.Start("升序.xlsx");

1、【C#】

            //创建一个工作簿，并加载Excel文档

            Workbook workbook = new Workbook();

            workbook.LoadFromFile("数据透视表.xlsx");

            //删除第一张工作表上名称为“PivotTable”的数据透视表 

            workbook.Worksheets[0].PivotTables.Remove("PivotTable");

            //删除第一张工作表上索引值为0的工作表，即第一个数据透视表 

            //workbook.Worksheets[0].PivotTables.RemoveAt(0); 

            //保存文档 

            workbook.SaveToFile("删除数据透视表.xlsx", ExcelVersion.Version2013);