0

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.

Community
  • 1
  • 1
Ram Kishore
  • 35
  • 1
  • 7

1 Answers1

1

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.

Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • I appreciate you for the response you have provided with. And to mention, yes I have used IRange.CopyFromDataTable and SetDataFlags.AllText. If I use SetDataFlags.InsertCells then it is insisting me to only use 2 columns. But I have 13 formula columns in each range which consists of 42 columns. Please provide me with a solution for this. – Ram Kishore Aug 04 '17 at 18:42
  • There is no requirement with SetDataFlags.InsertCells to use 2 columns, so I don't know where you are getting this. InsertCells does need *2 rows* (possibly 3 if you also use a header row). These rows should be pre-formatted (i.e., specify styling / NumberFormat / etc.) so that CopyFromDataTable(...) can copy your DataTable contents using those formats into new rows originating from the 2 rows you specify. Have you read the documentation for IRange.CopyFromDataTable(...) and SetDataFlags (see links above)? It should explain all of this in detail. – Tim Andersen Aug 04 '17 at 19:55
  • It worked @Tim Andersen, AllText was causing the issue. And since I was using Guid, it caused trouble in data table when I changed from AllText to InsertCells/NoColumnHeader/None etc. I casted the Guid as string and Voila!! It worked. Thank you for the answer. :) – Ram Kishore Aug 05 '17 at 16:13