Aspose.Cell .Xlsx Issues with Large Data in the Sheet

AonHewittDZ · November 2, 2012, 5:38am

Hi,

We recently migrated our code base for Aspose.Cells from xls to Xlsx format, Since then we are experiencing some vivid issues.

The formula’s are basic such as VLookUp and If Case in the sheet. The version of Aspose.Cells.dll is 7.2.2.0.

We also tried with 7.3.3.1 version but experiencing same problem.

Below is the description of the issues:

For a large data . xlsx file, the Workbook.Save(“file Name”, SaveFormat.Xlsx) method time out occurs. Here we are exporting data for 16380 columns and 10 rows

For replicating the same issue, please refer the attached Aspose_POC.zip.

In one particular scenario, if we try to calculate the Workbook Formula’s using Workbook.Calculate() (10000 columns and 10 rows).

Same set of logic works fine for a smaller set of Data(8200 Column and 7 Rows)

· This method throws an internal exception such as the formula in a Cell is incorrect.

Error Description:

"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name

: index\nError in calculating cell DLD24 in Worksheet XYZ"}

System.ApplicationException {Aspose.Cells.CellsException}

For more details please refer the attached file i.e. Aspose_Error_IndexOutOfRange.png.

· If we set this formula to null, then we are able to further save this file. But the

Formula’s appearing after the error cell are altered and are pointing to different cell’s.

· One more thing if we again generate the same excel but this time bypass the Workbook.Calculate() Method and then try to Save the Workbook in Memory Stream. Then also an internal error occurs.

For more details please refer the attached file i.e. Aspose_Error_Save.png.

amjad.sahi · November 2, 2012, 7:37am

Hi,

Thanks for providing us the sample project and screen shots with details for your issues.

I can see the performance issue. Since you are filling huge list of formulas to be calculated that may be expanded to more than 10K columns, it would surely demand more RAM or the process might result in time out. We have look into your mentioned issues if we can fix the issues and enhance the performance more.

We have logged a ticket with an id: CELLSNET-41168 for your issue(s). We will look into them to figure out them (if possible) soon.

By the way, could you try our latest fix/version: Aspose.Cells for .NET v7.3.3.2

and add a line of code to your code segment if it makes any difference e.g

try
{

wb.Settings.CreateCalcChain = false;

wb_Test.CalculateFormula();

}

Thank you.

AonHewittDZ · November 5, 2012, 2:47am

Hi,

In the scenario 2 mentioned in my first post.

If we try to calculate the Workbook Formula’s using Workbook.Calculate() (10000 columns and 10 rows).

Same set of logic works fine for a smaller set of Data(8200 Column and 7 Rows)

· This method throws an internal exception such as the formula in a Cell is incorrect.

Error Description:

"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name

: index\nError in calculating cell DLD24 in Worksheet XYZ"}

System.ApplicationException {Aspose.Cells.CellsException}

For more details please refer the attached file i.e. Aspose_Error_IndexOutOfRange.png.

We tried using new version(7.3.3.2) of dll and the changes suggested by you.It still give me the error as

"Index was out of range. Must be non-negative and less than the size of the collection.”

The only difference is that now the error message doesn’t show cell number.

We also tried transposing our sheet, so now my worksheet has 10000 rows and 10 columns(including few header columns), and we have formulas on every cell. Still it gives me the same error.

"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name

: index\nError in calculating cell W1243 in Worksheet XYZ"} System.ApplicationException {Aspose.Cells.CellsException}

Question: Is there any defined limitation on number of formula cells in a sheet when we use Aspose.Cells ? The total number for formula cells in my worksheet is 91,665.

Please assist, I need to create a worksheet with this or more number of formulas.

amjad.sahi · November 5, 2012, 4:43am

Hi,

Thanks for testing our latest fix v7.3.3.2 and providing your feedback.

We are already working over your issue. Your provided detail will surely help us in figuring out your issue soon. Please spare us little time. Once we have any update on it, we will let you know here immediately.

Thank you.

shakeel.faiz · November 7, 2012, 2:47am

Hi,

Thanks for your posting and using Aspose.Cells.

We have looked into your issue. Could you please change your code as the following and try it at your end? Let us know your feedback

C#

private static void CreateFormulaWorkSheet()

{

//Validation val;

//CellArea area;

Range styleRange;

Range sourceRange;

Range attDetailRange;

Range choiceRange;

Style DropDownListStyle;

Workbook wb_Test = new Workbook();

wb_Test.FileFormat = FileFormatType.Xlsx;

DropDownListStyle = wb_Test.Styles[wb_Test.Styles.Add()];

DropDownListStyle.Font.Size = 8;

DropDownListStyle.Font.Name = “arial”;

DropDownListStyle.VerticalAlignment = TextAlignmentType.Bottom;

string luFormula = “”;

string luChoiceRange = “”;

string sheet_1 = “Sample_1”;

string sheet_1_Choice = “Choices”;

string sheet_2 = “Formula”;

string sheet_2_Formula = “Formula_1”;

string sheet_2_ChoiceValues = “Choices_Values”;

wb_Test.Worksheets.Add();

wb_Test.Worksheets.Add();

wb_Test.Worksheets.Add();

wb_Test.Worksheets.Add();

wb_Test.Worksheets.Add();

wb_Test.Worksheets[0].Name = sheet_1;

wb_Test.Worksheets[1].Name = sheet_1_Choice;

wb_Test.Worksheets[2].Name = sheet_2;

wb_Test.Worksheets[3].Name = sheet_2_Formula;

wb_Test.Worksheets[4].Name = sheet_2_ChoiceValues;

wb_Test.Worksheets[4].Cells[0, 0].PutValue(“Yes”);

wb_Test.Worksheets[4].Cells[1, 0].PutValue(“No”);

Validation val;

CellArea area;

val = wb_Test.Worksheets[sheet_1_Choice].Validations[wb_Test.Worksheets[sheet_1_Choice].Validations.Add()];

val.Type = Aspose.Cells.ValidationType.List;

val.Operator = OperatorType.None;

val.InCellDropDown = true;

val.Formula1 = “Yes,No,Default”;

area = new CellArea();

area.StartRow = 2;

area.EndRow = 10;

area.StartColumn = 2;

area.EndColumn = 16380;

val.AreaList.Add(area);

for (int i = 2; i <= 10; i++)

{

for (int j = 2; j <= 16380; j++)

{

wb_Test.Worksheets[sheet_2].Cells[i, j].Formula = BuildFormula(sheet_2, wb_Test.Worksheets[sheet_2_Formula].Cells[i, j].Name);

wb_Test.Worksheets[sheet_1_Choice].Cells[i, j].PutValue(“No”); //set default to No

wb_Test.Worksheets[sheet_1_Choice].Cells[i, j].SetStyle(DropDownListStyle);

// colIndxNum = “1”;

luChoiceRange = “’” + sheet_2_ChoiceValues + “’!” + wb_Test.Worksheets[sheet_2_ChoiceValues].Cells[0, 0].Name + “:” + wb_Test.Worksheets[sheet_2_ChoiceValues].Cells[1, 0].Name;

luFormula = “= VLOOKUP(’” + sheet_1_Choice + “’!” + wb_Test.Worksheets[sheet_2_ChoiceValues].Cells[i, j].Name + “,” + luChoiceRange + “,1,FALSE)”;

wb_Test.Worksheets[sheet_1].Cells[i, j].Formula = luFormula;

//try

// {

// wb_Test.CalculateFormula();

// }

//catch(Exception ex)

//{

//}

}//inner for

}//outer for

wb_Test.Settings.CreateCalcChain = false;

wb_Test.CalculateFormula();

wb_Test.Save(@“D:\Filetemp\WorkBookName.xlsx”, SaveFormat.Xlsx);

}

