Excel导入SQL数据库中
这是我使用的语句：
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'D:/test1.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
if (myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
string CnnStr = "server=server;uid=IUSR_ZHAO;pwd=33800722;Database=TNet";
SqlConnection conn = new SqlConnection(CnnStr);
conn.Open();
SqlCommand myCmd = null;
for (int i = 0; i < myDs.Tables[0].Rows.Count; i++)
{
strSql = "Insert Into T_Expert(Name,sex,E-Mail,Address,Telephone,JobTitle,age,Company,Specialty)values ('";
strSql+=myDs.Tables[0].Rows[i].ItemArray[0].ToString()+"','";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "','";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "','";
strSql += myDs.Tables[0].Rows[i].ItemArray[3].ToString() + "')";
strSql += myDs.Tables[0].Rows[i].ItemArray[4].ToString() + "','";
strSql += myDs.Tables[0].Rows[i].ItemArray[5].ToString() + "','";
strSql += myDs.Tables[0].Rows[i].ItemArray[6].ToString() + "')";
strSql += myDs.Tables[0].Rows[i].ItemArray[7].ToString() + "','";
strSql += myDs.Tables[0].Rows[i].ItemArray[8].ToString() + "')";
try
{
myCmd = new SqlCommand(strSql,conn);
myCmd.ExecuteNonQuery();
Response.Write("<script>alert('Succeed')</script>");
}
catch
{
Label1.Text="<script>alert('Failed');</script>";
}
}
conn.Close();
conn.Dispose();
}
但是每次都是返回“Failed!”上面的语句有错误吗？
已经解决了，原来是其中的“）”错了。
http://community.csdn.net/Expert/topic/4859/4859373.xml?temp=.7589533
private void BindGrid()
{
CustomerMainData customerMainData = (new CustomerMainSystem()).GetCustomerMain() ;
DataGrid1.DataSource = customerMainData.Table ;
DataGrid1.DataBind() ;
}
private void ImageButton_Save_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
string lstrFileName = loFile.PostedFile.FileName;
string excelpath = Server.MapPath("upfile") + @"\Temp";
if(!Directory.Exists(excelpath))
{
Directory.CreateDirectory(excelpath) ;
}
if(File_Text.PostedFile.ContentLength == 0)
{
Response.Write("<script language = javascript>window.alert('上传文件为空！')</script>") ;
BindGrid() ;
return ;
}
//获得文件名称
if(lstrFileName.ToString() == "")
{
Response.Write("<script language = javascript>alert('请选择上传文件！')</script>") ;
BindGrid() ;
return ;
}
//上传文件是否大于50MB
// if(File_Text.PostedFile.ContentLength > 50000000)
// {
// Response.Write(@"<script language=javascript>");
// Response.Write(@"alert('上传文件太大，请与管理员联系！');");
// Response.Write(@"</script>");
// BindGrid() ;
// return;
// }
if ((!File_Text.PostedFile.FileName.ToLower().EndsWith(".xls")))
{ //Test UpFile is Excel
Response.Write("<script language = JavaScript>window.alert('请上传.xls格式的文件！')</script>");
return;
}
string filetext = File_Text.PostedFile.FileName ;
File_Text.PostedFile.SaveAs(filetext) ;
OleDbConnection excelconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + excelpath + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""") ;
string excelcomm = "select * from [sheet1$] " ;
OleDbDataAdapter da = new OleDbDataAdapter(excelcomm, excelconn) ;
CustomerMainData customerMainData = new CustomerMainData() ;
da.Fill(customerMainData) ;
//Binding DataGrid
DataGrid1.DataSource = customerMainData.Table ;
DataGrid1.DataBind() ;
for(int i = 1; i < customerMainData.Rows.Count; i++)
{
object customername = DBNull.Value ;
object tracingcustomerid = DBNull.Value ;
object enterpriseclass = DBNull.Value ;
object subjection = DBNull.Value ;
object groupstructure = DBNull.Value ;
object localstructure = DBNull.Value ;
object region = DBNull.Value ;
object size = DBNull.Value ;
object layout = DBNull.Value ;
object eaprocess = DBNull.Value ;
object inforstatus = DBNull.Value ;
object remark = DBNull.Value ;
customername = customerMainData.Rows[i][CustomerMainData.CUSTOMERMAINNAME_FIELD].ToString() ;
tracingcustomerid = Functions.ToInt32(customerMainData.Rows[i][CustomerMainData.TRACINGCUSTOMERID_FIELD]) ;
enterpriseclass = Functions.ToInt32(customerMainData.Rows[i][CustomerMainData.ENTERPRISECLASS_FIELD]) ;
subjection = customerMainData.Rows[i][CustomerMainData.SUBJECTION_FIELD] ;
groupstructure = customerMainData.Rows[i][CustomerMainData.GROUPSTRUCTURE_FIELD] ;
localstructure = customerMainData.Rows[i][CustomerMainData.LOCALSTRUCTURE_FIELD] ;
region = Functions.ToInt32(customerMainData.Rows[i][CustomerMainData.REGION_FIELD]) ;
//string size = customerMainData.Rows[i][CustomerMainData.s]
layout = customerMainData.Rows[i][CustomerMainData.LAYOUT_FIELD] ;
eaprocess = customerMainData.Rows[i][CustomerMainData.EAPROCESS_FIELD] ;
inforstatus = customerMainData.Rows[i][CustomerMainData.INFORSTATUS_FIELD] ;
remark = customerMainData.Rows[i][CustomerMainData.REMARK_FIELD] ;
//(new CustomerMainSystem()).InsertCustomerMain(ref i, customername, tracingcustomerid,enterpriseclass,subjection, groupstructure, localstructure, region, size , layout, eaprocess, inforstatus, remark) ;
}
//After Bind Delete File And Folder
if(File.Exists("Temp"))
{
Directory.Delete(excelpath, true) ;
}
http://www.chinaaspx.com/Comm/Dotnetbbs/Showtopic.aspx?Forum_ID=5&Id=107247
以上转载，我还没验证，等我验证后，再更新之
