[ClosedXML]ASP.Net MVC5 使用ThirdParty來匯出Excel

2017-05-18

3927
0
MVC5
2017-06-29

MVC 5 使用Ajax 和 ClosedXML 來匯出Excel

最近在寫一個練習,被指定要用ClosedXML

又在寫Code的過程中踩了無限多個洞..所以才想說要把這篇文章給生出來..

先說明一下我匯出Excel曾經用過的方式

我想應該有很多種選擇,譬如直接using Microsoft.office.Interop.Excel或者是NPOI或者是EPPlus等等

而我本身在使用NPOI的時候覺得套件的用法不是這麼直覺封裝得太好了

在使用Interop.Excel時感覺跟NPOI差不多,所以在我摸過ClosedXML之前其實是比較愛用EPPlus的

那為什麼ClosedXML好用呢? 因為寫Code的行數少了很多..(好吧我真的很懶)

不過可能是不太熟而且網路上大部分都是提供DataTable的範例

這次的範例是使用Linq的Query結果來將資料帶入到套件中..

結果沒想到卻搞了蠻多天的...Orz..

下面是前端發出Ajax的語法這應該沒甚麼問題

但是成功的時候一定要用window.location.href來接,因為要這樣才會認為是要下載一個檔案

不然你會發現status是200 data是亂碼(其實是excel檔..但瀏覽器把它當文字的樣子),檔案卻沒有下載下來!!

<div>
  <button d="export">匯出</button>
</div>

<script type="text/javascript">
  $("#export").click(function () {
        $.ajax({
            url: "@Url.Action("cusDataExport")",
            success: function (response, textStatus, jqXHR) {
                window.location.href = '@Url.Action("cusDataExport", "客戶資料")';
            },
            error: function (xhr, status, error) {
                alert(error);
            }
        });
    });
</script>

再來就是Action的部分了..

public ActionResult cusDataExport()
{
    //ClosedXML的用法 先new一個Excel Workbook
    using (XLWorkbook wb = new XLWorkbook())
    {
        //取得我要塞入Excel內的資料
        var data = 客戶資料repo.All().Select(c => new { c.客戶名稱, c.電話, c.地址 });

        //一個wrokbook內至少會有一個worksheet,並將資料Insert至這個位於A1這個位置上
        var ws = wb.Worksheets.Add("cusdata", 1);
        //注意官方文件上說明,如果是要塞入Query後的資料該資料一定要變成是data.AsEnumerable()
        //但是我查詢出來的資料剛好是IQueryable ,其中IQueryable有繼承IEnumerable 所以不需要特別寫
        ws.Cell(1, 1).InsertData(data);
             
        //因為是用Query的方式,這個地方要用串流的方式來存檔
        using (MemoryStream memoryStream = new MemoryStream())
        {
            wb.SaveAs(memoryStream);
            //請注意 一定要加入這行,不然Excel會是空檔
            memoryStream.Seek(0, SeekOrigin.Begin);
            //注意Excel的ContentType,是要用這個"application/vnd.ms-excel" 不曉得為什麼網路上有的Excel ContentType超長,xlsx會錯 xls反而不會
            return this.File(memoryStream.ToArray(), "application/vnd.ms-excel", "Download.xlsx");
        }
    }
}

Code本來蠻簡潔有力的加了一堆註解後感覺變得很髒的感覺

那是因為真的太多細節要說了

1.千萬不要加上[HttpPost]這一個ActionFilter

因為在前端Ajax的確是用post的方式發出Request,但是不要忘了在成功的時候

可是用window.location.href來接這時候就是用Get了

2. 在InsertData的時候ws.Cell(1, 1).InsertData(data) Query的結果一定是要AsEnumerable

不然資料是不會複製到worksheet裡面的

3. 一定要加memoryStream.Seek(0, SeekOrigin.Begin)不然檔案會是空檔

4.ContentType很重要

不知道為什麼我在網路上查到xlsx的ContentType是application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

但是實際上用application/vnd.ms-excel就可以了

5.ClosedXML只支援2007以上的Excel版本但是如果在return File的時候Download.xlsx 改成xls 是可以的