讀取Excel裡面的內容轉為DataTable

標籤：vat   soft   查詢   ima   pen   space   exce   info   技術分享   

using System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Linq;using System.Text;namespace ExcelRead{    class ExcelHelper    {        private static string excelConstr;        private OleDbConnection conn = null;//操作資料庫        private OleDbDataAdapter ada = null;//填充dataset        public ExcelHelper(string path)        {            excelConstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties = Excel 12.0";            if (conn == null || conn.State == ConnectionState.Closed)            {                conn = new OleDbConnection(excelConstr);            }        }        public DataTable GetDataSource(string sheetName)        {            DataTable dt = new DataTable();            string sql = string.Empty;            sql = "select * from [" + sheetName + "]";            dt = GetDT(sql);            return dt;        }        /// <summary>        /// 擷取excel資料        /// </summary>        /// <param name="sql">用於查詢的sql</param>        /// <returns></returns>        public DataTable GetDT(string sql)        {            DataSet ds = new DataSet();            try            {                if (conn.State == ConnectionState.Closed)                {                    conn.Open();                }                ada = new OleDbDataAdapter(sql, conn);                ada.Fill(ds);            }            catch (Exception e)            {                throw e;            }            finally            {                conn.Close();            }            return ds.Tables[0];        }    }}

　　

//擷取Excel表裡Sheet1的資料
//調用               ExcelHelper _excelhelper = new ExcelHelper("Excel檔案路徑");//Excel的sheet名稱，後面要跟$符號  _excelhelper.GetDataSource("Sheet1$");

　　

 

讀取Excel裡面的內容轉為DataTable