C#实现Excel动态生成PivotTable

前端技术 2023/08/08 C#

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);
      }
    }
  }
}

编译运行，如下图所示：

 单击 [回款情况分析]，稍等片刻，会打开Excel，并自动生成透视表，如下图所示：

以上就是本文的全部内容，希望对大家的学习有所帮助

本文地址：https://www.stayed.cn/item/515

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案