服务器之家:专注于VPS、云服务器配置技术及软件下载分享
分类导航

PHP教程|ASP.NET教程|Java教程|ASP教程|编程技术|正则表达式|C/C++|IOS|C#|Swift|Android|VB|R语言|JavaScript|易语言|vb.net|

服务器之家 - 编程语言 - C# - C#实现Excel动态生成PivotTable

C#实现Excel动态生成PivotTable

2021-11-18 11:44JackWang-CUMT C#

这篇文章主要为大家详细介绍了C#实现Excel动态生成PivotTable的相关方法,感兴趣的小伙伴们可以参考一下

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

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

C#实现Excel动态生成PivotTable

数据源准备好后,我们先来建立一个web应用程序,然后用nuget加载epplus程序包,如下图所示:

C#实现Excel动态生成PivotTable

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

编译运行,如下图所示:

C#实现Excel动态生成PivotTable

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

C#实现Excel动态生成PivotTable

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

延伸 · 阅读

精彩推荐
  • C#浅谈C# winForm 窗体闪烁的问题

    浅谈C# winForm 窗体闪烁的问题

    下面小编就为大家带来一篇浅谈C# winForm 窗体闪烁的问题。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧...

    C#教程网7962021-12-21
  • C#聊一聊C#接口问题 新手速来围观

    聊一聊C#接口问题 新手速来围观

    聊一聊C#接口问题,新手速来围观,一个通俗易懂的例子帮助大家更好的理解C#接口问题,感兴趣的小伙伴们可以参考一下...

    zenkey7072021-12-03
  • C#C#直线的最小二乘法线性回归运算实例

    C#直线的最小二乘法线性回归运算实例

    这篇文章主要介绍了C#直线的最小二乘法线性回归运算方法,实例分析了给定一组点,用最小二乘法进行线性回归运算的实现技巧,具有一定参考借鉴价值,需要...

    北风其凉8912021-10-18
  • C#C#基础之泛型

    C#基础之泛型

    泛型是 2.0 版 C# 语言和公共语言运行库 (CLR) 中的一个新功能。接下来通过本文给大家介绍c#基础之泛型,感兴趣的朋友一起学习吧...

    方小白7732021-12-03
  • C#Unity3D UGUI实现缩放循环拖动卡牌展示效果

    Unity3D UGUI实现缩放循环拖动卡牌展示效果

    这篇文章主要为大家详细介绍了Unity3D UGUI实现缩放循环拖动展示卡牌效果,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参...

    诗远3662022-03-11
  • C#C# 后台处理图片的几种方法

    C# 后台处理图片的几种方法

    本篇文章主要介绍了C# 后台处理图片的几种方法,非常具有实用价值,需要的朋友可以参考下。...

    IT小伙儿10162021-12-08
  • C#c#学习之30分钟学会XAML

    c#学习之30分钟学会XAML

    一个界面程序的核心,无疑就是界面和后台代码,而xaml就是微软为构建应用程序界面而创建的一种描述性语言,也就是说,这东西是搞界面的...

    C#教程网8812021-12-10
  • C#C#实现的文件操作封装类完整实例【删除,移动,复制,重命名】

    C#实现的文件操作封装类完整实例【删除,移动,复制,重命名】

    这篇文章主要介绍了C#实现的文件操作封装类,结合完整实例形式分析了C#封装文件的删除,移动,复制,重命名等操作相关实现技巧,需要的朋友可以参考下...

    Rising_Sun3892021-12-28