雷火电竞-中国电竞赛事及体育赛事平台

歡迎來到入門教程網(wǎng)！

C#教程

當(dāng)前位置：主頁 > 軟件編程 > C#教程 >

C#實現(xiàn)Excel動態(tài)生成PivotTable

來源：本站原創(chuàng)|時間：2020-01-10|欄目：C#教程|點擊： 次

Excel 中的透視表對于數(shù)據(jù)分析來說，非常的方便，而且很多業(yè)務(wù)人員對于Excel的操作也是非常熟悉的，因此用Excel作為分析數(shù)據(jù)的界面，不失為一種很好的選擇。那么如何用C#從數(shù)據(jù)庫中抓取數(shù)據(jù)，并在Excel 動態(tài)生成PivotTable呢？下面結(jié)合實例來說明。

一般來說，數(shù)據(jù)庫的設(shè)計都遵循規(guī)范化的原則，從而減少數(shù)據(jù)的冗余，但是對于數(shù)據(jù)分析來說，數(shù)據(jù)冗余能夠提高數(shù)據(jù)加載的速度，因此為了演示透視表，這里現(xiàn)在數(shù)據(jù)庫中建立一個視圖，將需要分析的數(shù)據(jù)整合到一個視圖中。如下圖所示：

數(shù)據(jù)源準(zhǔn)備好后，我們先來建立一個web應(yīng)用程序，然后用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是一個開源的構(gòu)建類似win8 Metro風(fēng)格的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["銷售客戶經(jīng)理"];
        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，并自動生成透視表，如下圖所示：

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助

上一篇：C#使用Protocol Buffer(ProtoBuf)進(jìn)行Unity中的Socket通信

欄    目：C#教程

下一篇：Windows系統(tǒng)中C#調(diào)用WinRAR來壓縮和解壓縮文件的方法

本文標(biāo)題：C#實現(xiàn)Excel動態(tài)生成PivotTable

本文地址：http://www.jygsgssxh.com/a1/C_jiaocheng/6580.html

網(wǎng)頁制作CMS教程網(wǎng)絡(luò)編程軟件編程腳本語言數(shù)據(jù)庫服務(wù)器

如果侵犯了您的權(quán)利，請與我們聯(lián)系，我們將在24小時內(nèi)進(jìn)行處理、任何非本站因素導(dǎo)致的法律后果，本站均不負(fù)任何責(zé)任。

聯(lián)系QQ：835971066 | 郵箱：835971066#qq.com(#換成@)

Copyright © 2002-2020 腳本教程網(wǎng) 版權(quán)所有