I'm experiencing an error when trying to add a conditional formula that checks if the value is an Alphabetic character. Below is a snippet demonstrating this issue:
IConditionalFormats condition = sheet.Range["A1:A20"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Equal;
condition1.FirstFormula = "\'r\'"; // ERROR HERE
condition1.FontColor = ExcelKnownColors.Red;
condition1.Color = ExcelKnownColors.Red;
The code will compile fine, however when ran the following unhandled exception is thrown:
Syncfusion.XlsIO.Implementation.Exceptions.ParseException was unhandled
Message="r is not valid named range"
Source="Syncfusion.XlsIO.Base"
StackTrace:
at Syncfusion.XlsIO.Implementation.FormulaParser.CreateLocalName(Int32 iRefIndex, String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options)
at Syncfusion.XlsIO.Implementation.FormulaParser.CreateNamedRange(String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, Int32 iRefIndex)
at Syncfusion.XlsIO.Implementation.FormulaParser.ParseIdentifier(String identifier, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)
at Syncfusion.XlsIO.Implementation.FormulaParser.ParseFirstOperand(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions& options, ParseParameters arguments)
at Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)
at Syncfusion.XlsIO.Implementation.FormulaParser.Parse(String formula, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)
at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Dictionary`2 indexes, Int32 i, Dictionary`2 hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)
at Syncfusion.XlsIO.Implementation.ConditionalFormatImpl.set_FirstFormula(String value)
at Syncfusion.XlsIO.Implementation.ConditionalFormatWrapper.set_FirstFormula(String value)
at ExcelTestApp.Program.Main(String[] args) in C:\temp\ExcelTestApp\ExcelTestApp\ExcelTestApp\Program.cs:line 22
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
I attempted a number of variants for escaping the alphabetic character however nothing was successful.
Attached is a sample solution which demonstrates the issue. A support incident was opened for this (Incident #62049).
ExcelTestApp_54f929f9_3a391471.zip