Opening an Excel file containing defined names invalid formulas

[Patrick Leong]

Greetings,

I have a question with the Syncfusion XlsIO library regarding an issue opening an excel file.

We have to parse various XLS and XLSX files coming from a client. In order to read the file, I attempted to open the file using the following code:

excelEngine.Excel.Workbooks.OpenReadOnly(fileName, ExcelOpenType.Automatic, ExcelParseOptions.DoNotParseCharts);

However, when trying to open a specific XLS file, Syncfusion throws exception, indicating that a formula's argument list is incomplete.

Upon further investigation, I finally found the formula the error message indicates. It is actually in the defined names section. I suspect that some defined names got left over after attempting to make a chart, leaving off numerous defined names with bad / invalid formulas.

Those formulas have absolutely no value to exist in the workbook, as far as we concern; so we would like to see if there is anyway in Syncfusion to do one of the two following:

- Opening the excel file with formula validation disabled. (desirable)
- Removing the defined names

I imagine that removing the defined names requiring the workbook to be opened. So is there anyway, desirably, to open the workbook without formula validation during the opening phrase?

Thank you very much.

[Manoj Kumar G M]

Hi Patrick,

Thank you very much for your interest in Syncfusion Products.

To avoid throwing error when unknown named ranges were parsed, please set the "ThrowOnUnknownNames" property as false.

Following code snippet illustrates this:

workbook.ThrowOnUnknownNames = false;

Please go through the following online documentation link for more details.

http://help.syncfusion.com/ug_94/Reporting/XlsIO/Windows%20Forms/default.htm?turl=Documents%2Fworkbook.htm

Please let me know if you have any concern.

Regards,
Manojkumar.

[Patrick Leong]

Hi Manojkumar,

Thank you for the update and letting me know about the property.

However, the exception was thrown at the Open (or OpenReadOnly) statement. When opening the workbook with defined names containing bad formulas, this statement itself does not finish, thus I was not even able to get the IWorkbook reference. As a result, I did not have a chance to set that property at all.

Also, upon further investigation, the formulas embedded in the defined names are in the wrong format. Meaning, it is not that they reference to unknown cells - but the formula's syntax is wrong. I don't know how come Microsoft Office (and thus Interop itself) would be able to ignore that.

So is there any way to complete the Open / OpenReadOnly operation with bad formulas? Once I got a handle on the IWorkbook reference I will be able to work around it, but right now it does not even open.

Thanks for your concern and your assistance,
Patrick

[Manoj Kumar G M]

Hi Patrick,

Thanks for your update.

It would be great if you upload the offending file causing the exception, which could be helpful for further analysis.

Please let me know if you have any concern.

Regards,

Manojkumar.

[Omar Masri]

Is there an update on this issue. I would also like to be able to open a workbook without ThrowOnUnknownNames=false.

To do it needs to be a property that is accessible from ExcelEngine.Excel prior to the open call.

[Manoj Kumar G M]

Hi Patrick,

We are able to reproduce the reported issue at our side and suspect this could be a defect.

So we request you to open a new direct-trac incident with a simple issue reproducing sample for further follow up on this issue.

Direct-Trac Link:

https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents

Please, let us know if you require any further clarifications.

Regards,
Manojkumar.