Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Sun Jul 09, 2017 4:10 pm

Hi,

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);


//sheet.Range[
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, do let me know for any updates

Thanks and Regards,

Rakesh Chaturvedi

raka_12
 
Posts: 2
Joined: Thu Jul 06, 2017 1:28 pm

Mon Jul 10, 2017 6:26 am

Hello,

Thanks for your inquiry.
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.
Code: Select all
 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;
            }

Sorry for the inconvenience caused.
If there's still any issue, just feel free to contact us.

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Tue Jul 11, 2017 8:53 am

Hello Rakesh Chaturvedi,

How is your issue now? I wonder if I have made it clear.
Could you please give us some feedback at your convenience?

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Return to Spire.XLS