Problems with the SUM function- please help

Aspose.Cells Product Family
1

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

2

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

3

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.

4

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

5

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)”.

6

I love you man!!!

It works now, super!

Thanks a million times!
/Hans

7

@sixhoj,
Aspose.Cells has replaced Aspose.Excel that is no more available now. The new product supports all the features available in its predecessor product as well as available in different versions of MS Excel. You can use SUM function with Aspose.Cells also as shown in the following sample code.

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.ImportObjectArray(Enumerable.Range(1, 6).Cast<object>().ToArray(),
    0, //this table is start at row 
    0, false);
worksheet.Cells["A2"].Formula = "=SUM(A1,B1,C1,D1,E1,F1)";
workbook.CalculateFormula();
workbook.Save("output.xlsx");

You may refer to the following article for more details:
Formulas

Download the latest free trial version here to test the product features:
Aspose.Cells for .NET (Latest Version)

Here is a complete solution that can be used for testing different features of the product.