源代码下载
简单介绍:
结合EXT2.2+C#.net+Sql 2005实现将数据导入Excel保存,
并且利用Ext2.2版本最新的功能将数据导出的按钮添加到分页条里面,这个功能在以前需要扩展Ext2.0/2.1版本才能够实现.
(动态获取数据和实现分页).
功能:
1,将数据导出的按钮添加到分页条里
2,数据导入Excel2003/2007
3,动态获取数据和实现分页
html页面源代码
Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_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 id="Head1" runat="server">
<title>实现导入Excel</title>
<link rel="Stylesheet" type="text/css" href="ExtJS/resources/css/ext-all.css" />
<link rel="Stylesheet" type="text/css" href="ExtJS/resources/css/xtheme-purple.css" />
<script type="text/javascript" src="ExtJS/adapter/ext/ext-base.js"></script>
<script type="text/javascript" src="ExtJS/ext-all.js"></script>
<script type="text/javascript" src="ExtJS/ext-lang-zh_CN.js"></script>
<style type="text/css">
.panel_icon { background-image:url(images/first.gif)}
.center_icon { background-image:url(images/center.png)}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<div id="grid_div"></div>
<script type="text/javascript">
//这里主要实现一个从后台获取数据列，然后动态添加到ColumnModel中，再也不用手动配置的方式
function ready()
{
Ext.QuickTips.init();
//声明函数变量
var data;
//动态添加列，这是关键代码
var addColumn = function()
{
this.fields = '';
this.columns = '';
this.addColumns=function(name,caption)
{
if(this.fields.length > 0)
{
this.fields += ',';
}
if(this.columns.length > 0)
{
this.columns += ',';
}
this.fields += '{name:"' + name + '"}';
this.columns += '{header:"' + caption + '",dataIndex:"' + name + '",width:100,sortable:true}';
};
};
//从服务器端获取列，然后动态添加到ColumnModel中
Ext.Ajax.request
({
url:"gridJson.aspx?param=column",
success:function(response,option)
{
if(response.responseText=="")
{
return;
}
data = new addColumn();
var res = Ext.util.JSON.decode(response.responseText);
for(var i=0;i<res.length;i++)
{
for(var p in res[i])
{
data.addColumns(p,p);
}
}
//动态生成GridPanel
makeGrid();
},
failure:function()
{
Ext.Msg.alert("消息","绑定数据出错！");
}
});
//动态生成GridPanel
var makeGrid = function()
{
var cm = new Ext.grid.ColumnModel(eval('([' + data.columns + '])'));
cm.defaultSortable = true;
var fields = eval('([' + data.fields + '])');
var newStore = new Ext.data.Store
({
proxy:new Ext.data.HttpProxy({url:"gridJson.aspx?param=data"}),
reader:new Ext.data.JsonReader({totalProperty:"totalPorperty",root:"root",fields:fields})
});
newStore.load({params:{start:0,limit:9}});
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',
icon:"../images/plugin.gif",
toggleHandler: function(btn, pressed)
{
var title = "数据下载";
//如果是作为uri后面的参数传值，那就需要对字符进行编码
//如果是通过post的方式传数据，就不要做任何的编码
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 = "gridJson.aspx?param=InportExcel" + "&filename=" + escape(title) + "&header=" +header+ "&dataIndex=" +dataIndex;
window.location.href = uri;
}
},
'-'
]
});
var gridPanel = new Ext.grid.GridPanel
({
title:"导出Excel",
cm:cm,
id:"grid_panel",renderTo:"grid_div",
store:newStore,
frame:false,
border:true,
layout:"fit",
pageSize:9,
autoWidth:true,
height:300,
width:600,
viewConfig:{forceFit:true},
bbar:pagingBar
});
};
}
Ext.onReady(ready);
</script>
</div>
</form>
</body>
</html>
后台源代码
Code
using Newtonsoft.Json;
using System.IO;
public partial class Json : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
#region 分页
int pagesize = 20;
int start = 1;
string field, asc_desc;
if (string.IsNullOrEmpty(Request["sort"]))
{
field = "ID";
asc_desc = "desc";
}
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 = Request["Param"];
switch (param)
{
case "data":
Response.Write(Select(field, asc_desc, pagesize, start));
break;
case "column":
Response.Write(GetDataColumn());
break;
case "InportExcel":
InportExcel();
break;
default:
break;
}
}
#region 导出数据到Excel
void InportExcel()
{
string filename = Convert.ToString(Request["filename"]);
if (filename.Trim() == "")
{
filename = "数据下载";
}
string header = Convert.ToString(Request["header"]);
string dataIndex = Convert.ToString(Request["dataIndex"]);
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 = ExtBusiness.GetMoreRow();
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)
{
Response.Write("<script>document.close();</script>");
Response.Clear();
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + context.Server.UrlEncode(String.Format("{0}.xls", filename)) + "\"");
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());
Response.Output.Flush();
Response.End();
}
}
}
catch (Exception ee)
{
string error = ee.Message;
}
return;
}
//处理：遇到比较长的数字字符串，比如身份证号码，就会在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
//获取数据
string Select(string field, string asc_desc, int pagesize, int start)
{
string json = "";
try
{
DataSet ds = ExtUtil.PaginationByTableName(field, asc_desc, pagesize, start, "TypeTable");//获取集合
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
json = ExtUtil.GetJsonString(ds);//获取Json字符串
int count = ExtUtil.GetCountByTableName("TypeTable");//获取行数
json = "{totalPorperty:" + count + ",root:" + json + "}";
}
}
catch (Exception ee)
{
string error = ee.Message;
}
return json;
}
//获取数据列
string GetDataColumn()
{
DataSet ds = ExtBusiness.GetMoreRow();//获取dataset集合
//只要ds不为null，则不管该表是否有数据，都有数据列生成
if (ds != null)
{
List<Hashtable> htList = new List<Hashtable>();
foreach (DataColumn col in ds.Tables[0].Columns)
{
Hashtable ht = new Hashtable();
ht.Add(col.ColumnName, col.ColumnName);
htList.Add(ht);
}
try
{
string json = JavaScriptConvert.SerializeObject(htList);//获取Json字符串
return json;
}
catch (Exception ee)
{
string error = ee.Message;
}
}
return "";
}
}
业务组件层
Code
using System.Data.SqlClient;
/// <summary>
///业务层组件
/// </summary>
public class ExtBusiness
{
/// <summary>
/// 获取集合的操作
/// </summary>
/// <returns></returns>
public static DataSet GetMoreRow()
{
string sql = "select * from TypeTable";
DataSet ds = new DataSet();
try
{
ds = ExtAccess.GetMoreRow(sql);
}
catch (Exception ee)
{
string error = ee.Message;
}
return ds;
}
/// <summary>
/// 根据表名获取数据操作
/// </summary>
/// <param name="tableName">int</param>
/// <returns></returns>
public static DataSet GetMoreRowByTableName(string tableName)
{
string sql = String.Format("select * from {0}", tableName);
DataSet ds = new DataSet();
try
{
ds = ExtAccess.GetMoreRow(sql);
}
catch (Exception ee)
{
string error = ee.Message;
}
return ds;
}
/// <summary>
/// 根据Sql语句获取数据操作
/// </summary>
/// <param name="sql">int</param>
/// <returns></returns>
public static DataSet GetMoreRowBySql(string cmdText)
{
string sql = String.Format("select * from ({0}) a ", cmdText);
DataSet ds = new DataSet();
try
{
ds = ExtAccess.GetMoreRow(sql);
}
catch (Exception ee)
{
string error = ee.Message;
}
return ds;
}
/// <summary>
/// 获取图片类别的集合的操作
/// </summary>
/// <param name="typeID">int</param>
/// <returns></returns>
public static DataSet GetMoreRow(string typeID)
{
string sql = "select * from TypeTable where PID = @TypeID";
SqlParameter[] prams =
{
new SqlParameter("@TypeID",typeID)
};
DataSet ds = new DataSet();
try
{
ds = ExtAccess.GetMoreRow(sql, prams);
}
catch (Exception ee)
{
string error = ee.Message;
}
return ds;
}
/// <summary>
/// 获取图片类别的集合的操作
/// </summary>
/// <param name="typeID">int</param>
/// <returns></returns>
public static DataSet GetAllRow(string typeID)
{
string sql = string.Format("select * from TypeTable where PID = '{0}'", typeID);
DataSet ds = new DataSet();
try
{
ds = ExtAccess.GetMoreRow(sql);
}
catch (Exception ee)
{
string error = ee.Message;
}
return ds;
}
public static bool HasChildNode(string id)
{
string sql = "select ID from TypeTable where PID = @TypeID";
SqlParameter[] prams =
{
new SqlParameter("@TypeID",id)
};
bool flag = true;
try
{
int ID = Convert.ToInt32(ExtAccess.GetOneItem(sql, prams));
if (ID > 0)
{
flag = false;
}
}
catch (Exception ee)
{
string error = ee.Message;
}
return flag;
}
}
数据组件层
Code
using System.Data.SqlClient;
/// <summary>
/// 数据访问组件层
/// </summary>
public class ExtAccess
{
/// <summary>
/// 连接字符串
/// </summary>
static string dataBaseString = ConfigurationManager.ConnectionStrings["SampleAccess"].ConnectionString;
static string dd = dataBaseString;
/// <summary>
/// 创建命令
/// </summary>
/// <param name="conn"></param>
/// <param name="cmd"></param>
/// <param name="cmdText"></param>
/// <param name="prams"></param>
private static void CreateCommand(SqlConnection conn, SqlCommand cmd, string cmdText, params SqlParameter[] prams)
{
conn.ConnectionString = dataBaseString;
if (conn.State == ConnectionState.Closed)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (cmdText.Contains(" "))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (prams != null)
{
foreach (SqlParameter p in prams)
cmd.Parameters.Add(p);
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static int Add(string cmdText)
{
return Add(cmdText, null);
}
public static int Add(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
int o = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return o;
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static int Edit(string cmdText)
{
return Edit(cmdText, null);
}
public static int Edit(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
int o = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return o;
}
}
/// <summary>
/// 删除一条数据
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static int DeleteOneRow(string cmdText)
{
return DeleteOneRow(cmdText, null);
}
public static int DeleteOneRow(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
int o = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return o;
}
}
/// <summary>
/// 删除多条数据
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static int DeleteMoreRow(string cmdText)
{
return DeleteMoreRow(cmdText, null);
}
public static int DeleteMoreRow(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
int o = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return o;
}
}
/// <summary>
/// 获取一行数据
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static DataRow GetOneRow(string cmdText)
{
return GetOneRow(cmdText, null);
}
public static DataRow GetOneRow(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
DataRow row = new DataTable().NewRow();
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
foreach (DataColumn col in ds.Tables[0].Columns)
{
row[col.ColumnName] = Convert.ToString(ds.Tables[0].Rows[0]["col.ColumnName"]);
}
}
return row;
}
}
/// <summary>
/// 获取多行数据
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static DataSet GetMoreRow(string cmdText)
{
return GetMoreRow(cmdText, null);
}
public static DataSet GetMoreRow(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// 得到一项数据
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static object GetOneItem(string cmdText)
{
return GetOneItem(cmdText, null);
}
public static object GetOneItem(string cmdText, params SqlParameter[] prams)
{
using (SqlConnection conn = new SqlConnection())
{
SqlCommand cmd = new SqlCommand();
CreateCommand(conn, cmd, cmdText, prams);
object o = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return o;
}
}
/// <summary>
/// 得到总数
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
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 value = 0;
object o = cmd.ExecuteScalar();
if (o == null)
value = 0;
else
value = Convert.ToInt32(o);
cmd.Parameters.Clear();
return value;
}
}
}
公共组件层
Code
using Newtonsoft.Json;
using System.Collections;
using System.Collections.Generic;
/// <summary>
///ExtUtil 的摘要说明
/// </summary>
public class ExtUtil
{
/// <summary>
/// 获取JsonString
/// </summary>
/// <param name="ds">DataSet</param>
/// <returns></returns>
public static string GetJsonString(DataSet ds)
{
string res = ""; ;
IList<Hashtable> mList = new List<Hashtable>();
try
{
foreach (DataRow row in ds.Tables[0].Rows)
{
Hashtable ht = new Hashtable();
foreach (DataColumn col in ds.Tables[0].Columns)
{
ht.Add(col.ColumnName, row[col.ColumnName]);
}
mList.Add(ht);
}
res = JavaScriptConvert.SerializeObject(mList);
}
catch (Exception ee)
{
string error = ee.Message;
}
return res;
}
/// <summary>
/// 根据SQL语句分页
/// </summary>
/// <param name="field">排序字段</param>
/// <param name="asc_desc">排序方向</param>
/// <param name="pagesize">页大小</param>
/// <param name="start">页索引</param>
/// <param name="sqlText">表名或者完整语句</param>
/// <returns></returns>
public static DataSet PaginationBySQL(string field, string asc_desc, int pagesize, int start, string sqlText)
{
string sql = String.Format("WITH Data_DataSet AS ( " +
" SELECT ROW_NUMBER() OVER " +
" (ORDER BY " + field + " " + asc_desc + " ) AS Row," +
" * FROM ( " + sqlText + " ) aa )" +
" SELECT * FROM Data_DataSet " +
" WHERE Row between ({0}-1)* {1}+1 and {0}*{1}", start, pagesize);
return ExtAccess.GetMoreRow(sql);
}
/// <summary>
/// 根据表名分页
/// </summary>
/// <param name="field">排序字段</param>
/// <param name="asc_desc">排序方向</param>
/// <param name="pagesize">页大小</param>
/// <param name="start">页索引</param>
/// <param name="sqlText">表名或者完整语句</param>
/// <returns></returns>
public static DataSet PaginationByTableName(string field, string asc_desc, int pagesize, int start, string tableName)
{
string sql = String.Format("WITH MOVIES AS ( " +
" SELECT ROW_NUMBER() OVER " +
" (ORDER BY " + field + " " + asc_desc + " ) AS Row," +
" *" +
" FROM " + tableName + " )" +
" SELECT *" +
" FROM MOVIES " +
" WHERE Row between ({0}-1)* {1}+1 and {0}*{1}", start, pagesize);
return ExtAccess.GetMoreRow(sql);
}
/// <summary>
/// 根据表名获取总数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetCountByTableName(string tableName)
{
return ExtAccess.GetCount(String.Format("select Count(*) from {0} ", tableName));
}
/// <summary>
/// 默认根据表名获取总数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetCount(string tableName)
{
return ExtAccess.GetCount(String.Format("select Count(*) from {0} datatable", tableName));
}
/// <summary>
/// 根据Sql语句获取总数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetCountBySql(string sql)
{
return ExtAccess.GetCount(sql);
}
/// <summary>
/// 根据Sql语句获取总数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetCountNumberBySql(string tableName)
{
return ExtAccess.GetCount(String.Format("select Count(*) from ({0}) datatable", tableName));
}
}
源代码下载