When trying to load Excel file with Workbook.Load, I get exception if the excel file have Conditional Formatting rule that has formula. The exception is Infragistics.Documents.Excel.FormulaParseException.
Here is the StackTrace:
at Infragistics.Documents.Excel.ConditionalFormatting.ConditionBase.ParseSingleTargetFormula(String formula, Workbook workbook, Nullable`1 cellReferenceMode, FormulaType formulaType, Int32 sourceRowIndex, Int16 sourceColumnIndex) at Infragistics.Documents.Excel.ConditionalFormatting.ConditionBase.ParseSingleTargetFormula(String formula, Nullable`1 cellReferenceMode) at Infragistics.Documents.Excel.ConditionalFormatting.OperatorConditionalFormat.SetOperand1Formula(String formula, Nullable`1 cellReferenceMode) at Infragistics.Documents.Excel.Serialization.Excel2007.ConditionFactory_XLSX.CreateOperatorConditionalFormat(ConditionalFormatInfo cfi, CfRuleInfo rule) at Infragistics.Documents.Excel.Serialization.Excel2007.ConditionFactory_XLSX.Create(ConditionalFormatInfo cfi, CfRuleInfo rule) at Infragistics.Documents.Excel.Serialization.Excel2007.ConditionalFormatInfo.Commit() at Infragistics.Documents.Excel.Serialization.Excel2007.XLSX.PartManagers.ObjectModelWorksheetPartManager.LoadWorksheetEndElement(XmlPartManagerBase partManager) at Infragistics.Documents.OfficeOpenXml.Core.XmlReadManager.LoadChildElementsHelper(Int32 startStackDepth, Int32 currentChildIndex, AlternateContentStatus alternateContentStatus, Boolean isContinuation, Boolean justPopped) at Infragistics.Documents.OfficeOpenXml.Core.XmlReadManager.<>c__DisplayClass19_0.<LoadChildElementsHelper>b__6() at Infragistics.Documents.Core.WorkItem.WorkItemAsync.ExecuteCore(WorkItemScheduler scheduler) at Infragistics.Documents.Core.WorkItem.Execute(WorkItemScheduler scheduler) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at TryThrowExceptionDispatchInfo(Object ) at Infragistics.Documents.Core.WorkItem.ExceptionInfo.Rethrow() at Infragistics.Documents.Core.WorkItem.RethrowIfExceptional() at Infragistics.Documents.Core.WorkItemExtensions.GetResult[TResult](WorkItem`1 this) at Infragistics.Documents.Core.Async.<>c__DisplayClass10_0`1.<Try>b__0(WorkItem`1 t) at Infragistics.Documents.Core.WorkItemExtensions.<>c__DisplayClass5_0`1.<Then>b__0() at Infragistics.Documents.Core.WorkItem.WorkItemSync.ExecuteCore(WorkItemScheduler scheduler) at Infragistics.Documents.Core.WorkItem.Execute(WorkItemScheduler scheduler) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at TryThrowExceptionDispatchInfo(Object ) at Infragistics.Documents.Core.WorkItem.ExceptionInfo.Rethrow() at Infragistics.Documents.Core.WorkItem.RethrowIfExceptional() at Infragistics.Documents.Core.WorkItemExtensions.GetResult[TResult](WorkItem`1 this) at Infragistics.Documents.OfficeOpenXml.Core.OfficeDocumentManager.<>c__DisplayClass66_0.<LoadPart>b__4(WorkItem`1 t2) at Infragistics.Documents.Core.WorkItemExtensions.<>c__DisplayClass5_0`1.<Then>b__0() at Infragistics.Documents.Core.WorkItem.WorkItemSync.ExecuteCore(WorkItemScheduler scheduler) at Infragistics.Documents.Core.WorkItem.Execute(WorkItemScheduler scheduler) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at TryThrowExceptionDispatchInfo(Object ) at Infragistics.Documents.Core.WorkItem.ExceptionInfo.Rethrow() at Infragistics.Documents.Core.WorkItem.RethrowIfExceptional() at Infragistics.Documents.Core.WorkItemExtensions.GetResult[TResult](WorkItem`1 this) at Infragistics.Documents.OfficeOpenXml.Core.OfficeDocumentManager.<LoadCore>b__51_0(WorkItem`1 t) at Infragistics.Documents.Core.WorkItemExtensions.<>c__DisplayClass8_0`1.<Then>b__0() at Infragistics.Documents.Core.WorkItem.WorkItemAsync.ExecuteCore(WorkItemScheduler scheduler) at Infragistics.Documents.Core.WorkItem.Execute(WorkItemScheduler scheduler) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at TryThrowExceptionDispatchInfo(Object ) at Infragistics.Documents.Core.WorkItem.ExceptionInfo.Rethrow() at Infragistics.Documents.Core.WorkItem.RethrowIfExceptional() at Infragistics.Documents.Core.WorkItem.RunNextWorkItem(WorkItemScheduler scheduler) at Infragistics.Documents.Core.WorkItem.Execute(WorkItemScheduler scheduler) at Infragistics.Documents.Core.WorkItemScheduler.SynchronousImpl.Execute(WorkItem workItem) at Infragistics.Documents.Core.WorkItemExtensions.GetResult[TResult](WorkItem`1 this) at Infragistics.Documents.Excel.Workbook.Load(Stream stream, WorkbookLoadOptions loadOptions)
The rule is something like this:
This is happening with the latest version 2018.1.
The file that I am trying to open has other rules, which seem to work fine, but the one that contains formula throws.
It would be nice addition the framework if there are option (static or per file load) to control whether some features of the workbook should be included and parsed when loading files. With some of the older version I had a problem, which was resolved with updating to the latest version, but then a new problem is introduced.
Hello Daniel,
I have been investigating into the behavior you are reporting, and I have put together an Excel file with a formula and conditional formatting rules on the data column that has that formula, but I cannot seem to reproduce the exception you are seeing when loading with the Workbook.Load method. This leads me to believe that there might be something specific about your scenario that I am missing, or perhaps something specific about the workbook.
I have attached the sample project along with the Workbook I used to test this. Please test this project on your PC; whether or not it works correctly may help indicate the nature of this problem.
If the project does not work correctly, this indicates either a problem possibly specific to your environment, or a difference in the DLL versions we are using. My test was performed using version 18.1.20181.82 in Infragistics for WPF 2018.1.
If the project does show the product feature working correctly, this indicates a possible problem in the code of your application. It will help if you can provide a small, isolated sample application that demonstrates the behavior you are seeing.
Or, if this sample project is not an accurate demonstration of what you're trying to do, please feel free to modify it and send it back, or send a small sample project of your own if you have one.
Please let me know if you have any other questions or concerns on this matter.
ExcelFormulaFormatTest.zip
Hello Andrew,
Thanks for your time and effort. I was able to check that your excel file works just fine, which leads me to some specifics of the excel file. Sadly I cannot share it with you, but will try to isolate an Excel file that represents the exception I am experiencing. The assembly versions I am using are the same as yours.
Best Regards,
Daniel Tsviatkov
Thank you for your update on this matter. I will continue to monitor this forum thread while I await an update from your end containing an Excel file that reproduces this exception that you are seeing.