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.
@bcurrie007,
We are glad to share that we have introduced a new product Aspose.Cells which has replaced the older product Aspose.Excel. Aspose.Cells is much more superior as compared to Aspose.Excel in terms of features, performance and support for the latest versions of MS Excel. It also supports formulas with quite a simple syntax as shown in the following sample code:
// Instantiating a Workbook object
Workbook workbook = new Workbook();
// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();
// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];
// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);
// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);
// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";
// Calculating the results of formulas
workbook.CalculateFormula();
// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();
// Saving the Excel file
workbook.Save(dataDir + "output.xls");
For more information on Formula, you may visit the following link:
Formulas
You can download the latest version of Aspose.Cells here:
Aspose.Cells for .NET (Latest Version)
Ready to run demo is available here.