Problem loading Jasper generated XLSX files properly

When loading an XLSX file that is generated by Jasper into Aspose cells (version 8.1.0), we noticed two issues:

1. It requires more than 600MB of heap space in order to load a 12MB xlsx file (that is generated by Jasper reports). 600MB of heap space to load a 12MB xlsx file is too much for our application.

2. All the cells loaded into aspose.cells has bold, italic and strikeout enabled, when in fact the original file does not have any bold, italic or strikeout (or strike through) content.

However, if we open the Jasper generated xlsx file in Microsoft Excel, and then in Excel save it back as another xlsx file, then loading this Excel-saved xlsx file into aspose.cells would have no problem with the font/text format issue, and uses about 160MB of heap space to load.

In multiple ways, the above issues are show stoppers for us. Can anybody please help?

Andrew

Hi,



Please try our latest version/fix: Aspose.Cells for Java 8.2.1.4 if it makes any difference.

If you still find the issue, kindly do post your template XLSX file and paste your sample code here to reproduce the issue on our end, we will check it soon.

Thank you.

Just tried aspose.cells for java 8.2.1.4 and got the same results (still takes a about 650MB of heap space and text format is still messed up). Unfortunately, posting a sample xlsx report we need to process is not an option because it has proprietary information. I'll see if I can create a test report with "dummy" data in it. But that will require some work.

Hi,


We appreciate your work in this regard, we will wait for the template file with dummy data to reproduce the issue on our end.

By the way, could you try to set the Memory preference option(s) before rendering the file if it makes any difference, please see the document/ article for your reference:
http://www.aspose.com/docs/display/cellsjava/Optimizing+Memory+Usage+while+Working+with+Big+Files+having+Large+Datasets

Thank you.

ok, I was able to produce a small dummy report file that illustrates the text format issue. Since it's a small file, it cannot be used to illustrate the large memory footprint issue. The Jasper generated XLSX file is attached. I used the following code to examine the content of this Jasper generated XLSX file:

Workbook doc1 = new Workbook("C:/Temp/Test_In_File.xlsx");
debugXlsx(doc1);

public static void debugXlsx(Workbook wb) throws Exception {
WorksheetCollection wsc = wb.getWorksheets();
Worksheet ws = wsc.get(0);
Cells cells = ws.getCells();
ColumnCollection cc = cells.getColumns();
RowCollection rc = cells.getRows();
int colCount = cc.getCount();
int rowCount = rc.getCount();

for (int rowIdx = 0; rowIdx < rowCount; rowIdx++) {
Row r = rc.get(rowIdx);
System.out.print("[" + rowIdx + "]: ");

for (int colIdx = 0; colIdx < colCount; colIdx++) {
Cell cell = r.get(colIdx);
Style style = cell.getStyle();
Font font = style.getFont();

if (font.isBold()) {
System.out.print("B");
}

if (font.isItalic()) {
System.out.print("I");
}

if (font.isStrikeout()) {
System.out.print("S");
}
System.out.print(",");
}
System.out.println();
}
}

The above code will print the letter B if it sees that the cell is bold, and I if italic and S if strikeout, and the following was being printed on the console (first 20 lines). As you can see, pretty much every cell was reported to be bold, italic and strikethrough. However, if you open the attached Jasper generated XLSX file in Microsoft Excel, there is no bold, italic or strikethrough content.

[0]: BIS,BIS,BIS,BIS,,,,,,,,,,,,,,,,,,,,,,
[1]: BIS,BIS,BIS,BIS,,,,,,,,,,,,,,,,,,,,,,
[2]: BIS,BIS,,,,,,,,,,,,,,,,,,,,,,,,
[3]: BIS,BIS,,,,,,,,,,,,,,,,,,,,,,,,
[4]: BIS,BIS,,,,,,,,,,,,,,,,,,,,,,,,
[5]: BIS,BIS,,,,,,,,,,,,,,,,,,,,,,,,
[6]: BIS,,,,,,,,,,,,,,,,,,,,,,,,,
[7]: BIS,BIS,,,,,,,,,,,,,,,,,,,,,,,,
[8]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[9]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[10]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[11]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[12]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[13]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[14]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[15]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[16]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[17]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[18]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[19]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,
[20]: BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,BIS,

Hi,


Thanks for the template file.

I observed the issue as you mentioned by using your sample code with your template file. I found the text formatting
issue while reading the cells’ styles in the template file provided by you. It prints almost every cell has bold, italic and strike
through effects. We need to look into it thoroughly if this is an issue with the product or there is
some issue in your codes, we will do it soon. I have logged a ticket with an id “CELLSJAVA-41055” for your issue.

Once we have any update on it, we will let you know here.

Thank you.

Thanks for the reply. As I have mentioned before, this is a show stopper issue for us. Can you please revert with status and ETA for when the issue can be fixed before end of day today?

Andrew

Hi,


