高效處理與分析海量數據是決策中的重要一環。通過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對象創建數據透視表，並對數據透視表的行字段、列字段、值字段等進行設置，從而創建一個完善的數據透視表。
以下是操作步驟：
- 導入所需模塊。
- 創建
Workbook類的實例。 - 使用
Workbook.LoadFromFile方法載入指定路徑的現有Excel文件到工作簿實例中。 - 使用
Workbook.Worksheets[]屬性獲取工作簿中的一個工作表。 - 定義數據源範圍：通過
Worksheet.Range[]屬性選取單元格區域。 - 使用
Workbook.PivotCaches.Add方法添加數據源範圍到工作簿的緩存中創建一個XlsPivotCache對象。 - 在當前工作表中定義位置並使用
Worksheet.PivotTables.Add方法以及之前創建的XlsPivotCache對象創建一個數據透視表。數據透視表可創建在其他工作表中。 - 為數據透視表添加行字段，通過
XlsPivotTable.PivotFields[]屬性選擇對應的列，並設置其軸類型為行。 - 添加值字段，選擇需要求和的數據列，並通過
XlsPivotTable.DataFields.Add方法添加到數據透視表中。 - 使用
XlsPivotCache.CalculateData()方法更新數據透視表值。 - 使用
XlsPivotTable.BuiltInStyle屬性應用內置樣式。 - 使用
XlsPivotTable.AutoFormatType屬性設置數據透視表的自動格式類型。 - 使用
Workbook.SaveToFile方法保存修改後的工作簿到指定路徑。 - 釋放資源。
代碼示例
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()方法在工作表中創建數據透視圖，並對其進行一些格式設置，從而完成數據透視圖的創建。
以下是操作步驟：
- 導入所需模塊。
- 創建
Workbook類的實例。 - 使用
Workbook.LoadFromFile方法載入Excel文件。 - 使用
Workbook.Worksheets[]屬性獲取數據透視表所在的工作表。 - 通過
Worksheet.PivotTables[]屬性獲取數據透視表。 - 使用
Worksheet.Charts.Add方法在選定的數據透視表基礎上創建一個圖表。 - 設置新創建的數據透視圖的位置，通過設定
Chart類的TopRow,LeftColumn,BottomRow和RightColumn屬性來定義圖表在工作表上的位置。 - 通過設置
Chart.ChartTitle屬性設置圖表標題。 - 使用
Workbook.SaveToFile方法保存修改後的工作簿。 - 釋放資源。
代碼示例
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工作表中創建數據透視表和數據透視圖。
