We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

ArrayIndexOutOfBoundsException and excelsheet formatting issue during library migration

Hello,

we are facing issues while generating the excel sheet report in our application. With jar aspose-cells-jdk15-2.0.1.jar, this report is getting generated properly. We have upgraded the jar to aspose-cells-2.5.0.4.jar to get some new features and we are facing issues. I have tested the application with the latest available jar as of now i.e aspose-cells-2.5.3.jar and issues is still there.

This isssue is coming while setting the style to the sheet. Even for lower number of records i.e; approx 500, formatting is not proper; half of the records are formatted correctly, while the other half the formatting is skewed.

The exception is as such:

java.lang.ArrayIndexOutOfBoundsException: -32759

at java.util.ArrayList.get(ArrayList.java:324)

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

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

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

at com.aspose.cells.Cell.setStyle(Unknown Source)

at caf.opm.preclosing.business.amap.authoSheet.impl.AbstractGenericAuthoSheetReportServiceImpl.populateProductAggregateColumn(AbstractGenericAuthoSheetReportServiceImpl.java:637)

It would be great if we the forum can help with the situation and give valueable inputs to improve the same.

Thanks.

Hi,

Please make sure that you doing formatting (using Style object) accordingly referring to the following documents, go through the documents here:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/approaches-to-format-data-in-cells.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/setting-display-formats-of-numbers-dates.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/dealing-with-font-settings.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/colors-and-palette.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/adding-borders-to-cells.html
etc.

If you still find any issue, kindly do create and paste here the sample code with template files here to reproduce the issue on our end. We will check it soon.

Thank you.

Hello,

I have gone through the documentation given by Amjad but unable to resolve the problem. I have tried to extract all the required information into the log file and posted as an attachement with this post.

Let me know if I can help with some more information.

For your information, I am using the excelsheet template file to load the data.

Thanks.

Hi,

Thanks for the log file.

We appreciate if you could create a sample console demo application, give us complete JAVA program with your template file to reproduce the issue on our end. We will look into your issue soon.

Thank you.

Hello,

I would like to appreciate the sincerity that you have shown to address my issue.

Here I am attaching the code snippet used to generate the report.

Due to my constraint I cannot provide complete running code or demo code as well as template file.

So Please if possible try to find out the solution.

Incase if any information is required don't hesitate to ask me.

Thanks.

Hi,

Thanks for providing us some code segments regarding your issue.

We will try to investigate to find out the issue (if we can) based on your code segments. I have logged a ticket for investigation with an id: CELLSJAVA-26666.
Once we have any update, we will let you know.

Thank you.

Hello,

I am also working on the same project, and I made some investigations regarding this problem. What I tried is to wrap the line where the code breaks in a try {} catch block, in order to continue the process even if this error occurs.

With this modification, and when I have many records to process (1000+), another error occurs during trying to save the workbook:

