Feeds:
文章
留言

Archive for 三月 12th, 2007

MSN SpaceGoogle DocGoogle Blog
Chui-Wen Chiu(Arick)
2007.03.12 建立

測試環境:
1. Windows XP Professional SP2
2. Visual Studio 2005
3. Microsoft Excel 2003

有時候我們可能需要從 Excel 萃取出我們所需要的資料,如果透過 Automation 還需要產生 Excel 實體有些麻煩,此時我們可以透過 ADO.NET 提供的 OleDbConnection 來連接 Excel 檔案,透過一致的存取方式來取得我們所需資料。

首先,我們先引入 System.Data 來取用 OleDbConnection, OleDbCommand 和 OleDbDataReader 等類別,下面就是一個簡單的查詢範例

using System;
using System.Data.OleDb;

namespace ImportExcelData {
    class Program {
        static void Main(string[] args) {
            using (CExcel excel = new CExcel(@"c:student.xls")) {
                OleDbDataReader reader = excel.DoQuery(@"SELECT [E-mail] FROM [student$] WHERE [E-mail] = ‘a@hinet.net’");// Table 名稱對應格式為 [Sheet名稱$]
                while (reader.Read()) {
                    Console.WriteLine(reader["E-mail"].ToString());
                }
            }           
        }
    }

    public class CExcel :IDisposable  {
        public CExcel(String excelFile) {
            _excelConn = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", excelFile) );
            _excelConn.Open();
            _command = new OleDbCommand();
            _command.Connection = _excelConn; 
        }

        public OleDbDataReader DoQuery(String sql) {        
            _command.CommandText = sql ;

            return _command.ExecuteReader();                        
        }

        public void Close() {
            if (_excelConn.State != System.Data.ConnectionState.Closed) {
                _excelConn.Close();
            }
        }

        public void Dispose() {
            Close();
        }

        private OleDbConnection _excelConn;
        private OleDbCommand _command;
    }
}

參考資料:
http://www.c-sharpcorner.com/UploadFile/vandita/exceltoaccessinterop02242007093426AM/exceltoaccessinterop.aspx

Read Full Post »