代码之家 › 专栏 › 技术社区 › XamDev

使用webapi在excel中导出数据

asp.net-core excel c#

XamDev · 技术社区 · 6 年前

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

我们正在使用asp.netweb api开发的核心。

基本上我们的前端是在角5从我们的位置

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

2 回复 | 直到 6 年前

Tonto 6 年前

导出数据

private ExcelPackage CreateDoc(string title, string subject, string keyword)
    {
        var p = new ExcelPackage();
        p.Workbook.Properties.Title = title;
        p.Workbook.Properties.Author = "Application Name";
        p.Workbook.Properties.Subject = subject;
        p.Workbook.Properties.Keywords = keyword;
        return p;
    }

public ExcelPackage getApplicantsStatistics()
    {
        ExcelPackage p = CreateDoc("Applicant Statistics", "Applicant statistics", "All Applicants");
        var worksheet = p.Workbook.Worksheets.Add("Applicant Statistics");

        //Add Report Header
        worksheet.Cells[1, 1].Value = "Applicant Statistics";
        worksheet.Cells[1, 1, 1, 3].Merge = true;

      //Get the data you want to send to the excel file
        var appProg = _unitOfWork.ApplicantsProgram
                        .AllIncluding(pr => pr.Program1)
                        .GroupBy(ap => ap.Program1.Name)
                        .Select(ap => new { programName = ap.Key, TotalNum = ap.Count() })
                        .ToList();
        //First add the headers
        worksheet.Cells[2, 1].Value = "SR No";
        worksheet.Cells[2, 2].Value = "Program";
        worksheet.Cells[2, 3].Value = "No. of Applicants";

        //Add values
        var numberformat = "#,##0";
        var dataCellStyleName = "TableNumber";
        var numStyle = p.Workbook.Styles.CreateNamedStyle(dataCellStyleName);
        numStyle.Style.Numberformat.Format = numberformat;

        for (int i = 0; i < appProg.Count; i++)
        {
            worksheet.Cells[i + 3, 1].Value = i + 1;
            worksheet.Cells[i + 3, 2].Value = appProg[i].programName;
            worksheet.Cells[i + 3, 3].Value = appProg[i].TotalNum;
        }
        // Add to table / Add summary row
        var rowEnd = appProg.Count + 2;
        var tbl = worksheet.Tables.Add(new ExcelAddressBase(fromRow: 2, fromCol: 1, toRow: rowEnd, toColumn: 3), "Applicants");
        tbl.ShowHeader = true;
        tbl.TableStyle = TableStyles.Dark9;
        tbl.ShowTotal = true;
        tbl.Columns[2].DataCellStyleName = dataCellStyleName;
        tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;
        worksheet.Cells[rowEnd, 3].Style.Numberformat.Format = numberformat;

        // AutoFitColumns
        worksheet.Cells[2, 1, rowEnd, 3].AutoFitColumns();
        return p;
    }

返回的ExcelPackage对象可以下载到MVC文件中

byte[] reportBytes;
            using (var package = _excelRep.getApplicantsStatistics())
            {
                reportBytes = package.GetAsByteArray();
            }
            return File(reportBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);