SetRowsToRepeatAtTop does not work on worksheets which names require escaping

[Bitz]

Do you want to request a feature or report a bug?

• Bug
• Feature

Version of ClosedXML

0.94.2

What is the current behavior?

Using:

ws.PageSetup.SetRowsToRepeatAtTop(1, row);

The values that are expect to be populated in the Page Setup screen are not being populated:

And the behaviour is not as expected.

What is the expected behavior or new feature?

1. The fields in the highlighted field above should be populated.
2. In print view, the data headers should be repeated like so:
   

Did this work in previous versions of our tool? Which versions?

Not sure- first time using ClosedXML

Reproducibility

Code to reproduce problem:

static void Main(string[] args)
{
    File.WriteAllBytes("Document.xlsx", GetReport_Excel());
}

static DataTable GetTable()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    for (int i = 0; i < 400; i++)
    {
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
    }
    return table;
}


public static byte[] GetReport_Excel()
{
    //Generated Datatable
    DataTable dt = GetTable();

    #region Excel Stuff
    var columns = dt.Columns.Count;
    DateTime date = DateTime.Now;

    List<string> locations = new List<string> { "A", "B", "C" };
    
    XLWorkbook wb = new XLWorkbook();
    var ws = wb.Worksheets.Add(date.ToString("MM-dd-yyyy"));
    int row = 1;
    //Build titles
    ws.Cell(row, 1).Value = "All Patient Visits on " + date.ToString("MM-dd-yyyy");
    IXLRange headerRange = ws.Range(row, 1, row, columns).Merge().AddToNamed("Title");
    var headerHight = FormatTitle(ref headerRange, 16);
    row++;

    ws.Cell(row, 1).Value = "Resources: All resources";
    headerRange = ws.Range(row, 1, row, columns).Merge().AddToNamed("Resources");
    FormatTitle(ref headerRange);
    row++;

    ws.Cell(row, 1).Value = "Location: " + string.Join(", ", locations);
    headerRange = ws.Range(row, 1, row, columns).Merge().AddToNamed("Location");
    FormatTitle(ref headerRange);
    row++;

    //Insert data
    ws.Cell(row, 1).InsertTable(dt);


    //Format data table
    var firstTable = ws.Tables.FirstOrDefault();
    if (firstTable != null)
    {
        firstTable.Theme = XLTableTheme.TableStyleMedium18;
        firstTable.CellsUsed().Style.Alignment.WrapText = true;
        //firstTable.Sort("Time ASC, Location ASC");
    }

    //For some reason, these adjust actions do not account for visible overflows with larger text, sow e have to set the height again after the fact.
    //ws.Columns().AdjustToContents();
    //ws.Rows().AdjustToContents();
    ws.Row(1).Height = headerHight;

    ws.PageSetup.SetRowsToRepeatAtTop(1, row);
    ws.PageSetup.PagesWide = 1;
    #endregion

    //We export it as a byte array for users to download.
    using (MemoryStream fs = new MemoryStream())
    {
        wb.SaveAs(fs);
        fs.Position = 0;
        return fs.ToArray();
    }
}

public static int FormatTitle(ref IXLRange cells, int size = 12)
{
    cells.Style.Font.Bold = true;
    cells.Style.Font.FontSize = size;
    double idealHeight = size * 1.3125;
    return (int)Math.Floor(idealHeight);
}

I am aware of the weird conversion to byte[] then back to file process I am using. I ported over code that usually calls for a byte[]. Not the point anyways lol.

• I attached a sample spreadsheet.

No files to be included. Use the above code to generate one.

[Bitz]

Hey,

So I managed to get a workaround, but I do not think this is the standard way of approaching Repeating Rows.

ws.NamedRanges.Add("_xlnm.Print_Titles", $"'{ws.Name}'!$1:${row}");

I wouldn't consider the issue solved because the

ws.PageSetup.SetRowsToRepeatAtTop(1, row);

approach is still not working as expected.

[Pankraty]

The reason to the issue was that your worksheet had name date.ToString("MM-dd-yyyy") and it was not escaped properly:

I will prepare a fix. In the meantime, as a workaround, consider naming a worksheet using letters and digits only. Or, as you said, add a named range manually :)

[Pankraty]

Moreover, ClosedXML does not consider such name worth escaping at all