首页 > 代码库 > 使用 OLEDB读取 excel(不用Excel对象).
使用 OLEDB读取 excel(不用Excel对象).
参考: How to read from an Excel file using OLEDB
为了使用方便,我做成了工具类(OledbHelp.cs),好以后使用.
注:连接字符串中,Provider=xx是从这个连接模仿,主要是考虑是否把第1行当成表头: http://www.connectionstrings.com/excel/
OledbHelp.cs类的代码:
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data.OleDb; 6 using System.Data; 7 using System.Collections; 8 9 namespace Oledb {10 class OledbHelp {11 public string FileName { get; private set; }12 private string ConnectString = null;13 14 /// <summary>15 /// if contains header.16 /// </summary>17 readonly bool hasHeader = false;18 public bool HasHeader { get { return hasHeader; } }19 20 /// <summary>21 /// Initialize connection string.22 /// </summary>23 /// <param name="fileName">full file name.</param>24 /// <param name="hasHeader">ture if the .xls file contains header;otherwise false.</param>25 public OledbHelp(string fileName, bool hasHeader) {26 if (string.IsNullOrEmpty(fileName)) throw new ArgumentNullException("fileName");27 FileName = fileName;28 this.hasHeader = hasHeader;29 ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=‘" + FileName + "‘;";30 31 //specify it contains header.32 if (HasHeader)33 ConnectString += "Extended Properties=‘Excel 12.0 Xml;HDR=YES‘";34 }35 36 public OleDbConnection GetConnection() {37 return new System.Data.OleDb.OleDbConnection(ConnectString);38 }39 40 public DataSet GetDataSet(string sql, OleDbConnection connection) {41 OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);42 DataSet set = new DataSet();43 adapter.Fill(set);44 return set;45 }46 47 /// <summary>48 /// Get DataRows by specified column-name.49 /// </summary>50 /// <param name="set">The input set.</param>51 /// <param name="columnName">The specified column-name</param>52 /// <returns></returns>53 public List<DataRow> GetDataRow(DataSet set, string columnName) {54 DataTable t = set.Tables[0]; //get the first table as default.55 List<DataRow> rows = new List<DataRow>();56 foreach (DataRow r in t.Rows) {57 rows.Add((DataRow)r[columnName]);58 };59 return rows;60 }61 }62 }
主调用代码:
1 string fileName = @"D:\cs\office\excel\excel_data\tmp.xls";2 string sql = "select [姓名] from [msg$]"; //可以在sql 语句指定列字段.3 OledbHelp ole = new OledbHelp(fileName, true);4 using (OleDbConnection conn = ole.GetConnection()) {5 DataSet set = ole.GetDataSet(sql, conn);6 dgv.DataSource = set.Tables[0];7 }
运行的效果:
使用 OLEDB读取 excel(不用Excel对象).
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。