C#实现Excel动态生成PivotTable

内容摘要
Excel 中的透视表对于数据分析来说，非常的方便，而且很多业务人员对于Excel的操作也是非常熟悉的，因此用Excel作为分析数据的界面，不失为一种很好的选择。那么如何用C#从数据库中
文章正文

Excel 中的透视表对于数据分析来说，非常的方便，而且很多业务人员对于Excel的操作也是非常熟悉的，因此用Excel作为分析数据的界面，不失为一种很好的选择。那么如何用C#从数据库中抓取数据，并在Excel 动态生成PivotTable呢？下面结合实例来说明。

一般来说，数据库的设计都遵循规范化的原则，从而减少数据的冗余，但是对于数据分析来说，数据冗余能够提高数据加载的速度，因此为了演示透视表，这里现在数据库中建立一个视图，将需要分析的数据整合到一个视图中。如下图所示：

数据源准备好后，我们先来建立一个web应用程序，然后用NuGet加载Epplus程序包，如下图所示：

 在index.aspx前台页面中，编写如下脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<%@ 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库。

编写后台脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
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，并自动生成透视表，如下图所示：

以上就是本文的全部内容，希望对大家的学习有所帮助
代码注释
上一篇win10预览版10105/10107怎么样 win10预览版10105和win10预览版10107详细介绍下一篇Java如何实现URL带请求参数(get/post)及得到get和post请求url和参数列表的方法

作者：喵哥笔记

IDC笔记

学的不仅是技术，更是梦想！