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://HdhCmsTestw3.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测试数据pact = true ;
pivottable测试数据pactdata = 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，并自动生成透视表，如下图所示：
以上就是本文的全部内容，希望对大家的学习有所帮助
dy("nrwz");
查看更多关于C#实现Excel动态生成PivotTable的详细内容...