CM_Dayton CM_Dayton - 3 years ago 237 Question

Receiving error using EPPlus Sheet.PrinterSettings.RepeatColumns

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


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 Source

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.


Dim file = New FileInfo(filePath)
If file.Exists Then
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)

    ws.DeleteColumn(1, 2)
    ws.PrinterSettings.RepeatColumns = ws.Cells("A:A")
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


  1. Re-work your code so that you don't have to call DeleteColumn
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download