How to copy a sheet? - CPAN::Forum

Posted on 2009-06-28 15:53:31-07 by hachejp

How to copy a sheet?

Hola! Is there any way to generate new sheets copying from a previous one? Thanks!

Direct Responses: 11253 | Write a response

Posted on 2009-08-08 10:41:36-07 by gucci in response to 11077

Re: How to copy a sheet?

The interface com.sun.star.sheet.XSpreadsheets provides the methods to insert, copy and move a sheet within the same collection of sheets. The following proposal copies from one document to another. The source document needs to be stored to disk. it can work with the latest openoffice version, May be there is a workaround for unsaved documents. I don't know.

REM ***** BASIC ***** REM copy first sheet of this document to position 2 of a new one Sub Main REM source document Dim sURL$, sLinkSheetName$ sURL = thisComponent.getURL() sLinkSheetName = thisComponent.Sheets.getByIndex(0).getName() REM target document Dim doc, sheets, sName$, pos% doc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_default",0, Array()) sheets = doc.getSheets() sName = getUniqueName(sheets, "Copied") pos = 1 REM new sheet Dim sh sheets.insertNewByName(sName, pos) sh = sheets.getByName(sName) REM link the new sheet sh.link(sURL, sLinkSheetName, "calc8", "", com.sun.star.sheet.SheetLinkMode.NORMAL) REM break link sh.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE) End Sub Function getUniqueName(oContainer,sName$) Dim i%,sNew$ sNew = sName Do while oContainer.hasByName(sNew) i = i +1 sNew = sName &"_"& i loop getUniqueName = sNew End Function

Direct Responses: 11872 | Write a response

Posted on 2009-12-04 00:21:31-08 by trendle in response to 11253

Re: How to copy a sheet?

However... given the context of the forum I expect that the questioner was not wanting an answer in BASIC... (still it's nice to know there's more than one way to skin a cat, as they say).

There is a way in OODoc but you need to add library with XPath.

use OpenOffice::OODoc ; use OpenOffice::OODoc::File ; use OpenOffice::OODoc::XPath ;

Then it is fairly easy (eventually) to copy a sheet.

my $spread = $doc->getElement('//office:spreadsheet'); # 2 Context set to spreadsheet $doc->currentContext($spread); # 2b Report number of sheets my $allSheets = [ $doc->getElementList('//table:table') ]; printf("Sheet count = %d\n",scalar(@$allSheets)); # 3 Get last sheet (setting maximum size to be 26 x 26 ==> as in NormalizeSheet ) my $sheet = $doc->getTable(-1,26,26); # 4 Context set to last sheet $doc->currentContext($sheet); # 4b Get sheet name my $shname = $sheet->getAttribute('table:name'); printf("Last Sheet Name -> $shname\n"); my $new_sheet; my $new_name; # 5 Now replicate the blank sheet into a new sheet preceding, (always maintain a blank last sheet). $new_sheet = $doc->replicateElement($sheet,$sheet,position => 'before'); $doc->currentContext($new_sheet); # 6 Stick some stuff into the Sheet populateSheet($doc,$new_sheet,$dataBlock); my $new_name = $new_sheet->getAttribute('table:name'); printf("Add Sheet Name -> $new_name\n"); # etc... repeat as necessary

This is a cut from something I'm working on which keeps a blank form as the last sheet, and makes a copy before the blank to put new data into it. The 'populateSheet' proc is just my way of stuffing data into the new sheet, you can make up your own for whatever purpose you want.

regards.

Direct Responses: 11873 | Write a response

Posted on 2009-12-04 09:20:34-08 by jmgdoc in response to 11872

Re: How to copy a sheet?

Following the previous answer (thanks to trendle), some code simplifications are possible.

First, the two following use statements are not needed:

use OpenOffice::OODoc::File ; use OpenOffice::OODoc::XPath ;

As soon as the main OpenOffice::OODoc top module is used, File and XPath (and others) are loaded too.

Second, there is a getTableList() method that returns all the tables/sheets of the document (or the current context) as a list. So it's not necessary to explicitly select the "office:spreadsheet" context then to use the getElementList() with an XPath expression in order to get this list. We just need to write

my $allSheets = [ $doc->getTableList() ];

Such coding style could improve the program readability for people who don't know the ODF XML schema.
In addition, there is a document-level tableName() mnemonic method that allows the user to get (or set) a sheet name without knowledge of the XML "table:name" attribute. So, the following instruction:

my $shname = $sheet->getAttribute('table:name');

should be replaced by:

my $shname = $doc->tableName($sheet);

Such coding style could improve the program readability for people who don't know the ODF XML schema.

Direct Responses: 11876 | Write a response

Posted on 2009-12-04 20:13:08-08 by trendle in response to 11873

Re: How to copy a sheet?

@ jmgdoc,

Thanks for the improvements. They should help everyone to have more readable code.

Unfortunately my code was the result of several tries as I got to understand the package, so it did not end up verey elegant. I should try and use the top level functions as much as possible.

Direct Responses: 11877 | Write a response

Posted on 2009-12-04 21:57:22-08 by jmgdoc in response to 11876

Re: How to copy a sheet?

@trendle,

It's a known issue with OpenOffice::OODoc; it lacks a user-friendly cookbook and/or tutorial. Up to now, I never could afford to go beyond the code and the severe reference manual. As a consequence, ideas and returns of experience are welcome...

Direct Responses: Write a response

Perl Weekly newsletter
A free weekly newsletter for people who are busy to read all the blogs. click here to check it out.