ArrayIndexOutOfBoundsException occuring on Workbook.save()

Aspose.Cells Product Family
1

Hello,



I’m using Aspose Cells for Java (version unknown, the license we have is for LicenseVersion 2.1 according to the .LIC file)



I’ve got an application that is opening an existing Excel file, and then inserting rows and creating Validation objects on some CellAreas. Everything was working fine until I just scaled up the tests for large sets of data.



When Workbook.save() is invoked (any of the save methods, whether it be to file OutputStream doesn’t matter) an ArrayIndexOutOfBoundsException is thrown if I have ~ 1500 rows in my Worksheet.



If I reduce the row count it works fine, but there is a specific number of rows (exactly 1366 in this case) this exception occurs.



The stack trace I get is as follows (omitted proprietary classes from stack trace, starting with entry point to Workbook.save()):



java.lang.ArrayIndexOutOfBoundsException: 8254

at com.aspose.cells.aE.a(Unknown Source)

at com.aspose.cells.aE.b(Unknown Source)

at com.aspose.cells.CellArea.a(Unknown Source)

at com.aspose.cells.Validations.a(Unknown Source)

at com.aspose.cells.bT.l(Unknown Source)

at com.aspose.cells.bT.b(Unknown Source)

at com.aspose.cells.bT.w(Unknown Source)

at com.aspose.cells.bT.a(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)



This is a big problem for us, as there could easily be thousands of rows of data in multiple worksheets, and just ~ 1500 on a single worksheet is causing this problem.



As you can see from the stacktrace the problem lies somewhere inside the CellArea, which we are using pretty extensively as this is a highly customized Workbook with Worksheets that have detailed requirements for protection, data validation etc.



If I simply remove the code that adds the CellArea objects to the Worksheets, the problem goes away and I can create a very large Workbook with 65000 rows on all 9 worksheets without issue.



However, using the CellArea Objects, once we have about 1500 rows in a single Worksheet that are using these CellArea Objects, we see this Exception.



I can tell you that there is at least 1 CellArea associated with each row that spans 1 to many cells in that row.



Any idea how to avoid this Exception?



2

I would like to also add the following information:



The CellAreas are being added as a list to the Validation object via Validation.setAreaList().



Something we noticed is that method signature for Validation.setAreaList() takes an ArrayList instead of the List interface, and makes me wonder if there is a direct limitation on how many CellArea Objects can be attached.



Otherwise, I would have expected this function to take a List, not ArrayList specifically.



ArrayList itself does not impose this limitation, but I cannot see the Aspose source to see what is being done with the ArrayList.

3

Hi,

Thanks for providing us the details.

Could you post your template file with sample code here to reproduce the issue, we will check it soon.

Thank you.

4

I cannot attach the actual code causing the problem in our product, but I just put together a full working sample that reproduces the behavior.



Requirements: aspose cells JAR, JDK 1.5



When NUM_TEST_OBJECTS is set to 1500, the exception occurs

When NUM_TEST_OBJECTS is set to 1000, no exception occurs

5

Hi,

Thank you for considering Aspose.

Yes, this exception is occurring because there are too many CellAreas and the data cannot be contained by one record of Excel file. For your case, I don’t think you need to create so many CellAreas. In fact, you can just make only one CellArea(0, 0, NUM_TEST_OBJECTS, 0) and add it to the Validation, and this way it will improve the performance of your application too.

Thank You & Best Regards,

6

The reason there are so many is that in our real application, each row has different requirements for the Validation.



Having the CellArea span multiple rows isn’t an option in this case…

7

Hi,

Well, I am not very clear about your scenario of using Validations, can’t the areas of validation be merged so to decrease the size of the area list? If so, I think you can create more validations to split those areas, such as, to create different validation for every row, rather than to create a validation that covers all rows.

Thank You & Best Regards,

8

Hi,

After checking your sample CellAreaTest.java file, I think you can simply change your code as follows:


for (int i = 0; i < testObjects.size(); i++) {
cellAreas.add(new CellArea(i, 0, i, 0));
}
to:
cellAreas.add(newCellArea(0, 0, testObjects.size()-1, 0));

And we are still not very clear about the your scenario of using Validations. If you could provide us some more sample codes for your application that could build the range(CellAreas) for Validation, we can help you to check whether it can be optimized more.

Alternatively, if you do not want to collect and merge the CellAreas of the Validations before calling Validation.setAreaList(ArrayList), you can use Validation.addCellArea(CellArea) to add CellAreas one by one to the Validations. So, in this way, our component will check and merge CellAreas for the Validations automatically if there are some CellAreas to be merged in one CellArea.

For the exception i.e.., ArrayIndexOutOfBoundsException, we will handle it in our next fix soon. But even we handle this exception and create such a file without any issue, if there are too many CellAreas set for the Validations, we are afraid MS Excel might not handle a lot of Validations properly.

