我正在使用最新稳定版本的 EPPlus 4.1(也尝试过相同结果的4.5.0.1-beta版本)。
我试图将使用 EPPlus 创建的多个 Excel 文件合并成一个包含多个工作表的单个 Excel 文件。其中一些工作表可能包含条件格式,我设置如下:
我试图将使用 EPPlus 创建的多个 Excel 文件合并成一个包含多个工作表的单个 Excel 文件。其中一些工作表可能包含条件格式,我设置如下:
'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
这两种方法都能产生相同的结果,并且对源文件都可以正常工作。
然后,我通过将工作表复制到新文件中来合并这些文件,如下所示:
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
代码行_pckMaster.Save()
产生了一个错误。在此行执行之前,我在调试器中检查了源文件和目标文件的工作表,看到了以下图片:
源文件:
目标文件:
工作表中的条件格式节点在添加到新文件后会出现异常。如果源文件没有任何条件格式,则将工作簿中的工作表复制到新工作簿并保存该文件的操作可以正常执行。
是否有人遇到过相同的问题?这是一个bug吗?还是我做错了什么?
条件格式 '(New System.Linq.SystemCore_EnumerableDebugView(Of OfficeOpenXml.ExcelWorksheet)(_pckMaster.Workbook.Worksheets).Items(1)).ConditionalFormatting' 引发了类型为 '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()