net core3.1使用 EPPlus 导出 Excel 表格

文章目录

• • 一. 直接看代码：
  • 二.效果图如下：
  • 三. EPPlus 详细信息
  • 参考文献

---

一. 直接看代码：

 [HttpGet("excel")]
        public async Task<IActionResult> GetExcelAsync(string productid, int month)
        {
            // 把数据找出来
            Guid productId = Guid.Parse(productid);
            Models.BatchDto rel1 = await _partRep.GetPartDetailAsync(productId, month);
            List<BatchLists> date1 = rel1.batchLists;


            if (date1 == null)
            {
                throw new ArgumentNullException("date1 的数据是空");
            }

            // 写一个 excelpackage
            // 下边这一行不知道是做什么用的，但是删了就有问题。
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;

            using var ep = new ExcelPackage();

            // 添加 sheet
            using var worksheet = ep.Workbook.Worksheets.Add("导出数据测试55555");
            int x = 1;
            int y = 1;

            var columnTitles = new List<string>()
                       {  "零件名","月初数","月末数","月计划完成数","月实际完成数","月进度",
           "批次名","批次计划数","批次计划完成时间","批次实际完成数","批次实际完成时间","工序数","总工序数","批次进度","备注"
                        };

            foreach (var columnTitle in columnTitles)
            {
                // 设置样式
                var cell = worksheet.Cells[x, y++];
                cell.Style.Font.Bold = true;
                cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cell.Value = columnTitle;
            }

            foreach (var item in date1)
            {
                x++;
                y = 1;
                var cell = worksheet.Cells[x, y++];
                cell.Value = item.PartName;
                cell = worksheet.Cells[x, y++];
                cell.Value = item.MonthBeginNumber as int?;
                cell = worksheet.Cells[x, y++];
                cell.Value = item.MonthEndNumber as int?;
                cell = worksheet.Cells[x, y++];
                cell.Value = item.MonthPlan as int?;
                cell = worksheet.Cells[x, y++];
                cell.Value = item.MonthActual as int?;
                cell = worksheet.Cells[x, y++];
                cell.Value = item.MonthProgress as double?;
                cell = worksheet.Cells[x, y++];
                cell.Value = item.BatchName;
                cell = worksheet.Cells[x, y++];
                cell.Value = item.BatchPlanNumber as int?;

                cell = worksheet.Cells[x, y++];
                cell.Style.Numberformat.Format = "mm-dd-yy";
                cell.Value = item.BatchPlanTime as DateTime?;


                cell = worksheet.Cells[x, y++];
                cell.Value = item.BatchActualNumber as int?;
                cell = worksheet.Cells[x, y++];
                cell.Style.Numberformat.Format = "mm-dd-yy";
                cell.Value = item.BatchActualTime as DateTime?;

                cell = worksheet.Cells[x, y++];
                cell.Value = item.Procedure as int?;

                cell = worksheet.Cells[x, y++];
                cell.Value = item.ProcedureNumber as int?;

                cell = worksheet.Cells[x, y++];
                cell.Value = item.BatchProgress as double?;

                cell = worksheet.Cells[x, y++];
                cell.Value = item.Note;
            }

            using (var stream = new MemoryStream())
            {
                ep.SaveAs(stream);
                return new FileContentResult(stream.ToArray(), 
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    FileDownloadName = "导出数据测试.xlsx"
                };
            }

二.效果图如下：

---

三. EPPlus 详细信息

如果想查看 EPPlus 的更详细信息，更多功能，建议直接查看官网https://github.com/EPPlusSoftware/EPPlus.Sample.NetCore

参考文献

[1] https://github.com/EPPlusSoftware/EPPlus.Sample.NetCore
[2] https://www.cnblogs.com/ouqi/p/13633362.html#4803693