How to write Excel Addin functions!!

haxman has asked for the wisdom of the Perl Monks concerning the following question:

Using Spreadsheet::WriteExcel or Excel::Writer::XLSX

Is there a way to write the functions defined in some ADDINs of Excel.

 
$worksheet->write($i,1,"=BDH()"); #fails to write in Excel
[download]

This above query not able to be interpreted by perl as function/formula. Whereas, below one is written as string in excel but a string not as function. Hence, Excel fails to formulate this string.

 
$worksheet->write_string($i,1,"=BDH()");
[download]

Please provide some solution to write such formulas in Excel. I know it is not directly supported but there should be some workaround.

Comment on How to write Excel Addin functions!! Select or Download Code

Replies are listed 'Best First'.
Re: How to write Excel Addin functions!! by Corion (Patriarch) on Sep 21, 2011 at 09:21 UTC
Have you read the section Formulas and Functions in Excel in the Spreadsheet::WriteExcel documentation? I think it is not possible to use functions declared in an Add-In with these modules.	[reply]
Re: How to write Excel Addin functions!! by planetscape (Chancellor) on Sep 22, 2011 at 09:28 UTC
See Inserting a Button and a Macro in and excel file HTH, planetscape	[reply]
Re: How to write Excel Addin functions!! by MidLifeXis (Monsignor) on Sep 22, 2011 at 09:55 UTC
In the code for Spreadsheet::WriteExcel::Formula, it appears that the functions each have some sort of internal code representing it. Given that, and since I am not seeing any obvious way implementing this, I would guess that it would require a patch to the S::WE:F module. --MidLifeXis	[reply]
Re: How to write Excel Addin functions!! by jmcnamara (Monsignor) on Sep 22, 2011 at 23:09 UTC
You have figured out some of this already but just to give you a definitive answer: Third party/add-in functions aren't supported by Spreadsheet::WriteExcel and there is no easy way to add them Third party/add-in functions are supported by Excel::Writer::XLSX which requires perl 5.10. There is a version of Excel::Writer::XLSX on GitHub that supports perl 5.8.2 which you might be able to backport to 5.6.0 with an reasonable amount of work. -- John.	[reply]
Re: How to write Excel Addin functions!! by haxman (Initiate) on Sep 22, 2011 at 09:34 UTC
I cant use OLE as whole server is on unix plaform	[reply]
Re: How to write Excel Addin functions!! by haxman (Initiate) on Sep 22, 2011 at 13:59 UTC
Ya, i tried to patch this up in Formula.pm. It works for xlsx by just adding a Function (say PLSFUNCT) of desired name in Formula.pm hash list of functions. But this approach fails for xls writing. I m able to achive the required in xlsx format but since the server has old version of perl 5.6.0 so xlsx not supported. I, somehow, need to achieve this for xls format I am able to add function using Spreadsheet::WriteExcelXML but in the end its a XML spreadsheet not a proper xls format. Requirement is some how to do this in perl 5.6.0. And produce Excel file should me able to support macros (i.e. no XML Spreadsheet). I m trying to make this Excel::XLSX module (minimum perl 5.10) to work with this version of perl 5.6 but still lots of Headaches (realy messi).	[reply]