BUG - row height issues, and changed named region Print_Titles value after generation

[hidegh]

Description:

• We have an unstandard XLSX form (with merged cells, validations, conditional formatting, and repeating title at printing).
• Generating the data is OK
• But there are numerous issues with the output
  • Inside the XLSX | Formulas | Name Manager - the Print_Titles region no more refers to the $2:$4 lines (although if I open the XLSX | Page Layout | Print Titles - i still see $2:$4 (PRint_Areas adjusted just fine)
  • ~~Rows from 64 to 200-ish got hidden (or have 0 height) - whereas row 64 should be part of the print (bottom margin)~~ My mistake: the hidden rows were part of the original template, although not sure if they are pushed down when new rows are inserted to the tables...
  • For some reason, when the 2nd table generation finished, all the rows below started to have extra heights - when I do insert rows in the SAMPLE Excel manually, everything shifts as expected
  • In some cases, despite this setup, if something changed in the XLSX (outside the tables and regions defined for templating) the 2nd table row heights shrunk to 1/2 (like if I'd called auto adjust)...

If then protected for tersting via pwd: 0000

Notes:

• The Named Regions (Contacts/Employees) into which I do generate the data are a 2-row region (entire rows, so original ref. like $46:$47)
• Adding an extra row does not help either, and the output is non-deterministic...and row hiding occurs there too - see last screenshot(s)

--

Output:

Sample XLSX: Annual Questionnaire - TX - SAMPLE.xlsx

Sample app: ClosedXml.Report.Issue01.zip

Extra screenshots:

[hidegh]

Was playing around, and based on the manual approach, the issue is not within XLSX - see updated Program.cs

One thing to note: the odd Print_Title reference issue might be EXCEL or ClosedXML related bug - was able to reproduce with the manual approach (I might have tried NPOI to see if it's ClosedXML or Excel).

// See https://aka.ms/new-console-template for more information
using ClosedXML.Excel;
using ClosedXML.Report;
using System.Diagnostics;

Console.WriteLine("Hello, World!");

TestManual();

static void TestManual()
{

    // Open existing workbook
    using var wb = new XLWorkbook(@".\Annual Questionnaire - TX - SAMPLE.xlsx");

    // Find the named range "Clients"
    var ws = wb.Worksheet(1); // or use wb.Worksheet("SheetName") if known
    var clientsRange = wb.DefinedName("Contacts")?.Ranges.FirstOrDefault()!;

    // Get the first row
    var firstRowNumber = clientsRange.FirstRow().RowNumber();
    var firstRow = ws.Row(firstRowNumber);

    int startingRowNo = firstRow.RowNumber();
    int copyCount = 20;

    // Insert a new blank row **after the first row**, shifting all rows below
    ws.Row(startingRowNo).InsertRowsBelow(copyCount);

    for (int i = 0; i < copyCount; i++)
    {
        var newRow = ws.Row(startingRowNo + i + 1);
        newRow.Height = firstRow.Height;
        firstRow.CopyTo(newRow);
    }

    // Save to MemoryStream
    using var memoryStream = new MemoryStream();
    wb.SaveAs(memoryStream);

    // Save MemoryStream to file
    ExcelHelpers.SaveToTempFileAndOpen(memoryStream);
}

static void TestTemplating()
{
    var template = new XLTemplate(@".\Annual Questionnaire - TX - SAMPLE.xlsx");
    var data = QuestionnaireDataGeneratorDto.GenerateMany(1);
    template.AddVariable(data[0]);
    template.Generate();
    ExcelHelpers.SaveToTempFileAndOpen(template.Workbook);
}


public static class ExcelHelpers
{
    public static void SaveToTempFileAndOpen(MemoryStream memoryStream)
    {
        var outputFileName = $"c:/temp/output-{DateTime.Now:yyyyMMdd-HHmmss}.xlsx";
        File.WriteAllBytes(outputFileName, memoryStream.ToArray());
        Process.Start(new ProcessStartInfo(outputFileName) { UseShellExecute = true });
    }

    public static void SaveToTempFileAndOpen(IXLWorkbook wb)
    {
        using (var memoryStream = new MemoryStream())
        {
            wb.SaveAs(memoryStream);
            SaveToTempFileAndOpen(memoryStream);
        }
    }

}

[hidegh]

Since i map entire rows in the Named Formulas, I'd expect:

1. proper shifting of rows if a template row is added (so that design below does not break)
2. i'd expect new template rows to have same settings applied as the original (incl. height)