完整示例:
ImportDetail.aspx代码如下 :
<%
@ Page Language
=
"
C#
"
AutoEventWireup
=
"
true
"
Codebehind
=
"
ImportDetail.aspx.cs
"
Inherits
=
"
ExcelImportDetail._Default
"
%>
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
<!
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
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
...
{
public partial class _Default : System.Web.UI.Page
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
private readonly string AddressBookConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AddressBookConnectionString"].ToString();
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
protected void Page_Load(object sender, EventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
if (!IsPostBack)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
InsertData();
GridView2.DataSource = GetDataSet();
GridView2.DataBind();
}
}
private DataSet CreateDataSet()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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++)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Tempdataset.Tables[0].Rows[i].SetAdded();
}
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
// test builder commandtext
string tmp = builder.GetUpdateCommand().CommandText;
int Rows = Adapter.Update(Tempdataset);
if (Rows > 0)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
ClientScript.RegisterStartupScript(this.GetType(), "Import Message"," alert("" + Rows.ToString() + " Rows Import The Database !")",true);
}
else
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
ClientScript.RegisterStartupScript(this.GetType(), "Import Message", " alert("Import Data Failuer !")", true);
}
}
catch (Exception ex)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
throw new Exception("Exception", ex);
}
}
private DataSet GetDataSet()
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
DataSet ds = new DataSet();
String cmdtext = " Select * FROM part1_detail ";
SqlConnection Conn = new SqlConnection(AddressBookConnString);
SqlDataAdapter Adapter = new SqlDataAdapter(cmdtext, Conn);
try
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
Adapter.Fill(ds);
}
catch (Exception ex)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
throw new Exception("Exception", ex);
}
return ds;
}
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
protected void GridView2_PageIndexChanging(object sender, GridViewPageEventArgs e)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
...{
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
>
数据库创建如下:
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]