C#實現Excel動態生成PivotTable

2020-01-24 01:12:10

字體：大中小

來源：轉載

供稿：網友

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" style="display: inline-block;">            <div style="width: 230px; height: 230px; float: left; ">              <a class="metro-tile" style="cursor: pointer; width: 230px; height: 110px; display: block; background-color:#ff0000; color: #fff; margin-bottom: 10px;">                                 <input type="button" runat="server" id="Button1" name="btn1" value="回款情況分析" onserverclick="btn1_ServerClick"                           style="background-color:transparent; color:white; font-size:16px;float:left; border:0; width:230px; height:110px; cursor:pointer;"/>                            </a>              <a class="metro-tile" style="cursor: pointer; width: 230px; height: 110px; display: block; background-color:#ff6a00; color: #fff;">                 <input type="button" runat="server" id="Button2" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"                           style="background-color:transparent; color:white; font-size:16px;float:left; border:0; width:230px; height:110px; cursor:pointer;"/>              </a>            </div>            <div style="width: 230px; height: 230px; float: left; margin-left: 10px">              <a class="metro-tile" style="cursor: pointer; width: 230px; height: 230px; display: block; background-color:#ffd800; color: #fff">                 <input type="button" runat="server" id="btn1" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"                           style="background-color:transparent; color:white; font-size:16px;float:left; border:0; width:230px; height:230px; cursor:pointer;"/>              </a>            </div>            <div style="width: 230px; height: 230px; float: left; margin-left: 10px">              <a class="metro-tile" style="cursor: pointer; width: 230px; height: 110px; display: block; background-color:#0094ff; color: #fff; margin-bottom: 10px;">                 <input type="button" runat="server" id="Button3" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"                           style="background-color:transparent; color:white; font-size:16px;float:left; border:0; width:230px; height:110px; cursor:pointer;"/>              </a>              <a class="metro-tile" style="cursor: pointer; width: 110px; height: 110px; margin-right: 10px; display: block; float: left; background-color: #4800ff; color: #fff;">                 <input type="button" runat="server" id="Button4" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"                           style="background-color:transparent; color:white; font-size:16px;float:left; border:0; width:110px; height:110px; cursor:pointer;"/>              </a>              <a class="metro-tile" style="cursor: pointer; width: 110px; height: 110px; display: block; background-color: #b200ff; float: right; color: #fff;">                 <input type="button" runat="server" id="Button5" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"                           style="background-color:transparent; color:white; font-size:16px;float:left; border:0; width:110px; height:110px; cursor:pointer;"/>              </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().Replace("-", "_") + ".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);      }    }  }}

編譯運行，如下圖所示：