首页 > 代码库 > C# 读取Excel和DBF文件

C# 读取Excel和DBF文件

//获excel中多个sheet中的数据  /// <summary>        /// 读取导入Excel文件内容        /// </summary>        /// <param name="fileName">文件路径(上传后)</param>        /// <param name="columnString">Excel中的列 名</param>        /// <param name="isReadAllExcelSheet">是否读取多个Sheet</param>        /// <param name="message">(out)消息提示</param>        /// <returns></returns>        public DataTable ReadDataFromExcel(string fileName, string columnString, bool isReadAllExcelSheet, out string message)        {            message = "";            try            {                string strCon = "";                string fileExt = Path.GetExtension(fileName).ToLower();                if (fileExt == ".xls")                {                    strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;                }                else if (fileExt == ".xlsx")                {                    strCon = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 12.0;HDR=Yes;IMEX=1;" + (char)34;                }                else                {                    message = "读取失败,非excel文件格式。";                    return null;                }                OleDbConnection excelConnection = new OleDbConnection(strCon);                excelConnection.Open();                #region 获取所有sheet表名称                DataTable excelData = new DataTable();                DataTable getTableNameData = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                //获取excel中的第一个sheet中的数据                //ReadEachExcelSheetData(excelConnection, ((String)getTableNameData.Rows[0]["TABLE_NAME"]).ToString(), columnString, ref excelData);                //获取excel中有多个sheet中的数据                foreach (DataRow row in getTableNameData.Rows)                {                    excelData = ReadEachExcelSheetData2(excelConnection, ((String)row["TABLE_NAME"]).ToString(), columnString);                    if (excelData.Rows.Count <= 0)                    {                        break;                    }                }                getTableNameData = null;                #endregion                return excelData;            }            catch (Exception ex)            {                message = "数据文件或者内容格式有严重错误(" + ex.Message + "),请检查!";                return null;            }        }        public void ReadEachExcelSheetData(OleDbConnection excelConnection, string tableName, string columnString, ref DataTable excelData)        {            try            {                tableName = "[" + tableName + "]";                string sql = "";                string queryFieldText = string.Empty;                if (string.IsNullOrEmpty(queryFieldText))                {                    queryFieldText = "*";                }                else                {                    foreach (string column in columnString.Split(,))                    {                        queryFieldText += "[" + column + "],";                    }                    queryFieldText = queryFieldText.Trim(,);                }                sql = @"                                SELECT                                         {0}                                FROM                                         {1}                                ";                sql = string.Format(sql, queryFieldText, tableName);                DataSet ds = new DataSet();                OleDbDataAdapter myAdp = new OleDbDataAdapter(sql, excelConnection);                myAdp.Fill(ds, tableName);                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)                {                    excelData.Merge(ds.Tables[0]);                }            }            catch (Exception ex)            {                throw ex;            }        }//获取excel中第一个sheet中的数据  /// <summary>        /// 读取导入Excel文件内容        /// </summary>        /// <param name="fileName">文件路径(上传后)</param>        /// <param name="columnString">Excel中的列 名</param>        /// <param name="isReadAllExcelSheet">是否读取多个Sheet</param>        /// <param name="message">(out)消息提示</param>        /// <returns></returns>        public DataTable ReadDataFromExcel(string fileName, string columnString, bool isReadAllExcelSheet, out string message)        {            message = "";            try            {                string strCon = "";                string fileExt = Path.GetExtension(fileName).ToLower();                if (fileExt == ".xls")                {                    strCon = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;                }                else if (fileExt == ".xlsx")                {                    strCon = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + fileName + ";Extended Properties=" + (char)34 + "Excel 12.0;HDR=Yes;IMEX=1;" + (char)34;                }                else                {                    message = "读取失败,非excel文件格式。";                    return null;                }                OleDbConnection excelConnection = new OleDbConnection(strCon);                excelConnection.Open();                #region 获取所有sheet表名称                DataTable excelData = new DataTable();                DataTable getTableNameData = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                //获取excel中的第一个sheet中的数据                ReadEachExcelSheetData(excelConnection, ((String)getTableNameData.Rows[0]["TABLE_NAME"]).ToString(), columnString, ref excelData);                //获取excel中有多个sheet中的数据                //foreach (DataRow row in getTableNameData.Rows)                //{                //    excelData = http://www.mamicode.com/ReadEachExcelSheetData2(excelConnection, ((String)row["TABLE_NAME"]).ToString(), columnString);                //    if (excelData.Rows.Count <= 0)                //    {                //        break;                //    }                //}                getTableNameData = http://www.mamicode.com/null;                #endregion                return excelData;            }            catch (Exception ex)            {                message = "数据文件或者内容格式有严重错误(" + ex.Message + "),请检查!";                return null;            }        }  public DataTable ReadEachExcelSheetData2(OleDbConnection excelConnection, string tableName, string columnString)        {            DataTable excelData = new DataTable();            try            {                tableName = "[" + tableName + "]";                string sql = "";                string queryFieldText = string.Empty;                if (string.IsNullOrEmpty(queryFieldText))                {                    queryFieldText = "*";                }                else                {                    foreach (string column in columnString.Split(,))                    {                        queryFieldText += "[" + column + "],";                    }                    queryFieldText = queryFieldText.Trim(,);                }                sql = @"                                SELECT                                         {0}                                FROM                                         {1}                                ";                sql = string.Format(sql, queryFieldText, tableName);                DataSet ds = new DataSet();                OleDbDataAdapter myAdp = new OleDbDataAdapter(sql, excelConnection);                myAdp.Fill(ds, tableName);                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)                {                    excelData.Merge(ds.Tables[0]);                }            }            catch (Exception ex)            {                throw ex;            }            return excelData;        }

 

C# 读取Excel和DBF文件