分享程序员开发的那些事...
更新时间：2022-09-17 14:17:26
简单介绍:
结合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" "
---------------------------------------------------------------
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);
}
}