C# 如何在Excel 动态生成PivotTable

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

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

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

 在index.aspx前台页面中,编写如下脚本:

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="ExcelPivot.Web.index" %>
 2 
 3 <!DOCTYPE html>
 4 <html xmlns="http://www.w3.org/1999/xhtml">
 5 <head runat="server">
 6 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
 7     <title>Excel PivotTable</title>
 8     <link rel="stylesheet" type="text/css" href="css/style.css" />  
 9 </head>
10 <body>
11     <form id="form1" runat="server">
12        <div id="container">
13 
14             <div id="contents">
15 
16                 <div id="post">
17                     <header>
18                         <h1> Excel PivotTable </h1>
19                     </header>
20                     <div id="metro-array" style="display: inline-block;">
21                         <div style="width: 230px; height: 230px; float: left; ">
22 
23                             <a class="metro-tile" style="cursor: pointer; width: 230px; height: 110px; display: block;  color: #fff; margin-bottom: 10px;">
24                                 
25                                  <input type="button" runat="server" id="Button1" name="btn1" value="回款情况分析" onserverclick="btn1_ServerClick"  
26                                                    style=" color:white; font-size:16px;float:left; border:0; width:230px; height:110px; cursor:pointer;"/>
27                             
28                             </a>
29 
30                             <a class="metro-tile" style="cursor: pointer; width: 230px; height: 110px; display: block;  color: #fff;">
31                                  <input type="button" runat="server" id="Button2" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"  
32                                                    style=" color:white; font-size:16px;float:left; border:0; width:230px; height:110px; cursor:pointer;"/>
33                             </a>
34                         </div>
35 
36                         <div style="width: 230px; height: 230px; float: left; margin-left: 10px">
37 
38                             <a class="metro-tile" style="cursor: pointer; width: 230px; height: 230px; display: block;  color: #fff">
39                                  <input type="button" runat="server" id="btn1" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"  
40                                                    style=" color:white; font-size:16px;float:left; border:0; width:230px; height:230px; cursor:pointer;"/>
41                             </a>
42 
43                         </div>
44 
45                         <div style="width: 230px; height: 230px; float: left; margin-left: 10px">
46 
47                             <a class="metro-tile" style="cursor: pointer; width: 230px; height: 110px; display: block;  color: #fff; margin-bottom: 10px;">
48                                  <input type="button" runat="server" id="Button3" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"  
49                                                    style=" color:white; font-size:16px;float:left; border:0; width:230px; height:110px; cursor:pointer;"/>
50                             </a>
51 
52                             <a class="metro-tile" style="cursor: pointer; width: 110px; height: 110px; margin-right: 10px; display: block; float: left;  color: #fff;">
53                                  <input type="button" runat="server" id="Button4" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"  
54                                                    style=" color:white; font-size:16px;float:left; border:0; width:110px; height:110px; cursor:pointer;"/>
55                             </a>
56 
57                             <a class="metro-tile" style="cursor: pointer; width: 110px; height: 110px; display: block;  float: right; color: #fff;">
58                                  <input type="button" runat="server" id="Button5" name="btn1" value="sampe1" onserverclick="btn1_ServerClick"  
59                                                    style=" color:white; font-size:16px;float:left; border:0; width:110px; height:110px; cursor:pointer;"/>
60                             </a>
61                         </div>
62 
63                     </div>
64                 </div>
65 
66             </div>
67         </div>
68     </form>
69 </body>
70     <script src="js/tileJs.js" type="text/javascript"></script>
71 </html>

其中 TileJs是一个开源的构建类似win8 Metro风格的javascript库。

