Appending to Excel Spreadsheets in Perl on Windows AND Linux

Question

I want to append to an Excel spreadsheet. Win32::OLE works perfectly with this, but it doesn't work on Linux machines. Spreadsheet::WriteExcel doesn't seem to be able to append while it can work on both platforms. I found out about Excel::Writer::XLSX, but it reports that it does not have as many features as Spreadsheet...

I would also like to create hyperlinks, so clicking on some text will lead you to another sheet in the Excel workbook. Is this possible and on which one (Excelwriter/etc)?

So two questions: 1) What should I use so I can append to spreadsheets while working on both platforms?' 2) Is it possible to create hyperlinks to lead to other sheets and using which one?

Here is the code I used for Win32::OLE that appended a new worksheet:

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
print "Opening Excel...\n";

$Excel = Win32::OLE->GetActiveObject('Excel.Application')
   || Win32::OLE->new('Excel.Application', 'Quit');

#appending to existing excel file
my $book = $Excel->Workbooks->Open($full_name) || die "Error: Could not open Excel workbook";

#putting this sheet at the beginning
my $sheet = $book->Worksheets->Add({Before => $book->Worksheets(1)}) or die "Error: " . Win32::OLE->LastError;

Why? All I want to do is append to a file on both platforms and create hyperlinks to link to other sheets. I don't KNOW what code to use right now and don't even know if it's possible. I'll add code though to show what I did using win32::OLE if that helps I guess? — petranaya

bleepzter bleepzter · Accepted Answer · 2011-05-19T19:29:40

This is partial and maybe a non-valid answer:

Use the open XML SDK for office. Any of the *.xlsx files are actually *.zip files with many xml files inside that describe the document. Using the sdk you could make links between spreadsheets and workbooks. Unfortunately it works on Windows only... :(

Appending to Excel Spreadsheets in Perl on Windows AND Linux

1 Answers