public JsonResult ReadExcel()
{
try
{
string strPath = "";
strPath = Server.MapPath("~/UpLoads/ImportData/");
strPath += "ImportData.xls";
//string strConn = "PRovider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
string strConn = "Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + strPath + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
OleDbConnection Oleconn = new OleDbConnection(strConn);
OleDbDataAdapter excelCommand = null;
DataSet excel_ds = new DataSet();
string strSql = "select * from [sheet1$]";
try
{
Oleconn.Open();
excelCommand = new OleDbDataAdapter(strSql, Oleconn);
excelCommand.Fill(excel_ds, "exdtSource");//得到dataset
this.jsondata.JsonData = DataTableToHash(excel_ds.Tables[0]);
this.jsondata.Success = true;
//读取成功后,删除上传的文件
DelUpFile_Excel();
}
catch (System.Exception ex)
{
throw new Exception("Excel转化数据时出错" + ex.Message);
}
finally
{
Oleconn.Close();
Oleconn.Dispose();
}
}
catch (Exception e)
{
this.jsondata.Success = false;
this.jsondata.ErrInfo = "读取excel文件失败:" + e.Message;
}
return this.Json(jsondata, JsonRequestBehavior.AllowGet);
{
try
{
string strPath = "";
strPath = Server.MapPath("~/UpLoads/ImportData/");
strPath += "ImportData.xls";
//string strConn = "PRovider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
string strConn = "Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + strPath + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
OleDbConnection Oleconn = new OleDbConnection(strConn);
OleDbDataAdapter excelCommand = null;
DataSet excel_ds = new DataSet();
string strSql = "select * from [sheet1$]";
try
{
Oleconn.Open();
excelCommand = new OleDbDataAdapter(strSql, Oleconn);
excelCommand.Fill(excel_ds, "exdtSource");//得到dataset
this.jsondata.JsonData = DataTableToHash(excel_ds.Tables[0]);
this.jsondata.Success = true;
//读取成功后,删除上传的文件
DelUpFile_Excel();
}
catch (System.Exception ex)
{
throw new Exception("Excel转化数据时出错" + ex.Message);
}
finally
{
Oleconn.Close();
Oleconn.Dispose();
}
}
catch (Exception e)
{
this.jsondata.Success = false;
this.jsondata.ErrInfo = "读取excel文件失败:" + e.Message;
}
return this.Json(jsondata, JsonRequestBehavior.AllowGet);
}
External table is not in the expected format. 若是遇到这个问题的话 红色标记的切换下,就不会有问题了
excel 从索引 163 处开始,初始化字符串的格式不符合规范. 这个问题是我上面的代码 strConn 里面的值没有用‘ ’包含起来,所以就出现了这个问题
百度了好久终于解决了,记录下来