簡單介紹:
結合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
SET QUOTED_IDENTIFIER ON
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);
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)
int? count;
count = Convert.ToInt32( cmd.ExecuteScalar() );
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);
}
本文轉自溫景良部落格園部落格，原文連結：http://www.cnblogs.com/wenjl520/archive/2008/11/03/1325567.html，如需轉載請自行聯系原作者