你的位置:首页 > ASP.net教程

[ASP.net教程]C# 如何在Excel 动态生成PivotTable


  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 ="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="width: 230px; height: 230px; float: left; ">22 23               <a class="metro-tile" style="cursor: pointer; width: 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; width:230px; height:110px; cursor:pointer;"/>27               28               </a>29 30               <a class="metro-tile" style="cursor: pointer; width: 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; width:230px; height:110px; cursor:pointer;"/>33               </a>34             </div>35 36             <div style="width: 230px; height: 230px; float: left; margin-left: 10px">37 38               <a class="metro-tile" style="cursor: pointer; width: 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; width:230px; height:230px; cursor:pointer;"/>41               </a>42 43             </div>44 45             <div style="width: 230px; height: 230px; float: left; margin-left: 10px">46 47               <a class="metro-tile" style="cursor: pointer; width: 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; width:230px; height:110px; cursor:pointer;"/>50               </a>51 52               <a class="metro-tile" style="cursor: pointer; width: 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; width:110px; height:110px; cursor:pointer;"/>55               </a>56 57               <a class="metro-tile" style="cursor: pointer; width: 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; width: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 OfficeOpen 8 using OfficeOpen 9 using OfficeOpen 10 using OfficeOpen 11 using OfficeOpen 12 using OfficeOpen 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, OfficeOpen 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 field103         var field002 = pivotTable.Fields["年"];104         pivotTable.ColumnFields.Add(field002);105         field002.Sort = OfficeOpen106         var field005 = pivotTable.Fields["月"];107         pivotTable.ColumnFields.Add(field005);108         field005.Sort = OfficeOpen109 110         //data field111         var field003 = pivotTable.Fields["回款金额"];112         field003.Sort = OfficeOpen113         pivotTable.DataFields.Add(field003);114 115         pivotTable.RowGrandTotals = false;116         pivotTable.ColumGrandTotals = false;117        118         //save file119         excel.Save();120         //open excel file121         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,并自动生成透视表,如下图所示: