Severe performance issues - large sheets with complex formulas

We are experiencing severe performance issues when working with large sheets (about 1 Mio fields) and formulas.


The performance issues increase exponentially.

It is almost impossible using this component, since creating the sheets takes so long (in this case, about 5 minutes).

Can you please optimize component or tell us how to interact.

Attached I submit code example for you to reproduce.

Thanks
TELSi

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Drawing;

namespace AsposeTest {
class Program {

static void Main( string[] args ) {
Workbook workbook = new Workbook( FileFormatType.Xlsx );
Worksheet sheet = workbook.Worksheets[0];

for( int col=0; col < 1027; col++ )
sheet.Cells[0, col].Value = "3";

for( int row=1; row < 1000; row++ ) {
for( int col=0; col < 1027; col++ ) {
Cell cell = sheet.Cells[row, col];
string rowIndex = CellsHelper.RowIndexToName( row );
string formula =
"=-IF(BZ"+rowIndex+"=\"Yes\","+
"IF(AW"+rowIndex+"*{0}-CK"+rowIndex+">0,AW"+rowIndex+"*{0}-CK"+rowIndex+",0),"+
"IF(AY"+rowIndex+"*{0}-CK"+rowIndex+">0,AY"+rowIndex+"*{0}-CK"+rowIndex+",0)"+
")";

cell.Formula = formula;
}
}
workbook.Save( "test.xlsx", Aspose.Cells.SaveFormat.Xlsx );
}
}
}

Hi,

Please download and try the latest version:

Aspose.Cells for .NET 7.1.2



Please provide us your sample expected output file, so that we could see how to improve the performance of inserting such formulas.

Hi,

For this performance issue, please try the following code instead of the loop:

C#


sheet.Cells[1, 0].SetSharedFormula(“=-IF(BZ2=“Yes”,IF(AW2*{0}-CK2>0,AW2*{0}-CK2,0),IF(AY2*{0}-CK2>0,AY2*{0}-CK2,0))”, 999, 1027);

Please also see the complete code example that shows how to copy single formula to first 100 rows with their rows references.

Please see the output file generated by the code.

C#


string filePath = @“F:\Shak-Data-RW\Downloads”;

Workbook workbook = new Workbook();


Worksheet sheet = workbook.Worksheets[0];


sheet.Cells[“C1”].SetSharedFormula("=A1+B1", 100, 1);


//Save it in your directory in xlsx format with name prueba.xlsx

workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

Thanks for your hint regarding: SetSharedFormula. We will change our code and test how it affects performance.

Regarding version: We were already using version 7.2 which caused performance issues.

Hi,

It’s good to know your problem is resolved with SetSharedFormula. Let us know your feedback with performance.

If you get any other question. Please feel free to post. We will help you asap.