kinanson的技術回憶

前言

紀錄一下如何使用ClosedXml來製作Excel，並使用MemoryStream的方式，最後再使用Web Api Response提供下載，目前ClosedXml也有了Github的連結了，可以直接來Github這邊觀看說明，wiki會是developer的好夥伴(https://github.com/ClosedXML/ClosedXML)

開始動手實做吧

先從nuget下載吧

為了需求我新建兩個類別，一個是投票數的，一個則是投票的IP和裝置紀錄

    public class VoteResult
    {
        public int Id { get; set; }
        public string Option { get; set; }
        public int VoteNumber { get; set; }
    }

    public class IPList
    {
        public string DeviceId { get; set; }
        public string IP { get; set; }
        public string Option { get; set; }

    }

接著就是Web Api的部份

public class ExcelController : ApiController
    {
        public HttpResponseMessage Get()
        {

            var voteResult = new List<VoteResult>
            {
                new VoteResult {Id=1,Option="java",VoteNumber=100 },
                new VoteResult {Id=2,Option="c#",VoteNumber=100 },
                new VoteResult {Id=3,Option="javascript",VoteNumber=120 },
                new VoteResult {Id=4,Option="ruby",VoteNumber=100 },
                new VoteResult {Id=5,Option="python",VoteNumber=100 },
                new VoteResult {Id=6,Option="go",VoteNumber=100 },
            };
          
            var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add("統計結果"); //新增頁籤1
            ws.Cell(1, 1).Value = "選項";
            ws.Cell(1, 2).Value = "得票數(票)";
            ws.Cell(2, 1).Value = voteResult.Select(x => new { Option = x.Option, VoteNumber = x.VoteNumber });

            var ipListResult = new List<IPList>
            {
                new IPList {DeviceId="anson",IP="123.1.2.3",Option="java" },
                new IPList {DeviceId="anson1",IP="123.1.2.4",Option="c#" },
                new IPList {DeviceId="anson2",IP="123.1.2.5",Option="c#" },
                new IPList {DeviceId="anson3",IP="123.1.2.6",Option="javascript" },
                new IPList {DeviceId="anson4",IP="123.1.2.7",Option="javascript" },
                new IPList {DeviceId="anson5",IP="123.1.2.8",Option="ruby" },
                new IPList {DeviceId="anson6",IP="123.1.2.9",Option="go" },
            };

            var ws1 = workbook.Worksheets.Add("IP清單"); //新增頁籤1
            ws1.Cell(1, 1).Value = "Device ID";
            ws1.Cell(1, 2).Value = "IP";
            ws1.Cell(1, 3).Value = "投票選項";
            ws1.Cell(2, 1).Value = ipListResult;
            ws.Columns().AdjustToContents();
            ws1.Columns().AdjustToContents();

            using (MemoryStream memoryStream = new MemoryStream())
            {
                workbook.SaveAs(memoryStream); //把做完的excel放到memoryStream裡
                var response = new HttpResponseMessage(HttpStatusCode.OK);
                response.Content = new ByteArrayContent(memoryStream.ToArray());
                response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                response.Content.Headers.ContentDisposition.FileName = $"test{ DateTime.Now}.xlsx";
                response.Content.Headers.ContentLength = memoryStream.Length; //這行會告知瀏覽器我們檔案的大小
                return response;
            }
        }
    }

結論

這邊其實也非常簡單，稍微紀錄一下，關於CloseXml的部份其實細節也非常多，所以有興趣的可以視需求觀看wiki的部份，去針對excel做任何細調。