1.安装nuget包
2.封装方法
public byte[] ExportToByteArray(IEnumerable<string> headerText, IEnumerable<string[]> dataList, string extraMessage = null) { IWorkbook workbook = Write(headerText, dataList, extraMessage); var memoryStream = new MemoryStream(); workbook.Write(memoryStream); return memoryStream.ToArray(); } private static IWorkbook Write(IEnumerable<string> headerText, IEnumerable<string[]> dataList, string extraMessage = null) { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = wb.CreateSheet("Sheet1"); var rowIndex = 0; if (!string.IsNullOrEmpty(extraMessage)) { SetExcelRowForExtraMessage(sheet, extraMessage); rowIndex += 1; } SetExcelHeaderRow(sheet, rowIndex, headerText.ToList()); rowIndex += 1; if (!dataList.Any()) { return wb; } for (var i = 0; i < dataList.Count(); i++) { SetExcelBodyRow(sheet, rowIndex + i, dataList.ElementAt(i)); } //优化表格的列宽 int maxColumnIndex = dataList.Max(row => row.Count()); for (var i = 0; i < maxColumnIndex; i++) { sheet.AutoSizeColumn(i); } return wb; }
3.Controller调用方法
/// <summary> /// 基金净值下载 /// </summary> [HttpGet] public HttpResponseMessage DownloadExecl() { try { //获取数据 var productList = _productService.GetProductList(); var exportData = ProductList.Select(t => new { t.Date, t.Name }).ToList(); //下载文件 var fileName = $"{DateTime.Today:yyyyMMdd}.xlsx"; var bytes = _exportor.ExportToByteArray(HeaderText, exportData, ""); var response = GetFileResponse(fileName, bytes); return response; } catch (Exception) { return new HttpResponseMessage(System.Net.HttpStatusCode.NoContent); } } private HttpResponseMessage GetFileResponse(string fileName, byte[] bytes) { HttpResponseMessage response = new HttpResponseMessage(System.Net.HttpStatusCode.OK); var mime = System.Web.MimeMapping.GetMimeMapping(fileName); var stream = new MemoryStream(bytes); response.Content = new StreamContent(stream); response.Content = new StreamContent(stream); bool isFirefox = Request.Headers.UserAgent.ToString() .IndexOf("Firefox", StringComparison.CurrentCultureIgnoreCase) > -1; response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue(mime); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = isFirefox ? fileName : HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) }; response.Content.Headers.ContentLength = stream.Length; return response; } /// <summary> /// Excel列头 /// </summary> private static readonly string[] HeaderText = { "时间", "名称" };