[.NET]應用ADO.NET OleDB 方式將 DataSet 匯出轉成 Excel

應用ADO.NET OleDB 方式將 DataSet 匯出轉成 Excel
讓匯出的Excel在上傳回Server的時候是可以讀取的

剛看到 F6 Team使用ASP.NET的GridView滙出/儲存資料至Excel,Word,Txt檔
這篇有人回應,利用 GridView or DataGrid 轉出的 Excel 會不是 Excel 的格式,
所以在將匯出的 Excel 傳回去給Server處理的時候,無法讀取這個Excel。

就Dotjum知道應用 .NET 將資料集DataSet 匯出成 Excel 的方式有三種方式:

1.透過 DataGird、GridView 方式來轉出 Excel ,可以參考在 點部落 其他朋友的文章有介紹,

擴展 GridView 控制項 - 支援 Excel 及 Word 匯出
輸出成Excel,並且有多個工作表(Work Sheet)
將ASP.NET的成果,匯出 Excel檔案
[ASP.NET] GridView 轉 Excel
使用ASP.NET的GridView滙出/儲存資料至Excel,Word,Txt檔 

2. 應用 Microsoft.Office.Interop.Excel ,把TSQL下給Excel,Excel去叫資料回來,這個方法我用在WinForm.
3.應用ADO.NET OleDB 方式將 DataSet 匯出轉成 Excel

之前使用第一種方法GridView匯出的資料Excel修改後,要傳回Server在判斷,Server端會無法讀取Excel,
GridView匯出Excel方式已經含一些Html格式在裡面,所以讀取方面會出現錯誤,
之前有看過在 GridView 轉出Excel,加上一些程式碼去過濾資料,能夠轉出的Excel是可以回傳上Server端的.NET解析,
但好像沒有記下來,後來我採用第三種方式來處理.
詳細的處理流程如下

1.先連線到資料庫取出資料到 DataTable
2.產生一個臨時的 .xsl
3.透過 OleDB 的方式將 DataTable資料每一筆 Insert 進去
4.Response.WriteFile 臨時的 .xsl
5.刪除臨時的 .xsl 
(要注意的是寫入的資料夾記得要開給ASP.NET權限)

實做的程式碼


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Text;
using System.Data.OleDb;

public partial class DataSetToExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnDSToExcel_Click1(object sender, EventArgs e)
    {
        //這邊是取SqlServer最新資料
        DataSet dsData = new DataSet();
        SqlConnection sqlConn = new SqlConnection();
        sqlConn = new SqlConnection(@"連線字串");

        SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT TOP 10 * FROM dbo.Products", sqlConn);
        sqlAdapter.Fill(dsData, "Sheet1");
        DataTable dtTable = dsData.Tables["Sheet1"];
        //取目前目錄
        string physicPath = HttpContext.Current.Server.MapPath(this.Context.Request.Path);
        //產生一個臨時檔案
        string fileName = Guid.NewGuid() + ".Xls";
        //把臨時檔案的實體Full路徑
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName + ";Extended Properties=Excel 8.0;";
        //傳給產生Excel的程式處理 該副程式會把資料填進去		 
        DataTableToExcel(dtTable, connString);        
        Response.Clear();
        //要給的實體路徑
        Response.WriteFile(physicPath + fileName);
        string httpHeader = "attachment;filename=backup.Xls";
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", httpHeader);
        Response.Flush();
        //刪除臨時文件
        System.IO.File.Delete(physicPath + fileName);
        Response.End();
    }
 
    public void DataTableToExcel(DataTable dt, string connString)
    {
        //先算出欄位及列數
        int rows = dt.Rows.Count;
        int cols = dt.Columns.Count;
        //用來建立命令 
        StringBuilder sb = new StringBuilder();

        sb.Append("CREATE TABLE ");
        sb.Append(dt.TableName + " ( ");
        //用來做開TABLE的欄名資訊
        for (int i = 0; i < cols; i++)
        {
            if (i < cols - 1)
                sb.Append(string.Format("{0} varchar,", dt.Columns[i].ColumnName));
            else
                sb.Append(string.Format("{0} varchar)", dt.Columns[i].ColumnName));
        }
        //把要開啟的臨時Excel建立起來
        using (OleDbConnection objConn = new OleDbConnection(connString))
        {
            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = objConn;

            objCmd.CommandText = sb.ToString();


            objConn.Open();
            //先執行CreateTable的任務
            objCmd.ExecuteNonQuery();


            //開始處理資料內容的新增
            #region 開始處理資料內容的新增
            //把之前 CreateTable 清空
            sb.Remove(0, sb.Length);
            sb.Append("INSERT INTO ");
            sb.Append(dt.TableName + " ( ");
            //這邊開始組該Excel欄位順序
            for (int i = 0; i < cols; i++)
            {
                if (i < cols - 1)
                    sb.Append(dt.Columns[i].ColumnName + ",");
                else
                    sb.Append(dt.Columns[i].ColumnName + ") values (");
            }
            //這邊組 DataTable裡面的值要給到Excel欄位的
            for (int i = 0; i < cols; i++)
            {
                if (i < cols - 1)
                    sb.Append("@" + dt.Columns[i].ColumnName + ",");
                else
                    sb.Append("@" + dt.Columns[i].ColumnName + ")");
            }
            #endregion


            //建立插入動作的Command
            objCmd.CommandText = sb.ToString();
            OleDbParameterCollection param = objCmd.Parameters;

            for (int i = 0; i < cols; i++)
            {
                param.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, OleDbType.VarChar));
            }

            //使用參數化的方式來給予值
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < param.Count; i++)
                {
                    param[i].Value = row[i];
                }
                //執行這一筆的給值
                objCmd.ExecuteNonQuery();
            }


        }//end using
    }
    
}

透過將DataTable的資料使用 ADO.NET OleDB  方式新增資料進去方式,最後匯出的Excel就會是一般的Excel格式。
Dotjum的方法可能程式碼多了些,因該還有更好的方法,還請知道的朋友,可以分享更好的方法。