//从excel导入到数据库
protected void Import_Click(object sender, EventArgs e)
{
string filePath = "";
string getErrMsg = "";
DataSet excelDs = new DataSet();
if (FileUpload1.PostedFile.FileName == "")
{
Response.Write("<script language=javascript>alert('请选择要上传的文件!');</script>");
return;
}
//从Excel读取数据
filePath = FileUpload1.PostedFile.FileName;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=" + filePath;
OleDbConnection excelConn = new OleDbConnection(connString);
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn);
try
{
ExcelDA.Fill(excelDs, "users");
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
excelConn.Close();
excelConn = null;
}
//将数据写入数据库
if (excelDs.Tables["0"].Rows.Count != 0)
{
string sql = "";
string strConn = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString.ToString();
SqlConnection sqlConn = new SqlConnection(strConn);
sqlConn.Open();
SqlCommand myCommand = sqlConn.CreateCommand();
SqlTransaction myTrans = sqlConn.BeginTransaction();
myCommand.Transaction = myTrans;
try
{
for (int i = 0; i < excelDs.Tables[0].Rows.Count; i++)
{
sql = "insert into users(userID, userName, address) values('" + excelDs.Tables[0].Rows[i]["ID"].ToString() + "','" + excelDs.Tables[0].Rows[i]["用户名"].ToString() + "','" + excelDs.Tables[0].Rows[i]["地址"].ToString() + "')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
} myTrans.Commit(); }
catch (Exception ex)
{
getErrMsg = ex.Message.ToString();
Response.Write(ex.Message.ToString());
myTrans.Rollback();
}
finally
{
sqlConn.Close();
sqlConn = null;
}
}
//返回提示信息
if (getErrMsg == "" || getErrMsg == null)
{
Response.Write("<script language='Javascript'>alert('导入成功!')</script>");
return;
}
else
{
Response.Write("<script language='Javascript'>alert('导入失败!')</script>");
return;
}
}
执行到红色脚本的时候报错:异常详细信息: System.IndexOutOfRangeException: 无法找到表 0。
请问是什么原因啊? 谢谢
protected void Import_Click(object sender, EventArgs e)
{
string filePath = "";
string getErrMsg = "";
DataSet excelDs = new DataSet();
if (FileUpload1.PostedFile.FileName == "")
{
Response.Write("<script language=javascript>alert('请选择要上传的文件!');</script>");
return;
}
//从Excel读取数据
filePath = FileUpload1.PostedFile.FileName;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=" + filePath;
OleDbConnection excelConn = new OleDbConnection(connString);
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn);
try
{
ExcelDA.Fill(excelDs, "users");
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
excelConn.Close();
excelConn = null;
}
//将数据写入数据库
if (excelDs.Tables["0"].Rows.Count != 0)
{
string sql = "";
string strConn = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString.ToString();
SqlConnection sqlConn = new SqlConnection(strConn);
sqlConn.Open();
SqlCommand myCommand = sqlConn.CreateCommand();
SqlTransaction myTrans = sqlConn.BeginTransaction();
myCommand.Transaction = myTrans;
try
{
for (int i = 0; i < excelDs.Tables[0].Rows.Count; i++)
{
sql = "insert into users(userID, userName, address) values('" + excelDs.Tables[0].Rows[i]["ID"].ToString() + "','" + excelDs.Tables[0].Rows[i]["用户名"].ToString() + "','" + excelDs.Tables[0].Rows[i]["地址"].ToString() + "')";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
} myTrans.Commit(); }
catch (Exception ex)
{
getErrMsg = ex.Message.ToString();
Response.Write(ex.Message.ToString());
myTrans.Rollback();
}
finally
{
sqlConn.Close();
sqlConn = null;
}
}
//返回提示信息
if (getErrMsg == "" || getErrMsg == null)
{
Response.Write("<script language='Javascript'>alert('导入成功!')</script>");
return;
}
else
{
Response.Write("<script language='Javascript'>alert('导入失败!')</script>");
return;
}
}
执行到红色脚本的时候报错:异常详细信息: System.IndexOutOfRangeException: 无法找到表 0。
请问是什么原因啊? 谢谢
解决方案 »
- 坐等高手求解:ViewState问题
- 转成货币类型的怎么转???
- asp.net 返回的json 中怎么去掉头和尾<string>
- 滚动图片的问题
- 如何读配置文件中的Key?
- DataGrid初始化时默认为只有一行的模板列(均为文本框),当单击按钮时增加新的一行模板列。但当单击按钮时第一行输入的值,会被刷新掉? asp.net(c#)
- Timeout expired问题,每个连接都显式close了,还不行
- asp.net 中 Codebehind 是怎么结合呢? 我将界面与代码开分.就不开兴了.
- 奇怪问题,同命名空间同文件夹下的Class竟然找不到
- 未启用约束
- .net调用存储过程问题
- 因为我不会写正则表达式,所以我推荐【.NET正则表达式库】http://regex-lib.net/
这里加一句
Response.Flush();
看看具体的错误.
然后根据错误找原因.
但是ssss.xls确实是存在的啊