generating Excel spreadsheets in perl that reference 3rd party add-in functions

Question

I am trying to generate an xls file with Spreadsheet::SimpleExcel that calls a function that is defined in a third party add in (Bloomberg, if it matters). The underlying WriteExcel package does not let me write this out because it does not know about this add-in function. I see the giant hash table of built-in functions that the module knows about and could potentially add this function to it, but I don't know the attributes of the function, especially the 'ptg code'.

Is there a way to either determine what the ptg code (I assume this is something like an opcode) of this third-party function, or is there some way to trick the module into letting me write out these functions without it having to know these details? Or some third plan that I haven't thought of?

Answer 1

I think you need to use Win32::OLE and it's a bit messy. Try:

$xls->AddIns->Add( $xll_path );
$xls->RegisterXLL( $xll_file );

where $xll_path is the full path to the xll for Bloomberg and $xll_file is just the xll name.

Answer 2

Spreadsheet::WriteExcelXML and Excel::Writer::XLSX should be able to generate Excel files with third party functions.

Both modules use the same interface as Spreadsheet::WriteExcel although with fewer features.