[C#] Aspose後端透過API回傳Excel

  • 2320
  • 0
  • 2018-07-24

後端做一個response,讓API回傳檔案

程式碼範例為Excel,而Word方法也差不多,只是插入資料的方式有些不同

先取得Aspose license!!

然後到Aspose下載套件,或是直接到NuGet抓

大概流程為

1. 先把把資料從database抓來
2. 用aspose開啟空白樣板的excel檔
3. 跑迴圈塞資料到excel裡
4. 把塞完資料的excel轉成Memory stream存在記憶體裡
5. 最後這個memory stream附加在封包中return

Excel範例:

using System.net.Http; //response
using Aspose.Cells; //Excel
using System.Web.Hosting; //server path
using System.IO.File; //memory stream

[HttpGet]
public HttpResponseMessage DownloadSomething(string Index = '')
{
    //範本檔路徑
    string DemoFilePath = HostingEnviroment.MapPath("~/Templates/DemoFile.xlsx");

    //開啟Aspose
    Aspose.Cells.License lc = new Aspose.Cells.License();
    string LicensFilePath = HostingEnvironent.MapPath("~/Aspose.Total.lic");
    lc.SetLicense(LicensFilePath);

    //開啟範本檔
    if(File.Exists(DemoFilePath))
    {
        Workbook document = new Workbook(DemoFilePath);
        Woksheet ws = document.Worksheets[0];
        
        //取代字串方式
        ws.Replace("@Title@", "理查德筆記");
        
        //輸入資料
        int row = 0;
        string InsertString = "理查德筆記";
        ws.Cells[row, 1].Value = InsertString.TrimEnd('');
        ws.Cells.Rows[row].Height = 30; //列高
        
        //複製列
        ws.Cells.CopyRow(ws.Cells, 1, 2); //複製列1至列2

        //存到記憶體
        MemoryStream ms = new MemoryStream();
        document.Save(ms, Aspose.Cells.SaveFormat.Xlsx);
        ms.Position = 0; //Rewind the stream position back to zero, so it's ready for next reader;
        
        //回傳封包
        HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = new StreamContent(ms);
        response.Content.Header.ContentDisposition = new System.net.Http.Headers.ContentDispositionHeaderValue("attachment");
        response.Content.Header.ContentDisposition.FileName = "理查德筆記.xlsx";
        response.Content.Header.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/vnd.ms-excel");

        return response;        
    }
    return new HttpResponseMessage(HttpStatusCode.NotFound);
}

 

Word範例:

using System.net.Http; //response
using Aspose.Words; //Word
using System.Web.Hosting; //server path
using System.IO.File; //memory stream

[HttpGet]
public HttpResponseMessage DownloadSomething(string Index = '')
{
    //範本檔路徑
    string DemoFilePath = HostingEnviroment.MapPath("~/Templates/DemoFile.docx");

    //開啟Aspose
    Aspose.Words.License lc = new Aspose.Words.License();
    string LicensFilePath = HostingEnvironent.MapPath("~/Aspose.Total.lic");
    lc.SetLicense(LicensFilePath);

    //開啟範本檔
    if(File.Exists(DemoFilePath))
    {
        Document document = new Document(DemoFilePath);
        DocumentBuilder builder = new DocumentBuilder(document); //游標
        
        //抓取文章裡面table
        NodeCollection AllTable = document.GetChildNodes(NodeType.Table, true); 
        Table TargetTable = (Table)AllTable[0];

        //取代字串方式
        document.Range.Replace("@Title@", "理查德筆記", false, false);
        
        //輸入資料
        int row = 0;
        string InsertString = "理查德筆記";
        builder.MoveTo(TargerTable.Rows[0].Cells[0].FirstParagraph);
        builder.Write(InsertString);
        
        //複製Table列
        TargetTable.Rows.Insert(1, TargetTable.Rows[2].Clone(true));//複製第2列插入至第1列

        //合併儲存格(垂直合併1~2列第1行,跳過3再合併4~5列第1行)
        TargerTable.Rows[0].Cells[0].CellFormat.VerticalMerge = CellMerge.First;
        TargerTable.Rows[0].Cells[0].CellFormat.VerticalMerge = CellMerge.Previous;
        TargerTable.Rows[0].Cells[0].CellFormat.VerticalMerge = CellMerge.None;
        TargerTable.Rows[0].Cells[0].CellFormat.VerticalMerge = CellMerge.First;
        TargerTable.Rows[0].Cells[0].CellFormat.VerticalMerge = CellMerge.Previous;
        
        
        //存到記憶體
        MemoryStream ms = new MemoryStream();
        document.Save(ms, Aspose.Words.SaveFormat.Docx);
        ms.Position = 0; //Rewind the stream position back to zero, so it's ready for next reader;
        
        //回傳封包
        HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = new StreamContent(ms);
        response.Content.Header.ContentDisposition = new System.net.Http.Headers.ContentDispositionHeaderValue("attachment");
        response.Content.Header.ContentDisposition.FileName = "理查德筆記.docx";
        response.Content.Header.ContentType = new System.Net.Http.Headers.MeadiaTypeHeaderValue("application/vnd.ms-word");
        //ContentType也可是application/vnd.openxmlformats-officedocument.wordprocessingml.document 

        return response;        
    }
    return new HttpResponseMessage(HttpStatusCode.NotFound);
}