首页 > 代码库 > 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文件
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。