我编了一段上传EXCEL文件,然后EXCEL中的数据导入SQL Server中的代码(C#.NET),但是这只能针对一张EXCEL表,如果是多张EXCEL表,改怎么办呢?它的字段是不确定的啊,谢谢各位大侠了,帮帮忙!
protected void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Standard"].ConnectionString);
con.Open();
DataTable dt = getExcelDate();
for (int i = 0; i < dt.Rows.Count; i++)
{
string sqlstr = "insert into table3 values ('" + dt.Rows[i]["DB_PART_NO"].ToString() + "','" + dt.Rows[i]["OS_PART_NAME"].ToString() + "','" + dt.Rows[i]["s"].ToString() + "','" + dt.Rows[i]["k"].ToString() + "','" + dt.Rows[i]["r"].ToString() + "','" + dt.Rows[i]["d"].ToString() + "','" + dt.Rows[i]["l"].ToString() + "','" + dt.Rows[i]["b"].ToString() + "','" + dt.Rows[i]["p"].ToString() + "') ";
SqlCommand cmd1 = new SqlCommand(sqlstr, con);
cmd1.ExecuteNonQuery();
con.Close();
}
//上传Excel文件
public DataTable getExcelDate()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Standard"].ConnectionString);
con.Open();
string file_name = FileUpload1.FileName.ToString();//获取上传文件名
string file_fullname = Server.MapPath("NX")+"\\"+file_name;//获取上传文件的路径
string sql = "select * from OPENROWSET ('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;Database="+file_fullname+";HDR=YES;IMEX=1',sheet1$)";//读取Excel中的数据 SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
con.Close();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Standard"].ConnectionString);
con.Open();
DataTable dt = getExcelDate();
for (int i = 0; i < dt.Rows.Count; i++)
{
string sqlstr = "insert into table3 values ('" + dt.Rows[i]["DB_PART_NO"].ToString() + "','" + dt.Rows[i]["OS_PART_NAME"].ToString() + "','" + dt.Rows[i]["s"].ToString() + "','" + dt.Rows[i]["k"].ToString() + "','" + dt.Rows[i]["r"].ToString() + "','" + dt.Rows[i]["d"].ToString() + "','" + dt.Rows[i]["l"].ToString() + "','" + dt.Rows[i]["b"].ToString() + "','" + dt.Rows[i]["p"].ToString() + "') ";
SqlCommand cmd1 = new SqlCommand(sqlstr, con);
cmd1.ExecuteNonQuery();
con.Close();
}
//上传Excel文件
public DataTable getExcelDate()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Standard"].ConnectionString);
con.Open();
string file_name = FileUpload1.FileName.ToString();//获取上传文件名
string file_fullname = Server.MapPath("NX")+"\\"+file_name;//获取上传文件的路径
string sql = "select * from OPENROWSET ('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;Database="+file_fullname+";HDR=YES;IMEX=1',sheet1$)";//读取Excel中的数据 SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
con.Close();
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货