Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

How to write Excel Addin functions!!

by haxman (Initiate)
on Sep 21, 2011 at 09:17 UTC ( #927105=perlquestion: print w/ replies, xml ) Need Help??
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

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()");

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
Re: How to write Excel Addin functions!!
by Corion (Pope) on Sep 21, 2011 at 09:21 UTC
Re: How to write Excel Addin functions!!
by planetscape (Canon) on Sep 22, 2011 at 09:28 UTC
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
Re: How to write Excel Addin functions!!
by MidLifeXis (Prior) 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

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).

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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://927105]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (14)
As of 2014-10-20 18:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (88 votes), past polls