using System; using System.Collections.Generic; using System.Text; using System.Data.OleDb; using System.Data; namespace MyExcel { public class ExcelConnector { string connString; public ExcelService(string path) { connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;"; } private OleDbConnection conn; public OleDbConnection Connection { get { if (conn == null) { conn = new OleDbConnection(connString); conn.Open(); } else if (conn.State == ConnectionState.Broken) { conn.Close(); conn.Open(); } else if (conn.State == ConnectionState.Closed) { conn = new OleDbConnection(connString); conn.Open(); } return conn; } } /// <summary> /// 获取Excel 中的工作表 /// </summary> /// <returns></returns> public List<string> GetDataFromExcelWithAppointSheetName() { DataTable dtSheetName = null; try { dtSheetName = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); List<string> strTableNames = new List<string>(); for (int k = 0; k < dtSheetName.Rows.Count; k++) { string s = dtSheetName.Rows[k]["TABLE_NAME"].ToString(); Console.WriteLine(s); //过滤一下没用的表,Excel 默认生成的隐藏文件 if (!s.Contains("_FilterDatabase") && s.LastIndexOf('_') + 1 != s.Length) { strTableNames.Add(s); } } return strTableNames; } catch (Exception) { return null; } finally { Connection.Dispose(); } } /// <summary> /// 通过工作表名 获取数据 /// </summary> /// <param name="name"></param> /// <returns></returns> public DataTable GetContentBySheetName(string name) { DataTable dt = new DataTable(); OleDbDataAdapter myCommand = null; string strExcel = "select * from [" + name + "]"; try { myCommand = new OleDbDataAdapter(strExcel, Connection); dt = new DataTable(); myCommand.Fill(dt); return dt; } catch (Exception) { return null; } finally { myCommand.Dispose(); Connection.Dispose(); } } } }