Exceptions With xls Document

mstandfuss · June 6, 2019, 4:05pm

Version 19.5, Java Aspose Cells.

The original document (A.PC.00004109.0.0.xls) that was causing the issue lead to this exception.
Caused by: java.lang.IllegalStateException: Invalid encoding: null
at com.aspose.cells.Encoding.a(Unknown Source)
at com.aspose.cells.Encoding.a(Unknown Source)
at com.aspose.cells.zlo.B(Unknown Source)
at com.aspose.cells.zlo.a(Unknown Source)
at com.aspose.cells.zlo.a(Unknown Source)
at com.aspose.cells.zcfj.p(Unknown Source)
at com.aspose.cells.zcfj.a(Unknown Source)
at com.aspose.cells.zjl.a(Unknown Source)
at com.aspose.cells.zjl.a(Unknown Source)
at com.aspose.cells.zjl.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.(Unknown Source)
… 5 more
Caused by: java.lang.IndexOutOfBoundsException
at java.nio.ByteBuffer.wrap(ByteBuffer.java:375)
… 17 more

After opening it in Excel and resaving it (resaved.xls), that error went away but a new one surfaced.
Caused by: java.lang.ArrayIndexOutOfBoundsException: 10
at com.aspose.cells.zlo.a(Unknown Source)
at com.aspose.cells.zlo.a(Unknown Source)
at com.aspose.cells.zlo.w(Unknown Source)
at com.aspose.cells.zlo.a(Unknown Source)
at com.aspose.cells.zcfj.p(Unknown Source)
at com.aspose.cells.zcfj.a(Unknown Source)
at com.aspose.cells.zjl.a(Unknown Source)
at com.aspose.cells.zjl.a(Unknown Source)
at com.aspose.cells.zjl.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.(Unknown Source)

Sample Code:
File file = new File("");
InputStream inputStream = FileUtils.openInputStream(file);
Workbook workbook = new Workbook(inputStream);

ahsaniqbalsidiqui · June 6, 2019, 4:53pm

@mstandfuss,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42923 - Exceptions while loading XLS Document

mstandfuss · June 6, 2019, 8:07pm

Thanks appreciate the quick response.

ahsaniqbalsidiqui · June 7, 2019, 2:40am

@mstandfuss,
You are welcome.

amjad.sahi · June 10, 2019, 8:50am

@mstandfuss,

We evaluated your issue further. Well, your given file is of Excel 95 format. The exception is caused by reading charts. I am afraid, currently we do not support reading charts of this (older) file format.

mstandfuss · June 10, 2019, 1:17pm

We do not support, as in we currently do not support but are logging something to fix, or we will never support?

Are there any workarounds for this? We have to deal with Excel 95 files in our application and were hoping to switch from using Apache POI to Aspose Cells across the board for all Excel file handling. Since this happens right off the bat we are unable to interact with the file at all. If we could work around the graphs, maybe we could at least put a disclaimer in our application that we can do Excel 95 files, just not the graphs part.

Thanks

amjad.sahi · June 10, 2019, 2:48pm

@mstandfuss,

The issue is still open, so we might fix it in some future versions (although not in near future).

As a workaround to cope with the issue, currently, you may try to re-save the file (after opening the file into MS Excel 2007/2010/2013, etc.) as “Excel Workbook” (as .XLSX) or “Excel 97-2003 Workbook” (as .XLS), Now try using Aspose.Cells if it works fine with the re-saved file.

mstandfuss · June 10, 2019, 9:28pm

Unfortunately that will not be possible for us the way our application works currently. The user uploads an excel file into our platform and we use Aspose to manipulate/extract information from it. There is no manual intermediate step where we could open it in MS Excel to change the format of it without prompting our users to do so prior to uploading. Which they are usually dumping mass amounts of data that the excel file might be many layers deep in so also not probable at that level either.

If you have any other workarounds that come to mind we would appreciate it and give it a try.

Thanks

amjad.sahi · June 11, 2019, 8:10am

@mstandfuss,

Well, currently you may check the file format first (by using FileFormatUtil.DetectFileFormat(string)) and if it is Excel95 file, you may specify the LoadFilter to ignore loading Charts, Shapes, …etc. (these elements are not supported in Excel 95 file format at the moment). Also, we will evaluate whether the option that we ignore Charts and Shapes automatically is appropriate for most users, if true, then we may do that in later fixes/versions before we could support them.

mstandfuss · June 11, 2019, 1:16pm

Ah great, I was unaware of the DetectFileFormat method, I will take a look at utilizing this and report back. Much thanks!

mstandfuss · June 13, 2019, 8:20pm

I tried to use these loading options but am still getting the Invalid encoding.

int loadFilterOptions = LoadDataFilterOptions.ALL & ~LoadDataFilterOptions.CHART &
~LoadDataFilterOptions.FORMULA & ~LoadDataFilterOptions.TABLE &
~LoadDataFilterOptions.PIVOT_TABLE & ~LoadDataFilterOptions.SHAPE;

Am I doing that how you were expecting?

I should point out that all this current bit of code cares about is the comments, hidden sheet/row/columns, and locked row/sheet/columns. Same ArrayIndexOutOfBoundsException with the resaved version I provided as well.

ahsaniqbalsidiqui · June 14, 2019, 5:10am

@mstandfuss,
Could you please try latest version/fix as we have tested your given file A.PC.00004109.0.0.xls with following code and cannot find exception:

LoadOptions opts = new LoadOptions();
opts.setLoadFilter(new LoadFilter(LoadDataFilterOptions.ALL &;
~LoadDataFilterOptions.CHART &;
~LoadDataFilterOptions.FORMULA &; ~LoadDataFilterOptions.TABLE &;
~LoadDataFilterOptions.PIVOT_TABLE &; ~LoadDataFilterOptions.SHAPE));
new Workbook("A.PC.00004109.0.0.xls", opts);

aspose-cells-19.5.6.zip (6.5 MB)

Let us know your feedback.

mstandfuss · June 17, 2019, 2:38pm

Yes you are right it is running fine now, I must have been running my older code instead of the updated. It works the way you specify. I will copy my full code below in case anyone else is interested, until this gets patched. Appreciate the help.

try (FileInputStream inputStream = new FileInputStream(path)) {

	int loadFilterOptions = LoadDataFilterOptions.ALL & ~LoadDataFilterOptions.CHART &
			~LoadDataFilterOptions.FORMULA & ~LoadDataFilterOptions.TABLE &
			~LoadDataFilterOptions.PIVOT_TABLE & ~LoadDataFilterOptions.SHAPE;
	LoadOptions loadOptions = new LoadOptions(loadFilterOptions);
    //Optional to handle large excel files at degraded performance 
	loadOptions.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
	Workbook workbook = new Workbook(inputStream, loadOptions);
	WorksheetCollection worksheetCollection = workbook.getWorksheets();
	...
}

amjad.sahi · June 17, 2019, 2:59pm

@mstandfuss,

Thanks for your confirmation.

Good to know that your are able to sort it out now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

ahsaniqbalsidiqui · November 28, 2019, 7:08am

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

ahsaniqbalsidiqui · December 2, 2019, 7:37am

@mstandfuss,
Please try our latest version/fix: Aspose.Cells for Java v19.11.5:
aspose-cells-19.11.5.zip (6.7 MB)

Your issue should be fixed in it.

Let us know your feedback.

aspose.notifier · December 20, 2019, 9:55am

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