Excel表导入SQL数据库中出现"外部表不是预期的格式"这个错误

Question

行 36:         OleDbDataAdapter adata = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
行 37:         DataSet ds = new DataSet();
行 38:         adata.Fill(ds);行 39:         return ds;
行 40:         
 源文件: e:\OA\OA\Admin\message\Addmessage.aspx.cs    行: 38 

Answer 1

public DataSet ds()
{
            string type2 = FileUpload1.FileName;
            var type3 = type2.Substring(type2.LastIndexOf('.'));
            if(type3.Equals(".xls") || type3.Equals(".xlsx"))
            {
                string newName = Server.MapPath("..//App_Data//Uploads//") + DateTime.Now.ToString("hhmmss") + ".xls";                FileUpload1.SaveAs(newName);
                string connStr = string.Empty;                if (type3.Equals(".xlsx"))
                {
                    connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + newName +
                              ";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";
                }                else if (type3.Equals(".xls"))
                {
                    connStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + newName +
                              ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
                }
                OleDbConnection conn = new OleDbConnection(connStr);
                if (conn.State.ToString() == "Closed")
                {
                    conn.Open();
                }
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                var tableName = dt.Rows[0][2].ToString().Trim();
                var str1 = string.Format("Select * from [{0}]", tableName+"A:I");
                OleDbDataAdapter oda = new OleDbDataAdapter(str1, conn);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                conn.Close();
                File.Delete( newName );
          }
}

Answer 2

看看 是 Sheet1$ 这个excel表格不存在  还是连接语句写错了，或者是excel表格格式与你连接的excel版本不一致同时请注意下你的姐结贴率