AonHewittDZ · November 9, 2012, 9:29am

Hi,

The changes suggested above, you have created only one Validation Object and applied it over the rest of the Sheet.

But these changes won’t be applicable to our business scenario. Since each Cell in the worksheet could refer to a different set of Validation Rule(ie. a Date Validation, Percent Validation ,Numeric Validation etc).

Moreover, We tried to cut down the number of validations on the cells or even removed all validations from the Worksheet , still the issue#2 persists.

For further analysis of the problem at your end, we are attaching a sample file(Test Workbook.Xlsx).

We are able to generate the Workbook similar to the Sample file, but when we increase either the number of Columns or number of rows or both (programmatically), we are unable to Save or even use CalculateFormula() method in the Code(As mentioned in the Initial Thread).

amjad.sahi · November 9, 2012, 11:27am

Hi,

Thanks for your feedback and sharing the sample file.

We have logged it against your existing issue “CELLSNET-41168” in our database.

We will look into it further, once we have any feedback, we will let you know here.

Thank you.

AonHewittDZ · November 19, 2012, 9:22am

Hi Team,

Any progress made on the “CELLSNET-41168” related issues ?
Please Let us know, if any other information is required from our side.

shakeel.faiz · November 20, 2012, 12:32am

Hi,

Thanks for your posting and using Aspose.Cells.

Please use the code as we have given in the previous post.

You must not call the Workbook.CalculateFormula() in the loop.

AonHewittDZ · November 20, 2012, 1:02am

Hi,

In response of the thread 424492, we clearly mentioned that the code changes suggested does not suffice our business scenario.
"The changes suggested above, you have created only one Validation Object and applied it over the rest of the Sheet.

But these changes won't be applicable to our business scenario. Since each Cell in the worksheet could refer to a different set of Validation Rule(ie. a Date Validation, Percent Validation ,Numeric Validation etc).

Moreover, We tried to cut down the number of validations on the cells or even removed all validations from the Worksheet , still the issue#2 persists."

As already discussed in the thread, In our actual scenario we have commented the
// Workbook.CalculateFormula() Method,

and used the below mentioned flags

wb.Settings.CreateCalcChain = false;
wb.Settings.ReCalculateOnSave = false;

and tried to Save the Workbook. Then also the same error occurs i.e.
"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name : index\nError in calculating cell DLD24 in Worksheet XYZ"}

shakeel.faiz · November 24, 2012, 8:31am

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please spare us some time to look into this issue before we could help/advise you.

We have also logged your comments in our database. Once there is some update for you, we will let you know asap.

Besides, you should also test your issue with the latest version:
Aspose.Cells
for .NET v7.3.4.1 and check its results at your end.

shakeel.faiz · November 28, 2012, 8:34pm

AonHewitt DZ:

Hi,

In response of the thread 424492, we clearly mentioned that the code changes suggested does not suffice our business scenario.
"The changes suggested above, you have created only one Validation Object and applied it over the rest of the Sheet.
But these changes won't be applicable to our business scenario. Since each Cell in the worksheet could refer to a different set of Validation Rule(ie. a Date Validation, Percent Validation ,Numeric Validation etc).

Moreover, We tried to cut down the number of validations on the cells or even removed all validations from the Worksheet , still the issue#2 persists."

As already discussed in the thread, In our actual scenario we have commented the
// Workbook.CalculateFormula() Method,

and used the below mentioned flags

wb.Settings.CreateCalcChain = false;
wb.Settings.ReCalculateOnSave = false;

and tried to Save the Workbook. Then also the same error occurs i.e.
"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name : index\nError in calculating cell DLD24 in Worksheet XYZ"}

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We are unable to investigate your issue properly. Could you please post your latest project here to look into this issue further?

You might also remove the code workbook.CalculateFormula(), save the workbook to xlsx file and post it here. It could also be helpful in investigating this issue.

aspose.notifier · February 7, 2019, 4:46pm

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan