Issues handling large amount of data validation areas

Hi team,

Through our system our customers are able to freely design Excel forms generated with Aspose. Recently one customer generated a form with so many separate data validation areas that the file was viewed as corrupted by Excel and could not be opened without repair.

It is clear that we need to apply some sort of limit and after looking into this I have ended up with two requests for you:

  1. Do you know of any specific limit on the number of data validation areas that we (or Aspose automatically) could enforce? Or is this limited by available memory?

  2. When we handle the areas we want to add to existing validations we have a huge performance issue that comes from the fact that upon calling validation.addArea(newArea), Aspose will check all existing areas to see if newArea is already there. We have already done our area check up in a much faster way and would desperately need a way to get around this check that takes an increasing amount of time as more and more areas are added. I would like to request an alternative method with a flag to control whether or not existing areas are checked. Either addArea(area, boolean skipCheck) for adding quickly or setAreas(areas) to directly replace all previous areas with a new list.

Best regards
Claes

@spygordon,

For your queries/requirements:

  1. Yes, it is same with MS Excel’s limit for XLS/XLSX file format. For the specific value of the limit, we need to check documents. For XLS, the limit is not a static value. It is determined by the total data size in the validation block. If the data of the validation’s other settings is large, then the allowed count of cellarea will be a bit small. Anyways, it should not be greater than about 1K areas. There is also implicit limit for the XLSX file format, though we cannot get the public document for that in Microsoft document specification. By the way, some users reported bugs about the corrupted documents and we think it is also about 1K. And it is not a static value either. We don’t know exactly what data may influence it yet.

  2. We have logged a ticket for your requirements as following:

  • CELLSJAVA-43032 - Add Validation.addArea (CellArea cellArea, boolean skipArea) or Validation.setAreas() method/overloads to the APIs.
    We will look into it and check the feasibility if we can support it.
    Once we have an update on it, we will let you know.

Thank you Amjad!

@spygordon,

You are welcome.

BTW, the approximate limit of 1K validation areas, is this per validation or for all validations in total?
(I am assuming it is per validation)

@spygordon,

Yes, your understanding is correct.

@spygordon,

This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-43032”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@spygordon,

Please try our latest version/fix: Aspose.Cells for Java v19.10.3(attached)

Your issue should be fixed in it.
We have provided new methods for your requirement while taking performance consideration:

Validation.AddArea(CellArea cellArea, bool checkIntersection, bool checkEdge)
Validation.AddAreas(CellArea[] areas, bool checkIntersection, bool checkEdge)

The parameter "checkIntersection" denotes whether checking the intersection of given area(s) with existing Validations’ areas. For your situation, it can be false.

The parameter "checkEdge" denotes whether check the situation that one of the new added areas will become the new top-left one for Validation’s applied areas. Validation’s settings depend on the top-left one of its applied ranges, so if one of the new added areas will become the new top-left one of Validation’s applied ranges, the internal data should be reset or rebuilt too. If you are sure that no one of the added area(s) is the top-left, this parameter can be set as false for performance considerations. For the top-left area, firstly its StartRow is smallest in all applied ranges, secondly its StartColumn is the smallest one of those areas who have the smallest StartRow.

Let us know your feedback.
Aspose_Cells_Java_v19.10.3.zip (6.6 MB)

The issues you have found earlier (filed as CELLSJAVA-43032) have been fixed in Aspose.Cells for Java v19.11. This message was posted using Bugs notification tool by ahsaniqbalsidiqui

Thank you, it works fine.

(I was unable to test this until now since we had delays in renewing our license, but now we are up and running again with a fresh one.)

Best Regards
Claes

@spygordon,

Good to know that you are up and running again with latest version of Aspose.Cells.