Well, since we have logged the issue today 3-4 hours before. Please spare us little time (3-5 working days) to thoroughly investigate your issue on our end, hopefully your issue would be fixed in the next week.

Once we figure it out or we have some other update on it, we will let you know here immediately.

Thank you.

Hello Aspose support, it has been 3 days since we last heard from you. This has become an urgent issue for our project. Can you please provide an update? Can you please provide us direct email/phone contacts in order to expedite the process in creating a solution for this critical and urgent issue?

Andrew

Hi,


To update your further, we have fixed your issue now. We will provide you a fix in a couple of days hopefully after incorporating other enhancements and fixes and conducting some extensive testing. Once, it is available for you, we will let you know here by posting in this thread.

Keep in touch.

Thank you.

Dear Aspose Support,

Thanks for the reply. We are currently using aspose.cells 8.1.0 (which is the currently approved version we can use in our company) and in our company, we have to go thru a lengthy process in order to have another version to be certified and approved.

For the interest of enabling to meet our project deadlines, can you share what the problem was? and whether there is a way to "get around" the issue with the 8.1.0 version (without requiring us to upgrade)?

Andrew

Hi,


We are scheduled to release the next official release of the product i.e., Aspose.Cells for .NET v8.3.3 in the mid of next month or so, this official release would include the fix for your issue, so you may use that version if you do not want the interim fix here. Also, there is not any labor involved while upgrading from 8.1.0 to 8.3.3, the APIs are the same with a very few minor changes or new enhancements that you may or may not have to do in your existing codes, please see the documents here for API Reference Changes for your reference:
http://www.aspose.com/docs/display/cellsnet/Migrating+from+Earlier+Versions+of+Aspose.Cells
Also, if your license subscription allows (check the expiry date by opening your license file into notepad), you may upgrade to new versions for free.

Well, we consider it is a bug in older versions, so we fixed it now in the latest version (the fixes can only be included in the latest code base). I am afraid, there may not be any workaround to cope with it apparently. Anyways, if we found any workaround, we will surely share it with you here.

Thank you.

Will the interim fix have a different version number (different from 8.1.0)?

When using Aspose to parse XLSX data created with Microsoft Excel, everything worked as expected. The issue was only when Jasper generated XLSX and Aspose. So what exactly was the incompatibility between the Jasper generated XLSX and Aspose?

We are thinking perhaps there is something we can do with the Jasper generated XLSX data before passing it to Aspose 8.1.0 for processing.

Hi,


Well, the interim fix’s version number would be v8.2.2.1 (latest fix).

For your curiosity, we have evaluated your issue in details. Actually, in your file (Jasper generated XLSX file), there are entries like: " " stored in the source xml file. If the file is saved by MS Excel and the font is not bold, the element “B” should not exist there. We just check whether the element b exists to get whether the font is bold or not. So, we have fixed this issue accordingly in the latest fix. MS Excel does not save the attribute “val” of the element “b”.

Thanks for your understanding!

Hi,


Please try our latest fix/version: Aspose.Cells for Java v8.2.2.1

We have fixed the issue in it now.

Let us know your feedback.

Thank you.

Thanks for the reply. I'd like to go back to the large memory footprint issue related to Jasper generated XLSX file. When loading a large Jasper generated XLSX file (12MB file in our test case), we found that Aspose needs at least 1.3GB of heap space. Perhaps these extra/unnecessary tags are causing unnecessary memory allocation? As I mentioned in my first message, this kind of memory footprint is too large for our application.

Also after using Aspose to save the data (loaded from this 12MB XLSX file) into XSLB format, when we opened the resulting .xlsb file in Microsoft Excel, it took a long time to open. I didn't measure the exact time, but it was definitely more than 5 minutes to open it up. We would get a lot of complaints from our users if we send them excel files that take so long to open up.

In addition, the resulting XLSB file (saved with Aspose) had a file size of about 6.5MB. But after we opened the XLSB file in Micorosft Excel (about > 5 minutes later), when we saved the file in Microsoft Excel back as XLSB, the file generated by Excel was about 3MB. Why the Aspose generated XLSB files are so much bigger?

Are the above two phenomena also caused by these extra/unnecessary tags created by Jasper?

Hi,


We appreciate if you could give our latest version a try if it makes any difference regarding memory footprint and file size issue. Also, we would recommend to utilize memory perference options while loading the large Excel file which are supported by Aspose.Cells now, see the document for your reference:
http://www.aspose.com/docs/display/cellsjava/Optimizing+Memory+Usage+while+Working+with+Big+Files+having+Large+Datasets


Alternative, we have LightCells APIs which are specifically used for reading data in even driven mode and creating huge Excel spreadsheets when you have memory concerns and want to get better performance and efficiency.

If you still have any issue, kindly do attach your template Excel file and paste your sample code/ JAVA program (runnable) here, so we could simulate the issue on our end and try to figure it out soon.

Thank you.

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


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.