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.

Non-Excel basic Formulae

Thread ID:

Created:

Updated:

Platform:

Replies:

45446 Jun 21,2006 07:30 AM Jul 28,2006 08:24 PM Windows Forms 11
loading
Tags: XlsIO
Administrator [Syncfusion]
Asked On June 21, 2006 07:30 AM

Hi, I''m trying to generate some spreadsheets with a combination of excel native formulae and bloomberg formulae - the bloomberg formulae will be updated whenever the spreadsheet is opened in Excel. However, I''m having problems outputting the formulae not native to Excel. Is there no way to emit formulae and not have XlsIO try to parse them?

Administrator [Syncfusion]
Replied On June 21, 2006 03:33 PM

Hi Daniel, Here is some code snippet to achieve this //Define Function IAddInFunctions unknownFunctions = workbook.AddInFunctions; unknownFunctions.Add( "TEST" ); //Use Function sheet.Range[ "A1" ].Formula = "TEST(45)"; Best regards, Stephen. >Hi, I''m trying to generate some spreadsheets with a combination of excel native formulae and bloomberg formulae - the bloomberg formulae will be updated whenever the spreadsheet is opened in Excel. > >However, I''m having problems outputting the formulae not native to Excel. Is there no way to emit formulae and not have XlsIO try to parse them?

Administrator [Syncfusion]
Replied On June 22, 2006 05:42 AM

Thanks for the response. I''ve just tried this, and I get an exception thrown : "Expression Error : Formula string can''t be empty". When I look at the AddInFunctions collection on the workbook I can see a new addin function, but the name returns a null reference exception. Does the Add() method work? I also saw the IWorkBook.ThrowOnUnknownNames property and tried setting this to false (assuming it would ignore the unknown formula name), but it did nothing...

Administrator [Syncfusion]
Replied On June 23, 2006 12:38 PM

Hi Daniel, I''m afraid.I could see the Add() method working and cant see any problems using it. Here is the sample I used to test this issue:XlsIO_Addins.zip Could you please modify the above sample to show your problem for further investigating of this issue. Thanks, Bharath

Administrator [Syncfusion]
Replied On June 26, 2006 05:41 AM

Bloomberg formulae begin with "BLP", if I replace the "age()" in your example with "BLP()" (or "blp()"), your example application throws an "unknown function name" exception. i.e. workbook.AddInFunctions.Add( "blp()" ); sheet.Range[ "A3" ].Formula = "blp(A1)"; doesn''t work. It''s probably important to mention that bloomberg formulae also have more than one parameter and these aren''t necessarily cell references. If I actually try your age() function with bloomberg like parameters, it throws an exception, i.e. workbook.AddInFunctions.Add( "age()" ); sheet.Range[ "A3" ].Formula = "age(A1 + \" CORP\", \"PX_LAST\")"; throws "Index was outside the bounds of the array."

Administrator [Syncfusion]
Replied On June 26, 2006 06:24 AM

I''m actually confused as to how your age() function ends up working. By this I mean it actually *does something*...I was rather expecting a "#NAME" error to appear (which is what I expect for the bloomberg formulae). This would appear to be a function which either syncfusion or excel recognise, so is not really a good test. There''s also a bug here with regards to external references. Any formula of the form "X!Y" should not be parsed by syncfusion (there is an alternate way to call bloomberg via an external reference ("=BLP|M!''x, y''") but syncfusion incorrectly tries to parse this and throws an exception).

Administrator [Syncfusion]
Replied On June 28, 2006 06:58 PM

Hi Daniel, Sorry for the delay in getting back to you. Sorry for the confusion regarding previous update.I am able to see the problem.Now I tried the addin function with the formula,sheet.Range[ "A3" ].Formula = "blp(A1+\" CORP\",\"PX_LAST\")"; and it is not throwing any exception.Here is the sample I tried:XlsIO_Addins.zip Currently we dont support link to the external sheet in formulae and this is in our feature request. Please track the status of this feature through the following link. http://www.syncfusion.com/support/features/xlsio/Default.aspx?ToDo=view&questId=929 Please take a look at the attachment and let me know if you have any questions. Thanks, Bharath

Administrator [Syncfusion]
Replied On July 3, 2006 05:17 AM

Just thought I''d let you know that I finally figured out what was causing the "Index out of bounds" exception - SPACES. Your formula parsing routine seems to throw an exception if the formula has spaces in it (I usually put them in to aid legibility). i.e. range.Formula = "BLP(A1+\"CORP\",\"PX_LAST\")"; will work fine, whereas range.Formula = "BLP(A1 + \" CORP\", \"PX_LAST\")"; will throw an exception.

Administrator [Syncfusion]
Replied On July 3, 2006 12:26 PM

Hi Daniel, Sorry for the confusion.Currently,We don''t support spaces inside the Formulae.However, I will consult our development team regarding this issue and update you as soon as when I hear back. Thanks, Bharath.

Administrator [Syncfusion]
Replied On July 13, 2006 07:21 PM

Hi Daniel, Sorry for the delay in getting back to you.Currently we have no plans to support spaces inside the formulae.Please let me know if you have any other queries.Thanks for your interest in Syncfusion products. Thanks, Bharath.

Joseph Spivey
Replied On July 28, 2006 05:20 PM

Daniel, Thank you so much for your follow up regarding spaces. I have just spent hours trying to debug a formula type conditional format that worked OK in excel, but bombed in xlsIO. As soon as I knocked the spaces out of the formula, it worked. Note to SyncFusion, if you won''t support spaces, a better error message than "formula string can not be empty" would be appreciated :-)

Thanks again Daniel, Joe

>Just thought I''d let you know that I finally figured out what was causing the "Index out of bounds" exception - SPACES.
>
>Your formula parsing routine seems to throw an exception if the formula has spaces in it (I usually put them in to aid legibility).
>
>i.e.
>range.Formula = "BLP(A1+\"CORP\",\"PX_LAST\")";
>will work fine, whereas
>range.Formula = "BLP(A1 + \" CORP\", \"PX_LAST\")";
>will throw an exception.

Administrator [Syncfusion]
Replied On July 28, 2006 08:24 PM

Hi Joe,

Sure,an error message is the one that should be added in the future.Thanks for your interest in Syncfusion products.

Regards,
Bharath.

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.

;