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

Question

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

Answer 1

从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:--如果接受数据导入的表已经存在
insert into 表 select * from 
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

Answer 2

--如果导入数据并生成表
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

Answer 3

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

Answer 4

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

Answer 5

sql server自带这样的功能的。

Answer 6

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

Answer 7

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;
            }