2014年6月27日 星期五

ASP.NET MVC 匯出 Excel 簡單做 - 使用 ClosedXML

經歷了前兩篇的「匯入 Excel 簡單做」之後,有了匯入就會有匯出的功能,匯出 Excel 的功能可以使用很多方式來完成,例如直接使用 Open XML SDK、NPOI、EPPlus 或是一些需付費的第三方套件,這邊我會建議,如果專案預算允許的情況下,在穩定性以及日後維護的考量,可以考慮購買需付費的第三方套件。

這一篇文章的範例會延續前兩篇匯入 Excel 的內容,所以建議各位先看過這兩篇文章:

ASP.NET MVC 匯入 Excel 簡單做 - Part.1 檔案上傳

ASP.NET MVC 匯入 Excel 簡單做 - Part.2 匯入資料

 


這邊會使用 ClosedXML 來完成匯出 Excel 功能,有關 ClosedXML 的說明與使用,請參閱以下連結:

ClosedXML - The easy way to OpenXML – CodePlex

令人驚豔的Excel程式庫 - ClosedXML - 黑暗執行緒

ClosedXML 在 CodePlex 上的說明文件與範例還蠻清楚的,所以想要進一步了解的朋友就要好好地閱讀文件,

http://closedxml.codeplex.com/documentation

 

前端處理

先在 Index.cshtml 上面增加一個 Button,當按下匯出的 Button 後,會先去檢查資料表內是否有資料,有資料的話就開始進行資料匯出 Excle 的處理。

image

位於 Index.cshtml 下方的 Javascript 程式入口,增加兩個 Action 方法的 URL 連結,一個是檢查是否有資料,另一個就是匯出 Excel 的處理,

image

project.ZipCode.js

Javascript Code 增加一個 function,這個 function 會先檢查有無資料,無資料就顯示訊息,有資料就進行匯出,

image

image

//================================================================================================
/// <reference path="_references.js" /> Ps.如果你要新增給IntelliSense用的js檔案, 請加在該檔案中
//================================================================================================
 
(function (app) {
    //===========================================================================================
    var current = app.ZipCode = {};
    //===========================================================================================
 
    jQuery.extend(app.ZipCode,
    {
        Initialize: function (actionUrls) {
            /// <summary>
            /// 初始化函式
            /// </summary>
            /// <param name="actionUrls"></param>
 
            jQuery.extend(project.ActionUrls, actionUrls);
 
            //上傳檔案事件處理
            current.UploadEventHandler();
 
            //匯出資料
            $('#ButtonExport').click(function () {
                current.ExportDate();
            });
        },
 
        UploadEventHandler: function () {
            /// <summary>
            /// 上傳匯入資料
            /// </summary>
 
            $("#UploadForm").ajaxForm({
                iframe: true,
                dataType: "json",
                success: function (result) {
                    $("#UploadForm").resetForm();
                    if (!result.Result) {
                        project.AlertErrorMessage("錯誤", result.Msg);
                    }
                    else {
                        $('#ResultContent').html(result.Msg);
                        project.ShowMessageCallback("訊息", "檔案上傳完成, 點選「確認」後開始進行資料匯入", function () {
                            current.ImportData(result.Msg);
                        });
                    }
                },
                error: function (xhr, textStatus, errorThrown) {
                    $("#UploadForm").resetForm();
                    project.AlertErrorMessage("錯誤", "檔案上傳錯誤");
                }
            });
        },
 
        ImportData: function (savedFileName) {
            /// <summary>
            /// 資料匯入
            /// </summary>
            /// <param name="mainID"></param>
 
            $.ajax({
                type: 'post',
                url: project.ActionUrls.Import,
                data: { savedFileName: savedFileName },
                async: false,
                cache: false,
                dataType: 'json',
                success: function (data) {
                    if (data.Msg) {
                        project.AlertErrorMessage("錯誤", data.Msg);
                        $('#UploadModal').modal('hide');
                    }
                    else {
                        project.ShowMessageCallback("訊息", "匯入完成", function () {
                            $('#UploadModal').modal('hide');
                            window.location.reload();
                        });
                    }
                },
                error: function () {
                    project.AlertErrorMessage("錯誤", "資料匯入發生錯誤");
                    $('#UploadModal').modal('hide');
                }
            });
        },
 
        ExportDate: function () {
            /// <summary>
            /// 資料匯出
            /// </summary>
 
            $.ajax({
                type: 'post',
                url: project.ActionUrls.HasData,
                dataType: 'json',
                cache: false,
                async: false,
                success: function (data) {
                    if (data.Msg) {
                        current.HasData = data.Msg;
                        if (current.HasData == 'False') {
                            project.AlertErrorMessage("錯誤", "尚未建立任何資料, 無法匯出資料.");
                        }
                        else {
                            window.location = project.ActionUrls.ExportData;
                        }
                    }
                },
                error: function (xhr, textStatus, errorThrown) {
                    $("#UploadForm").resetForm();
                    project.AlertErrorMessage("錯誤", "資料匯出錯誤");
                }
            });
        }
 
    });
})
(project);

 

