xls和xlsx的打开方式

我就是我 2022-08-22 15:21 170阅读 0赞

AccessDatabaseEngine.exe自然是要装的，但是关键是Provider=Microsoft.Jet.Oledb.4.0一定要替换成Provider=Microsoft.ACE.OLEDB.12.0因为其中的Jet和ACE很相似但是却很容易忽略，这个是我的代码：

ResultInfo<List<HighEndNetFinancialEntity>> Result = new ResultInfo<List<HighEndNetFinancialEntity>>();
                string ver = "";
                int pos = FilePath.LastIndexOf(".") + 1;
                string postFileName = FilePath.Substring(pos, FilePath.Length - pos);
                if (postFileName == "xls")
                {
                    ver = "8.0";
                }
                else if (postFileName == "xlsx")
                {
                    ver = "12.0";
                }
                else
                {
                    Result.Message = "请上传Exel-xls文件。";
                    Result.IsSuccess = false;
                    return Result;
                }
    
                DataSet result = null;
                //string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FilePath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
                //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";" + "Extended Properties=\"Excel " + ver + ";IMEX=1\"";
                 string strConn="";
                if (postFileName == "xls")
                 strConn = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + FilePath + "; Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=1;\""; 
                 else if (postFileName == "xlsx")
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FilePath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=1;\""; 
                OleDbConnection conn = new OleDbConnection(strConn);
                try
                {
                    conn.Open();
                    string strExcel = "select * from [sheet1$]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                    result = new DataSet();
                    myCommand.Fill(result, "HighEndNetFinancial");
                }
                catch (Exception e)
                {
                    if (e != null)
                    {
                        Result.Message += e.Message;
                        return Result;
                    }
                }
                finally
                {
                    conn.Close();
                }