用C#在Excel工作表中创建数据透视表和数据透视图

高效处理与分析海量数据是决策中的重要一环。通过C#编程在Excel中自动化创建数据透视表与数据透视图，能够将原始数据转化为可交互的多维度分析表格和图表。同时，通过编程实现的自动化流程可确保数据更新的同步性与分析结果的一致性，尤其适用于需要定期生成标准化分析报告的场景。相较于静态表格，数据透视图表通过动态筛选与钻取，为决策者提供更多的数据细节，从而进行更深度的分析。本文将介绍如何在.NET框架使用C#在Excel工作簿中创建数据透视表和数据透视图。

• 用C#在Excel文件中创建数据透视表
• 用C#在Excel文件中创建数据透视图

本文所使用的方法需要用到免费的Free Spire.XLS for .NET，NuGet：PM> Install-Package FreeSpire.XLS。

用C#在Excel文件中创建数据透视表

在创建数据透视表时，我们需要先用指定的单元格范围通过Workbook.PivotCaches.Add(CellRange)方法创建一个XlsPivotCache对象，然后再使用Worksheet.PivotTables.Add(name: string, location: CellRange, XlsPivotCache)基于XlsPivotCache对象创建数据透视表，并对数据透视表的行字段、列字段、值字段等进行设置，从而创建一个完善的数据透视表。
以下是操作步骤：

1. 导入所需模块。
2. 创建Workbook类的实例。
3. 使用Workbook.LoadFromFile方法载入指定路径的现有Excel文件到工作簿实例中。
4. 使用Workbook.Worksheets[]属性获取工作簿中的一个工作表。
5. 定义数据源范围：通过Worksheet.Range[]属性选取单元格区域。
6. 使用Workbook.PivotCaches.Add方法添加数据源范围到工作簿的缓存中创建一个XlsPivotCache对象。
7. 在当前工作表中定义位置并使用Worksheet.PivotTables.Add方法以及之前创建的XlsPivotCache对象创建一个数据透视表。数据透视表可创建在其他工作表中。
8. 为数据透视表添加行字段，通过XlsPivotTable.PivotFields[]属性选择对应的列，并设置其轴类型为行。
9. 添加值字段，选择需要求和的数据列，并通过XlsPivotTable.DataFields.Add方法添加到数据透视表中。
10. 使用XlsPivotCache.CalculateData()方法更新数据透视表值。
11. 使用XlsPivotTable.BuiltInStyle属性应用内置样式。
12. 使用XlsPivotTable.AutoFormatType属性设置数据透视表的自动格式类型。
13. 使用Workbook.SaveToFile方法保存修改后的工作簿到指定路径。
14. 释放资源。

代码示例

using Spire.Xls;
using Spire.Xls.Core;

namespace CreatePivotTable
{
    class Program
    {
        static void Main(string[] args)
        {
            // 创建Workbook对象
            Workbook workbook = new Workbook();

            // 载入Excel文件
            workbook.LoadFromFile("Sample.xlsx");

            // 获取第一个工作表
            Worksheet sheet = workbook.Worksheets[0];

            // 获取用于创建数据透视表的数据所在的单元格范围
            CellRange range = sheet.Range[1, 1, 16, 8];

            // 创建一个XlsPivotCache对象
            PivotCache pivotCache = workbook.PivotCaches.Add(range);

            // 创建一个数据透视表
            PivotTable pivotTable = sheet.PivotTables.Add("Sales Analysis", sheet.Range[18, 1], pivotCache);

            // 添加行字段
            IPivotField field1 = pivotTable.PivotFields[sheet.Range["C1"].Value];
            field1.Axis = AxisTypes.Row;
            IPivotField field2 = pivotTable.PivotFields[sheet.Range["D1"].Value];
            field2.Axis = AxisTypes.Row;

            // 添加值字段
            IPivotField field3 = pivotTable.PivotFields[sheet.Range["F1"].Value];
            pivotTable.DataFields.Add(field3, "SUM: " + sheet.Range["F1"].Value, SubtotalTypes.Sum);
            IPivotField field4 = pivotTable.PivotFields[sheet.Range["G1"].Value];
            pivotTable.DataFields.Add(field4, "SUM: " + sheet.Range["G1"].Value, SubtotalTypes.Sum);
            IPivotField field5 = pivotTable.PivotFields[sheet.Range["H1"].Value];
            pivotTable.DataFields.Add(field5, "SUM: " + sheet.Range["H1"].Value, SubtotalTypes.Sum);

            // 更新数据透视表
            pivotTable.CalculateData();

            // 设置数据透视表格式
            pivotTable.AutoFormatType = PivotAutoFomatTypes.Table2;

            // 保存工作簿
            workbook.SaveToFile("output/CreatePivotTable.xlsx");
            workbook.Dispose();
        }
    }
}

结果文档

用C#在Excel文件中创建数据透视图

数据透视图基于数据透视表创建，我们可以使用Worksheet.PivotTables.get_Item()方法从Excel工作表中获取数据透视表，然后使用使用数据透视表通过Worksheet.Charts.Add()方法在工作表中创建数据透视图，并对其进行一些格式设置，从而完成数据透视图的创建。
以下是操作步骤：

1. 导入所需模块。
2. 创建Workbook类的实例。
3. 使用Workbook.LoadFromFile方法载入Excel文件。
4. 使用Workbook.Worksheets[]属性获取数据透视表所在的工作表。
5. 通过Worksheet.PivotTables[]属性获取数据透视表。
6. 使用Worksheet.Charts.Add方法在选定的数据透视表基础上创建一个图表。
7. 设置新创建的数据透视图的位置，通过设定Chart类的TopRow, LeftColumn, BottomRow和RightColumn属性来定义图表在工作表上的位置。
8. 通过设置Chart.ChartTitle属性设置图表标题。
9. 使用Workbook.SaveToFile方法保存修改后的工作簿。
10. 释放资源。

代码示例

using Spire.Xls;
using Spire.Xls.Core;

namespace CreatePivotTable
{
    class Program
    {
        static void Main(string[] args)
        {
            // 创建Workbook对象
            Workbook workbook = new Workbook();

            // 载入Excel文件
            workbook.LoadFromFile("output/CreatePivotTable.xlsx");

            // 获取第一个工作表
            Worksheet sheet = workbook.Worksheets[0];

            // 获取数据透视表
            IPivotTable pivotTable = sheet.PivotTables[0];

            // 创建数据透视图
            Chart pivotChart = sheet.Charts.Add(ExcelChartType.BarClustered, pivotTable);

            // 设置图表的位置
            pivotChart.TopRow = 18;
            pivotChart.LeftColumn = 8;
            pivotChart.BottomRow = 35;
            pivotChart.RightColumn = 20;

            // 设置图表标题
            pivotChart.ChartTitle = "";

            // 保存工作簿
            workbook.SaveToFile("output/CreatePivotChart.xlsx");
            workbook.Dispose();
        }
    }
}

结果文档

本文演示了如何使用C#在Excel工作表中创建数据透视表和数据透视图。