Free Support Forum - aspose.com

Aspose.cells.workbook.open throws io error

I am trying to upload an Excel 2007 sheet but workbook.open throws this error:

AsposeCellsErproSheetReader.fileIsValid(): java.io.IOException caught: java.io.IOException: It is not a valid excel2007 file.Unsupported formula of name : ='[2]Input Data'!$C$14
java.io.IOException: It is not a valid excel2007 file.Unsupported formula of name : ='[2]Input Data'!$C$14 at com.aspose.cells.Workbook.open(Unknown Source) at com.jovus.erpro.server.excel.AsposeCellsErproSheetReader.fileIsValid(AsposeCellsErproSheetReader.java:372)

Here is my code:

try
{
workbook = new com.aspose.cells.Workbook();
String excelFileName = excelFile.getAbsolutePath().toLowerCase();

if (excelFileName.endsWith(".xlsx"))
{
workbook.open(excelFileName, com.aspose.cells.FileFormatType.EXCEL2007);
}
else
{
workbook.open(excelFileName, com.aspose.cells.FileFormatType.EXCEL2003);
}

}
catch (IOException e)
{

excelFileName = testworkbook.xlsx

FileFormatType.EXCEL2007 = 10

I can open the workbook in Excel2007 - I cannot open it in Excel 2003 - error says: "The converter failed to open the file."

I can successfully save the Excel 2007 workbook as Excel 2003 (.xls) and can open this version in Excel 2003.

Why am I getting this error? Please help - thank you.

Hi,

Could you post your .xlsx file here, we will check your issue and figure if out (if we found the issue) asap.

Thank you.

Attached is a sheet causing the problem.

One thing that is curious is that this sheet was originally a .xls sheet. I converted it to .xlsx (I'm running Excel 2003) and then got the error on this new .xlsx sheet.

Now, if I create a brand new .xlsx sheet (from Excel 2003), and then copy the contents of the .xls sheet to the new .xlsx sheet, it will work.

Hi,

Thank you for considering Aspose.

The template file you have uploaded is not supported by Excel 2003. If you try and open the file in Excel 2003 it will give you an error that "File is not in a recognizable format". That is why when you try to open this file in your code, you get the same issue.

This is may be because your file has not been properly converted to .xlsx format. Can you please post your .xls file as well so we can figure out the issue?

Thank you & Best Regards,

I incorrectly told you I could not open the file -- I can. Sorry. That was another sheet and I could not send because of confidentiality.

Attached are two files: the original .xls & the converted file .xlsx.

I am able to open in Excel 2003, both the .xls AND the .xlsx. What I cannot do is open the .xlsx using the Apose.cells.workbook.open code.

I

Hi,

Thank you for providing the template files. We have tested your files by using the latest version of Aspose.Cells and we were unable to reproduce the problem you have mentioned. This problem may be occurring due to the use of an older version of Aspose.Cells. Please try the attached latest fix of Aspose.Cells.

Thank you & Best Regards,

Thank you for the very quick responses!

Please note that we are not working with dll - we are working the with Aspose.Cells.jar file. Do you have a fix for this as well?

We're working with java, running Tomcat, servlets.

I was just about to post a very similar problem.

My issue happens any time that I open an Excel 2003 file in Excel 2007 then Save As the .xlsx format. I am using very simple data, no formulas at all. I have Aspose version 1.9.5 Hotfix.

Attached is my converted .xlsx file and below is the error message that I am receiving. Both versions of Excel are able to open this file, but Aspose cannot.

====================================================================
2008-10-23 14:36:09.630 ID0000 GRP= | Caught Exception in openFileAspose(): It is not a valid excel2007 file: Invalid page setup zoom:this number must be between 10 and 400
java.io.IOException: It is not a valid excel2007 file: Invalid page setup zoom:this number must be between 10 and 400
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.open(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.open(Unknown Source)
at com.saic.afm.util.excel.ExcelRead.openFileAspose(ExcelRead.java:295)


Sorry, attached now is the .xlsx file!

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Sorry for misunderstanding and providing the wrong dll. Please try the Aspose.Cells for Java latest fix from the following path.

http://www.aspose.com/community/files/51/file-format-components/aspose.cells/category1195.aspx

Please do let us know if you still face any problem.

Thank you & Best Regards,

Hi,

Thank you for your template file. Was this xlsx file created by MS Excel? The Zoom value of PageSetup is invalid in it, when resave it again by MS Excel2007, the newly created .xlsx file is fine. Anyway, now we try to catch such Exception and ignore the invalid value for Zoom of PageSetup. Please try this attached version.

Hi,

For the Exception "java.io.IOException: It is not a valid excel2007 file.Unsupported formula of name : ='[2]Input ...", it is caused by the Extern reference for defined Names in this workbook, I am afraid Extern reference is not supported by current version. We will look into and support it in our later versions, currently please use the fix attached in this thread, we have caught such Exception and ignored the Name having Extern reference, thank you.

I have the exact same problem, also caused by an Extren reference for defined Names. I’m not seeing the patch on the thread, even though I am signed in… how do I retrieve it?

Hi,

Thank you for considering Aspose.

Please try the latest fix of Aspose.Cells attached with this post.

Thank you and Best Regards,

using that patch I still get the unsupported formula errors...

is there a mode I can start it in so that it just ignores formulas that are not supported rather than throwing an exception?

If you look inside the xml for the excel file(MainExcelFile\xl\worksheets\sheet1.xml) the value I want (500) is in there, I just need Aspose Cells to ignore formulas that it can't undestand and not throw it up as an IOException.

I've attached sample files and unit test.

Your help is MUCH appreciated. I assume that this is this a different problem than the one the patch was fixing?

Hi,

Since your template file has external reference to other file. And, as Johnson has shared the external reference is not currently supported, we will certainly support the feature in our future versions.

Thank you.

Hi,

Thank you for your template file and test case, please try this fix, we have ignored all unsupported formula when reading in xlsx files.

Thank you, the unit test I had sent you now passes! Unfortunately when I use the patch with the live xlsx file I still get an IO error. I've narrowed it down once again and have attached a zip as before containing a file that throws the same error, as well as a test case.

I can't figure out what is invalid about this file, it loads fine in Excel. The second sheet has a print title, but there doesn't seem to be anything wrong with it. Really strange is that when you remove sheet1 from the workbook the excel file loads fine. Thank you again for your assistence.

Okay... a little more information. The error is on sheet1 of the file that I uploaded in the zip. You can delete the second sheet (which does have a print range) and still get the following error:

java.io.IOException: It is not a valid excel2007 file: Invalid print titles columns

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

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

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

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

When you set a row / column print range for sheet1 (none is showing in excel), then the error goes away. I'm wondering though why exceptions need to be thrown for this? If excel ignores them and the page loads, it makes it difficult that Aspose doesn't, especially when no clue is given as to which sheet contains the error? We have very large spreadsheets, and since the invalid named range doesn't actually show, this makes it hard to fix manually. Future: could there be a debug option that decides whether or not IO errors are thrown or ignored on workbook open? For now if this error could be ignored that would be fine. I've tested it without his sheet and everything else is being resolved fine, so this appears to be the last hurdle to opening a 2007 excel file with Aspose Cells.

Thanks!

Wrote to soon... here is another one I'm getting....

java.io.IOException: It is not a valid excel2007 file: Invalid FontUnderlineType:none
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.open(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.open(Unknown Source)

Help? Any recommendations on something I can do to ignore these and still open the file? If the font underline type is invalid, why can't the code just ignore font underline properties (and maybe log it as a warning with some indication as to where (sheet / cell) the error happened)? What does it help me to have an exception with no clue as to where the error is in the file?

Thanks for your help. Any advice you might have is appreciated, as it is a must to be able to open office 2007 files in our application.