黑毛到白毛的攻城獅之路: 用 OLEDB 讀寫 Excel 檔

2010年12月29日星期三

用 OLEDB 讀寫 Excel 檔

其實原本是要直接讀寫 Excel 的，但時間上來不及讓我慢慢找相關資料
只大概知道可以用Microsoft.Office.Interop.Excel 來做
不過簡單測了一下卻出現 Excel 檔案打不開的問題（疑似是安全性問題之類的？）
另外還可以用 NPOI 函式庫，好像網路上蠻多人都用這個方法的。

用 OLEDB 把 Excel 當做小型資料庫來處理的話
主要缺點就是不支援 DELETE，只能 INSERT 和 UPDATE
所以遇到要 DELETE 時只能建新的 EXCEL，把要保留的 DataRow 都寫過去（INSERT）。

微軟的網站上有關 OLEDB 處理 Excel 的說明可以參考
「HOW TO：使用 ADO.NET 擷取與修改利用 Visual Basic .NET 之 Excel 活頁簿中的記錄」

下面簡單寫個 INSERT 的範例：

        
          
          
              
              using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb; 
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
 
namespace excel_control {
    public partial class Form1 : Form {
        private void InsertData(DataTable dt) {
            // HDR=Yes 表示 Excel 的第一行視為欄位名稱
            string _connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                              "Data Source=xxxx.xls" +
                              ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            OleDbConnection conn = new OleDbConnection(_connstr);
            conn.Open();
 
            DataRowCollection rows = dt.Rows;
            string sql; // sql 指令
            int queryResult = 0; // 記錄整體的影響筆數
            for (int i = 0; i < rows.Count; i++) {
                sql = "INSERT INTO [Sheet1$](";
                /* 自動抓取資料欄位的名稱 */
                for (int j = 0; j < dsOutput.Tables[0].Columns.Count; j++) {
                    sql += dt.Columns[j].ColumnName;
                    if (j != dt.Columns.Count - 1)
                        sql += ", ";
                }
                sql += ") VALUES (";
                /* 要 INSERT 的資料內容 */
                for (int j = 0; j < dt.Columns.Count; j++) {
                    sql += "'" + rows[i][j].ToString().Trim() + "'";
                    if (j != dt.Columns.Count - 1)
                        sql += ", ";
                }
                sql += ")";
                command = new OleDbCommand(sql, conn);
                queryResult += command.ExecuteNonQuery();
            }
            conn.Close();
        }
    }
}

            

        
      