vs2005中Excel 数据倒入SQL数据库_vs 2005 excel导入数据库代码示例-优快云博客

本文介绍了一个使用ASP.NET实现Excel数据导入的具体案例，包括页面布局、代码实现及数据库交互过程。示例展示了如何从Excel文件中读取数据并插入到SQL Server数据库中，同时实现了数据分页展示。

完整示例：

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">

<head>

<title>Excel Import </title>

</head>

<body>

</tr>

<td>

<asp:GridView ID="GridView2" runat="server" OnPageIndexChanging="GridView2_PageIndexChanging"

AllowPaging="true">

</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();

}

web.config 如下：

<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]