後端處理

先透過 NuGet 安裝 ClosedXML 套件

image

 

ExportExcelResult.cs

在 Infrastructure 目錄下另外建立 CustomResults 目錄,並且在這個目錄裡新增 ExportExcelResult.cs,

image

這個 ExportExcelResult 是繼承 ActionResult 類別,它要做的事情就是將取得的資料做轉換然後匯出 Excel 檔案,會做成 Custom ActionResult 的方式是因為考量之後系統裡會有多個資料匯出 Excel 的需求,如果每增加一次匯出的功能然後就要再重複一次相同的程式處理,這樣的作法並不恰當,另外一個考量的因素將匯出 Excel 的程式從原本的 Action 方法裡抽出來,讓 Action 方法的程式流程可以單純一點,這在等一下的匯出 Action 方法裡可以看到。

using ClosedXML.Excel;
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Mvc;
 
namespace BlogSample.Infrastructure.CustomResults
{
    public class ExportExcelResult : ActionResult
    {
        public string SheetName { get; set; }
        public string FileName { get; set; }
        public DataTable ExportData { get; set; }
 
        public ExportExcelResult()
        {
 
        }
 
        public override void ExecuteResult(ControllerContext context)
        {
            if (ExportData == null)
            {
                throw new InvalidDataException("ExportData");
            }
            if (string.IsNullOrWhiteSpace(this.SheetName))
            {
                this.SheetName = "Sheet1";
            }
            if (string.IsNullOrWhiteSpace(this.FileName))
            {
                this.FileName = string.Concat(
                    "ExportData_",
                    DateTime.Now.ToString("yyyyMMddHHmmss"),
                    ".xlsx");
            }
 
            this.ExportExcelEventHandler(context);
        }
 
