Free Support Forum - aspose.com

Problems with the SUM function- please help

Hi,

I am developing an application that uses your Aspose.Excel component. I am dynamicaly creating formulas depending upon the output from the database and write them to the workeheet cells in question.

So the formulas look like this:
"=SUM(C9;C10;C11;C12;C13)"

Output is set to Excel 2003 (its the version im using at the moment on my local developer machine)

I write it to the correct cell using the following syntax: (C#)
objExcel.Worksheets[0].Cells[Convert.ToInt32(objDataTable.RowsIdea[“ExcelRowNumber”]), ExcelSettings.ColumnNo_AmountPreviousYear].Formula = strFormula.ToLower().Trim();

When Excel is opened with the spreadsheet i see that the cells containing the formulas have the following error: #NAME? and the formula looks like this: =SUM(‘C9;C10;C11;C12;C13’()), which does not evaluate at all…

What am i doing wrong?

Could you please help me, i have to deliver the solution next week to a customer…

Sincerely
/Hans Sxh?j
Systems Developer, MCSD
Visma Consulting AB

Hi again,

After some additional testing i have come to realize that “;” should not be used from Aspose.Excel when providing formulas to the .Formula method, instead i use “,” chars for separation. Now i still get the error displayed in the Excel cell containing the formula: #Name?

Although now if i select the formula field in Excel and evaluate the formula, it calulates correctly.

So the formula is right, although, why does it display the error #Name? instead of calulating my formula?

I tried using the method provided for calulating values from formulas:
objExcel.CalculateFormula();

But when executed i get the following error thrown:
"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index"

Why is that?

Also another question, if i have formulas put in cells in Excel dynamically by using Aspose.Excel, will the formulas evaluate and calculate the values automatically for the user when the Excel worksheet is opened? Or do i have to purchase the Enterprise edition for that?

Please help me,
Hans

Dear Hans,

1. The “#Name” error may be caused by one of cells in C9 to C13 refers to an invalid name. Could you please post your template file here?

2. I will check this calculation error after you post your template file.

3. When you just want to put formula in cells and don’t want to retrieve formula result at run time, you don’t need to call CalculateFormula method. MS Excel will automatically evaluate and calculate them when the file is opened.

Hi,

1.) What does "invalid name" refer to, i do not understand, what is an invalid name? What are the possible causes of this error in Excel?
I do not use a template file, the entire worksheet is created from scratch every time.

Could it be that my format string for thease cells has something to do with it?
objStyleAmountColumnDetail.Custom = "#,##0;-#,##0;";

Im posting my Excel file that is the end result the user gets, perhaps you could find something there that leads us to the source of this #NAME? error.

P.S - I am still using the evaluation version, until i know which version to buy (Professional or Enterprise) for this project, perhaps this has something to do with it?

Thanks for your quick response,
/Hans

Dear Hans,

Formulas in Aspose.Excel should be English. So the formula should be “=SUM(C8,C14,C15,C16,C17)”, not “=SUMMA(C8,C14,C15,C16,C17)”.

I love you man!!!

It works now, super!

Thanks a million times!
/Hans