Search code examples
c#excelspreadsheetgear

Excel formula is displayed as text upon exporting using SpreadsheetGear

I have a template and I need to populate data into dynamically generated ranges each of which consists of 23 columns. Also I am using data table to populate data into the range.

The formula is also generated dynamically for each column and row:

"=IF(COUNT(" + prefix + "J" + rowNum + ":" + prefix + "L" + rowNum + ")>0," + prefix + "J" + rowNum + "+" + prefix + "K" + rowNum + "+" + prefix + "L" + rowNum + ",\"\")";

The above formula will look like this when value gets applied dynamically:

=IF(COUNT(J12:L12)>0,J12+K12+L12,"")

In the above formula, prefix is the column name i'll determine and replace dynamically and rowNum is the iterated value for each row and that is also determined and replaced.

The problem I face here is that the formula is generated dynamically and placed in required column in excel but placed as a text. If I press 'F2' and then press Enter, the formula gets applied.

Note: I have checked and verified that there is no space before =

Before:

enter image description here

After:

enter image description here

I didn't change anything, I just pressed F2 and went inside the formula and just press Enter key and the formula gets applied

I have tried various options like

R1C1
R1C1local
ShowFormulas
Ctrl + ' issue

Since I have more than 100 columns and infinite rows to calculate, I can't goto each range and set the range as formula and then calculate.

I have applied a macro code OnWorkbookOpen to apply the formula, I just want to know is there any way to implement this with SpreadsheetGear and without macro code.

Solution

  • It would help if you provided the SpreadsheetGear-related routine to see if there are any issues in that code.

    Since you mention using DataTables I am guessing you are using the IRange.CopyFromDataTable(...) method. Given that formulas are being treated as text, I will also guess that you are passing in the SetDataFlags.AllText option.

    If this is the case, the you are seeing the expected behavior for this AllText flag, as all values in your DataTable will be treated as text, including formulas. You would need to stop using the AllText option to prevent this from happening.