完整示例:
ImportDetail.aspx代码如下 :
<%
@ Page Language
=
"
C#
"
AutoEventWireup
=
"
true
"
Codebehind
=
"
ImportDetail.aspx.cs
"
Inherits
=
"
ExcelImportDetail._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 >
< title > Excel Import </ title >
</ head >
< body >
< form runat = " server " >
< table width = " 100% " >
< tr style = " height: 100px " >
</ tr >
< tr align = " center " >
< td >
< asp:GridView ID = " GridView2 " runat = " server " OnPageIndexChanging = " GridView2_PageIndexChanging "
AllowPaging = " true " >
< FooterStyle BackColor = " #FFFFCC " ForeColor = " #330099 " />
< RowStyle BackColor = " #fbffff " ForeColor = " #333333 " />
< SelectedRowStyle BackColor = " #FFCC66 " Font - Bold = " True " ForeColor = " #663399 " />
< PagerStyle BackColor = " #FFFFCC " ForeColor = " #330099 " />
< HeaderStyle BackColor = " #5D96d5 " Height = " 20px " Font - Bold = " True " ForeColor = " White " />
</ asp:GridView >
</ td >
</ tr >
< tr >
</ tr >
</ table >
</ form >
</ body >
</ html >
<! 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 > Excel Import </ title >
</ head >
< body >
< form runat = " server " >
< table width = " 100% " >
< tr style = " height: 100px " >
</ tr >
< tr align = " center " >
< td >
< asp:GridView ID = " GridView2 " runat = " server " OnPageIndexChanging = " GridView2_PageIndexChanging "
AllowPaging = " true " >
< FooterStyle BackColor = " #FFFFCC " ForeColor = " #330099 " />
< RowStyle BackColor = " #fbffff " ForeColor = " #333333 " />
< SelectedRowStyle BackColor = " #FFCC66 " Font - Bold = " True " ForeColor = " #663399 " />
< PagerStyle BackColor = " #FFFFCC " ForeColor = " #330099 " />
< HeaderStyle BackColor = " #5D96d5 " Height = " 20px " Font - Bold = " True " ForeColor = " White " />
</ asp:GridView >
</ td >
</ tr >
< tr >
</ tr >
</ table >
</ form >
</ body >
</ html >
ImportDetail.aspx.cs 代码如下:
using
System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ExcelImportDetail
... {
public partial class _Default : System.Web.UI.Page
...{
private readonly string AddressBookConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AddressBookConnectionString"].ToString();
protected void Page_Load(object sender, EventArgs e)
...{
if (!IsPostBack)
...{
InsertData();
GridView2.DataSource = GetDataSet();
GridView2.DataBind();
}
}
private DataSet CreateDataSet()
...{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=d:/CollectedErrors.xls;" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
return myDataSet;
}
private void InsertData()
...{
SqlConnection Conn = new SqlConnection(AddressBookConnString);
SqlDataAdapter Adapter = new SqlDataAdapter("Select * From part1_detail", Conn);
SqlCommandBuilder builder = new SqlCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
DataSet Tempdataset = CreateDataSet();
for (int i = 0; i < Tempdataset.Tables[0].Rows.Count; i++)
...{
Tempdataset.Tables[0].Rows[i].SetAdded();
}
try
...{
// test builder commandtext
string tmp = builder.GetUpdateCommand().CommandText;
int Rows = Adapter.Update(Tempdataset);
if (Rows > 0)
...{
ClientScript.RegisterStartupScript(this.GetType(), "Import Message"," alert("" + Rows.ToString() + " Rows Import The Database !")",true);
}
else
...{
ClientScript.RegisterStartupScript(this.GetType(), "Import Message", " alert("Import Data Failuer !")", true);
}
}
catch (Exception ex)
...{
throw new Exception("Exception", ex);
}
}
private DataSet GetDataSet()
...{
DataSet ds = new DataSet();
String cmdtext = " Select * FROM part1_detail ";
SqlConnection Conn = new SqlConnection(AddressBookConnString);
SqlDataAdapter Adapter = new SqlDataAdapter(cmdtext, Conn);
try
...{
Adapter.Fill(ds);
}
catch (Exception ex)
...{
throw new Exception("Exception", ex);
}
return ds;
}
protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
...{
this.GridView2.PageIndex = e.NewPageIndex;
GridView2.DataSource = this.GetDataSet();
GridView2.DataBind();
}
}
}
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ExcelImportDetail
... {
public partial class _Default : System.Web.UI.Page
...{
private readonly string AddressBookConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AddressBookConnectionString"].ToString();
protected void Page_Load(object sender, EventArgs e)
...{
if (!IsPostBack)
...{
InsertData();
GridView2.DataSource = GetDataSet();
GridView2.DataBind();
}
}
private DataSet CreateDataSet()
...{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=d:/CollectedErrors.xls;" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
return myDataSet;
}
private void InsertData()
...{
SqlConnection Conn = new SqlConnection(AddressBookConnString);
SqlDataAdapter Adapter = new SqlDataAdapter("Select * From part1_detail", Conn);
SqlCommandBuilder builder = new SqlCommandBuilder(Adapter);
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
DataSet Tempdataset = CreateDataSet();
for (int i = 0; i < Tempdataset.Tables[0].Rows.Count; i++)
...{
Tempdataset.Tables[0].Rows[i].SetAdded();
}
try
...{
// test builder commandtext
string tmp = builder.GetUpdateCommand().CommandText;
int Rows = Adapter.Update(Tempdataset);
if (Rows > 0)
...{
ClientScript.RegisterStartupScript(this.GetType(), "Import Message"," alert("" + Rows.ToString() + " Rows Import The Database !")",true);
}
else
...{
ClientScript.RegisterStartupScript(this.GetType(), "Import Message", " alert("Import Data Failuer !")", true);
}
}
catch (Exception ex)
...{
throw new Exception("Exception", ex);
}
}
private DataSet GetDataSet()
...{
DataSet ds = new DataSet();
String cmdtext = " Select * FROM part1_detail ";
SqlConnection Conn = new SqlConnection(AddressBookConnString);
SqlDataAdapter Adapter = new SqlDataAdapter(cmdtext, Conn);
try
...{
Adapter.Fill(ds);
}
catch (Exception ex)
...{
throw new Exception("Exception", ex);
}
return ds;
}
protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
...{
this.GridView2.PageIndex = e.NewPageIndex;
GridView2.DataSource = this.GetDataSet();
GridView2.DataBind();
}
}
}
web.config 如下:
<
connectionStrings
>
< add name = " AddressBookConnectionString " connectionString = " Data Source=C1030SQLEXPRESS;Initial Catalog=Test;Integrated Security=True "
providerName = " System.Data.SqlClient " />
</ connectionStrings >
< add name = " AddressBookConnectionString " connectionString = " Data Source=C1030SQLEXPRESS;Initial Catalog=Test;Integrated Security=True "
providerName = " System.Data.SqlClient " />
</ connectionStrings >
数据库创建如下:
CREATE TABLE [dbo].[part1_detail](
[LIST] [ float ] NOT NULL,
[Tracking_NO] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[DCN] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[Fields_with_Error] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[Error_Explanation] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[UCID] [ float ] NULL,
[DeDupe] [ float ] NULL,
[Validating] [ float ] NULL,
[keystroke_in_error] [ float ] NULL,
[No_Error] [ float ] NULL,
[Web_Entered] [ float ] NULL,
[Total_Keystrokes] [ float ] NULL,
[Batch_NO] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_part1_detail] PRIMARY KEY CLUSTERED
(
[LIST] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
[LIST] [ float ] NOT NULL,
[Tracking_NO] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[DCN] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[Fields_with_Error] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[Error_Explanation] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
[UCID] [ float ] NULL,
[DeDupe] [ float ] NULL,
[Validating] [ float ] NULL,
[keystroke_in_error] [ float ] NULL,
[No_Error] [ float ] NULL,
[Web_Entered] [ float ] NULL,
[Total_Keystrokes] [ float ] NULL,
[Batch_NO] [nvarchar]( 255 ) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_part1_detail] PRIMARY KEY CLUSTERED
(
[LIST] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]