java.lang.ArrayIndexOutOfBoundsException: -32768
at java.util.ArrayList.set(ArrayList.java:339)
at com.aspose.cells.gB.a(Unknown Source)
at com.aspose.cells.gt.(Unknown Source)
at com.aspose.cells.it.(Unknown Source)
at com.aspose.cells.iy.b(Unknown Source)
at com.aspose.cells.iy.a(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at caf.opm.preclosing.business.amap.report.impl.AuthoSheetReportServiceImpl.generateReport(AuthoSheetReportServiceImpl.java:292)

I’m not sure if this problem is related to the original one, but I thought it could be helpful for you to investigate about our current issue.

Thanks.

(test made using Aspose Cells 2.5.3)

Hi,

Thanks for sharing your findings.

We will investigate your issue soon.

Thank you.

Hi,

It is because you have used too many different styles in one workbook. Currently we only support to set 32768 different styles for one workbook. Even for ms excel, it only supports 4096 different styles for xls file. The reason of that with some old versions you did not get such kind of issue is in old versions we did not gather and cache styles when applying style to cell. Even without exception, the styles exceeds limit will be removed or become invalid in the generated excel file.


To support more styles, we need to check the limit of different excel file formats first. If file format allows, we can make enhancement for extending this limit in later versions.


Currently we can only provide a fix to check whether user has created too many styles(more than 32768) and give a CellsException with meaningful code and message to user instead of the ArrayIndexOutofBoundsException. For your situation, I am afraid you have to change the logic of your application to avoid using so many different styles.

Hello,

I’ve found a way to generate our report without getting the exception!

But first, let me explain how we used to generate this report:

Before generating the XLS file, we read the template (hosted in our database), and search for specific cells to retrieve the styles. We have about 400 styles (many of them are similar though), because we have 5 different types of lines, with about 80 columns each.
We use a “DTO” object to store some information about the cells, as well as the Style itself.
In our code, during this initialization phase, we do something like that:

myDto.setStyle1(cells.getCell(START_ROW_INDEX, columnNo).getStyle());
myDto.setStyle2(cells.getCell(START_ROW_INDEX + 1, columnNo).getStyle());
myDto.setStyle3(cells.getCell(START_ROW_INDEX + 2, columnNo).getStyle());


Then, we have the “real” Excel file generation.

For each record, we insert a new row in our Excel file, and on each column, we apply the Style on the cell. We do something like that:

cells.insertRows(rowIndex, 1);
for (Data data : someData) { // we iterate on the 80 columns…
Cell cell = cells.getCell(XXX);
cell.setStyle((Style) myDto.getStyle1());

}

It seems that when many records are processed, we get the famous exception. Note that we are talking of more than 100,000 cell style application (some reports may also have more than 2 millions of them!).


Now, the solution I found consists in re-generating the Style instead of getting it from the template. In others words, during the initialization I now do that:

myDto.setStyle1(recreateStyle(workbook, cells.getCell(START_ROW_INDEX, columnNo).getStyle()));
myDto.setStyle2(recreateStyle(workbook, cells.getCell(START_ROW_INDEX + 1, columnNo).getStyle()));
myDto.setStyle3(recreateStyle(workbook, cells.getCell(START_ROW_INDEX + 2, columnNo).getStyle()));


and the code for recreateStyle method is simply a “transfert” of properties:

private Style recreateStyle(Workbook workbook, Style raw) {
// We create a new Style from the Workbook…
Style style = workbook.createStyle();
// We apply all the properties from the “raw” Style
style.setNumber(raw.getNumber());
style.setName(raw.getName());
style.setColor(raw.getColor());

// We do the same thing on the Font
Font font = style.getFont();
Font rawFont = raw.getFont();
font.setBold(rawFont.isBold());

style.setFont(font);
return style;
}

No modification has been done in the rest of the code, especially on the report generation part itself.

This modification give two advantages:

  • It works, even with reports with lots of data :slight_smile:
  • It works really faster (I didn’t measure it, but it seems that 2x or 3x faster!)
What I am thinking is that when we get the Style from the template itself (i.e. the old code), we keep some properties in that Style object that may not be handled correctly by Aspose, especially when these objects are used more than 100,000 times.
So using a fresh Style, generated from the code itself, help us to keep a Style with good properties, and also improve the performances!

So I think we will use this approach now…

Anyway, if you need more details about the problem we were facing, do not hesitate to ask.

Thanks again.

A minor addition:

Using Style.copy() does not help. Having that code:

private Style recreateStyle(Workbook workbook, Style raw) {
Style style = workbook.createStyle();
style.copy(raw);
return style;
}

will generate the error. So this seems to confirm that the problem is located in one or several properties of the Style object (or their value).


Hi,

The root cause of your issue is you set style objects that belongs to one workbook to the cells of another workbook. So, those styles will always be taken as different style from all cached styles of the new workbook and will be copied and put into new workbook's style pool. We will make enhancement for such situation of setting style of one workbook to another workbook in later fixes.

Thank you.

Hi,

We have fixed this issue. Please download Aspose.Cells for Java v2.5.3.1.

Hello,

Thanks Shakeel for this very special news for us. I really appricate Aspose team efforts in resloving this issue. I understand that currently this fix is available to forum member.

I have some questions about this release -

Would it be possible to make this solution public so that for us it will be easy to use this solution?

Currently this jar has been release under jdk5. we are using jdk6 in our application. so, will we have any compatibility issue?

Thanks.

Hello,

Thanks for your fast correction!
I’ve tested this new version 2.5.3.1, and in our case, it solves the problem!

Just a question though: with our old version of Aspose (2.0.1), applying the Style object on the Cell was changing the height of the row (this was the expected behavior in our report).
This seems not to be the case anymore, and we have to set the row explicitely (i.e. using cells.getRow(rowIndex).setHeight(xxx);). Was there a modification on the API that can explain this behavior change? (it is just for my curiosity, not a bug :wink: )

Hi,

Formerly we have Cells.setRowHeight() method to adjust the row’s height. Now, we have also Row.setHeight() method. Both approaches can be used to adjust the row heights.

Moreover, you may use Aspose.Cells for Java’s latest fixed version for your need that is stable and you may use it on Production environment. You can use this fix as an official release. It is an intermediate kind of version which is provided as a fix against some bugs/enhancements. Normally, a bug fix version is posted in the support forums. You can use this fix for your requirements as long as you wish; it will behave like an official release. It has all the functionality of Aspose.Cells for Java v2.5.3.0 with addition of further enhancements. Also, our next official release of the product would include the functionality of this fix as well.

Thank you.


Hi,

We have made many enhancements in later versions from V2.0.1. In some old versions, row’s height relevant properties may be initialized or changed incorrectly when changing other properties. For a row, if its height relevant properties has not been set, its height should be adjusted automatically in MS Excel (Auto-fit) corresponding with cell’s content and style. For example, see the following code:
Workbook wb = new Workbook();
Worksheets wss = wb.getWorksheets();
Worksheet sheet;
sheet = wss.getSheet(0);
Cells cells = sheet.getCells();
cell = cells.getCell(0, 0);
cell.setValue(“It is a text for wrapping\none more line”);
Style style = cell.getStyle();
style.setTextWrapped(true);
cell.setStyle(style);
wb.save(“res.xls”);
When viewing the generated Excel file in MS Excel, the first row’s height will be expanded according with the cell A1’s content.

If in your case the row’s height has not been expanded, maybe you have set its height manually in other place, or you have set the worksheet’s default height manually before this row to be instantiated. If you need to keep the row’s height/auto-fit property, please use Row.setAutoFitHeight() method. Or please call Cells.setStandardHeightXXX() methods at last after all needed rows are initialized.

Thank you.

The issues you have found earlier (filed as 26666) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.