Set dynamic formula to a cell in MS Excel worksheet in .NET

Aspose.Cells Product Family
1

Hi,

I am trying something like below to set a dynamic formula.

"=SUMIF(H8:H" + maxrange.ToString() + ",I" + minrange_deal + ",L8:L" + maxrange.ToString() + ")";

Doing this bcs the no. of rows a dynamic so trying to define range dynamically.But its not working

Pls give me the urgent solution.

Thanks,

Mukund

This message was posted using Aspose.Live 2 Forum
2

Hi Mukund,

As per our conversation on live chat, I have tested your scenario using your template file, it works fine. I am using latest version/fix v4.9.1.8 (attached). I can set/get the dynamic formula string fine. And, I can also get the calculated values same as MS Excel does when Workbook.CalculateFormula is called. Aspose.Cells for .NET works similar to MS Excel as far as formula calculation is concerned.

Here is my test code that works fine.

Workbook workbook = new Workbook();
workbook.Open(“e:\test\DynamciBPCashFlowFormula.xls”);
Worksheet sheet1 = workbook.Worksheets[0];
Cells cells = sheet1.Cells;
int maxrange, minrange_deal;
string startcell_deal = “L10”;
maxrange = 21;
minrange_deal = 26;

string formula_deal = “=SUMIF(H8:H” + maxrange.ToString() + “,I” + minrange_deal + “,L8:L” + maxrange.ToString() + “)”;
sheet1.Cells[startcell_deal].Formula = formula_deal;

MessageBox.Show(sheet1.Cells[startcell_deal].Formula); //Formula Value is fine

workbook.CalculateFormula();

MessageBox.Show(sheet1.Cells[startcell_deal].StringValue); //works OK same as MS Excel

workbook.Save(“e:\test\outBook.xls”);



Kindly try the attached version, if you still find any issue, give us complete detail and complete sample code (you may create a sample code similar to mine), template files (input + output files) etc. to reproduce the issue, we will check it soon.

Thank you.