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

 

转载于:https://www.cnblogs.com/zoro-zero/p/4206483.html

相关资源：C#编程经验技巧宝典