Interop to Spire Migration, formula is getting hard coded

Question

I am moving my C# project from the Interop to Spire.

Below piece of code is working in Interop, but not in Spire:-

Interop:-

When I provide the formula "=SUM(F2,G2)" across the range, it automatically gets appied to the range of cells for example:- "=SUM(F3,G3)" for 3rd row "=SUM(F4,G4)" for 4th row and so on:-

Below is the piece of code

string formula = string.Format("=SUM(F" + "{0}" + ",G" + "{0}" + ")", 2);
Excel.Range to = xlWorkSheet.Range[xlWorkSheet.Cells[2, cl + 1], xlWorkSheet.Cells[rw, cl + 1]];    
to.Formula = formula;

Spire:-

But when I apply the same code, it hard codes the formula to the "=SUM(F2,G2)" across the range:- "=SUM(F2,G2)" for 3rd row "=SUM(F42,G2)" for 4th row and so on:-

Below is the piece of code

string formula = string.Format("=SUM(F" + "{0}" + ",G" + "{0}" + ")", 2);
CellRange to = xlWorkSheet.Range[2, cl + 1, rw, cl + 1];    
to.Formula = Formula;

I do not want to iterate through the range and put the formula individually in the cell, due to performance issues.

Any help is much appreciated.

score 0 · Answer 1 · answered Aug 22 '17 at 02:09

You asked the same question on Spire forum, one of the technical support engineer already gave you an answer. In case you didn't see it, I quote it here for your reference.

In our Spire.XLS, when you set a formula for a cell range, the formula is a fixed one. We do this beacuase we need take some other occasions into consideration. It is an easy work for us to change the property "formula" to your desired effect like Interop, and the iteration can help to acheive it. However, if we changed it, there's no access for those who prefer the fixed formula. On the other hand, if we set it as a fixed formula, users who want an automatic formula can make some modifications according to their own situations. Moreover, there's no need to worry about the performance issue since the result you desired is also acheived by an internal iteration. Below is the iteration for your reference.
 int totalRows= sheet.Rows.Length;
        CellRange to;
        for (int CurrentRow = 1; CurrentRow < totalRows+1; CurrentRow++)
        {
            string formula = string.Format("=SUM(F" + "{0}" + ",G" + "{0}" + ")", CurrentRow);
            to = sheet.Range[CurrentRow,cl+1];
            to.Formula = formula;
        }

Interop to Spire Migration, formula is getting hard coded

1 Answers1