Non-Excel basic Formulae

[Administrator]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.