2012开始此blog不再更新
新blog地址 http://cnblogs.com/vazumi
(关注更多web前端开发技术)
首先是在线测试例子: http://vazumi.net.s1.kingidc.net/example/export.htm
截图:
实现原理:
ligerui是基于jquery来搭建页面,一个grid控件本质上是几个table组合而成
如果可以将这个DOM的架构直接传回后台,自然可以输出为excel或者word或者pdf
为何不在前台Js实现html导出为excel,因为js来创建excel这玩意会碰到浏览器安全性问题,不实用
这个也贴一个方法,一个js搞定
function method1() {//整个表格拷贝到EXCEL中
//检索浏览器
if(navigator.userAgent.indexOf("MSIE")<0){
alert('请用ie浏览器进行表格导出');
return ;
}
var tableid="maingrid";
var curTbl = document.getElementById(tableid);
var oXL = null;
try {
oXL = GetObject("", "Excel.Application");
}
catch (E) {
try {
oXL = new ActiveXObject("Excel.Application");
}
catch (E2) {
alert("Please confirm:\n1.Microsoft Excel has been installed.\n2.Internet Options=>Security=>Setting \"Enable unsafe ActiveX\"");
return;
}
}
//创建AX对象excel
var oWB = oXL.Workbooks.Add();
//获取workbook对象
var oSheet = oWB.ActiveSheet;
//在此进行样式控制
oSheet.Rows(1+":"+1).RowHeight =20;//定义行高
oSheet.Rows(2+":"+2).RowHeight =30;
oSheet.Rows(1).HorizontalAlignment=3;
var sel = document.body.createTextRange(); //激活当前sheet
sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中
sel.select(); //全选TextRange中内容
sel.execCommand("Copy"); //复制TextRange中内容
oSheet.Paste(); //粘贴到活动的EXCEL中
oXL.Visible = true; //设置excel可见属性
oSheet.Application.Quit(); //结束当前进程
window.opener=null;
window.close();//关闭当前窗口
}
(这个方法,只可IE并且要允许不安全的activex运行,这也太危险了,这年头...)
下面采用的导EXCEL方法,适合不翻页的grid,而且无需再读一次数据库,
对于翻页的grid来说,要导全部,当然后台要再读一次数据库,这种导EXCEL方法baidu一大堆,这里不重复
代码:
grid.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title></title>
<link href="../lib/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet" type="text/css" />
<link href="../lib/ligerUI/skins/ligerui-icons.css" rel="stylesheet" type="text/css" />
<script src="../lib/jquery/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="../lib/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="../lib/ligerUI/js/plugins/ligerToolBar.js" type="text/javascript"></script>
<script src="../lib/ligerUI/js/plugins/ligerDialog.js" type="text/javascript"></script>
<script src="AllProductData.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
$("#toptoolbar").ligerToolBar({ items: [
{text: '导出Excel',id:'excel',icon:'print',click:itemclick},
{text: '导出Word' ,id:'word',icon:'print',click:itemclick}
]
});
$("#maingrid").ligerGrid({
columns: [
{ display: '主键', name: 'ProductID', type: 'int', totalSummary:{type: 'count'}},
{ display: '产品名', name: 'ProductName', align: 'left', width: 200 },
{ display: '单价', name: 'UnitPrice', align: 'right', type:'float',totalSummary:{render: function (suminf, column, cell){return '<div>最大值:' + suminf.max + '</div>';},align: 'left'}},
{ display: '仓库数量', name: 'UnitsInStock', align: 'right', type: 'float',totalSummary:{type: 'sum'}}
],
dataAction: 'local',
data: AllProductData, sortName: 'ProductID',
showTitle: false, totalRender: f_totalRender,
width: '100%', height: '100%',heightDiff:-10
});
$("#pageloading").hide();
});
function f_totalRender(data, currentPageData)
{
return "总仓库数量:"+data.UnitsInStockTotal;
}
function itemclick(item)
{
grid = $("#maingrid").ligerGetGridManager();
if(item.id)
{
switch (item.id)
{
case "excel":$.ligerDialog.open({url: "../service/print.aspx?exporttype=xls"});return;
case "word":$.ligerDialog.open({url: "../service/print.aspx?exporttype=doc"});return;
}
}
}
</script>
</head>
<body style="padding:0px; overflow:hidden; height:100% ">
<div id="toptoolbar"></div>
<div id="maingrid" style="margin:0; padding:0"></div>
<div style="display:none;"></div>
</body>
</html>
导出页面print.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="print.aspx.cs" Inherits="example" EnableEventValidation = "false" ValidateRequest="false" %>
<html>
<head>
<title></title>
<link href="../lib/ligerUI/skins/aqua/css/ligerui-all.css" rel="stylesheet" type="text/css" />
<script src="../lib/jquery/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="../lib/ligerUI1.1.0/js/ligerui.min.js" type="text/javascript"></script>
<script type="text/javascript">
function GetQueryString(name)
{
var reg = new RegExp("(^|&)"+name+"=([^&]*)(&|$)");
var r= window.location.search.substr(1).match(reg);
if (r!=null) return unescape(r[2]);return null;
}
function gethtml(g)
{
parent.$(".l-grid-header-table",g).attr("border","1");
parent.$(".l-grid-body-table",g).attr("border","1");
$("#hf").val(
parent.$(".l-grid-header",g).html()+ //这里把表头捞出来
parent.$(".l-grid-body-inner",g).html()+ //表身,具体数据
parent.$(".l-panel-bar-total",g).html()+"<br/>"+ //这是全局汇总,1.1.0版本新添加的
parent.$(".l-bar-text",g).html() //这是翻页讯息
);
parent.$(".l-grid-header-table",g).attr("border","0");
parent.$(".l-grid-body-table",g).attr("border","0");
// parent.$(".l-grid-header-table",g).removeAttr("border");
// parent.$(".l-grid-body-table",g).removeAttr("border");
}
function init()
{
if (GetQueryString("exporttype")=="xls")
{
document.getElementById("btnxls").click();
}
else
{
document.getElementById("btndoc").click();
}
setTimeout(function ()
{
parent.$.ligerDialog.close();
}, 3000);
}
</script>
</head>
<body style="padding:20px" οnlοad="init()">
<form id="form1" runat="server">
导出中...
<div style="visibility:hidden">
<asp:Button ID="btnxls" runat="server" Text="导出Excel" οnclick="Button1_Click" OnClientClick="gethtml('#maingrid')"/>
<asp:Button ID="btndoc" runat="server" Text="导出Word" οnclick="Button2_Click" OnClientClick="gethtml('#maingrid')"/>
</div>
<asp:HiddenField ID="hf" runat="server" />
</form>
</body>
</html>
print.aspx.cs
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
namespace service
{
public partial class print : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
}
void exportexcel()
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=tmp.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
oHtmlTextWriter.WriteLine(hf.Value);
Response.Write(oStringWriter.ToString());
Response.End();
}
void exportword()
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=tmp.doc");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
Response.ContentType = "application/ms-word";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
oHtmlTextWriter.WriteLine(hf.Value);
Response.Write(oStringWriter.ToString());
Response.End();
}
protected void Button1_Click(object sender, EventArgs e)
{
exportexcel();
}
protected void Button2_Click(object sender, EventArgs e)
{
exportword();
}
}
}
代码原理:在点导出按钮的时候,弹一个print.aspx页面,这个页面把grid的html传给自己一个叫hf的hidden里面,然后后台response输出这个html