Free Support Forum - aspose.com

Formula values do not get applied correctly

Hi, We are using aspose cell component (version : 8.3.1.0)

We have issue with setting value as formula.

E.G

worksheet.Cells[“A1”].PutValue("=10*5",true, true);

It should set values as “50”.

Similar issue : =SUM(B1:B3) does not evaluate and set value as plain text.

Any help would be appreciated !!!

@Ankurkumar,
You may please use Formula property to set the formula in a cell. Please give it a try and share the feedback.

Workbook workbook = new Workbook();
//workbook.Worksheets[0].Cells["A1"].PutValue("=5*10");
workbook.Worksheets[0].Cells["A1"].Formula = "=5*10";
workbook.Worksheets[0].Cells["B1"].Formula = "=SUM(B1:B3)";
workbook.CalculateFormula();
workbook.Save(path + "output.xlsx");

If it does not fulfil your requirement, please share more details along with the sample input file and expected output file created by MS Excel for our reference. We will analyse the information here and provide assistance accordingly.

Thank you for your response…

It is working , but In our product, our customer only set that as string value.

Is there a way to find string has formula or not ?

E.G

Workbook workbook = new Workbook();
string value = “=5*10”;

if(value is string)
{
workbook.Worksheets[0].Cells[“A1”].PutValue("=510");
}
else // value is Formula
{
workbook.Worksheets[0].Cells[“A1”].Formula = "=5
10";
workbook.CalculateFormula();
}

workbook.Save(path + “output.xlsx”);

Thanks !!!

@Ankurkumar,

Well, an Excel formula starts with “=”, so you may write your own code to check if the given string has “=” as first char. If true, it will be MS Excel formula/function which should be set using Cell.Formula attribute. Otherwise, you may simply input it using Cell.PutValue() method. Mind you, if a string starts with “=” character but is inserted using Cell.PutValue() method, it will be still taken as string and not formula.

Hope, this helps a bit.