        /// <summary>
        /// Exports the excel event handler.
        /// </summary>
        /// <param name="context">The context.</param>
        private void ExportExcelEventHandler(ControllerContext context)
        {
            try
            {
                var workbook = new XLWorkbook();
 
                if (this.ExportData != null)
                {
                    context.HttpContext.Response.Clear();
 
                    // 編碼
                    context.HttpContext.Response.ContentEncoding = Encoding.UTF8;
 
                    // 設定網頁ContentType
                    context.HttpContext.Response.ContentType =
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 
                    // 匯出檔名
                    var browser = context.HttpContext.Request.Browser.Browser;
                    var exportFileName = browser.Equals("Firefox", StringComparison.OrdinalIgnoreCase)
                        ? this.FileName
                        : HttpUtility.UrlEncode(this.FileName, Encoding.UTF8);
 
                    context.HttpContext.Response.AddHeader(
                        "Content-Disposition",
                        string.Format("attachment;filename={0}", exportFileName));
 
                    // Add all DataTables in the DataSet as a worksheets
                    workbook.Worksheets.Add(this.ExportData, this.SheetName);
 
                    using (var memoryStream = new MemoryStream())
                    {
                        workbook.SaveAs(memoryStream);
                        memoryStream.WriteTo(context.HttpContext.Response.OutputStream);
                        memoryStream.Close();
                    }
                }
                workbook.Dispose();
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

 

ZipCodeController.cs

HasData

[HttpPost]
public ActionResult HasData()
{
    JObject jo = new JObject();
    bool result = !db.TaiwanZipCodes.Count().Equals(0);
    jo.Add("Msg", result.ToString());
    return Content(JsonConvert.SerializeObject(jo), "application/json");
}

Export

底下的程式要做個說明,Export 這個 Action 方法最後是使用剛才所建立的 ExportExcelResult 回傳,而 ExportExcelResult 要先傳入三個資料,Excel WorkSheet 的名稱、匯出檔案名稱以及要匯出的資料,在 ExportExcelResult 裡使用 ClosedXML 將資料匯出到 Excel,傳入的資料是使用 DataTable 類別,所以有個私有方法 GetExportData() 是用來取得要匯出的資料,經由 EF 取得原始資料後再轉為 JArray,然後再轉為 DataTable,最後丟給 ExportExcelResult 去處理。

public ActionResult Export()
{
    var exportSpource = this.GetExportData();
    var dt = JsonConvert.DeserializeObject<DataTable>(exportSpource.ToString());
 
    var exportFileName = string.Concat(
        "TaiwanZipCode_",
        DateTime.Now.ToString("yyyyMMddHHmmss"),
        ".xlsx");
 
    return new ExportExcelResult
    {
        SheetName = "臺灣郵遞區號",
        FileName = exportFileName,
        ExportData = dt
    };
}
 
private JArray GetExportData()
{
    var query = db.TaiwanZipCodes
                  .OrderBy(x => x.Sequence)
                  .ThenBy(x => x.ID)
                  .ThenBy(x => x.Zip);
 
    JArray jObjects = new JArray();
 
    foreach (var item in query)
    {
        var jo = new JObject();
        jo.Add("ID", item.ID);
        jo.Add("Zip", item.Zip);
        jo.Add("CityName", item.CityName);
        jo.Add("Town", item.Town);
        jo.Add("Sequence", item.Sequence);
        jObjects.Add(jo);
    }
    return jObjects;
}

 

執行結果

一開始沒有任何資料

image

沒有資料可匯出的情況下執行匯出,顯示錯誤訊息

image

接著再匯入資料

image

匯出資料

image

匯出的 Excel 檔案內容

image

 

P.S.
使用 ClosedXML 所匯出的 Excel 檔案在開啟的時候是不會出現格式錯誤的訊息視窗,如下:

SNAGHTML96fda0

上面那個是我們以前開發 ASP.NET WebForms 時,最常將 GridView 的 HTML 轉存為 Excel 檔案後開啟的狀況。

 

這邊做的是簡單的資料匯出 Excel 操作,雖然示範的是單一表格的資料匯出,當然也可以是多張表格的資料匯出,但還是只限於單純的行列資料格式,其實還可以做得比較複雜一些,例如讓使用者自行決定匯出的檔案名稱,或是匯出查詢結果的資料,甚至於讓使用者自行挑選資料欄位做匯出,日後有機會再說明如何做出這些功能。

 

以上

11 則留言:

  1. 作者已經移除這則留言。

    回覆刪除
  2. 適用於.NET、ASP.NET、ASP.NET MVC 匯出Excel和pdf 付費的第三方套件,能否建議哪幾套可供參考,謝謝

    回覆刪除
    回覆
    1. Hello, 你好
      因為每個專案或是開發團隊的需求各有不同,所以我無法直接給你建議,
      你可以到 Visual Studio Gallery 去找尋試用版套件,有很多商用的第三方套件都會提供試用版,
      藉此評估套件是否符合專案的使用,例如 Syncfusion, Telerik, DevExpress, ASPOSE 等等,都是相當多人使用.

      刪除
  3. kevin 你好:
    我按照你的方式實作,目前遇到一個問題,HasData 回傳 true,但是後續 Export 卻失敗,我試過用 Alert 方式秀出資訊,但是仍不知原因在哪,不知你是否遇過這種情形,或者可以提供一些方式,讓我追查原因,謝謝。

    回覆刪除
    回覆
    1. 如果你有看這部落格最上方有一個「範例程式 @ GitHub」然後點進去看,
      你就可以發現到其實我是有把這個系列文章的範例放到 Github 上面的,
      https://github.com/kevintsengtw/MVC-Excel-Import-Export
      把 Source Code 下載後,範例專案是可以編譯過後能夠執行的,其他的自己找原因了,
      因為老實說,按照你的提問內容,我是不會知道你的問題原因是什麼,
      清楚的問題內容往往就已經解決一半問題,
      如果角色互換,我向你提出這樣類似的問題,你能夠僅靠隻字片語就可以幫我找出問題的錯誤原因嗎?

      刪除
    2. 作者已經移除這則留言。

      刪除
    3. Hi Kevin :
      我已經找到問題了,謝謝

      刪除
  4. 作者已經移除這則留言。

    回覆刪除
  5. 作者已經移除這則留言。

    回覆刪除
  6. 請問這個套件是否可以用於產出txt、pdf或無副檔名的檔案
    能否自訂每一欄的欄寬、對齊方式?

    回覆刪除

提醒

千萬不要使用 Google Talk (Hangouts) 或 Facebook 及時通訊與我聯繫、提問,因為會掉訊息甚至我是過了好幾天之後才發現到你曾經傳給我訊息過,請多多使用「詢問與建議」(在左邊,就在左邊),另外比較深入的問題討論,或是有牽涉到你實作程式碼的內容,不適合在留言板裡留言討論,請務必使用「詢問與建議」功能(可以夾帶檔案),謝謝。