C# 上传excel文档解析出里面数据

港控/mmm° 2022-01-09 18:33 255阅读 0赞

if (fileExt.ToUpper() == ".XLS" || fileExt.ToUpper() == ".XLSX" || fileExt.ToUpper() == ".XLSM" || fileExt.ToUpper() == ".XLSB")
                    {
                        int Sp_Count = orFilePath.Split(Convert.ToChar("\\")).Length;
                        //string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=No;IMEX=1';" + "data source=" + filePath;
                        string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0; HDR=NO; IMEX=1\"";
                        OleDbConnection Conn = new OleDbConnection(connStr);
                        Conn.Open();
                        //获取excel文档名字为Sheet1的工作表 (如没有此表则异常)
                        //string SheetName = "[Sheet1$]";
                        //DataSet ds = new DataSet();
                        //OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM {0}", SheetName), Conn);
                        //odda.Fill(ds, SheetName);
                        //Conn.Close();
    
                       //获取动态名称
                       //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　
                        DataTable dtSheetName = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                        //包含excel中表名的字符串数组
                        string[] strTableNames = new string[dtSheetName.Rows.Count];
                        for (int k = 0; k < dtSheetName.Rows.Count; k++)
                        {
                            strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                        }
                        DataSet ds = new DataSet();
                        OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", strTableNames[0]), Conn);
                        odda.Fill(ds, strTableNames[0]);
                        Conn.Close();
    
                    }