C#–使用OleDb讀取Excel

一直忘記發這篇(艸
果然比想像中複雜了些,明明就是M$的產品哪…

幸好我拿來測試的樣本一開始就有字串數字混著的欄位,不至於後期才爆掉…

這時需要一個重要的手續就是…改機碼(Registry)

參考文章裡面提到的位置是在:

「HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel」

在我有安裝Office的Win7環境下,該機碼的位置是在:

「HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel」

在 Excel 機碼中 ImportMixedTypes 的預設值為 Text,代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話,Jet Engine 預設會將欄位的資料格式自動轉成文字(Text)格式!

在 Excel 機碼中 TypeGuessRows 的預設值為 8,代表當你讀入 Excel 檔案時若每一列的資料格式不一致的話,Jet Engine 會先讀取前 8 列的資料,用已判斷之後的資料格式是否固定。假設若前 8 列的資料都是「日期」格式,那麼在第 8 列之後的資料 Jet Engine 會自動解析成「日期」格式,但只要遇到有的資料不是「日期」格式時,就會回傳 Null 給 Jet Engine,也就是本篇文章一開始的問題。

程式碼部分

// 完整程式碼
public static void Excel2Xml(string _filePath)
{
    // check file's path
    if (string.IsNullOrEmpty(_filePath))
        return;

    // get file name & folder
    string fileName = Path.GetFileNameWithoutExtension(_filePath);
    string fileFolder = Path.GetDirectoryName(_filePath);

    // link data
    OleDbConnection myConnection;
    string provider = "Microsoft.Jet.OLEDB.4.0";
    string extendedProperties = "Excel 8.0;IMEX=1;";
    string connectionInitText = string.Format("provider={0};Data Source='{1}';Extended Properties='{2}'", provider, _filePath, extendedProperties);
    // create
    myConnection = new OleDbConnection(connectionInitText);
    // open
    myConnection.Open();

    // get first sheet
    DataTable dt = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    string firstSheetName = dt.Rows[0]["TABLE_Name"].ToString();
    // check '$'
    if (firstSheetName.IndexOf("$") < 0)
    { firstSheetName += "$"; }

    OleDbDataAdapter myCommand;
    // select
    string myCommandInitText = string.Format("select * from [{0}]", firstSheetName);
    myCommand = new OleDbDataAdapter(myCommandInitText, myConnection);
    // use table name to node title
    myCommand.TableMappings.Add("Table", firstSheetName.Remove(firstSheetName.LastIndexOf("$")));
    //myCommand.TableMappings.Add("Table", "Row");

    // fill dataSet
    DataSet ds;
    ds = new DataSet(fileName);
    myCommand.Fill(ds);

    // close OleDbConnection
    myConnection.Close();

    // Write to file
    ds.WriteXml(fileFolder + Path.DirectorySeparatorChar +  fileName + ".xml");
}

以下片段說明:

    // link data
    OleDbConnection myConnection;
    string provider = "Microsoft.Jet.OLEDB.4.0";
    string extendedProperties = "Excel 8.0;IMEX=1;";
    string connectionInitText = string.Format("provider={0};Data Source='{1}';Extended Properties='{2}'", provider, _filePath, extendedProperties);
    // create
    myConnection = new OleDbConnection(connectionInitText);
    // open
    myConnection.Open();

變數 provider 表示資料的讀取方式 or 工具(?)。
變數 extendedProperties 表示一些額外參數。
變數 connectionInitText 是初始化 OleDbConnection 用的字串。

    // get first sheet
    DataTable dt = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    string firstSheetName = dt.Rows[0]["TABLE_Name"].ToString();
    // check '$'
    if (firstSheetName.IndexOf("$") < 0)
    { firstSheetName += "$"; }

透過方法 OleDbConnection.GetOleDbSchemaTable() 取得表格資料。
這裡我們需要擷取表格資料,參數1和參數2的詳細參考這裡

另外為了接下來的選擇語法,適當的加上’$’符號。

    // select part
    OleDbDataAdapter myCommand;
    // select
    string myCommandInitText = string.Format("select * from [{0}]", firstSheetName);
    myCommand = new OleDbDataAdapter(myCommandInitText, myConnection);
    // use table name to node title
    myCommand.TableMappings.Add("Table", firstSheetName.Remove(firstSheetName.LastIndexOf("$")));

就…使用語法篩選,並加入資料名稱對應。

    // final part
    // fill dataSet
    DataSet ds;
    ds = new DataSet(fileName);
    myCommand.Fill(ds);

    // close OleDbConnection
    myConnection.Close();

    // Write to file
    ds.WriteXml(fileFolder + Path.DirectorySeparatorChar +  fileName + ".xml");

最後,填入並寫出到xml檔。

參考

How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual C# .NET (中文版本)

OleDbSchemaGuid Class
OleDbSchemaGuid.Tables Field

OleDbDataAdapter 建構函式 (String, OleDbConnection)
DataAdapter.TableMappings 屬性

額外爬文的途中發現「NPOI」這玩意,似乎不錯用,之後再來研究研究XD

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s