[ASP.net] 把資料庫資料匯出成Excel給Client端下載(底層ADO.net方式)

2011-05-03

24894
0
ASP.net WebForm
2011-05-05

既然有Excel匯入到資料庫，當然我也碰過逆向操作

從資料庫撈出資料，然後做商業邏輯運算，再弄成Excel檔，送出給Client端下載

貼出來給網友們參考

1. 前置動作，因為客戶可能會要求Excel做跨欄合併，格子要加Border、背景色等等，這種繁雜的美化處理我們就不在程式碼內做

事先做好一份Excel Sample檔，把客戶要求的顏色、格子、跨欄合併等等先處理好，丟到測試機

例如範列：Sheet1(待會程式要塞資料到A2和C2的格子)

Sample001

Sheet2(待會程式要塞資料到A1當標題，A3開始有三列，要塞三筆Record資料)

Sample002

然後程式就從測試機Copy那份Excel Sample檔到temp目錄，並從DB撈資料塞值給那份temp Excel，最後把它輸出給Client端下載，流程大致是這樣。

程式碼：


using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;

//這次畫面上只要擺一個按鈕控制項就好

/***Copy Start***/
//引用Microsoft Excel相關參考  
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
//移機時記得Bin底下的Microsoft.Office.Interop.Excel.dll和office.dll等，Excel相關dll也要Copy過去  
/***Copy End***/

public partial class ExportExcel : System.Web.UI.Page
{
    /***Copy Start***/
    //*** Excel Interop reference ***/
    Microsoft.Office.Interop.Excel.Application xlApp = null;
    Workbook wb = null;
    Worksheet[] ws = new Worksheet[2];//準備2個sheet，可以依需求自行增加
    //*******************************/

    //要開啟Sample Excel格式檔的路徑
    string excel_sample_filepath = "";
    //要Copy一份Excel的目的地目錄
    string excel_upload = "";
    protected void Page_Load(object sender, EventArgs e)
    {
        this.excel_sample_filepath = Server.MapPath("~/File/Sample.xls");
        this.excel_upload = Server.MapPath("~/File/temp/"); 
    }




    //按鈕Click事件
    protected void cmdExportExcel_Click(object sender, EventArgs e)
    {

        //先Copy另一份檔案在相同目錄下，待會要塞資料到該檔案excel_copy_filePath 
        string excel_copy_filePath = System.IO.Path.Combine(this.excel_upload, Guid.NewGuid().ToString() + ".xls");
        System.IO.File.Copy(this.excel_sample_filepath,
            //目的地位置
                  excel_copy_filePath, true);


        try
        {

            if (this.xlApp == null)
            {
                this.xlApp = new Microsoft.Office.Interop.Excel.Application();
            }

            //開啟Excel
            this.xlApp.Workbooks.Open(excel_copy_filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            this.wb = this.xlApp.Workbooks[1];//指定到第一個Workbook
            this.wb.Save();


            for (int i = 0; i < xlApp.Worksheets.Count; i++)//走訪實體全部的sheet
            {
                //將實體Worksheet references 到陣列
                this.ws[i] = (Worksheet)xlApp.Worksheets[i + 1];
            }

            //寫進Excel
            if (this.ws[0] != null)
                SaveOrInsertSheet01(excel_copy_filePath, this.ws[0]);

            //寫進Excel
            if (this.ws[1] != null)
                SaveOrInsertSheet02(excel_copy_filePath, this.ws[1]);



        }//不加Catch，程式出錯的話，可以看得見原始碼，並執行finally
        finally
        {
            this.xlApp.Workbooks.Close();
            this.xlApp.Quit();
            try
            {
                
                //刪除 Windows工作管理員中的Excel.exe 處理緒.
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.xlApp);
                //有二個Worksheet
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.ws[0]);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.ws[1]);

            }
            catch { }
            this.xlApp = null;
            this.wb = null;
            this.ws = null;


            //下載該Excel，請參考另一篇文章：[ASP.net] 按下Button，跳出視窗讓Client端儲存檔案 
            //http://www.dotblogs.com.tw/shadow/archive/2011/03/02/21633.aspx
            this.downloadExcel(excel_copy_filePath);

