Receiving error using EPPlus Sheet.PrinterSettings.RepeatColumns

Question

I am working with EPPlus in VB.Net.

I have some code that copies data between two Excel documents, then cleans up that data. Everything works fine until I attempt to set Column A of the destination document to repeat on all pages.

I have this code:

Dim destFileInfo As FileInfo = New FileInfo(DestFile)
Using dstPackage As ExcelPackage = new ExcelPackage(destFileInfo)
   Dim SNRSheet As ExcelWorksheet 
   '[...snipped code to copy data between documents...]
   SNRSheet.deleteColumn(StageCol, LastColNum+2)
   SNRSheet.PrinterSettings.RepeatColumns = SNRSheet.Cells("A:A")
   dstPackage.Save()
End using

Everything (copying data between documents, deleting unneeded columns, etc.) works, except the RepeatColumns call.

This throws:

Row cannot be less than 1. Parameter name: value

The stack trace says:

at OfficeOpenXml.ExcelCellAddress.set_Row(Int32 value) at OfficeOpenXml.ExcelPrinterSettings.get_RepeatRows() at OfficeOpenXml.ExcelPrinterSettings.set_RepeatColumns(ExcelAddress value) at ExcelHandler.cExcel... cExcel.vb:line 718

Line 718 is the RepeatColumns line. If I comment out that line, the code works without error.

I also tried

SNRSheet.PrinterSettings.RepeatColumns = New ExcelAddress("SNR Calcs!A:A")

and

Dim Range1 As ExcelRange = SNRSheet.Cells("A:A")
SNRSheet.PrinterSettings.RepeatColumns = Range1

but I get the same error.

How do I correctly indicate that this column should repeat on all pages?

Answer 1

Interesting. I've managed to reproduce the problem, but it only seems to occur when the destination document ALREADY EXISTS.

Although the Exception is thrown when setting the value of the RepeatColumns property, it seems to be caused by the call to DeleteColumn(). Comment that line out and the exception is not thrown.

Example:

Dim file = New FileInfo(filePath)
If file.Exists Then
    file.Delete()
End If

Using p = New ExcelPackage(file)
    Dim wb = p.Workbook
    Dim ws = If(wb.Worksheets.FirstOrDefault(), wb.Worksheets.Add("Sheet1"))

    For r As var = 1 To 20
        For c As var = 1 To 20
            ws.Cells(r, c).Value = String.Format("{0}-{1}", r, c)
        Next
    Next

    ws.DeleteColumn(1, 2)
    ws.PrinterSettings.RepeatColumns = ws.Cells("A:A")
    p.Save()
End Using

Congrats - you may have found a bug in EPPlus.

In the mean-time, I'd suggest the following as viable work-arounds:

1. Ensure that your destination document does not already exist on disk before saving the updated version

OR

2. Re-work your code so that you don't have to call DeleteColumn