如何实现把Excel 中的数据导入到SQL Server数据库的指定表中?

leuriat 2006-03-01 10:52:13

我现在有一批Excel文件,需要把它导入到SQL Server 2005的某一个表中去,
现在问题是,怎样把Excel中的数据导进去,用存储过程吗?还是用程序完成?
还有就是,怎样把Excel中的列和SQL Server中的列对应起来了?

...全文

1086 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

liudng 2006-03-25

打赏
举报

try
{
OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + txtRemoteAddress.Text + ";" + "Extended Properties=Excel 8.0;");//HDR=Yes;IMEX=1
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.CommandText = "select * from [目录$]";
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();

OleDbDataReader reader1 = command.ExecuteReader();

while (reader1.Read())
{
i++;
}
reader1.Close();
progressBar1.Minimum = 1;
progressBar1.Maximum = i;
progressBar1.Value = 1;
progressBar1.Step = 1;

OleDbDataReader reader = command.ExecuteReader();

while (reader.Read())
{
decimal mPrice = 1.00M;
int iAmount = 0;
int iLendAmount = 0;
DateTime iInDate = DateTime.Today;

if (reader["单价"] != DBNull.Value)
mPrice = Convert.ToDecimal(reader["单价"]);

Equipment equ = new Equipment(Guid.NewGuid().ToString(),//GUID
"None",//Code
reader["代码"].ToString(),//Number
reader["名称"].ToString(),//Name
reader["每套数量"].ToString(),//Volume
reader["适用版本"].ToString(),// Publish
"None",//Modle
"None",//Type
"None",//Place
"None",//Factory
reader["载体"].ToString(),//Media
mPrice,//Price
iAmount,//Amount
"None",//Unit
iLendAmount,//LendAmount
reader["适用范围"].ToString(),//Grade
reader["学科"].ToString(),//Subject
"None",//State
iInDate,
"None",//OpeMan
"None",//Location
reader["年份"].ToString(),//Issue
"None");
label3.Text = reader["名称"].ToString();
equ.SaveToDB();
progressBar1.PerformStep();

}
}
catch (Exception ex)
{
Equman.Lib.DefValues.ConnString = _Conn;
label2.Text = ex.Message;
return;
}

zlp321002 2006-03-01

打赏
举报

CREATE PROCEDURE readexceldata
@filename varchar(200), --Excel文件名
@exceltablename varchar(20), --EXcel内表名
@tablename varchar(200) --将要存在数据库里的表名
as
declare @sql varchar(1000)
set @sql=' insert into '+@tablename+
' SELECT * '+
' FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source='+@filename+
';User ID=Admin;Password=;Extended properties=Excel 5.0;HDR=yes;imex=1'')...['+@exceltablename+']'
exec(@sql)
go

cdo 2006-03-01

打赏
举报

sql server自带这样的功能的。

ghxwzh 2006-03-01

打赏
举报

用向导也很不错的

glen 2006-03-01

打赏
举报

private void DaoRuCantact()
{

//从文件得数据并导入
//上传的服务器临时目录
string FullPath=Page.Server.MapPath("..\\..\\")+"ExcelFolder\\TempFile\\"+Session.SessionID.ToString()+".Tmp";
FileImport.PostedFile.SaveAs(FullPath);
//从临时目录中连接Excel
OleDbConnection myConn=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FullPath+";Extended Properties=Excel 8.0;");

try
{
myConn.Open();
OleDbDataAdapter thisAdapter= new OleDbDataAdapter("SELECT * FROM [Sheet1$]",myConn);
OleDbCommandBuilder thisBuilder=new OleDbCommandBuilder(thisAdapter);
DataSet thisDataSet=new DataSet();
thisAdapter.Fill(thisDataSet);
DataTable dt1=thisDataSet.Tables[0];
object obj = Session["UserInfo"];
UserInfo userInfoObj = (UserInfo)obj;
CustomerLogic cusLogic = new CustomerLogic();

for(int i=0;i<dt1.Rows.Count;i++)
{
ContactDetailObj newConDetailObj = new ContactDetailObj();
newConDetailObj.First_Name = dt1.Rows[i][0].ToString();
newConDetailObj.Last_Name = dt1.Rows[i][1].ToString();

cusLogic.InsertContactDetail(newConDetailObj);
}
this.RegisterStartupScript("message","<script language='javascript' defer>alert('导入成功！');</script>");
}
catch(OleDbException ex)
{
this.RegisterStartupScript("message","<script language='javascript' defer>alert('导入失败！');</script>");
}
finally
{
myConn.Close();
}

}

心情解码 2006-03-01

打赏
举报

:)

--

andrw 2006-03-01

打赏
举报

用.net(ado)讀取excel,(.net)insert Sqlserver這樣就行了吧!

lovvver 2006-03-01

打赏
举报

--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

===
--将某个目录上的Excel表,导入到数据库中

--将所有的Excel文件放到一个目录中,假设为c:\test\,然后用下面的方法来做

create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree 'c:\test',1,1
declare tb cursor for select fn='c:\test'+fname from #t
where isf=1 and fname like '%.xls' --取.xls文件(EXCEL)
declare @fn varchar(8000)
open tb
fetch next from tb into @fn
while @@fetch_status=0
begin
--下面是查询语句,需要根据你的情况改为插入语句
--插入已有的表用:insert into 表 selct * from ...
--创建表用:select * into 表 from ...
set @fn='select * from
OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='+@fn+''',全部客户$)'
exec(@fn)
fetch next from tb into @fn
end
close tb
deallocate tb
drop table #t

lovvver 2006-03-01

打赏
举报

从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:

--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

lovvver 2006-03-01