C# 连接Excel，获取表格数据，获取多个sheet中的数据，获取多个sheet名

　　　　 /// <summary> /// 获取Excel内容。 /// </summary> /// <param name="sheetName">工作表名称，例：sheet1</param> /// <param name="filePath">Excel路径</param> /// <returns></returns> public static DataTable GetTableFromExcel(string sheetName, string filePath, string where = "") { string connStrTemplate = string.Empty; string fileType = System.IO.Path.GetExtension(filePath); if (string.IsNullOrEmpty(fileType)) return null; if (filePath == ".xls") connStrTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; else connStrTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; //connStrTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; //const string connStrTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;\""; DataTable dt = null; if (!System.IO.File.Exists(filePath)) { // don't find file return null; } OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath)); try { conn.Open(); if (sheetName == null || sheetName.Trim().Length == 0) { DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim(); } OleDbDataAdapter da = null; DataSet ds = new DataSet(); ; string strSQL = "Select * From [" + sheetName + "$]"; if (!string.IsNullOrWhiteSpace(where)) { strSQL = string.Format("Select * From [" + sheetName + "] Where {0}", where); } try { da = new OleDbDataAdapter(strSQL, conn); da.Fill(ds); } catch (Exception er) { da = new OleDbDataAdapter("Select * From [sheet1$]", conn); da.Fill(ds); } dt = ds.Tables[0]; } catch (Exception ex) { throw ex; } finally { conn.Close(); } return dt; }

//获取名称：

/// <summary> /// 获取sheet名 /// </summary> /// <param name="excelFile">Excel文件名及路径</param> /// <returns></returns> public static string[] GetExcelSheetNames(string fileName) { OleDbConnection objConn = null; System.Data.DataTable dt = null; try { string connString = string.Empty; string FileType = fileName.Substring(fileName.LastIndexOf(".")); if (FileType == ".xls") connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";Extended Properties=Excel 8.0;"; else//.xlsx connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; // 创建连接对象 objConn = new OleDbConnection(connString); // 打开数据库连接 objConn.Open(); // 得到包含数据架构的数据表 dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // 添加工作表名称到字符串数组 foreach (DataRow row in dt.Rows) { string strSheetTableName = row["TABLE_NAME"].ToString(); //过滤无效SheetName if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$")) { excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1); } i++; } return excelSheets; } catch (Exception ex) { return null; } finally { // 清理 if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } }

 

转载于:https://www.cnblogs.com/SeNaiTes/p/11381210.html