How can I create or read OpenOffice spreadsheets with Perl?

Question

What is a good way to create and read an OpenOffice spreadsheet in Perl?

Answer 1

I think the open office source document format is based on the OpenDocument specifications and, in fact, is a compressed XML format in zip format. It is true that you can probably manipulate it using your own perl XML processing tools.

In addition, there is an Open Office OpenDocument Connector module set in CPAN that offers a high-level API for the OpenDocument specification.

As far as I know, the table methods in these modules should provide both reading and writing data in OO Calc documents.

Answer 2

I used OpenOffice :: OOCBuilder to create large color tables. I really liked it. For simpler tasks, I often use the simple CSV format, which is easy to open with any spreadsheet program.

However, you can choose Excel material for compatibility with other people.

Answer 3

OpenOffice supports several formats if you are looking for something that can read / write Excel compatible spreadsheets, Spreadsheet :: ParseExcel for reading and Spreadsheet :: WriteExcel for writing. I used both of them, they are quite mature and work well.

Answer 4

This is version 2.4, because the latest version is unstable. He works with the latter, though sometimes. This allows you to use real-time DDE functions in Calc, rather than manipulating files. This uses Cygwin Perl on XP, but should work with others. I just want to say that the Excel VBA programming API is 10 times better and 10 times less confusing. This is pretty bad.

[code]

use Win32::OLE; Win32::OLE->Option(Warn => 3); # Turn on warnings for easier debugging #Win32::OLE->GetActiveObject # Get the currently running process or create a new one $objServiceManager = Win32::OLE->GetActiveObject("com.sun.star.ServiceManager") || Win32::OLE->new("com.sun.star.ServiceManager") || die "CreateObject: $!"; $Stardesktop = $objServiceManager->createInstance("com.sun.star.frame.Desktop"); # $Stardesktop->terminate();exit; # will kill ALL OpenOffice docs!!! # Doc = StarDesktop.loadComponentFromURL(sURL, "_default", 0, aMediaDesc) $propValue[0] = $objServiceManager->Bridge_GetStruct("com.sun.star.beans.PropertyValue"); $propValue[0]->{Name} = "Hidden"; # This does not work! $propValue[0]->{Value} = 1; #Open the file and update its links if you have DDE links in your file $propValue[1] = $objServiceManager->Bridge_GetStruct("com.sun.star.beans.PropertyValue"); $propValue[1]->{Name} = "UpdateDocMode"; $propValue[1]->{Value} = 3; # com.sun.star.document.UpdateDocMode.FULL_UPDATE $calc = $Stardesktop->loadComponentfromUrl("file:///C:/Documents and Settings/Chloe/Desktop/MyFile.ods", "MyCalc", 0, \@propValue ); # load a new blank spreadsheet $calc = $Stardesktop->loadComponentFromURL( "private:factory/scalc", "_blank", 0, [] ); # How to hide, as loading the document hidden does not work. $calc->getCurrentController->getFrame->getContainerWindow()->setVisible(0); $oSheet = $calc->getSheets->getByIndex(0); # how to execute an UNO command, such as menu items # http://wiki.services.openoffice.org/wiki/Framework/Article/OpenOffice.org_2.x_Commands $frame = $calc->getCurrentController->getFrame; $dispatchHelper = $objServiceManager->createInstance("com.sun.star.frame.DispatchHelper"); $dispatchHelper->executeDispatch( $frame, ".uno:CalculateHard", #".uno:UpdateAll", #".uno:UpdateAllLinks", #".uno:DataAreaRefresh", "_self", 0, [] ); $row = 5; $cellValue = $oSheet->getCellByPosition(0, $row)->getString(); # get a cell value # sort in decending order $range = $oSheet->getCellRangeByName("A1:P$row"); $fields[0] = $objServiceManager->Bridge_GetStruct("com.sun.star.table.TableSortField"); $fields[0]->{Field} = 7; # column number $fields[0]->{IsAscending} = 0; $unoWrap = $objServiceManager->Bridge_GetValueObject; $unoWrap->Set ("[]com.sun.star.table.TableSortField", \@fields); $sortDx = $range->createSortDescriptor(); $sortDx->[0]->{Name} = "ContainsHeader"; $sortDx->[0]->{Value} = 1; $sortDx->[3]->{Name} = "SortFields"; $sortDx->[3]->{Value} = $unoWrap; #$sortDx->[3]->{Value} = \@fields; # You would think this would work? It doesn't. $range->sort($sortDx); # create a new sheet to paste to $calc->getSheets->insertNewByName("NewSheet", 1 ); $sheet2 = $calc->getSheets->getByIndex(1); $calc->CurrentController->Select($sheet2); # copy row $pasteHere = $sheet2->getCellByPosition(0, 0)->CellAddress; $copyRange = $oSheet->getCellRangeByName("A1:Q1")->RangeAddress; $oSheet->copyRange($pasteHere, $copyRange); $cellValue = $sheet2->getCellByPosition(16, $row)->getValue()); # get cell value as integer $date = $sheet2->getCellByPosition(5, $row)->getString(); # must get dates as strings $calc->getCurrentController->getFrame->getContainerWindow()->setVisible(1); # set visible $calc->close(0); # close program window #print Win32::OLE->LastError, "\n"; 

[/code]