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" style="display: inline-block;"> 21 <div style=" 230px; height: 230px; float: left; "> 22 23 <a class="metro-tile" style="cursor: pointer; 230px; height: 110px; display: block; background-color:#ff0000; color: #fff; margin-bottom: 10px;"> 24 25 <input type="button" runat="server" id="Button1" name="btn1" value="回款情况分析" onserverclick="btn1_ServerClick" 26 style="background-color:transparent; color:white; font-size:16px;float:left; border:0; 230px; height:110px; cursor:pointer;"/> 27 28 </a> 29 30 <a class="metro-tile" style="cursor: pointer; 230px; height: 110px; display: block; background-color:#ff6a00; color: #fff;"> 31 <input type="button" runat="server" id="Button2" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 32 style="background-color:transparent; color:white; font-size:16px;float:left; border:0; 230px; height:110px; cursor:pointer;"/> 33 </a> 34 </div> 35 36 <div style=" 230px; height: 230px; float: left; margin-left: 10px"> 37 38 <a class="metro-tile" style="cursor: pointer; 230px; height: 230px; display: block; background-color:#ffd800; color: #fff"> 39 <input type="button" runat="server" id="btn1" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 40 style="background-color:transparent; color:white; font-size:16px;float:left; border:0; 230px; height:230px; cursor:pointer;"/> 41 </a> 42 43 </div> 44 45 <div style=" 230px; height: 230px; float: left; margin-left: 10px"> 46 47 <a class="metro-tile" style="cursor: pointer; 230px; height: 110px; display: block; background-color:#0094ff; color: #fff; margin-bottom: 10px;"> 48 <input type="button" runat="server" id="Button3" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 49 style="background-color:transparent; color:white; font-size:16px;float:left; border:0; 230px; height:110px; cursor:pointer;"/> 50 </a> 51 52 <a class="metro-tile" style="cursor: pointer; 110px; height: 110px; margin-right: 10px; display: block; float: left; background-color: #4800ff; color: #fff;"> 53 <input type="button" runat="server" id="Button4" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 54 style="background-color:transparent; color:white; font-size:16px;float:left; border:0; 110px; height:110px; cursor:pointer;"/> 55 </a> 56 57 <a class="metro-tile" style="cursor: pointer; 110px; height: 110px; display: block; background-color: #b200ff; float: right; color: #fff;"> 58 <input type="button" runat="server" id="Button5" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" 59 style="background-color:transparent; color:white; font-size:16px;float:left; border:0; 110px; height:110px; cursor:pointer;"/> 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:Windowsexplorer.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，并自动生成透视表，如下图所示：
