I'm using the RadSpreadProcessing library to import Excel files, process them, and export back out.
I come across an issue when cells have complex formulas.
These complex formulas have dependencies on other cell values which those values get set (SetValue(...) method) while processing the worksheet after its been imported.
When I try to get the cell value of the complex formula using this call:
cellSelection.GetValue().Value.GetResultValueAsString(cellSelection.GetFormat().Value)
it returns me a value of #NAME?.
When I try to get the value of the complex formula using this call:
cellSelection.GetValue().Value
I noticed a ResultValueType of Error.
But when the workbook is exported back out the cells do not contain #NAME? in them. They contain the correctly auto-calculated values.
In the attached ComplexFormulaIssue.PNG, Row 1 column A value gets set while processing the worksheet using the SetValue(...) method (set it from NX: 12 to 12 for example).
Row 1 Column B formula depends on Row 1 column A value (which works fine since it's an easier formula I guess. No issue here).
Row 1 Column C and D are normal text values.
Row 1 Column E has a complex formula that has dependencies to all the other cell values (this is the one that gives me issues).
I attached 2 other PNGs as examples of formulas I used in Row 1 Columns B and E.
Column B formula:
=IF(LEFT($A1,3)="NX:","", A1)
Column E formula:
=IF(LEFT(A1,3)="NX:","",IF(ISBLANK(A1),"",IF(A1<10,CONCAT(LEFT(C1,3),D1,"00",B1,0),CONCAT(LEFT(C1,3),D1,0,B1,0))))