Free Support Forum - aspose.com

Formula =sum(F9:I9) -- Slow Save Performance

When I add formulas to cells, the save is extremely slow. I only tested it with SUM. For a approximately 600 records/lines the save goes from 700 Milleseconds to about 15000 Milleseconds. Is this typical? Is it a designer file issue (Format for cells = Currency)? The values are in the tens of millions. Here’s a sample of the code:

For i = 0 To dt.Rows.Count - 1
currentRow += 1
Cells.SetRowHeight(currentRow, 20)
Dim dr As DataRow = dt.Rows(i)
Cells(currentRow, 0).PutValue(dr(“Value1”))
Cells(currentRow, 1).PutValue(dr(“Value2”))
Cells(currentRow, 2).PutValue(dr(“Value3”))
Cells(currentRow, 3).PutValue(dr(“Value4”))
Cells(currentRow, 4).PutValue(dr(“Value5”))
Cells(currentRow, 5).PutValue(dr(“Value6”))
Cells(currentRow, 6).Formula = “=sum(” + Excel.GetCellName(currentRow, 2) + “:” + Excel.GetCellName(currentRow, 5) + “)”
Next
Excel.Save(“MyExcel.xls”, FileFormatType.ExcelXP)

I did CType the values, but I thought that it could be the problem.

Hi, thanks for your consideration.

Yes. You are right. The slow performance is caused by parsing formula. I will optimize it.

Could you place these formulas in your designer file now? That will speed up the program.

In the case I was describing I could add the formula to the designer file. Do I just add it to the first record, in the example I used it would be J9. Does it copy that all the way through to each additional record? I only ask, because the number of rows will be dynamic. Also, in the same report every 10-30 records we do a subtotal. That is, we sum F9:F30 and then have a line break. This breaks the need for the formula on those rows. We also do a grand total at the end of the report. My guess is that those situations will need to be done via the API. Is that correct?

I am a Software Development Manager of 13 programmers. We are really hoping this tool will provide what we need in terms Excel automation. Being in the Finance industry, we do alot of it. Adding formulas and groupings are probably are two biggest needs. We found downloading the demo and getting it up and running really quick and easy.

Thanks for your quick response.

Hi,

Formula parsing is a really time-consuming task because we have to enalbe the flexibilty of formula. I plan to add a new API Cell.SimpleFormula. That provides about 3 time faster performance. But it can only be used to assign simple formula, such as “sum(F9:F30)”, “Average(A1, E2, F3)” and “A1+H3”. More complicated formula, such as “A1+Sum(F9:F30) - Average(A1, E2, F3)” cannot be assigned using this new api.

To make program faster, putting formula in designer file is better. You have to assign the formula in all needed cells. You can add surplus formulas to cells then use Cell.PutValue(null) to overwrite unneeded cells.