We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Opening an Excel file containing defined names invalid formulas

Thread ID:

Created:

Updated:

Platform:

Replies:

102271 Jan 16,2012 07:07 PM Jan 24,2012 10:27 PM ASP.NET Web Forms (Classic) 5
loading
Tags: XlsIO
Patrick Leong
Asked On January 16, 2012 07:07 PM

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 [Syncfusion]
Replied On January 18, 2012 06:17 AM

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
Replied On January 18, 2012 10:27 AM

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 [Syncfusion]
Replied On January 20, 2012 12:10 AM

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
Replied On January 23, 2012 05:16 PM

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 [Syncfusion]
Replied On January 24, 2012 10:27 PM

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.



CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;