C#实现Excel动态生成PivotTable

C#实现Excel动态生成PivotTable的完整攻略

动态生成PivotTable，其实就是利用C#程序将数据导入Excel表格中的PivotTable，并且使得PivotTable自动更新，并支持动态增加或删除数据。下面就是实现这个功能的完整攻略：

1. 创建Excel文件并设置PivotTable数据源

首先，需要在C#中安装对Excel操作的支持，这可以通过引用Microsoft.Office.Interop.Excel.dll来实现。接下来，创建一个新的Excel文件并设置PivotTable的数据源，示例代码如下：

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application excelApp = new Excel.Application();
Excel.Workbook workbook = excelApp.Workbooks.Add();
Excel.Worksheet worksheet = workbook.Worksheets[1];

worksheet.Range["A1"].Value = "Name";
worksheet.Range["B1"].Value = "Gender";
worksheet.Range["C1"].Value = "Age";
worksheet.Range["A2"].Value = "John";
worksheet.Range["B2"].Value = "Male";
worksheet.Range["C2"].Value = 26;
worksheet.Range["A3"].Value = "Lisa";
worksheet.Range["B3"].Value = "Female";
worksheet.Range["C3"].Value = 30;
worksheet.Range["A4"].Value = "Tom";
worksheet.Range["B4"].Value = "Male";
worksheet.Range["C4"].Value = 28;

Excel.Range dataRange = worksheet.Range["A1:C4"];
Excel.PivotCache cache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange);
Excel.PivotTable pivotTable = cache.CreatePivotTable(worksheet.Range["E3"]);

上述代码创建了一个Excel文件，将数据源写入到工作表中，并设置了数据源的范围。然后使用了CreatePivotTable方法创建了一个PivotTable对象，并将其放置在Excel工作表的E3单元格上。

2. 设置PivotTable行列和数据字段

PivotTable中的数据以行列的方式排列，同时还要指定数据汇总的方式。示例代码如下：

pivotTable.PivotFields("Gender").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Age").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.AddDataField(pivotTable.PivotFields("Name"), "Count of Name", Excel.XlConsolidationFunction.xlCount);

上述代码指定了行字段为“Gender”，列字段为“Age”，数据字段为“Name”，且使用了Count函数进行汇总。

3. 增加或删除PivotTable数据

动态生成PivotTable需要支持添加或删除数据，示例代码如下：

Excel.Range newDataRange = worksheet.Range["A5:C6"];
Excel.ListObject table = worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, newDataRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing);
cache.SourceData = table;

pivotTable.RefreshTable();

上述代码添加了两条新的数据到Excel表格中，并将新的数据范围指定为数据源，然后使用RefreshTable方法刷新PivotTable对象，以便自动更新汇总数据。

示例1：

以下是一个完整的程序，它创建一个新的Excel文件，并设置一个PivotTable，然后添加两条新的数据和更改现有数据，并刷新PivotTable对象以显示新的汇总。

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = excelApp.Workbooks.Add();
            Excel.Worksheet worksheet = workbook.Worksheets[1];

            worksheet.Range["A1"].Value = "Name";
            worksheet.Range["B1"].Value = "Gender";
            worksheet.Range["C1"].Value = "Age";
            worksheet.Range["A2"].Value = "John";
            worksheet.Range["B2"].Value = "Male";
            worksheet.Range["C2"].Value = 26;
            worksheet.Range["A3"].Value = "Lisa";
            worksheet.Range["B3"].Value = "Female";
            worksheet.Range["C3"].Value = 30;
            worksheet.Range["A4"].Value = "Tom";
            worksheet.Range["B4"].Value = "Male";
            worksheet.Range["C4"].Value = 28;

            Excel.Range dataRange = worksheet.Range["A1:C4"];
            Excel.PivotCache cache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange);
            Excel.PivotTable pivotTable = cache.CreatePivotTable(worksheet.Range["E3"]);

            pivotTable.PivotFields("Gender").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pivotTable.PivotFields("Age").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
            pivotTable.AddDataField(pivotTable.PivotFields("Name"), "Count of Name", Excel.XlConsolidationFunction.xlCount);

            Excel.Range newDataRange = worksheet.Range["A5:C6"];
            Excel.ListObject table = worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, newDataRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing);
            cache.SourceData = table;

            worksheet.Range["A2"].Value = "Jack";
            worksheet.Range["B2"].Value = "Male";
            worksheet.Range["C2"].Value = 25;

            pivotTable.RefreshTable();

            workbook.SaveAs("Test.xlsx");
            workbook.Close();
            excelApp.Quit();
        }
    }
}

示例2：

以下是一个PivotTable轴向的具体示例，其中行字段为Country，列字段为Year，数据字段为Gross Sales：

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = excelApp.Workbooks.Add();
            Excel.Worksheet worksheet = workbook.Worksheets[1];

            worksheet.Range["A1"].Value = "Country";
            worksheet.Range["B1"].Value = "Year";
            worksheet.Range["C1"].Value = "Gross Sales";
            worksheet.Range["A2"].Value = "USA";
            worksheet.Range["B2"].Value = 2020;
            worksheet.Range["C2"].Value = 10000;
            worksheet.Range["A3"].Value = "Canada";
            worksheet.Range["B3"].Value = 2020;
            worksheet.Range["C3"].Value = 20000;
            worksheet.Range["A4"].Value = "Mexico";
            worksheet.Range["B4"].Value = 2020;
            worksheet.Range["C4"].Value = 30000;

            Excel.Range dataRange = worksheet.Range["A1:C4"];
            Excel.PivotCache cache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange);
            Excel.PivotTable pivotTable = cache.CreatePivotTable(worksheet.Range["E3"]);

            pivotTable.PivotFields("Country").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pivotTable.PivotFields("Year").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
            pivotTable.AddDataField(pivotTable.PivotFields("Gross Sales"), "Sum of Gross Sales", Excel.XlConsolidationFunction.xlSum);

            workbook.SaveAs("Test.xlsx");
            workbook.Close();
            excelApp.Quit();
        }
    }
}

这个示例创建了一个PivotTable，其中行字段为Country，列字段为Year，数据字段为Gross Sales，并使用Sum函数进行汇总。