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函数进行汇总。
本文链接:http://task.lmcjl.com/news/3446.html