XlsIO can't open this file: ArgumentOutOfRangeException

[Ma-Estro]

XlsIO seems to be unable to open the attached XLSX file. An ArgumentOutOfRange exception is generated.

Exception class: System.ArgumentOutOfRangeException

Exception message (translated from Italian): Specified argument not in range. Parameter name: strToken

Stack trace:

   in Syncfusion.XlsIO.Implementation.FormulaParser.TryGetNamedRange(String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, Int32 iRefIndex, Ptg& result)

   in Syncfusion.XlsIO.Implementation.FormulaParser.ParseIdentifier(String identifier, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   in Syncfusion.XlsIO.Implementation.FormulaParser.ParseFirstOperand(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions& options, ParseParameters arguments)

   in Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   in Syncfusion.XlsIO.Implementation.FormulaParser.Parse(String formula, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   in Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Dictionary`2 indexes, Int32 i, Dictionary`2 hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn)

   in Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Dictionary`2 hashWorksheetNames)

   in Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula)

   in Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseNamedRanges(XmlReader reader)

   in Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseWorkbook(XmlReader reader, RelationCollection relations, FileDataHolder holder, String bookPath, Stream streamStart, Stream streamEnd, List`1& lstBookViews, Stream functionGroups)

   in Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.ParseWorkbook(List`1& themeColors, Boolean parseOnDemand)

   in Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.ParseDocument(List`1& themeColors, Boolean parseOnDemand)

   in Syncfusion.XlsIO.Implementation.WorkbookImpl.ParseExcel2007Stream(Stream stream, String password, Boolean parseOnDemand)

   in Syncfusion.XlsIO.Implementation.WorkbookImpl.ParseStream(Stream stream, String password, ExcelVersion version, ExcelParseOptions options)

   in Syncfusion.XlsIO.Implementation.WorkbookImpl..ctor(IApplication application, Object parent, Stream stream, ExcelParseOptions options, ExcelVersion version)

   in Syncfusion.XlsIO.Implementation.ApplicationImpl.CreateWorkbook(Object parent, Stream stream, ExcelVersion version, ExcelParseOptions options)

   in Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelVersion version, ExcelParseOptions options)

   in Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelOpenType openType, String fileName, ExcelVersion version, ExcelParseOptions options)

   in Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType, ExcelVersion version, ExcelParseOptions options)

   in Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType, ExcelParseOptions options)

   in Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType)

   in Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String filename)

What's wrong with my file? Or is it a problem with XlsIO?

Thanks and best regards

Riccardo De Agostini

Attachment: SalesReportTemplate_e1d2c4fb.zip

[Ishwarya Narayanan]

Hi Riccardo,

 

Thank you for using Syncfusion products.

 

We have analyzed the given file and found that the file is corrupted due to an invalid named range. This can be confirmed by opening the file in MS Excel itself. This can be repaired in MS Excel, which results in removal of such named ranges. Whereas, XlsIO throws exception and doesn’t repair it. This is not an issue. You can resave the file with MS Excel and open in XlsIO, which resolves the issue.

Please let us know if you need any clarification.

 

Reagrds,

Ishwarya N

[Riccardo De Agostini]

Hi Ishwarya,

the file has been created with LibreOffice Calc, which has no problem opening it. I don't have Excel, nor have I ever needed it before.

Is there a way to have XlsIO just ignore invalid named ranges?

Thanks and best regards
Riccardo

[Riccardo De Agostini]

Solved! I'm writing this both as a note to myself, and to anyone who may have the same problem in the future.

LibreOffice, prior to version 4.2, produced corrupted files when saving in MS Office XML-based formats (XLSX, DOCX, PPTX). Even when opening those files and saving them with a newer LibreOffice version, they still cannot be opened by MS Office programs (nor by XlsIO) without repairing.

I've found out a quick method for repairing LibreOffice-generated XLSX files using LibreOffice itself (tested with v4.3): just save the file in XLS format, close LibreOffice, then open the XLS file and save it in XLSX format again. No Excel required!

Of course it would still be nice if XlsIO had a way of just ignoring spurious data in XLSX files, but at least now there's a workaround of some sort.

Best regards

Riccardo

[Ishwarya Narayanan]

Hi Riccardo,

 

Thank you for updating us.

 

We consider your suggestion regarding the repairing part in XlsIO and request you to log a feature request by creating an incident from DitectTrac.

 

Please let us know if you need any clarification.

 

Regards,

Ishwarya N