C# 在Asp.net 中將DataTable匯出成Excel檔案

將資料庫中的數值匯出Excel，應該是超經典的入門習題之一。
若使用套件有很多方法可達成，這裡討論的是純粹使用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;


    }

}