这个是用来导入excel数据到sql 表table1。 运行后,没有报错,但是数据库没有数据导入。请大家帮忙看看,哪里有问题啊。
谢谢public DataTable getexceldata(string sexcelf)
{ DataSet ds = new DataSet();
OleDbConnection objCon = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sexcelf + ";" + "Extended Properties=Excel 8.0;");
objCon.Open();
DataTable schemaTab = objCon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTab.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
OleDbCommand objcmd = new OleDbCommand(strSql, objCon);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objCon);
myData.Fill(ds, tableName);
return ds.Tables[0];
} public void ImportDataToSQL(string filepath)
{ int lineno = 0; try { string connectionstr=ConfigurationManager .ConnectionStrings ["DB"].ConnectionString ; SqlConnection conn; conn = new SqlConnection(connectionstr); conn.Open(); DataTable dt=getexceldata (filepath); for (int i=0;i<dt.Rows.Count ;i++) string sqlinsert = "INSERT INTO [table1] ([Fcty],[PdtStyle]) VALUES ('" + dt.Rows[i]["factory"].ToString() + "','" + dt.Rows[i]["style"].ToString() + "')"; SqlCommand cmd=new SqlCommand (sqlinsert ,conn);
cmd.ExecuteNonQuery ();
lineno =i+2;
}
conn .Close ();
Response.Write("<script language=javascript>alert('Data Imported Successfully!')</script>");
}
catch (Exception ex) //捕捉异常
{
Response.Write("<script language=javascript>alert('Data Error:" + "the No." + lineno + " row has data error. " + ex.Message + "')</script>");
}
}
谢谢public DataTable getexceldata(string sexcelf)
{ DataSet ds = new DataSet();
OleDbConnection objCon = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sexcelf + ";" + "Extended Properties=Excel 8.0;");
objCon.Open();
DataTable schemaTab = objCon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTab.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
OleDbCommand objcmd = new OleDbCommand(strSql, objCon);
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objCon);
myData.Fill(ds, tableName);
return ds.Tables[0];
} public void ImportDataToSQL(string filepath)
{ int lineno = 0; try { string connectionstr=ConfigurationManager .ConnectionStrings ["DB"].ConnectionString ; SqlConnection conn; conn = new SqlConnection(connectionstr); conn.Open(); DataTable dt=getexceldata (filepath); for (int i=0;i<dt.Rows.Count ;i++) string sqlinsert = "INSERT INTO [table1] ([Fcty],[PdtStyle]) VALUES ('" + dt.Rows[i]["factory"].ToString() + "','" + dt.Rows[i]["style"].ToString() + "')"; SqlCommand cmd=new SqlCommand (sqlinsert ,conn);
cmd.ExecuteNonQuery ();
lineno =i+2;
}
conn .Close ();
Response.Write("<script language=javascript>alert('Data Imported Successfully!')</script>");
}
catch (Exception ex) //捕捉异常
{
Response.Write("<script language=javascript>alert('Data Error:" + "the No." + lineno + " row has data error. " + ex.Message + "')</script>");
}
}
protected void Button1_Click(object sender, EventArgs e){string saveDir = @"\\machine\temp\";
string appPath = Request.PhysicalApplicationPath;
if (FileUpload1.HasFile)
{
string fileName = Server.HtmlEncode(FileUpload1.FileName);
string fname = "SeasonalData" + DateTime.Now.ToString("yyyyMMddHHmmss") + fileName;
string sname = saveDir + fname;
string extension = System.IO.Path.GetExtension(fileName);
if ((extension == ".xls") | (extension == ".XLS"))
{
string savePath = sname;
FileUpload1.SaveAs(savePath);
ImportDataToSQL(savePath);
}}}
这里是不是少了个括号
string sqlinsert = "INSERT INTO [table1] ([Fcty],[PdtStyle]) VALUES ('" + dt.Rows[i]["factory"].ToString() + "','" + dt.Rows[i]["style"].ToString() + "')"; SqlCommand cmd=new SqlCommand (sqlinsert ,conn);
cmd.ExecuteNonQuery ();
lineno =i+2;
}
我是初学啊,这个代码也是扒别人的。不知道excel第一行字段名,必须要和数据库字段名一样吗?必须要和数据库结构相同吗?(比如说,数据库有8个字段,而我只是导入excel中两个字段)
下面这句有没有问题?string sqlinsert = "INSERT INTO [table1] ([Fcty],[PdtStyle]) VALUES ('" + dt.Rows[i]["factory"].ToString() + "','" + dt.Rows[i]["style"].ToString() + "')";
string sqlinsert = "INSERT INTO [table1] ([Fcty],[PdtStyle]) VALUES ('" + dt.Rows[i]["factory"].ToString() + "','" + dt.Rows[i]["style"].ToString() + "')";在程序这两个位置设置断点,先看看ds 里有没有数据,然后看看下面的sql语句在 sqlserver里能否正常执行
{ int lineno = 0; try { string connectionstr=ConfigurationManager .ConnectionStrings ["DB"].ConnectionString ; SqlConnection conn; conn = new SqlConnection(connectionstr); conn.Open(); DataTable dt=getexceldata (filepath); for (int i=0;i<dt.Rows.Count ;i++) string sqlinsert = "INSERT INTO [table1] ([Fcty],[PdtStyle]) VALUES ('" + dt.Rows[i]["factory"].ToString() + "','" + dt.Rows[i]["style"].ToString() + "')"; SqlCommand cmd=new SqlCommand (sqlinsert ,conn);
cmd.ExecuteNonQuery ();
lineno =i+2;
}
conn .Close ();
Response.Write("<script language=javascript>alert('Data Imported Successfully!')</script>");
}
catch (Exception ex) //捕捉异常
{
Response.Write("<script language=javascript>alert('Data Error:" + "the No." + lineno + " row has data error. " + ex.Message + "')</script>");
}
}