EPPlus - Error when copying worksheet with conditional formatting

Question

I'm using EPPlus 4.1, last stable version (Also tried 4.5.0.1-beta with same result).

I'm trying to merge several excel files (created using EPPlus), into single excel file with multiple worksheets. Some of worksheets may contain conditional formatting, which I set as below:

'Using columnCells As ExcelRange = workSheetData.Cells(headerLevels + 2, c + 1, workSheetData.Dimension.End.Row + 1, c + 1) 'headerLevels + 2
'    Dim cond1 = columnCells.ConditionalFormatting.AddGreaterThan()
'    cond1.Formula = "0.0"
'    cond1.Style.Fill.BackgroundColor.Color = ColorTranslator.FromHtml("#CBDEB7") 'light green

'    Dim cond2 = columnCells.ConditionalFormatting.AddLessThan()
'    cond2.Formula = "0.0"
'    cond2.Style.Fill.BackgroundColor.Color = ColorTranslator.FromHtml("#F1CDB1") 'light red
'End Using

Dim cfAddress As New ExcelAddress(headerLevels + 2, c + 1, workSheetData.Dimension.End.Row + 1, c + 1)
Dim cfRule1 = workSheetData.ConditionalFormatting.AddGreaterThan(cfAddress)
cfRule1.Formula = "0.0"
cfRule1.Style.Fill.BackgroundColor.Color = ColorTranslator.FromHtml("#CBDEB7") 'light green

Dim cfRule2 = workSheetData.ConditionalFormatting.AddLessThan(cfAddress)
cfRule2.Formula = "0.0"
cfRule2.Style.Fill.BackgroundColor.Color = ColorTranslator.FromHtml("#F1CDB1") 'light red

Both ways produce same result and work fine for the source files.

Then, I'm merging these files by copying worksheets into new file as below

Dim fileInfoMaster As New System.IO.FileInfo(filePath)
Using _pckMaster As New ExcelPackage(fileInfoMaster)

    For i As Integer = 0 To masterWorkbookParams.ListOfFileNames.Count() - 1
        filePath = folderPath & masterWorkbookParams.ListOfFileNames(i) & ".xlsx"
        If System.IO.File.Exists(filePath) Then
            Dim fileInfo As New System.IO.FileInfo(filePath)
            Using _pck As New ExcelPackage(fileInfo)

                Dim NewWorksheet As ExcelWorksheet = _pckMaster.Workbook.Worksheets.Add(masterWorkbookParams.ListOfSheetNames(i), _pck.Workbook.Worksheets(1))

            End Using
        End If
    Next i

    _pckMaster.Save()
End Using

The line _pckMaster.Save() produces an error. When I inspected in the debugger the worksheet of source file and worksheet of destination file before that line executed, I saw the following picture:

Source file:

Destination file:

The ConditionalFormatting node in the worksheet has an exception after we add a copy to the new file. If the source file does not have any conditional formatting, adding a copy of worsheet from one workbook to a new workbook and saving that file work as expected.

Did anyone have the same issue? Is it a bug? or I'm doing something wrong here?

ConditionalFormatting '(New System.Linq.SystemCore_EnumerableDebugView(Of OfficeOpenXml.ExcelWorksheet)(_pckMaster.Workbook.Worksheets).Items(1)).ConditionalFormatting' threw an exception of type 'System.ArgumentOutOfRangeException' OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingCollection {System.ArgumentOutOfRangeException}

StackTrace

System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingRule..ctor(eExcelConditionalFormattingRuleType type, ExcelAddress address, Int32 priority, ExcelWorksheet worksheet, XmlNode itemElementNode, XmlNamespaceManager namespaceManager)
OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingGreaterThan..ctor(ExcelAddress address, Int32 priority, ExcelWorksheet worksheet, XmlNode itemElementNode, XmlNamespaceManager namespaceManager)
OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingRuleFactory.Create(eExcelConditionalFormattingRuleType type, ExcelAddress address, Int32 priority, ExcelWorksheet worksheet, XmlNode itemElementNode)
OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingCollection..ctor(ExcelWorksheet worksheet)
OfficeOpenXml.ExcelWorksheet.get_ConditionalFormatting()

You might want to check that you are not copying duplicated conditional formatting rules — Yahya Hussein
@YahyaHussein Yes, I also tried to set conditional formatting for single cell with only one conditional rule. The result is the same, after this sheet is copied to a new workbook, there is an error on saving that file. — Alexey Polyanichko
@AlexeyPolyanichko Have you found a solution? I am having the same issue now and can't seem to find a way to fix it. — Kevin

J. Metcoff J. Metcoff · Accepted Answer · 2019-07-28T19:06:00

For anyone interested, I have created a correction for this in the latest EPPlus version. See Issue #464 on Github. Also mentioned as issue #266.

EPPlus - Error when copying worksheet with conditional formatting

1 Answers