C#完成Excel靜態生成PivotTable。本站提示廣大學習愛好者:(C#完成Excel靜態生成PivotTable)文章只能為提供參考,不一定能成為您想要的結果。以下是C#完成Excel靜態生成PivotTable正文
Excel 中的透視表關於數據剖析來講,異常的便利,並且許多營業人員關於Excel的操作也長短常熟習的,是以用Excel作為剖析數據的界面,不掉為一種很好的選擇。那末若何用C#從數據庫中抓取數據,並在Excel 靜態生成PivotTable呢?上面聯合實例來講明。
普通來講,數據庫的設計都遵守標准化的准繩,從而削減數據的冗余,然則關於數據剖析來講,數據冗余可以或許進步數據加載的速度,是以為了演示透視表,這裡如今數據庫中樹立一個視圖,將須要剖析的數據整合到一個視圖中。以下圖所示:
數據源預備好後,我們先來樹立一個web運用法式,然後用NuGet加載Epplus法式包,以下圖所示:
在index.aspx前台頁面中,編寫以下劇本:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="ExcelPivot.Web.index" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title>Excel PivotTable</title> <link rel="stylesheet" type="text/css" href="css/style.css" /> </head> <body> <form id="form1" runat="server"> <div id="container"> <div id="contents"> <div id="post"> <header> <h1> Excel PivotTable </h1> </header> <div id="metro-array" > <div > <a class="metro-tile" > <input type="button" runat="server" id="Button1" name="btn1" value="回款情形剖析" onserverclick="btn1_ServerClick" /> </a> <a class="metro-tile" > <input type="button" runat="server" id="Button2" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" /> </a> </div> <div > <a class="metro-tile" > <input type="button" runat="server" id="btn1" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" /> </a> </div> <div > <a class="metro-tile" > <input type="button" runat="server" id="Button3" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" /> </a> <a class="metro-tile" > <input type="button" runat="server" id="Button4" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" /> </a> <a class="metro-tile" > <input type="button" runat="server" id="Button5" name="btn1" value="sampe1" onserverclick="btn1_ServerClick" /> </a> </div> </div> </div> </div> </div> </form> </body> <script src="js/tileJs.js" type="text/javascript"></script> </html>
個中 TileJs是一個開源的構建相似win8 Metro作風的javascript庫。
編寫後台劇本:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using OfficeOpenXml; using OfficeOpenXml.Table; using OfficeOpenXml.ConditionalFormatting; using OfficeOpenXml.Style; using OfficeOpenXml.Utils; using OfficeOpenXml.Table.PivotTable; using System.IO; using System.Data.SqlClient; using System.Data; namespace ExcelPivot.Web { public partial class index : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } private DataTable getDataSource() { //createDataTable(); //return ProductInfo; SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Data Source=.;Initial Catalog=olap;Persist Security Info=True;User ID=sa;Password=sa"; conn.Open(); SqlDataAdapter ada = new SqlDataAdapter("select * from v_pm_olap_test", conn); DataSet ds = new DataSet(); ada.Fill(ds); return ds.Tables[0]; } protected void btn1_ServerClick(object sender, EventArgs e) { try { DataTable table = getDataSource(); string path = "_demo_" + System.Guid.NewGuid().ToString().WordStr("-", "_") + ".xls"; //string path = "_demo.xls"; FileInfo fileInfo = new FileInfo(path); var excel = new ExcelPackage(fileInfo); var wsPivot = excel.Workbook.Worksheets.Add("Pivot"); var wsData = excel.Workbook.Worksheets.Add("Data"); wsData.Cells["A1"].LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Medium6); if (table.Rows.Count != 0) { foreach (DataColumn col in table.Columns) { if (col.DataType == typeof(System.DateTime)) { var colNumber = col.Ordinal + 1; var range = wsData.Cells[2, colNumber, table.Rows.Count + 1, colNumber]; range.Style.Numberformat.Format = "yyyy-MM-dd"; } else { } } } var dataRange = wsData.Cells[wsData.Dimension.Address.ToString()]; dataRange.AutoFitColumns(); var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "Pivot"); pivotTable.MultipleFieldFilters = true; pivotTable.RowGrandTotals = true; pivotTable.ColumGrandTotals = true; pivotTable.Compact = true; pivotTable.CompactData = true; pivotTable.GridDropZones = false; pivotTable.Outline = false; pivotTable.OutlineData = false; pivotTable.ShowError = true; pivotTable.ErrorCaption = "[error]"; pivotTable.ShowHeaders = true; pivotTable.UseAutoFormatting = true; pivotTable.ApplyWidthHeightFormats = true; pivotTable.ShowDrill = true; pivotTable.FirstDataCol = 3; //pivotTable.RowHeaderCaption = "行"; //row field var field004 = pivotTable.Fields["發賣客戶司理"]; pivotTable.RowFields.Add(field004); var field001 = pivotTable.Fields["項目簡稱"]; pivotTable.RowFields.Add(field001); //field001.ShowAll = false; //column field var field002 = pivotTable.Fields["年"]; pivotTable.ColumnFields.Add(field002); field002.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending; var field005 = pivotTable.Fields["月"]; pivotTable.ColumnFields.Add(field005); field005.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending; //data field var field003 = pivotTable.Fields["回款金額"]; field003.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Descending; pivotTable.DataFields.Add(field003); pivotTable.RowGrandTotals = false; pivotTable.ColumGrandTotals = false; //save file excel.Save(); //open excel file string file = @"C:\Windows\explorer.exe"; System.Diagnostics.Process.Start(file, path); } catch (Exception ex) { Response.Write(ex.Message); } } } }
編譯運轉,以下圖所示:
單擊 [回款情形剖析],稍等少焉,會翻開Excel,並主動生成透視表,以下圖所示:
以上就是本文的全體內容,願望對年夜家的進修有所贊助