使用webapi在excel中导出数据

Question

我们正在开发 web api，从中可以下载 excel 和数据。在网上搜索时，我们发现了诸如 npoi、epplus、closedxml 之类的库。

我们真的需要使用这些库来处理 excel 还是采用标准方法？

我们正在使用 asp.net core 进行 web api 开发。

编辑：基本上我们的前端是从我们暴露 web api 的角度 5。在 web api 中，我们编写了获取数据的逻辑，在获取数据后，我们需要以提供的特定格式/模板（单元格、列明智、表格明智等）放置。我们需要在excel中导出相当多的行。

我们的数据库和 API 也是基于 azure 的。

对此的任何帮助表示赞赏！

Answer 1

我使用过 epplus，我认为它适用于这种情况。让我给你举个例子。导出数据private ExcelPackage CreateDoc(string title, string subject, string keyword)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; var p = new ExcelPackage();&nbsp; &nbsp; &nbsp; &nbsp; p.Workbook.Properties.Title = title;&nbsp; &nbsp; &nbsp; &nbsp; p.Workbook.Properties.Author = "Application Name";&nbsp; &nbsp; &nbsp; &nbsp; p.Workbook.Properties.Subject = subject;&nbsp; &nbsp; &nbsp; &nbsp; p.Workbook.Properties.Keywords = keyword;&nbsp; &nbsp; &nbsp; &nbsp; return p;&nbsp; &nbsp; }public ExcelPackage getApplicantsStatistics()&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; ExcelPackage p = CreateDoc("Applicant Statistics", "Applicant statistics", "All Applicants");&nbsp; &nbsp; &nbsp; &nbsp; var worksheet = p.Workbook.Worksheets.Add("Applicant Statistics");&nbsp; &nbsp; &nbsp; &nbsp; //Add Report Header&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[1, 1].Value = "Applicant Statistics";&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[1, 1, 1, 3].Merge = true;&nbsp; &nbsp; &nbsp; //Get the data you want to send to the excel file&nbsp; &nbsp; &nbsp; &nbsp; var appProg = _unitOfWork.ApplicantsProgram&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .AllIncluding(pr => pr.Program1)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .GroupBy(ap => ap.Program1.Name)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(ap => new { programName = ap.Key, TotalNum = ap.Count() })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList();&nbsp; &nbsp; &nbsp; &nbsp; //First add the headers&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[2, 1].Value = "SR No";&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[2, 2].Value = "Program";&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[2, 3].Value = "No. of Applicants";&nbsp; &nbsp; &nbsp; &nbsp; //Add values&nbsp; &nbsp; &nbsp; &nbsp; var numberformat = "#,##0";&nbsp; &nbsp; &nbsp; &nbsp; var dataCellStyleName = "TableNumber";&nbsp; &nbsp; &nbsp; &nbsp; var numStyle = p.Workbook.Styles.CreateNamedStyle(dataCellStyleName);&nbsp; &nbsp; &nbsp; &nbsp; numStyle.Style.Numberformat.Format = numberformat;&nbsp; &nbsp; &nbsp; &nbsp; for (int i = 0; i < appProg.Count; i++)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[i + 3, 1].Value = i + 1;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[i + 3, 2].Value = appProg[i].programName;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[i + 3, 3].Value = appProg[i].TotalNum;&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // Add to table / Add summary row&nbsp; &nbsp; &nbsp; &nbsp; var rowEnd = appProg.Count + 2;&nbsp; &nbsp; &nbsp; &nbsp; var tbl = worksheet.Tables.Add(new ExcelAddressBase(fromRow: 2, fromCol: 1, toRow: rowEnd, toColumn: 3), "Applicants");&nbsp; &nbsp; &nbsp; &nbsp; tbl.ShowHeader = true;&nbsp; &nbsp; &nbsp; &nbsp; tbl.TableStyle = TableStyles.Dark9;&nbsp; &nbsp; &nbsp; &nbsp; tbl.ShowTotal = true;&nbsp; &nbsp; &nbsp; &nbsp; tbl.Columns[2].DataCellStyleName = dataCellStyleName;&nbsp; &nbsp; &nbsp; &nbsp; tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[rowEnd, 3].Style.Numberformat.Format = numberformat;&nbsp; &nbsp; &nbsp; &nbsp; // AutoFitColumns&nbsp; &nbsp; &nbsp; &nbsp; worksheet.Cells[2, 1, rowEnd, 3].AutoFitColumns();&nbsp; &nbsp; &nbsp; &nbsp; return p;&nbsp; &nbsp; }返回的 ExcelPackage 对象可以作为下载发送到带有 MVC 的文件byte[] reportBytes;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; using (var package = _excelRep.getApplicantsStatistics())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; reportBytes = package.GetAsByteArray();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return File(reportBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);

Answer 2

有几个很好的库可以这样做，我最喜欢的是微软的 EPPlus 和 OpenXMLhttps://github.com/JanKallman/EPPlushttps://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk您的数据库和前端没有太大区别，因为一切都由后端组织。