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 = 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 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;

        }