使用Npoi简单生成Excel并赋值导出小案例

 public async Task<byte[]> ExportNewReportByQuotationId(Guid quotationId)
    {
        IWorkbook wookbook = new XSSFWorkbook();
        //Engineer Quote Sheet
        await DoEngineerQuoteWork(wookbook, quotationId);

        //IL Sheet
        await DoILSheetWork(wookbook, quotationId);

        string cpath = Directory.GetCurrentDirectory();
        Console.WriteLine("=====================" + cpath);
        string fulpath = Path.Combine(cpath, "wwwroot/myfiles/1.xlsx");
        using var file = File.Create(fulpath);
        wookbook.Write(file);

        MemoryStream stream = new MemoryStream();
        FileStream fileStream = new FileStream(fulpath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
        fileStream.CopyTo(stream);

        byte[] by = stream.ToArray();
        stream.Dispose();
        fileStream.Dispose();
        return by;
    }

 private async Task DoILSheetWork(IWorkbook wookbook, Guid quotationId)
    {

        ISheet sheet = wookbook.CreateSheet("IL");
        var listProductPns = await GetQuotationProductNames(quotationId);
        int modelCount = listProductPns.Count;
        int rowIndex = 0;//第几行
        IRow headRow1 = sheet.CreateRow(rowIndex);
        headRow1.Height = 350;
        ICellStyle cellStyleBgColor = wookbook.CreateCellStyle();
        cellStyleBgColor.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyleBgColor.FillForegroundColor = HSSFColor.Grey25Percent.Index;
        cellStyleBgColor.FillPattern = FillPattern.SolidForeground;
        IFont font = wookbook.CreateFont();
        font.IsBold = true;
        font.FontHeightInPoints = 12;
        cellStyleBgColor.SetFont(font);
        ICellStyle cellStyle = wookbook.CreateCellStyle();
        cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        ICell cellhead1 = headRow1.CreateCell(0);
        cellhead1.SetCellValue("");
        cellhead1.CellStyle = cellStyle;
        for (int mp = 0; mp < modelCount; mp++)
        {
            int mindex = mp + 1;
            ICell cellhead2 = headRow1.CreateCell(mindex);
            cellhead2.CellStyle = cellStyleBgColor;
            cellhead2.SetCellValue("HC Required(HC Qty)");//first input
        }
        int wlong = Encoding.UTF8.GetBytes("HC Required(HC Qty)").Length;
        int woneModelWidth = modelCount <= 1 ? (wlong * 256 + 260) : (wlong * 256 / 2 + 260);
        for (int mw = 0; mw < modelCount; mw++)
        {
            int mwIndex = mw + 1;
            sheet.SetColumnWidth(mwIndex, woneModelWidth);
        }
        ICell cellhead_b2 = headRow1.CreateCell(modelCount + 1);
        cellhead_b2.SetCellValue("Rate");
        cellhead_b2.CellStyle = cellStyleBgColor;
        if (modelCount > 1)
        {
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, modelCount));
        }

        ICell cellhead_b1 = headRow1.CreateCell(modelCount + 2);
        cellhead_b1.SetCellValue("Remark");
        cellhead_b1.CellStyle = cellStyleBgColor;
        rowIndex += 1;
        IRow headRow2 = sheet.CreateRow(rowIndex);
        ICell cell1 = headRow2.CreateCell(0);
        cell1.SetCellValue("HC List");
        cell1.CellStyle = cellStyleBgColor;
        sheet.SetColumnWidth(0, Encoding.UTF8.GetBytes("Materials Supervisors Mfg Tier 1").Length * 256 + 100);
        for (int p = 0; p < modelCount; p++)
        {
            int pindex = p + 1;
            ICell cell2 = headRow2.CreateCell(pindex);
            cell2.SetCellValue(listProductPns[p]);
            cell2.CellStyle = cellStyleBgColor;
        }
        int backIndex = modelCount + 1;
        ICell cellb2 = headRow2.CreateCell(backIndex);
        cellb2.SetCellValue("Rate");
        cellb2.CellStyle = cellStyleBgColor;
        ICell cellb1 = headRow2.CreateCell(backIndex + 1);
        cellb1.SetCellValue("Remark");
        cellb1.CellStyle = cellStyleBgColor;

        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, modelCount + 1, modelCount + 1));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, modelCount + 2, modelCount + 2));

        var datas = await (await _QuotationExportExcelRepostory.GetQueryableAsync()).Where(c => c.QuoTationId == quotationId && c.ExcelType == 0).FirstOrDefaultAsync();
        string jsonStr = datas?.JsonData ?? "";
        AssmblyExcelModel(jsonStr, modelCount, sheet, cellStyle);
    }

 private void AssmblyExcelModel(string jsonStr, int modelCount, ISheet sheet, ICellStyle cellStyle)
    {
        //到时可以配置出去
        var templateStr = ConnectionJsonDto.ExportILLeftNames.Split(",", StringSplitOptions.RemoveEmptyEntries).ToList();
        var ModelDatas = JsonConvert.DeserializeObject<List<ExcelILModelDto>>(jsonStr);
        for (int R = 0; R < templateStr.Count; R++)
        {
            int dataRow = R + 2;
            IRow row = sheet.CreateRow(dataRow);
            row.Height = 350;
            ICell cell = row.CreateCell(0);
            cell.SetCellValue(templateStr[R]);
            cell.CellStyle = cellStyle;

            for (int p = 0; p < modelCount; p++)
            {
                int pmodelIndex = p + 1;
                ICell cell2 = row.CreateCell(pmodelIndex);
                string nowValue = ModelDatas?.Where(c => c.RIndex == R && c.CIndex == pmodelIndex).FirstOrDefault()?.IV ?? "";
                cell2.SetCellValue(nowValue);
                cell2.CellStyle = cellStyle;
            }
            int b2Index = modelCount + 1;
            ICell cell_back2 = row.CreateCell(b2Index);
            string nowValue_b2 = ModelDatas?.Where(c => c.RIndex == R && c.CIndex == b2Index).FirstOrDefault()?.IV ?? "";
            cell_back2.SetCellValue(nowValue_b2);
            cell_back2.CellStyle = cellStyle;

            int b1Index = modelCount + 2;
            ICell cell_back1 = row.CreateCell(b1Index);
            string nowValue_b1 = ModelDatas?.Where(c => c.RIndex == R && c.CIndex == b1Index).FirstOrDefault()?.IV ?? "";
            cell_back1.SetCellValue(nowValue_b1);
            cell_back1.CellStyle = cellStyle;
        }
    }

 [HttpGet("ReportNewQuotation/{QuotationId}")]
    public async Task<ActionResult> ExportNewReportByQuotationId(Guid QuotationId)
    {
        try
        {
            //Quotation主要信息
            var quotationResult = await _quotationconfigAppService.GetByIdAsync(QuotationId);
            if (quotationResult.Data == null || string.IsNullOrEmpty(quotationResult.Data.RfqNo))
                throw new Exception("This Quotation donnot have RfqNo !");
            var by = await _exportReportAppService.ExportNewReportByQuotationId(QuotationId);
            return File(by, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"Quotation_{quotationResult.Data.RfqNo}.xlsx");
        }
        catch (System.Exception ex)
        {
            _loggerService.LogError(ex.Message);
            return Json(new ResponseResult { Code = Jabil.Service.Extension.Customs.Enum.ResultCode.Error, Msg = "error" });
        }
    }