.Net core Web API导出数据到Excel

前言

产品经理有一个需求，就是将cosmosDB里的数据，导出到Excel中.

1.新建一个.net core web api controller

添加引用：EPPlus.Core

 Install-Package EPPlus.Core

命名空间：

using OfficeOpenXml;

2.在HomeController里添加Export的方法

这里会遇到几个坑：
第一个是使用HttpResponseMessage的时候，返回的不是一个文件，而是一个HttpResponseMessage 类型的json；
第二个是返回的File的时候，注意要加上stream.position=0；
第三个是将stream保存在内存（小文件），有些虚机不一定有C盘，所以创建物理路径是存在risk，暂存在内存避免crash。要是大文件还是另寻他法，万一out of memory。

这里我们使用IActionResult返回类型。

    [HttpGet]
    [Route("export")]
    public async Task<IActionResult> Export()
    {
        string fileName = $"{Guid.NewGuid().ToString()}.xlsx";

        //store in memory rather than pysical directory
        var stream = new MemoryStream();
        var messages = await ConversationManager.GetConversationMessagesAsync();
        var columns = new ConversationMessage();
        using (ExcelPackage package = new ExcelPackage(stream))
        {
            // add worksheet
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Conversation Message");
            //add head
            worksheet.Cells[1, 1].Value = "From Id";
            worksheet.Cells[1, 2].Value = "To Id";
            worksheet.Cells[1, 3].Value = "Message";
            worksheet.Cells[1, 4].Value = "Time";
            worksheet.Cells[1, 5].Value = "Attachment";
            worksheet.Cells[1, 6].Value = "Conversation Id";

            //add value
            var rowNum = 2; // rowNum 1 is head
            foreach (var message in messages)
            {
                worksheet.Cells["A" + rowNum].Value = message.FromId;
                worksheet.Cells["B" + rowNum].Value = message.ToId;
                worksheet.Cells["C" + rowNum].Value = message.Message;
                worksheet.Cells["D" + rowNum].Value = message.MsgTime;
                worksheet.Cells["E" + rowNum].Value = message.Attachment;
                worksheet.Cells["F" + rowNum].Value = message.ConversationId;
                rowNum++;
            }
            package.Save();
        }
        stream.Position = 0;
        return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
    }

3.说明

最后在浏览器打开api，成功下载：

这里highlighted的部分就是代码中的Worksheets，还可以对表头进行加粗上色之类的，暂时没有这个需求就不深入了。

一开始使用如下方式：

[HttpGet]
public HttpResponseMessage DownloadFile(string fileName)
{
if (!string.IsNullOrEmpty(fileName))
{
	string filePath = "/images/";
	string fullPath = AppDomain.CurrentDomain.BaseDirectory + filePath + "/" + fileName;
	if (File.Exists(fullPath))
	{

		HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
		var fileStream = new FileStream(fullPath, FileMode.Open);
		response.Content = new StreamContent(fileStream);
		response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
                    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
		response.Content.Headers.ContentDisposition.FileName = fileName;
		return response;
	}
}

return new HttpResponseMessage(HttpStatusCode.NotFound);
}