编写后台脚本:

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Web.UI;
  6 using System.Web.UI.WebControls;
  7 using OfficeOpenXml;
  8 using OfficeOpenXml.Table;
  9 using OfficeOpenXml.ConditionalFormatting;
 10 using OfficeOpenXml.Style;
 11 using OfficeOpenXml.Utils;
 12 using OfficeOpenXml.Table.PivotTable;
 13 using System.IO;
 14 using System.Data.SqlClient;
 15 using System.Data;
 16 namespace ExcelPivot.Web
 17 {
 18     public partial class index : System.Web.UI.Page
 19     {
 20         protected void Page_Load(object sender, EventArgs e)
 21         {
 22 
 23         }
 24         private DataTable getDataSource()
 25         {
 26             //createDataTable();
 27             //return ProductInfo;
 28 
 29             SqlConnection conn = new SqlConnection();
 30             conn.ConnectionString = "Data Source=.;Initial Catalog=olap;Persist Security Info=True;User ID=sa;Password=sa";
 31             conn.Open();
 32 
 33             SqlDataAdapter ada = new SqlDataAdapter("select * from v_pm_olap_test", conn);
 34             DataSet ds = new DataSet();
 35             ada.Fill(ds);
 36 
 37             return ds.Tables[0];
 38 
 39 
 40 
 41         }
 42       
 43         protected void btn1_ServerClick(object sender, EventArgs e)
 44         {
 45             try
 46             {
 47                 DataTable table = getDataSource();
 48                 string path = "_demo_" + System.Guid.NewGuid().ToString().Replace("-", "_") + ".xls";
 49                 //string path = "_demo.xls";
 50                 FileInfo fileInfo = new FileInfo(path);
 51                 var excel = new ExcelPackage(fileInfo);
 52 
 53                 var wsPivot = excel.Workbook.Worksheets.Add("Pivot");
 54                 var wsData = excel.Workbook.Worksheets.Add("Data");
 55                 wsData.Cells["A1"].LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Medium6);
 56                 if (table.Rows.Count != 0)
 57                 {
 58                     foreach (DataColumn col in table.Columns)
 59                     {
 60                      
 61                         if (col.DataType == typeof(System.DateTime))
 62                         {
 63                             var colNumber = col.Ordinal + 1;
 64                             var range = wsData.Cells[2, colNumber, table.Rows.Count + 1, colNumber];
 65                             range.Style.Numberformat.Format = "yyyy-MM-dd";
 66                         }
 67                         else
 68                         {
 69 
 70                         }
 71                     }
 72                 }
 73 
 74                 var dataRange = wsData.Cells[wsData.Dimension.Address.ToString()];
 75                 dataRange.AutoFitColumns();
 76                 var pivotTable = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "Pivot");
 77                 pivotTable.MultipleFieldFilters = true;
 78                 pivotTable.RowGrandTotals = true;
 79                 pivotTable.ColumGrandTotals = true;
 80                 pivotTable.Compact = true;
 81                 pivotTable.CompactData = true;
 82                 pivotTable.GridDropZones = false;
 83                 pivotTable.Outline = false;
 84                 pivotTable.OutlineData = false;
 85                 pivotTable.ShowError = true;
 86                 pivotTable.ErrorCaption = "[error]";
 87                 pivotTable.ShowHeaders = true;
 88                 pivotTable.UseAutoFormatting = true;
 89                 pivotTable.ApplyWidthHeightFormats = true;
 90                 pivotTable.ShowDrill = true;
 91                 pivotTable.FirstDataCol = 3;
 92                 //pivotTable.RowHeaderCaption = "行";
 93 
 94                 //row field
 95                 var field004 = pivotTable.Fields["销售客户经理"];
 96                 pivotTable.RowFields.Add(field004);
 97 
 98                 var field001 = pivotTable.Fields["项目简称"];
 99                 pivotTable.RowFields.Add(field001);
100                 //field001.ShowAll = false;
101 
102                 //column field
103                 var field002 = pivotTable.Fields["年"];
104                 pivotTable.ColumnFields.Add(field002);
105                 field002.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending;
106                 var field005 = pivotTable.Fields["月"];
107                 pivotTable.ColumnFields.Add(field005);
108                 field005.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Ascending;
109 
110                 //data field
111                 var field003 = pivotTable.Fields["回款金额"];
112                 field003.Sort = OfficeOpenXml.Table.PivotTable.eSortType.Descending;
113                 pivotTable.DataFields.Add(field003);
114 
115                 pivotTable.RowGrandTotals = false;
116                 pivotTable.ColumGrandTotals = false;
117               
118                 //save file
119                 excel.Save();
120                 //open excel file
121                 string file = @"C:\Windows\explorer.exe";
122                 System.Diagnostics.Process.Start(file, path);
123 
124             }
125             catch (Exception ex)
126             {
127               Response.Write(ex.Message);
128             }
129         }
130     }
131 }

  编译运行,如下图所示:

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

原文链接:http://www.cnblogs.com/isaboy/p/csharp_excel_pivot_table.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏GreenLeaves

八、通用类型系统

1、通用类型简介         CLR中的一切都围绕类型展开,因为IL是面向对象的高级机器语言.类型向应用程序和其他类型公开了功能.通过类型,用一种编程语言写...

31660
来自专栏微服务

关于C#获取动态的时间差函数

   在QQ等空间中,我们常常看到某某条动态发布与多少秒前(32秒前)、1分钟前、1小时前、1天前、1个月前,但是我们对于数据库中存的时间格式为:2016-05...

330110
来自专栏智能大石头

借助Redis做秒杀和限流的思考

最近群里聊起秒杀和限流,我自己没有做过类似应用,但是工作中遇到过更大的数据和并发。 于是提出了一个简单的模型: var count = rds.inc(key)...

1.6K40
来自专栏GreenLeaves

六、CLR下的托管代码应用程序与非托管代码程序之间的性能对比

1、托管程序二次编译的问题,以及微软做的优化 五、CLR加载程序集代码时,JIT编译器对性能的产生的影响中介绍了CLR下托管应用程序的二次编译对性能产生的影响....

27940
来自专栏GreenLeaves

十、元数据概述

前面说过C#源文件经过C#编译器生成一个可执行的exe文件,该可执行的PE文件由以下几个部分组成: (1)、PE32(+)头   Windows要求的标准信息 ...

28970
来自专栏飞扬的花生

集合中随机取不重复的索引

有时候希望从一个集合中随机取n个元素不重复 那么就取到这n个数字的索引 public static int[] GetRandomArray(int Numb...

34380
来自专栏GreenLeaves

九、将cs文件快速的转换成可执行文件和响应文件(配置编译开关的文件)

1、将包含多个类型的源代码文件转换为可以部署的文件。有如下Program.cs的文件,代码如下: public sealed class Program...

33370
来自专栏GreenLeaves

五、CLR加载程序集代码时,JIT编译器对性能的产生的影响

1、CLR首次加载代码造成的性能损失      四、CLR执行程序集中代码介绍了CLR在首次执行一个类的时,会初始化一个内部结构,然后当目标方法被首次调用时,...

32970
来自专栏pangguoming

c#以POST方式模拟提交表单

这是我一年前写的一个用C#模拟以POST方式提交表单的代码,现在记录在下面,以免忘记咯。那时候刚学C#~忽忽。。很生疏。。代码看上去也很幼稚 臃肿不堪 #reg...

41190
来自专栏CodingToDie

百篇(2):c# 学习笔记

C# hello world /** 基本格式 */ using System; using System.Collections.Generic; u...

32440

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励