            //這裡不知道為啥就是刪不掉複本Excel檔，大概是Server端不知道Client端何時下載完檔案吧
            bool isDeleteFileFromServer = true;
            if (isDeleteFileFromServer)
            {
                System.IO.File.Delete(excel_copy_filePath);
            }  

            GC.Collect();
        }

        

    }
 

    #region 寫 Sheet01 
    private void SaveOrInsertSheet01(string excel_filename,Worksheet ws)
    {

        //設定
        ws.get_Range("A2", "A2").Value2 = "A2 哈囉";
        //每做修改，WorkBook就要儲存變更，不然下載Excel檔時會跳出視窗要使用者做儲存動作
        this.wb.Save();
        //水平置中
        ws.get_Range("A2", "A2").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        //this.wb.Save();
        //垂直置中
        ws.get_Range("A2", "A2").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
        this.wb.Save();

        ws.get_Range("C2", "C2").Value2 = "C2 世界！";
        this.wb.Save();
    }
    #endregion

    #region 寫 Sheet02 
    private void SaveOrInsertSheet02(string excel_filename,Worksheet ws)
    {
        //二維陣列，要塞進Sheet的資料
        //這裡可以改成從DB撈出來的DataTable資料集，依需求客製化吧
        string[,] fruits ={  {"1","Apple","40元"},
                             {"2","Banana","50元"},
                             {"3","cherry","1元"}};

        #region 寫入標題
        ws.get_Range("A1", "A1").Value2 = "水果資料表";
        this.wb.Save();
        //垂直置中
        ws.get_Range("A1", "A1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
        this.wb.Save();
        #endregion


        //塞資料的Sheet起始列範圍
        int startRow = 3;
        Range aRange = ws.get_Range("A" + startRow.ToString(), "C" + startRow.ToString());

        for (int row = 0; row < 3; row++)//走訪fruits陣列
        {
            for (int col = 0; col < 3; col++)
            {
                //把值塞進Sheet
                aRange.Cells[1, col + 1] = fruits[row, col];
                this.wb.Save();
                //以下寫法沒辦法塞值到Sheet
                //((object[,])aRange.Value2)[1, col + 1] = fruits[row, col];

                if (col == 2)
                {//抓下一列範圍
                    startRow++;
                    aRange = ws.get_Range("A" + startRow.ToString(), "C" + startRow.ToString());
                }
            }
        }
    }
    #endregion

    //下載該Excel，請參考另一篇文章：[ASP.net] 按下Button，跳出視窗讓Client端儲存檔案 
    //http://www.dotblogs.com.tw/shadow/archive/2011/03/02/21633.aspx
    protected void downloadExcel(string excel_copy_filePath)
    {
        //用戶端的物件
        System.Net.WebClient wc = new System.Net.WebClient();

        byte[] file = null;
        try
        {
            //用戶端下載檔案到byte陣列
            file = wc.DownloadData(excel_copy_filePath);
        }
        catch (Exception ex)
        {

            Response.Write("ASP.net禁止下載此敏感檔案(通常為：.cs、.vb、微軟資料庫mdb、mdf和config組態檔等)。<br/>檔案路徑：" + excel_copy_filePath + "<br/>錯誤訊息：" + ex.ToString());
            return;
        }


        Response.Clear();
        //取得副檔名
        string file_extension = System.IO.Path.GetExtension(excel_copy_filePath);
        //跳出視窗，讓用戶端選擇要儲存的地方                         //使用Server.UrlEncode()編碼中文字才不會下載時，檔名為亂碼
        Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode("Export" + file_extension));
        //設定MIME類型
        Response.ContentType = "application/octet-stream";


        try
        {
            //檔案有各式各樣，所以用BinaryWrite
            Response.BinaryWrite(file);
        }
        catch (Exception ex)
        {

            Response.Write("檔案輸出有誤，您可以在瀏覽器的URL網址貼上以下路徑嘗試看看。<br/>檔案路徑：" + excel_copy_filePath + "<br/>錯誤訊息：" + ex.ToString());
            return;
        }

        Response.End();
    }

    /***Copy End***/

}

如果每次修放資料都沒儲存變更的話，按下按鈕後會跳出以下訊息

跳出視窗叫人儲存