.NET EPPlus OpenXml - Repeat Excel row on every page

Question

I'm trying to maintain a row (header fields) on every Excel page. I'm using OpenXML contained in the EPPlus libraries.

I've tried following line of code:

myWorkSheet.PrinterSettings.RepeatRows = New ExcelAddress(String.Format("${0}:${0}", firstRowNummerExcel.ToString()))

This doesn't error or crash on me, but just doesn't do anything in particular...

The PrinterSettings object does seem to work, as executing following line of code works:

worksheetVertrokkenRunderen.PrinterSettings.ShowHeaders = true

This makes me think that perhaps the inserted ExcelAddress is incorrect?

Answer 1

I think I found my own answer by setting a recurring row in Excel and then reading that document into memory and extra what is in the RepeatRows property: Apparently, you must add the name of the worksheet as well...

myWorkSheet.PrinterSettings.RepeatRows = New ExcelAddress(String.Format("'{1}'!${0}:${0}", firstRowNummerExcel, myWorkSheetName))

This works :-)

Answer 2

There's currently a bug in EPPlus where setting both the RepeatColumns and RepeatRows at the same time causes an invalid reference to get created and is ignored by Excel.

The workaround is to add the following Named range manually. (Which is exactly the same as EPPlus is trying to do.)

worksheet.Names.AddFormula("_xlnm.Print_Titles", $"'{worksheet.Name}'!$A:$B,'{worksheet.Name}'!$1:$4");