asp.net2.0将EXCEL导入到MS Sql server2000

最新推荐文章于 2025-12-19 17:46:17 发布

转载最新推荐文章于 2025-12-19 17:46:17 发布 · 760 阅读

文章标签：

#excel #sql server #asp.net #dataset #properties #datagrid

asp.net 专栏收录该内容

135 篇文章

订阅专栏

假设你的Excel文件的存放路径为"D:/2.xls",表名为1,那么可以这么写代码;
OleDbConnection cn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=D://2.xls;Extend Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from [1$]"cn);
DataSet ds= new DataSet();
cn.Open();
da.Fill(ds,"newtabel");
da.Dispose();
cn.Close();
定义sql连接sqlcn,命令sqlcmd,代码我就不写了
sqlcn.Open()
for(int i=0;i<ds.Tabels["newtabel"].Rows.Count;i++)
{
cmd=new SqlCommand("insert into monthdata (目标字段,自己写)values('"+ds.Tabels["newtabel"].Rows[i][0]+"'.......)",sqlcn);//这里注意对应关系,以及数据类型就可以了
sqlcmd.ExecuteNoQuery();
}
sqlcmd.Dispose();
sqlcn.Close();

以下仅供参考
   string strConn;
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D://2.xls;Extended Properties=Excel 8.0";
   OleDbConnection conn = new OleDbConnection(strConn);
   OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [1$]", strConn);
   DataSet ds = new DataSet();
   myCommand.Fill(ds,"newtabel");
   DataGrid1.DataBind();
   SqlConnection sqlcn=DB.createCon();
            sqlcn.Open();
             for(int i=0;i<ds.Tables ["newtabel"].Rows.Count;i++)
            {
          SqlCommand MyAdd=new SqlCommand("insert into monthdata (DateTypeID,DataTitle,DataName,DanWei,MonthTol,LastMonthTol,Amount,BFB,DataMonth)values('"+ds.Tables ["newtabel"].Rows [i][0]+"','"+ds.Tables["newtabel"].Rows[i][1]+"','"+ds.Tables["newtabel"].Rows[i][2]+"','"+ds.Tables["newtabel"].Rows[i][3]+"','"+ds.Tables["newtabel"].Rows[i][4]+"','"+ds.Tables["newtabel"].Rows[i][5]+"','"+ds.Tables["newtabel"].Rows[i][6]+"','"+ds.Tables["newtabel"].Rows[i][7]+"','"+ds.Tables["newtabel"].Rows[i][8]+"')",sqlcn);//这里注意对应关系,以及数据类型就可以了
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][0].ToString (),SqlDbType.VarChar,20));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][1].ToString (),SqlDbType.VarChar,50));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][2].ToString (),SqlDbType.VarChar,20));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][3].ToString (),SqlDbType.VarChar,20));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][4].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][5].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][6].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][7].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][8].ToString (),SqlDbType.VarChar,20));
     MyAdd.ExecuteNonQuery ();
            }