Hope, you understand what we have explained.

Thank you.

9

Yes I understand what you are saying.



I have tried using Validation.addCellArea(CellArea) as well, but that results in the same Exception.



The problem for us is that each row of data as it is processed has one of many types of validation requirements. For now, I can work around the issue by sorting the data in such a way that identical validations are grouped together and allow for larger CellArea’s to be used.



However, that is basically causing our business data to be sorted in an unnatural way in the resulting Excel file…

10

We have worked around the issue for now by sorting the data into large continuous sets that share the same type of Validations.



However, we are unsure that this should really be required. One would think that Excel should be able to support at minimum one validation per row…





11

Hi,

Thanks for sharing further details.

We will look into it soon.

Thank you.

12
Hi,
We have tested the validations in MS Excel 2003, it can't support many CellAreas for the list of one Validation either. When the CellArea list's size reaches up to 1020, MS Excel will give a warning message that says data will be lost when saving the file. I think it's mainly due to the fact that MS Excel cannot support it, our component should not create such a Validation for you too as we follow the MS Excel standards. However, for such situation, we have replaced the ArrayIndexOutOfBoundsException with more precise Exception message to show that there are more CellAreas that are exceeding the limit for one Validation.
So, I think you have to decrease the list size of CellAreas of a Validation, or you can save the file as Excel2007 XLSX format, because MS Excel2007 can support more CellAreas for one Validation. To decrease the number of CellAreas of one Validation for saving as Excel2003 file format, one way is as you are already doing currently, i.e.., to sort the data into large continuous sets to allow large CellArea(s) to be used. Another way is to create multiple Validations to split those areas, such as, to create different Validation for every row. For example, following codes in your previous sample CellAreaTest i.e..,
private static void applyValidations(final Worksheet ws, final List testObjects) {
final ArrayList cellAreas = new ArrayList();
for (int i = 0; i < testObjects.size(); i++) {
cellAreas.add(new CellArea(i, 0, i, 0));
}
if (cellAreas.size() > 0) {
final int validationIdx = ws.getValidations().add();
final Validation validation = ws.getValidations().get(validationIdx);
validation.setAlertType(ValidationAlertType.STOP);
validation.setErrorMessage("Invalid Id");
validation.setErrorTitle("Invalid Id");
validation.setFormula1(Double.toString(0));
validation.setFormula2(Double.toString(testObjects.size()));
validation.setInCellDropDown(false);
validation.setShowError(true);
validation.setType(ValidationType.WHOLE_NUMBER);
validation.setAreaList(cellAreas);
}
}
can be changed to:
private static void applyValidations1(Worksheet ws, final List testObjects)
{
for(int i=0; i<testObjects.size(); i++)
{
ArrayList cellAreas = new ArrayList();

cellAreas.add(new CellArea(i, 0, i, 0));

if (cellAreas.size() > 0) {
final int validationIdx = ws.getValidations().add();
final Validation validation = ws.getValidations().get(validationIdx);
validation.setAlertType(ValidationAlertType.STOP);
validation.setErrorMessage("Invalid Id");
validation.setErrorTitle("Invalid Id");
validation.setFormula1(Double.toString(0));
validation.setFormula2(Double.toString(testObjects.size()));
validation.setInCellDropDown(false);
validation.setShowError(true);
validation.setType(ValidationType.WHOLE_NUMBER);
validation.setAreaList(cellAreas);
}
}
}
doing so, the resultant file can be created and will work fine with MS Excel.
For the issue Validation.addCellArea() does not merge CellAreas for you automatically, if there are some CellAreas to be merged, I think it is due to the fact that you are using an old version of Aspose.Cells for Java, with the newer version(s), we recommend you to change your code as given below, it will create a Validation whose CellArea list only contains one CellArea(0, 0, NUM_TEST_OBJECTS, 0):
private static void applyValidations2(final Worksheet ws, final List testObjects) {
final int validationIdx = ws.getValidations().add();
final Validation validation = ws.getValidations().get(validationIdx);

validation.setAlertType(ValidationAlertType.STOP);
validation.setErrorMessage("Invalid Id");
validation.setErrorTitle("Invalid Id");
validation.setFormula1(Double.toString(0));
validation.setFormula2(Double.toString(testObjects.size()));
validation.setInCellDropDown(false);
validation.setShowError(true);
validation.setType(ValidationType.WHOLE_NUMBER);

for (int i = 0; i < testObjects.size(); i++) {
validation.addCellArea(new CellArea(i, 0, i, 0));
}
}
Hopefully it will help you alot.
Thank you.
13

Thank you for taking the time to look into this and clarifying the issue and solution(s) available.



I’m happy to see that a proper Exception will be thrown in this situation now, and we will work around the limitation in the way that best suits our business needs.