ASP代码中如何把EXCEL数据导入到SQL SERVER数据库中?

'定义打开Excel表格的函数

Function OpenExcel(path)

dim excel,rs,strsql

On Error Resume Next

Set rs = Server.CreateObject("ADODB.RecordSet")

Set excel = Server.CreateObject("ADODB.Connection")

excel.Open "Driver={Microsoft Excel Driver (*.xls)}DBQ=" &path

If Err.number<>0 Then

Response.Write "请检查上传的Excel文件内部格式，文件无法打开，导入失败！"

Response.End

End If

strsql = "SELECT * FROM [Sheet1$]" '在这里指定工作薄名称,默认是Sheet1$

Set rs = excel.Execute(strsql)

Set OpenExcel = rs

End Function

'读取文件中的内容

Dim rsInfo

Set rsInfo = Server.CreateObject("ADODB.RecordSet")

Set rsInfo = OpenExcel("E:/a.xls") '这里的文件路径请用Server.Path来获取

'检查读取结果

If rsInfo.State<>1 Then

Response.Write "请检查Excel文件中的工作表命名是否为Sheet1，导入失败！"

Response.End

End If

If rsInfo.EOF And rsInfo.BOF Then

Response.Write "没有找到Excel表中的数据，导入失败！"

Response.End

End If

If IsNull(rsInfo.Fields(0)) or Trim(rsInfo.Fields(0))="" Then

Response.Write "没有找到Excel表中的数据，导入失败！"

Response.End

End If

'这里指定导入数据的列数,列数少了退出

If rsInfo.Fields.Count<7 Then

Response.Write "Excel表中的数据列数不正确，导入失败！"

Response.End

End If

'创建数据库连接

dim dbrs,conn,sql

Set conn = Server.CreateObject("ADODB.Connection")

Set dbrs = Server.CreateObject("ADODB.Recordset")

'注: G_DB_ConnectString是连接数据库的字符串,自己定义

conn.ConnectionString = G_DB_ConnectString

conn.Open '打开数据库连接

'创建临时表

sql = "IF EXISTS (SELECT * FROM sysobjects WHERE xtype='U' and name='tmp_PartRes') "

sql = sql &"BEGIN Drop table tmp_PartRes END "

sql = sql &"Create table tmp_PartRes([ID] int identity(1,1),"

sql = sql &"PartID varchar(100),Brand varchar(100),[Package] varchar(100),"

sql = sql &"BatchNo varchar(100),[Price] varchar(100),[Stock] varchar(100) default('0'),"

sql = sql &"Brief varchar(100),StockFlag int default(1),"

sql = sql &"SuperFlag int default(1),SaleFlag int default(1))"

conn.execute sql

'取表结构 注意: 只取表的结构, 不要数据, 因为我这个是刚创建的临时表, 没有数据,

'如果表中存在数据, 要注意加上条件句, 防止取到数据 如: where ID = -1

sql = "SELECT * FROM tmp_PartRes"

dbrs.CursorLocation = 3 '这一定要设置为3

dbrs.Open sql,conn, 3, 4 '这里的参数必须是3和4

'取到表结构后, 必须要把活动连接及数据库连接关闭,这个很重要, 否则导入速度特慢.

Set dbrs.ActiveConnection = Nothing

conn.close

'提取Excel中的数据, 将excel中的数据放入到数据库表中.

While Not rsInfo.EOF

If Trim(rsInfo.Fields(0))<>"" Then

dbrs.AddNew

dbrs("PartID") = Ucase(Trim(rsInfo.Fields(0)))

dbrs("Brand") = Trim(rsInfo.Fields(1))

dbrs("Package") = Trim(rsInfo.Fields(2))

dbrs("BatchNo") = Trim(rsInfo.Fields(3))

dbrs("Price") = Trim(rsInfo.Fields(4))

If Trim(rsInfo.Fields(5))<>"" Then

dbrs("Stock") = Trim(rsInfo.Fields(5))

Else

dbrs("Stock") = "0"

End If

dbrs("Brief") = Trim(rsInfo.Fields(6))

End If

rsInfo.MoveNext

Wend

'更新记录集到数据库临时表

conn.Open '打开连接

dbrs.ActiveConnection = conn

dbrs.UpdateBatch '批量更新函数

'更新完成后, 关闭连接

dbrs.Close

Set dbrs = Nothing

rsInfo.Close

Set rsInfo = Nothing

private void add()

{

OleDbConnection connread = new OleDbConnection("provider=microsoft.jet.oledb.4.0data source=D:\\Book1.xlsextended properties=excel 8.0")

connread.Open()

OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", connread)

DataSet ds = new DataSet()

da.Fill(ds)

connread.Close()

SqlConnection conn = new SqlConnection("server=.integrated security=truedatabase=demo")

conn.Open()

SqlCommand cmd = new SqlCommand("create table name(name varchar(100),namea varchar(100),nameb varchar(100))", conn)

for (int i = 0i <ds.Tables[0].Rows.Counti++)

{

string p=string.Format("insert into name values('{0}','{1}','{2}')",ds.Tables[0].Rows[i]["name"].ToString().Trim(),ds.Tables[0].Rows[i]["namea"].ToString().Trim(),ds.Tables[0].Rows[i]["nameb"].ToString().Trim())

cmd = new System.Data.SqlClient.SqlCommand(p, conn)

cmd.ExecuteNonQuery()

}

conn.Close()

}

public static DataSet CreateDataSource()

{

OpenFileDialog dialog = new OpenFileDialog()

if (dialog.ShowDialog() != DialogResult.OK)

return null

string filename = dialog.FileName

string strConn

strConn = "Provider=Microsoft.Jet.OLEDB.4.0" +

"Data Source=" + filename +

"Extended Properties=Excel 8.0"

try

{

OleDbConnection conn = new OleDbConnection(strConn)

OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [数据$]", strConn)

DataSet myDataSet = new DataSet()

myCommand.Fill(myDataSet)

return myDataSet

}

catch (Exception ex )

{

MessageBox.Show("打开文件时出错：" + ex.Message)

return null

}

}

//sql 语句说明 "SELECT * FROM [数据$]"，其中 “数据”表示excel中sheet的名称，

如：excel文件中有个叫"Sheet1"的标签页，SQL语句为 SELECT * FROM [Sheet1$]"，excel的列会自动转化成DataTable的列，支持中文，会自动判断数据行。

每次只能打开一个sheet。

有了上面的数据，你再foreach 插入到SQL中去

---