若使用套件有很多方法可達成,這裡討論的是純粹使用oleDB的作法。
手邊以往的程式是從C#改寫成VB.net的版本,但現在改寫為C#,原來的版本卻找不著了,只好重新來過。
在環境部屬上,要考慮.net的應用程式集區必須調整為 32位元,不然會不能執行。
程式上,首先要引入
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
接著是連線字串。
因為使用單位還是用舊版excel,因此用以下的古早寫法。系統會在指定的路徑中產生空白的Excel檔。
string Xlsstr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO\"", path + filename);
然後建立一個 OldDB的連線,組出 Create Table 的語法,將TableSchema塞進去。
using (OleDbConnection XlsConn = new OleDbConnection(Xlsstr)) {
....
//組合出strCreate字串
OleDbCommand command = new OleDbCommand(strCreate, XlsConn);
command.ExecuteNonQuery();
}這當中有一些要注意的細節:
(1) 如果要對欄位動手腳、客製化欄位名稱,一定要在建立Table字串的這個地方處理。很奇怪,在後面的程式碼無法再改欄位名稱。
此外,資料庫欄位一般是存英文欄位名稱,但客戶要求的卻是顯示中文表頭。
當然也可以用Select sid as [學號]的方式處裡,但是程式碼不容易維護。
這裡嘗試的方法,是另外去抓資料表欄位內預先定義好的擴充屬性。範例程式碼中是用迴圈一筆筆去找,日後應該可以整個抓回來放在DT或陣列裡,用更節省資源的方法處理。
@"SELECT value
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @table, 'column', default)
where objname = @colunm ";
想要呈現的欄位名稱,如果名稱較長或有保留字,應該加上左右角括號,如 [field] ,避免建立失敗。
(2)宣告一個DataAdapter 作為中介,逐筆從來源的DataTable讀出資料,新增資料列,將資料列資料加入,最後將資料更新回Excel文件。
這部分比較偏向Dataset的操作,反而單純些。在網路上的範例中,常常都是用具體的DataTable名稱或欄位名稱操作,但常常容易失敗。這裡會建議用DsXls.Tables[0]、dr[j] = Dt.Rows[i][j];之類的方法處理,比較不容易有錯。
(3)確定檔案存在之後,另外用讀檔的方式將資料流導到前端網頁,供使用者下載。
為免資料外流,筆者習慣將檔案放在專案目錄以外的地方,或是App/data之下。總之,不能讓檔案在未經授權的情形下遭到下載。不過,這是另外一回事了。
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; /// <summary> /// excel 的摘要描述 /// </summary> public class excel { public excel() { // // TODO: 在這裡新增建構函式邏輯 // } /// <summary> /// /// </summary> /// <param name="Dt">資料來源</param> /// <param name="MainTableName">讀取Schema的主要工作表</param> /// <param name="SheetName">工作表名稱</param> /// <param name="filename">檔名</param> /// <param name="path">路徑</param> /// <param name="readColName">抓取延伸屬性當作欄位名稱</param> public void DataTableToExcel(DataTable Dt, string MainTableName, string SheetName, string filename, string path, bool readColName = false) { string Xlsstr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO\"", path + filename); DataSet DsXls = new DataSet(); using (OleDbConnection XlsConn = new OleDbConnection(Xlsstr)) { //Step1: 建立Excel空檔案 XlsConn.Open(); string strCreate = String.Empty; int limit = Dt.Columns.Count; for (Int32 i = 0; i < limit; i++) { string vNameOriginal = Dt.Columns[i].ColumnName; //預設用原來名稱,若有延伸屬性抓延伸屬性 if (readColName == true) { string vCname1 = GetColumnName(vNameOriginal, MainTableName); ; if (vCname1 != "" && vCname1 != null) { vNameOriginal = vCname1; } } strCreate += String.Format(",[{0}] Text(255)", vNameOriginal); } strCreate = String.Format("Create Table [{0}] ({1})", SheetName, strCreate.Substring(1)); OleDbCommand command = new OleDbCommand(strCreate, XlsConn); try { command.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception("建立Excel檔案時發生錯誤:"+ ex.Message ); } //Step2: 讀出該空白xls,以SheetName寫成一個DataTable String queryString = String.Format("Select * From [{0}]", SheetName); OleDbDataAdapter Da = new OleDbDataAdapter(); Da.SelectCommand = new OleDbCommand(queryString, XlsConn); OleDbCommandBuilder builder = new OleDbCommandBuilder(Da); Da.Fill(DsXls, SheetName); //需加入第二標題列時 /* DataRow dr0 = DsXls.Tables[0].NewRow(); for (Int32 j = 0; j < (Dt.Columns.Count); j++) { dr0[j] = "test"; } DsXls.Tables[0].Rows.Add(dr0); */ for (Int32 i = 0 ; i < (Dt.Rows.Count) ; i++) { //新增一列 DataRow dr = DsXls.Tables[0].NewRow(); for (Int32 j = 0; j < (Dt.Columns.Count) ; j++ ){ //name //dr[Dt.Columns[j].ColumnName] = Dt.Rows[i][j]; if (Dt.Rows[i][j] == DBNull.Value) { dr[j] = null; } else { dr[j] = Dt.Rows[i][j]; } } DsXls.Tables[0].Rows.Add(dr); } builder.GetInsertCommand(); //執行新增語法 Da.Update(DsXls, DsXls.Tables[0].TableName); } } protected string GetColumnName(string name, string Tablename) { string result = string.Empty; string query_str = @"SELECT value FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @table, 'column', default) where objname = @colunm "; using (SqlConnection conn1 = ConnFactory.ManageConn) { using (SqlCommand cmd = new SqlCommand(query_str, conn1)) { conn1.Open(); cmd.Parameters.AddWithValue("@colunm", name); cmd.Parameters.AddWithValue("@table", Tablename); result = (String)cmd.ExecuteScalar(); } } return result; } }