简单介绍:
结合EXT2.2+C#.net+Sql 2005实现将数据导入Excel保存,
并且利用Ext2.2版本最新的功能将数据导出的按钮添加到分页条里面,这个功能在以前需要扩展Ext2.0/2.1版本才能够实现.
(动态获取数据和实现分页).
功能:
1,将数据导出的按钮添加到分页条里
2,数据导入Excel2003/2007
3,动态获取数据和实现分页
----------------------------------------------------------------
环境:
1,EXT2.2
2,C#.net2008
3,Sql server 2005
----------------------------------------------------------------
运行说明:
1,自己建立一个数据库 Test,
2,下面是建立数据表的语句:
USE [Test]
GO
/****** 对象: Table [dbo].[ceshione] 脚本日期: 08/22/2008 13:27:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ceshione](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Author] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Source] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_ceshione] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----------------------------------------------------------------
3,下面是项目文件路径截图
----------------------------------------------------------------
效果截图:
下面是源代码
1,Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="GridPanel_Default" %>
<!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 runat="server">
<title>无标题页</title>
<link rel="stylesheet" type="text/css" href="resources/css/ext-all.css"/>
<script type="text/javascript" src="ExtBase/ext-base.js"></script>
<script type="text/javascript" src="ExtBase/ext-all.js"></script>
<script type="text/javascript" src="ExtBase/ext-lang-zh_CN.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div>
<div id="panel_id"></div>
<script type="text/javascript">
var grid;
function DataGrid()
{
var cm = new Ext.grid.ColumnModel
([
{header: "编号", 120, dataIndex: 'ID', sortable: true},
{header: "标题", 180, dataIndex: 'Title', sortable: true},
{header: "作者", 115, dataIndex: 'Author', sortable: true},
{header: "来源", 100, dataIndex: 'Source', sortable: true}
]);
var titleInfo = "导出数据到Excel";
var fields = [{name:"ID"},{name:"Title"},{name:"Author"},{name:"Source"}];
var newStore = new Ext.data.Store
({
proxy:new Ext.data.HttpProxy({url:"JsonData.aspx?param=initData"}),
reader:new Ext.data.JsonReader({totalProperty:"totalPorperty",root:"result",fields:fields})
});
newStore.load({params:{start:0,limit:16}});
var pagingBar = new Ext.PagingToolbar
({
displayInfo:true,
emptyMsg:"没有数据显示",
displayMsg:"显示从{0}条数据到{1}条数据，共{2}条数据",
store:newStore,
pageSize:16,
items:
[
'-',
{
pressed: true,
enableToggle:true,
text: '导出Excel',
cls: 'x-btn-text-icon details',
toggleHandler: function(btn, pressed)
{
debugger;
var title = titleInfo;
var cols = eval(cm);
var header = "";
var dataIndex = "";
for(var i=0;i<cols.config.length;i++)
{
header += cols.config[i].header+",";
dataIndex += cols.config[i].dataIndex+",";
}
var uri = "JsonData.aspx?param=InportExcel" + "&filename=" + title + "&header=" +header+ "&dataIndex=" +dataIndex;
window.location.href = uri;
}
},
'-'
]
});
this.gridPanel = new Ext.grid.GridPanel
({
cm:cm,
id:"grid_panel",
title:titleInfo,
store:newStore,
frame:false,
border:true,
layout:"fit",
pageSize:16,
autoWidth:true,
height:400,
viewConfig:{forceFit:true},
bbar:pagingBar
});
}
function MakePanel(obj)
{
this.panel_def = new Ext.Panel
({
layout:"fit",
border:true,
frame:true,
title:"数据浏览（数据下载功能---->导出数据到Excel2003/2007）",
autoWidth:true,
height:500,
id:"Viewport_ID",
renderTo:"panel_id",
items:[obj.gridPanel]
});
}
function loader()
{
Ext.QuickTips.init();
grid = new DataGrid();
MakePanel(grid);
}
Ext.onReady(loader);
</script>
</div>
</form>
</body>
</html>
---------------------------------------------------------------
2,JsonData.aspx.cs
using Newtonsoft.Json;
public partial class GridPanel_JsonData : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
#region 分页参数
int pagesize = 5;
int start = 1;
string field, asc_desc;
if (string.IsNullOrEmpty(Request["sort"]))
{
field = "ID";
asc_desc = "ASC";
}
else
{
field = Request["sort"];
asc_desc = Request["dir"];
}
if (!string.IsNullOrEmpty(Request["limit"]))
{
pagesize = int.Parse(Request["limit"]);
start = int.Parse(Request["start"]);
}
start = start / pagesize;
start += 1;
#endregion
string param = Convert.ToString(Request["param"]);
if (param == "initData")
{
Bind_Data(field, asc_desc, pagesize, start, "ceshione");
}
if (param == "InportExcel")
{
string filename = Convert.ToString(Request["filename"]);
if (filename.Trim() == "")
{
filename = "数据下载";
}
object header = Convert.ToString(Request["header"]);
object dataIndex = Convert.ToString(Request["dataIndex"]);
InportExcel(filename,header,dataIndex);
}
}
private void Bind_Data(string field, string asc_desc, int pagesize, int start, string tableName)
{
DataSet ds = Business.GetPagingData(field, asc_desc, pagesize, start, tableName);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
GetJsonData(ds);
}
else
{
Response.Write("");
}
}
private void GetJsonData(DataSet ds)
{
List<Hashtable> hashList = new List<Hashtable>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow row = ds.Tables[0].Rows[i] as DataRow;
Hashtable ht = new Hashtable();
foreach (DataColumn col in ds.Tables[0].Columns)
{
ht.Add(col.ColumnName, row[col.ColumnName]);
}
hashList.Add(ht);
}
int? count = Access.GetCount("Select count(*) from ceshione");
string json = "{totalPorperty:" + count + ",result:" + JavaScriptConvert.SerializeObject(hashList) + "}";
Response.Write(json);
}
private void InportExcel(string filename, object header_, object dataIndex_)
{
string header = header_ as string;
string dataIndex = dataIndex_ as string;
if (header.Trim() == "" || dataIndex.Trim() == "")
{
return;
}
string[] cName = header.Substring(0, header.LastIndexOf(",")).Split(',');
string[] eName = dataIndex.Substring(0, dataIndex.LastIndexOf(",")).Split(',');
GridView gridViewAll = new GridView();//用于从数据库中将所有相关数据导入Excel中
System.Web.HttpContext context = System.Web.HttpContext.Current;
try
{
DataSet ds = Access.GetDataSet("Select * from ceshione") as DataSet;
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < cName.Length; i++)
{
BoundField bf = new BoundField();
bf.DataField = Convert.ToString(eName[i]);
bf.HeaderText = Convert.ToString(cName[i]);
gridViewAll.Columns.Add(bf);
}
gridViewAll.RowDataBound += new GridViewRowEventHandler(gridViewAll_RowDataBound);
gridViewAll.AutoGenerateColumns = false;
gridViewAll.DataSource = ds;
gridViewAll.DataBind();
if (gridViewAll.Rows.Count > 0)
{
context.Response.Write("<script>document.close();</script>");
context.Response.Clear();
context.Response.Charset = "GB2312";
context.Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + context.Server.UrlEncode(String.Format("{0}.xls", filename)) + "\"");
context.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gridViewAll.RenderControl(htw);
StringWriter sw2 = new StringWriter();
sw2 = sw;
gridViewAll.Dispose();
Response.Output.Write(sw.ToString());
}
}
}
catch (Exception ee)
{
string error = ee.Message;
}
return ;
}
#region 导出数据
//处理：遇到比较长的数字字符串，比如身份证号码，就会在Excel里当成数字看待，
//并转换成科学计数法的格式，造成数据的丢失,下面的方法就可以有效解决这个问题
protected void gridViewAll_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
for (int i = 0; i < e.Row.Cells.Count; i++)
{
e.Row.Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
}
#endregion
}
3,数据库组件层
public class Access
{
public Access()
{ }
static string connstring = ConfigurationManager.AppSettings["connectionString_yin_test"];
private static void CreateCommand(SqlConnection conn, SqlCommand cmd, string cmdText, params SqlParameter[] prams)
{
conn.ConnectionString = connstring;
if (conn.State == ConnectionState.Closed)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (prams != null)
{
foreach (SqlParameter p in prams)
cmd.Parameters.Add(p);
}
}
public static DataSet GetDataSet(string cmdText)
{
return GetDataSet(cmdText,null);
}
public static DataSet GetDataSet(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
public static int? GetCount(string cmdText)
{
return GetCount(cmdText, null);
}
public static int? GetCount(string cmdText,params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
int? count;
count = Convert.ToInt32( cmd.ExecuteScalar() );
cmd.Parameters.Clear();
return count;
}
}
}
4,分页代码
public class Business
{
public static DataSet GetPagingData(string field, string asc_desc, int pagesize, int start,string tableName)
{
string sql = "WITH MOVIES AS ( " +
" SELECT ROW_NUMBER() OVER " +
" (ORDER BY " + field + " " + asc_desc + " ) AS Row," +
" *" +
" FROM " + tableName + " )" +
" SELECT *" +
" FROM MOVIES " +
" WHERE Row between (@start-1)* @pagesize+1 and @start*@pagesize";
SqlParameter[] prams =
{
new SqlParameter("@start",start),
new SqlParameter("@pagesize",pagesize)
};
return Access.GetDataSet(sql, prams);
}
}