求excel 导入sql 的实例

private void btnLoad_Click(object sender, EventArgs e)

        {

            ds = new DataSet();

            this.openFileDialog.ShowDialog();

            string Path = this.openFileDialog.FileName;

            if (Path == "openFileDialog") return;

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";

            //string sText;

            OleDbConnection conn = new OleDbConnection(strConn);

            string strExcel = "";

            strExcel = "select * from [sheet1$]";

            OleDbCommand cmd = new OleDbCommand(strExcel, conn);

            try

            {

                conn.Open();

                OleDbDataAdapter oda = new OleDbDataAdapter(cmd);

                oda.Fill(ds);

                this.ddvData.DataSource = ds.Tables[0];

                //for (int i = 0; i < this.ddvData.RowCount; i++)

                //{

                //    this.ddvData.Rows[i].ReadOnly = true;

                //}

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            finally

            {

                conn.Close();

            }

            conn.Close();

        }

            if (ds == null) MessageBox.Show("无数据！");

            string table = "testtable";

            string strConn = ConfigurationSettings.AppSettings["SQLConnectString"];

            SqlConnection conn = new SqlConnection(strConn);

            string cCmd = "if exists (select * from sysobjects where id = object_id(N'[dbo].[" + table + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[" + table + "] create table " + table + " (";

            for (int i = 0; i < this.ddvData.ColumnCount; i++)

            {

                cCmd += this.ddvData.Columns[i].HeaderText + " varchar(30),";

            }

            cCmd.Substring(0, cCmd.Length - 1);

            cCmd += ")";

            SqlCommand ccmd = new SqlCommand(cCmd, conn);

            try

            {

                conn.Open();

                ccmd.ExecuteReader();

                conn.Close();

                string strCmd = "insert into " + table + " (";

                for (int i = 0; i < this.ddvData.ColumnCount; i++)

                {

                    strCmd += this.ddvData.Columns[i].HeaderText;

                    if (i != this.ddvData.ColumnCount - 1)

                        strCmd += ",";

                }

                strCmd += ") values (";



                //strCmd.Substring(0, strCmd.Length - 1);



                for (int i = 0; i < this.ddvData.RowCount; i++)

                {

                    string strCs = "";

                    for (int j = 0; j < this.ddvData.ColumnCount; j++)

                    {

                        strCs += "'" + ds.Tables[0].Rows[i][j].ToString() + "'";

                        if (j != this.ddvData.ColumnCount - 1)

                            strCs += ",";

                    }

                    //strCs.Substring(0, strCs.Length - 1);

                    strCs += " )";

                    string scmd = strCmd + strCs;

                    SqlCommand cmd = new SqlCommand(scmd, conn);

                    conn.Open();

                    cmd.ExecuteReader();

                    conn.Close();

                }

                //  if (i > 0)

                MessageBox.Show("success");

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            finally

            {

                conn.Close();

            }

            conn.Close();

create proc p_exporttb 

@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent 

@path nvarchar(1000),   --文件存放目录 

@fname nvarchar(250),   --文件名 

@sheetname varchar(250)=''  --要创建的工作表名,默认为文件名 

as 

declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int 

declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000) 

 

--参数检测 

if isnull(@fname,'')='' set @fname='temp.xls' 

if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#') 

 

--检查文件是否已经存在 

if right(@path,1)<>'' set @path=@path+'' 

create table #tb(a bit,b bit,c bit) 

set @sql=@path+@fname 

insert into #tb exec master..xp_fileexist @sql 

 

--数据库创建语句 

set @sql=@path+@fname 

if exists(select 1 from #tb where a=1) 

 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READ' 

       +';CREATE_DB="'+@sql+'";DBQ='+@sql 

else 

 set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES' 

    +';DATABASE='+@sql+'"' 

 

--连接数据库 

exec @err=sp_oacreate 'adodb.connection',@obj out 

if @err<>0 goto lberr 

 

exec @err=sp_oamethod @obj,'open',null,@constr 

if @err<>0 goto lberr 

 

--创建表的SQL 

declare @tbname sysname 

set @tbname='##tmp_'+convert(varchar(38),newid()) 

set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a' 

exec(@sql) 

 

select @sql='',@fdlist='' 

select @fdlist=@fdlist+',['+a.name+']' 

 ,@sql=@sql+',['+a.name+'] ' 

  +case 

   when b.name like '%char' 

   then case when a.length>255 then 'memo' 

    else 'text('+cast(a.length as varchar)+')' end 

   when b.name like '%int' or b.name='bit' then 'int' 

   when b.name like '%datetime' then 'datetime' 

   when b.name like '%money' then 'money' 

   when b.name like '%text' then 'memo' 

   else b.name end 

FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype 

where b.name not in('image','sql_variant','varbinary','binary','timestamp') 

 and a.id=(select id from tempdb..sysobjects where name=@tbname) 

 

if @@rowcount=0 return 

 

select @sql='create table ['+@sheetname 

 +']('+substring(@sql,2,8000)+')' 

 ,@fdlist=substring(@fdlist,2,8000) 

 

exec @err=sp_oamethod @obj,'execute',@out out,@sql 

if @err<>0 goto lberr 

 

exec @err=sp_oadestroy @obj 

 

--导入数据 

set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES 

   ;DATABASE='+@path+@fname+''',['+@sheetname+'$])' 

 

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']') 

 

set @sql='drop table ['+@tbname+']' 

 

exec(@sql) 

return 

 

 

lberr: 

 exec sp_oageterrorinfo 0,@src out,@desc out 

lbexit: 

 select cast(@err as varbinary(4)) as 错误号 

  ,@src as 错误源,@desc as 错误描述 

 select @sql,@constr,@fdlist 

 

 

 

GO