asp.net里数据导入出现Microsoft Jet 数据库引擎找不到对象'Sheet1$'如何解决
protected void BtnExcel_Click(object sender, EventArgs e){
if (this.FileUpload1.HasFile == false)
{
Alert.alert("请先上传文件");
return;
}
string xls = System.IO.Path.GetExtension(this.FileUpload1.FileName).ToString().ToLower();
if (xls == ".xls" || xls == ".xlsx")
{
}
else
{
Alert.alert("请先上传Excel文件，格式为.xls,或者 .xlsx");
return;
}
string FileName = DateTime.Now.ToString("yyyMMddhhmmss") + ".xls";
this.FileUpload1.PostedFile.SaveAs(Server.MapPath("xls/") + FileName);
DataSet ds = GetExcelData(); //从Excel获取数据
if (ds == null)
{
Alert.alert("该Excel的工作簿必须是Sheet1");
return;
}
int Rows = ds.Tables.Rows.Count; //Sheet1中的数据行数
int Columns = ds.Tables.Columns.Count; //Sheet1中的数据列数
File.Delete(Server.MapPath("xls/") + FileName); //文件读取到DataSet后删除上传文件
if (Rows == 0)
{
Alert.alert("该Excel没有任何记录,请重新上传");
return;
}
if (Columns != 8)
{
this.lbTag.Text = "错误信息，Excel字段与数据库不匹配";
return;
}
//检查数据列是否符合条件，否则退出
try
{
if (ds.Tables.Rows["编号"].ToString().Trim() != null &&
ds.Tables.Rows["名称"].ToString().Trim() != null &&
ds.Tables.Rows["作者"].ToString().Trim() != null &&
ds.Tables.Rows["出版社"].ToString().Trim() != null &&
ds.Tables.Rows["价格"].ToString().Trim() != null &&
ds.Tables.Rows["状态"].ToString().Trim() != null
)
{
}
}
catch (Exception ex)
{
this.lbTag.Text = "错误信息：Excel字段与数据库字段不匹配，应该为“ 编号、名称、作者、出版社、价格、读者、读者类型、状态";
return;
}
//验证数据过程
for (int i = 0; i < Rows; i++)
{
//验证必填项
if (FixBad.Fix(ds.Tables.Rows["编号"].ToString().Trim()).Length == 0)
{
Alert.alert("数据行：" + (i + 1).ToString() + "，编号不能为空");
return;
}
//验证必填项
if (FixBad.Fix(ds.Tables.Rows["名称"].ToString().Trim()).Length == 0)
{
Alert.alert("数据行：" + (i + 1).ToString() + "，名称不能为空");
return;
}
//验证必填项
if (FixBad.Fix(ds.Tables.Rows["价格"].ToString().Trim()).Length == 0)
{
Alert.alert("数据行：" + (i + 1).ToString() + "，价格不能为空");
return;
}
}
InsertDB(ds);
}
/// <summary>
/// 该方法实现从Excel中导出数据到DataSet中，其中filepath为Excel文件的绝对路径，sheetname为表示那个Excel表,此用Sheet1；
/// </summary>
/// <param name="ds">ds</param>
private void InsertDB(DataSet ds)
{
SqlConnection _con = new SqlConnection(PubConstant.ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = _con;
StringBuilder sb = new StringBuilder();
if (ds.Tables.Rows.Count > 0)
{
for (int i = 0; i < ds.Tables.Rows.Count; i++)
{
//此处可以添加判断条件及类别的查询及增加
sb.Append(" INSERT INTO T_BookInfo(bookID,bookName,author,publisher,price,readerID,readerType,bookStatus) VALUES('");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "','");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "','");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "','");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "','");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "','");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "','");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "','");
sb.Append(ds.Tables.Rows.ItemArray.ToString() + "' ) ");
cmd.CommandText = sb.ToString();
}
}
_con.Open();
int j = cmd.ExecuteNonQuery();
_con.Close();
if (j > 0)
{
lbTag.Text = "Insert into DB table Sucessfully!";
}
}
/// <summary>
/// get data source from excel file
/// </summary>
/// <returns>dataset ds</returns>
private DataSet GetExcelData()
{
DataSet ds = new DataSet();
string filePath = FileUpload1.PostedFile.FileName;
string connStr03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"; ;
string connStr07 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES'";
string queryStr = "select * from ";
OleDbConnection conn03 = new OleDbConnection(connStr03);
OleDbConnection conn07 = new OleDbConnection(connStr07);
if (FileUpload1.HasFile)
{
string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
if (fileExt == ".xls")
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
myAdapter.Fill(ds);
}
else if (fileExt != ".xlsx")
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter(queryStr, conn03);
myAdapter.Fill(ds);
}
else
{
lbTag.Text = "The file is not exist!";
}
}
return ds;
}
}
你在数据库里面建立了Sheet1$ 吗
页:
[1]