Excel 中的透視表對於數據分析來說，非常的方便，而且很多業務人員對於Excel的操作也是非常熟悉的，因此用Excel作為分析數據的界面，不失為一種很好的選擇。那麼如何用C#從數據庫中抓取數據，並在Excel 動態生成PivotTable呢？下面結合實例來說明。
一般來說，數據庫的設計都遵循規范化的原則，從而減少數據的冗余，但是對於數據分析來說，數據冗余能夠提高數據加載的速度，因此為了演示透視表，這裡現在數據庫中建立一個視圖，將需要分析的數據整合到一個視圖中。如下圖所示：
數據源准備好後，我們先來建立一個web應用程序，然後用NuGet加載Epplus程序包，如下圖所示：
在index.aspx前台頁面中，編寫如下腳本：
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="ExcelPivot.Web.index" %> 2 3 <!DOCTYPE html> 4 <html xmlns="http://www.w3.org/1999/xhtml"> 5 <head runat="server"> 6 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> 7 <title>Excel PivotTable</title> 8 <link rel="stylesheet" type="text/css" href="css/style.css" /> 9 </head> 10 <body> 11 <form id="form1" runat="server"> 12 <div id="container"> 13 14 <div id="contents"> 15 16 <div id="post"> 17 <header> 18 <h1> Excel PivotTable </h1> 19 </header> 20 <div id="metro-array" > 21 <div > 22 23 <a class="metro-tile" > 24 25 <input type="button" runat="server" id="Button1" name="btn1" value="回款情況分析" onserverclick="btn1_ServerClick" 26 /> 27 28 </a> 29 30 <a class="metro-tile" > 31 <input type="button" runat="server" id="Button2" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 32 /> 33 </a> 34 </div> 35 36 <div > 37 38 <a class="metro-tile" > 39 <input type="button" runat="server" id="btn1" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 40 /> 41 </a> 42 43 </div> 44 45 <div > 46 47 <a class="metro-tile" > 48 <input type="button" runat="server" id="Button3" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 49 /> 50 </a> 51 52 <a class="metro-tile" > 53 <input type="button" runat="server" id="Button4" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 54 /> 55 </a> 56 57 <a class="metro-tile" > 58 <input type="button" runat="server" id="Button5" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 59 /> 60 </a> 61 </div> 62 63 </div> 64 </div> 65 66 </div> 67 </div> 68 </form> 69 </body> 70 <script src="js/tileJs.js" type="text/javascript"></script> 71 </html>
其中 TileJs是一個開源的構建類似win8 Metro風格的javascript庫。
編寫後台腳本：
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Web.UI;
6 using System.Web.UI.WebControls;
7 using OfficeOpenXml;
8 using OfficeOpenXml.Table;
9 using OfficeOpenXml.ConditionalFormatting;
10 using OfficeOpenXml.Style;
11 using OfficeOpenXml.Utils;
12 using OfficeOpenXml.Table.PivotTable;
13 using System.IO;
14 using System.Data.SqlClient;
15 using System.Data;
16 namespace ExcelPivot.Web
17 {
18 public partial class index : System.Web.UI.Page
19 {
20 protected void Page_Load(object sender, EventArgs e)
21 {
22
23 }
24 private DataTable getDataSource()
25 {
26 //createDataTable();
27 //return ProductInfo;
28
29 SqlConnection conn = new SqlConnection();
30 conn.ConnectionString = "Data Source=.;Initial Catalog=olap;Persist Security Info=True;User ID=sa;Password=sa";
31 conn.Open();
32
33 SqlDataAdapter ada = new SqlDataAdapter("select * from v_pm_olap_test", conn);
34 DataSet ds = new DataSet();
35 ada.Fill(ds);
36
37 return ds.Tables[0];
38
39
40
41 }
42
43 protected void btn1_ServerClick(object sender, EventArgs e)
44 {
45 try
46 {
47 DataTable table = getDataSource();
48 string path = "_demo_" + System.Guid.NewGuid().ToString().Replace("-", "_") + ".xls";
49 //string path = "_demo.xls";
50 FileInfo fileInfo = new FileInfo(path);
51 var excel = new ExcelPackage(fileInfo);
52
53 var wsPivot = excel.Workbook.Worksheets.Add("Pivot");
54 var wsData = excel.Workbook.Worksheets.Add("Data");
55 wsData.Cells["A1"].LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Medium6);
56 if (table.Rows.Count != 0)
57 {
58 foreach (DataColumn col in table.Columns)
59 {
60
61 if (col.DataType == typeof(System.DateTime))
62 {
63 var colNumber = col.Ordinal + 1;
64 var range = wsData.Cells[2, colNumber, table.Rows.Count + 1, colNumber];
65 range.Style.Numberformat.Format = "yyyy-MM-dd";
66 }
67 else
68 {
69
70 }
71 }
72 }
73
74 var dataRange = wsData.Cells[wsData.Dimension.Address.ToString()];
75 dataRange.AutoFitColumns();
76 var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "Pivot");
77 pivotTable.MultipleFieldFilters = true;
78 pivotTable.RowGrandTotals = true;
79 pivotTable.ColumGrandTotals = true;
80 pivotTable.Compact = true;
81 pivotTable.CompactData = true;
82 pivotTable.GridDropZones = false;
83 pivotTable.Outline = false;
84 pivotTable.OutlineData = false;
85 pivotTable.ShowError = true;
86 pivotTable.ErrorCaption = "[error]";
87 pivotTable.ShowHeaders = true;
88 pivotTable.UseAutoFormatting = true;
89 pivotTable.ApplyWidthHeightFormats = true;
90 pivotTable.ShowDrill = true;
91 pivotTable.FirstDataCol = 3;
92 //pivotTable.RowHeaderCaption = "行";
93
94 //row field
95 var field004 = pivotTable.Fields["銷售客戶經理"];
96 pivotTable.RowFields.Add(field004);
97
98 var field001 = pivotTable.Fields["項目簡稱"];
99 pivotTable.RowFields.Add(field001);
100 //field001.ShowAll = false;
101
102 //column field
103 var field002 = pivotTable.Fields["年"];
104 pivotTable.ColumnFields.Add(field002);
105 field002.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending;
106 var field005 = pivotTable.Fields["月"];
107 pivotTable.ColumnFields.Add(field005);
108 field005.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending;
109
110 //data field
111 var field003 = pivotTable.Fields["回款金額"];
112 field003.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Descending;
113 pivotTable.DataFields.Add(field003);
114
115 pivotTable.RowGrandTotals = false;
116 pivotTable.ColumGrandTotals = false;
117
118 //save file
119 excel.Save();
120 //open excel file
121 string file = @"C:\Windows\explorer.exe";
122 System.Diagnostics.Process.Start(file, path);
123
124 }
125 catch (Exception ex)
126 {
127 Response.Write(ex.Message);
128 }
129 }
130 }
131 }
編譯運行，如下圖所示：
單擊 [回款情況分析]，稍等片刻，會打開Excel，並自動生成透視表，如下圖所示：
