C#实现Excel动态生成PivotTable
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，并自动生成透视表，如下图所示：
以上就是本文的全部内容，希望对大家的学习有所帮助
相关推荐
-
C#实现把txt文本数据快速读取到excel中
今天预实现一功能,将txt中的数据转到excel表中,做为matlab的数据源.搜集一些c#操作excel的程序.步骤如下: 下载一个Microsoft.Office.Interop.Excel.dll 在项目中引用. 编写代码如下: string path = "c://date//xyu.txt"; StreamReader sr = new StreamReader(path); string strLine = sr.ReadLine(); int rowNum = 1;
-
C#使用Ado.net读取Excel表的方法
本文实例讲述了C#使用Ado.net读取Excel表的方法.分享给大家供大家参考.具体分析如下: 微软NET提供了一个交互的方法,通过使用ADO.NET与Microsoft Office程序.可以使用内置的OLEDB来访问Excel的XLS表格.下面的例子演示了如何在C#编程读取Excel工作表.需要引用System.Data.OleDb库 using System; using System.Data.OleDb; namespace ConsoleApplication1 { class P
-
C#自定义导出数据到Excel的类实例
本文实例讲述了C#自定义导出数据到Excel的类.分享给大家供大家参考.具体如下: C#自定义Excel操作类,可以用于将DataTable导出到Excel文件,从Excel文件读取数据. using System; using System.IO; using System.Data; using System.Collections; using System.Data.OleDb; using System.Web; using System.Web.UI; using System.We
-
C#实现将数据导出到word或者Excel中的方法
本文实例讲述了C#实现将数据导出到word或者Excel中的方法.分享给大家供大家参考.具体如下: void OutToWord() { if (dataGridView1.Rows.Count >= 1) { string tempstr =""; Stream myStream; SaveFileDialog dlg =new SaveFileDialog(); dlg.Filter = "(Word文件)*.doc|*.doc"; //dlg.Filte
-
C#导出数据到Excel文件的方法
本文实例讲述了C#导出数据到Excel文件的方法.分享给大家供大家参考.具体实现方法如下: /// <summary> /// 导出到Excel类,项目需引用Microsodt.Office.Interop.Excel, /// 类文件需using System.Data与System.Windows.Forms命名空间 /// </summary> public class CToExcel { /// <summary> /// 导出到Excel /// </
-
C#使用oledb操作excel文件的方法
本文实例讲述了C#使用oledb操作excel文件的方法.分享给大家供大家参考.具体分析如下: 不管什么编程语言都会提供操作Excel文件的方式,C#操作Excel主要有以下几种方式: 1.Excel 说明:利用Office 的Excel组件来操作excel文件 优点:能够完全操作Excel文件,生成丰富文件内容 缺点:需要电脑安装Excel,会启动Excel进程这在web上很不方便 2.OpenXML 说明:一个操作字处理文档的组件包括Excel 优点:能够操作操作Excel2007版本文件
-
c#读取excel方法实例分析
本文实例讲述了c#读取excel方法.分享给大家供大家参考.具体分析如下: Provider根据实际EXCEL的版本来设置,推荐使用ACE接口来读取.需要Access database Engine. 注意修改注册表以下两项的值为0.否则导入EXCEL当单元格内字符长度超过255会发生截断现象!!! HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\AccessConnectivity Engine\Engines\Excel\TypeGuess
-
C#定制Excel界面并实现与数据库交互的方法
Excel是微软办公套装软件的一个重要的组成部分,它可以进行各种数据的处理.统计分析和辅助决策操作,广泛地应用于管理.统计财经.金融等众多领域.(另外,Excel还是伦敦一所会展中心的名称)..NET可以创建Excel Add-In对Excel进行功能扩展,这些扩展的功能包括自定义用户函数,自定义UI,与数据库进行数据交互等. 一 主要的Excel开发方式 1 VBA VBA是一种Visual Basic的宏语言,它是最早的Office提供定制化的一种解决方案,VBA是VB的一个子集,和Visu
-
C#读取Excel的三种方式以及比较分析
(1)OleDB方式 优点:将Excel直接当做数据源处理,通过SQL直接读取内容,读取速度较快. 缺点:读取数据方式不够灵活,无法直接读取某一个单元格,只有将整个Sheet页读取出来后(结果为Datatable)再在Datatable中根据行列数来获取指定的值. 当Excel数据量很大时.会非常占用内存,当内存不够时会抛出内存溢出的异常. 读取代码如下: public DataTable GetExcelTableByOleDB(string strExcelPath, string tabl
-
C#实现将DataTable内容输出到Excel表格的方法
本文实例讲述了C#实现将DataTable内容输出到Excel表格的方法.分享给大家供大家参考.具体如下: 1.关于本文 本文描述了一个函数(SaveToExcel),该函数可以将DataTable数据内的数据输出到Excel表格中 2.相关说明 1)本文中使用这个函数将一个DataTable中的内容输出到路径名为addr的目录下: 复制代码 代码如下: public void SaveToExcel(string addr, System.Data.DataTable dt) 2)这个函数需要