Npoi操作Excel单元格合并

  public async Task<byte[]> ExportNewReportByQuotationId(Guid quotationId)
    {

        //string sql = @$"select ""Id"" as ModelId,  (q.qq).key as key,(q.qq).value as  myValue  from(with a as( select ""Id"" ,""Customer"" ,""Family"" ,""ModelPN""  from ""Eme_ProductModelMain"" epmm  limit 3) select ""Id"",jsonb_each(row_to_json(a)::jsonb-'qqq'::varchar)as qq from a )q ";
        // eqpm.""QuotationId"" ,eqpm.""ProductId"" ,
        string sql = @$"select epmm.""ModelPN""  from  ""qq_GoodsProductModels"" eqpm  left join ""qq_ProductModelMain"" epmm  on eqpm.""ProductId"" =epmm.""Id""  where eqpm.""QuotationId""='{quotationId}'";
        var listProductPns = await DbScoped.SugarScope.Ado.SqlQueryAsync<string>(sql);
        int modelCount = listProductPns.Count;


        IWorkbook wookbook = new XSSFWorkbook();
        ISheet sheet = wookbook.CreateSheet("IL");
        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;

        ICell cellhead1 = headRow1.CreateCell(0);
        cellhead1.SetCellValue("");
        cellhead1.CellStyle = cellStyleBgColor;
        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
        }
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, modelCount));
        sheet.SetColumnWidth(1, Encoding.UTF8.GetBytes("HC Required(HC Qty)").Length * 256 / 2 + 100);
        sheet.SetColumnWidth(2, Encoding.UTF8.GetBytes("HC Required(HC Qty)").Length * 256 / 2 + 100);


        ICell cellhead_b2 = headRow1.CreateCell(modelCount + 1);
        cellhead_b2.SetCellValue("Rate");
        cellhead_b2.CellStyle = cellStyleBgColor;

        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));

        AssmblyExcelModel(modelCount, sheet, cellStyleBgColor);

        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 void AssmblyExcelModel(int modelCount, ISheet sheet, ICellStyle cellStyleBgColor)
    {
        //到时可以配置出去
        var templateStr = ConnectionJsonDto.ExportILLeftNames.Split(",", StringSplitOptions.RemoveEmptyEntries).ToList();
        for (int R = 0; R < templateStr.Count; R++)
        {
            int dataRow = R + 2;
            IRow row = sheet.CreateRow(dataRow);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue(templateStr[R]);
            cell.CellStyle = cellStyleBgColor;
            for (int p = 0; p < modelCount; p++)
            {
                int pmodelIndex = p + 1;
                ICell cell2 = row.CreateCell(pmodelIndex);
                cell2.SetCellValue("");
                cell2.CellStyle = cellStyleBgColor;
            }

            ICell cell_back2 = row.CreateCell(modelCount + 1);
            cell_back2.SetCellValue("");
            cell_back2.CellStyle = cellStyleBgColor;

            ICell cell_back1 = row.CreateCell(modelCount + 2);
            cell_back1.SetCellValue("");
            cell_back1.CellStyle = cellStyleBgColor;

        }

    }

sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, modelCount));
(开始行，最后一行，开始列，最后一列）