[Solved] Copy and Paste Syntax

The Application Programming Interface and the OASIS Open Document Format

[Solved] Copy and Paste Syntax

Postby Tobin » Thu Mar 12, 2009 1:14 am

I'm sure a simple "Clipboard" Copy and Paste from one cell to another must look something like the following, but, I'm obviously missing something (besides brains).

REM ***** BASIC *****
Sub Main

Dim Cell1, Cell2 As Object
Dim Sheet As Object

Sheet = ThisComponent.Sheets(0)

Cell1 = Sheet.getCellByPosition(1,1)
Cell2 = Sheet.getCellByPosition(2,2)
Cell1.Copy
Cell2.Paste

End Sub

Can someone help me please?

Thanks
Last edited by Tobin on Thu Mar 12, 2009 3:26 pm, edited 1 time in total.
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Copy and Paste Syntax

Postby FJCC » Thu Mar 12, 2009 2:05 am

Here is one way to do this.

Code: Select all   Expand viewCollapse view
Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(0,0)
Cell2 = Sheet.getCellByPosition(0,1)
Sheet.copyRange(Cell2.CellAddress,Cell.RangeAddress)
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6160
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Copy and Paste Syntax

Postby Tobin » Thu Mar 12, 2009 2:55 pm

Thanks FJCC,
Interesting structure, I would have never guessed :-) .
This looks like it would work for a "Range" Copy and Paste as well. But -

REM ***** BASIC *****
Sub Main

Dim Cell1, Cell2 As Object
Dim Sheet As Object

Sheet = ThisComponent.Sheets(0)

'Cell1 = Sheet.getCellByPosition(1,1)
'Cell2 = Sheet.getCellByPosition(2,2)
'Sheet.copyRange(Cell2.CellAddress,Cell1.RangeAddress)

Cell1 = Sheet.getCellRangeByPosition(0,0,0,2)
Cell2 = Sheet.getCellRangeByPosition(1,0,1,1)
Sheet.copyRange(Cell1.CellAddress,Cell2.RangeAddress)

End Sub

- causes a "property or method not found" error.

Could you show other simple Copy and Paste structures?

Thanks Again
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Copy and Paste Syntax

Postby Tobin » Thu Mar 12, 2009 3:07 pm

OOPS!
I should have looked at the Global Index first ;)

Cell1 = Sheet.getCellRangeByPosition(0,0,0,2)
Cell2 = Sheet.getCellByPosition(1,0)
Sheet.copyRange(Cell2.CellAddress,Cell1.RangeAddress)

I'm still interested in other structures.

Thanks
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Copy and Paste Syntax

Postby Villeroy » Thu Mar 12, 2009 3:10 pm

Tobin wrote:- causes a "property or method not found" error.

Hint for easy debugging through better coding style:
Code: Select all   Expand viewCollapse view
Cell1 = Sheet.getCellRangeByPosition(0,0,0,2)
addrTarget = Cell1.getCellAddress()
Cell2 = Sheet.getCellRangeByPosition(1,0,1,1)
addr.Source = Cell2.getRangeAddress()
Sheet.copyRange(addrTarget,addrSource)

Now you get the same error in the second line of above snippet, which tells you that the cell-address of Cell1 could not be retrieved. What is the cell-address of A1:A3? There is none because it is not a single cell.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy and Paste Syntax

Postby FJCC » Thu Mar 12, 2009 3:16 pm

The method I showed does work for cell ranges. The source is then a CellRange as you would expect, the destination is still a cell address, which is the upper left cell of the range into which the source will be copied. So the statement is

Sheet.copyRange(TargetCell.CellAddress,SourceCellRange.RangeAddress)

You can also use dispatcher calls. If you record a macro to copy from the current cell to cell A2, you will get code like this.

Code: Select all   Expand viewCollapse view
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


Finally, for carefully controlled situations you can use

Cell2.Value = Cell1.Value

or
Cell2.String = Cell1.String

This is prone to errors. If Cell1 contains text and you assigns its value to Cell2, then Cell2 has the value zero.


I see others posted while I was typing! Here are some other methods.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6160
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Copy and Paste Syntax

Postby Tobin » Thu Mar 12, 2009 3:31 pm

Howdy,

Thank you both for replying.
<Cell2.String = Cell1.String> was what I was looking for - DA. Of course now that I'm looking at it, it seems so obvious. CopyRange is valuable to me as well - so I you gave me more than I expected :D .

Thanks Again
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: [Solved] Copy and Paste Syntax

Postby dech » Tue Oct 19, 2010 11:21 am

Hello guys,
Im looking for almost exact code, but I want it (copy and) paste all properties but not objects, sometimes not values ect. It means setting some flags for "copyrange()". How that code woudl be looking? I dont want to use macro recorder(UNO) because I need it work in hidden sheet.
Im ended up with this:
Code: Select all   Expand viewCollapse view
sub copy_range_and_paste_it_to_another_cell

oSheet = thisComponent.Sheets.getByIndex(0)
oSheet1 = thisComponent.Sheets.getByIndex(1)

source = oSheet.getCellRangeByName("A1:z100").getRangeAddress()
destination =  oSheet1.getCellByPosition(0,0).getCellAddress()

oSheet.copyRange( destination , source )
End sub

but this code copies/paste ALL properties (values,text,formats,formulas,objects....) and it would be nice to let it paste only some of them.
Thank you.
LO 5.2.7.2
dech
 
Posts: 21
Joined: Wed Sep 01, 2010 12:32 am

Re: [Solved] Copy and Paste Syntax

Postby Jan_J » Mon Jan 13, 2014 6:35 pm

Można. Próbka kodu jak to zrobić:
Code: Select all   Expand viewCollapse view
sub test()
    doc = thisComponent
    ark1 = doc.Sheets(0)
    ark2 = doc.Sheets(1)
    kopiujObszarWInneMiejsce(ark1, "C3:G9", ark2, "B4")
end sub

sub kopiujObszarWInneMiejsce(ark1, zZakresu, ark2, doKomorki)
    r1 = ark1.getCellRangeByName(zZakresu)
    r2 = ark2.getCellRangeByName(doKomorki)
    ark1.copyRange(r2.CellAddress, r1.RangeAddress)
end sub
JJ ∙ http://forum.openoffice.org/pl/
LO (5.0|5.1) ∙ AOO 4.1.2 ∙ Python (2.7|3.5) ∙ Unicode 8 ∙ L[sup]A[/sup]T[sub]E[/sub]X 2ε ∙ XML ∙ Unix tools ∙ Linux (2.6|3.x) ∙ Fedora ∙ CentOS ∙ SUSE
Jan_J
 
Posts: 138
Joined: Wed Apr 29, 2009 1:42 pm
Location: Poland

Re: [Solved] Copy and Paste Syntax

Postby Villeroy » Mon Jan 13, 2014 7:34 pm

The API does not have any paste-special equivalent.

rgTarget.setDataArray(rgSource.getDataArray()) dumps raw values of type double, integer or (empty) string. All strings will be taken as literal strings.

rgTarget.setFormulaArray(rgSource.getFormulaArray()) dumps all formulas of type (empty) string. A formula can be a plain integer or double as string ("3.14159") with decimal point and digits only, an ISO date/time ("yyyy-mm-dd hh:mm:ss"), an English boolean ("true", "false"), possibly some other numeric expression in English notation (don't know), an "ordinary string", a numeric string with leading apostrophe or an evaluated formula expression with leading =.

Both DataArrays and FormulaArrays are arrays within an array like the following array for 3 rows and 4 columns: ((A1,B1,C1,D1),(A2,B2,C2,D2),(A3,B3,C3,D3))

An array formula requires no equal sign nor braces: rgTarget.setArrayFormula("OFFSET(A1;1;2;3;4)")

As a decent programmer you work with templates and styles, just like you always do when operating an office suite by the GUI:
rgTarget.CellStyle = "MySophisticatedCellStyleWithLotsOfAttributes" saves a lot of work if you loaded the right template with your predefined styles.

Copying hard formatted attributes, conditional formats, merging is complicated but possible if you learn how to use http://www.openoffice.org/api/docs/comm ... anges.html and property sets.

Transfer annotations programmatically one by one.

Things become really hard with text cells having multiple formatted chunks of text or hyperlink fields. Cells with http://www.openoffice.org/api/docs/comm ... #FORMATTED
See http://www.oooforum.org/forum/viewtopic.phtml?t=47340 which copies such contents from Calc to Writer.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Copy and Paste Syntax

Postby ddavis1086 » Wed Aug 31, 2016 10:03 pm

Hello. I am in need of some help please. Being new here and not really a programmer, what I want to do I think is really simple.
Really don't understand why it does not work.

In Calc, I have 12 sheets.

The source sheet is sheet 1 (with the first sheet being 0 as I have learned) that has columns from A to K

There are 10 destination sheets with the first one starting at sheet 2. They contain no data.

What I need to do is copy rows starting in column B from the source (sheet 1) into the destination sheet (sheet 2) and then repeat until rows in column B though column K are copied into
the sheets 2 to 12. The column destination will always remain in the same place, column 1.

I can kinda get this to work but have run into an error that does not make sense to me.

Here is the code (sorry I do not know how to paste it the code area):

Code: Select all   Expand viewCollapse view
sub copy_range_and_paste_it_to_another_cell

oSheet = thisComponent.Sheets.getByIndex(1)
oSheet1 = thisComponent.Sheets.getByIndex(2)

source = oSheet.getCellRangeByPosition(1,0,0,100).getRangeAddress()
destination =  oSheet1.getCellByPosition(1,0).getCellAddress()

oSheet.copyRange( destination , source )
End sub

The issue is this. When I change the getCellRangeByPosition from 0 to 1, which I think would be the next column to get my source from, I get this error:

IndexOutOfBoundsException

If I leave it a 0 then it copies column 0 into my source sheet. Why cannot I set the source to be column 1 from the source sheet, there is data there.
Please be so kind to help me.

Thank you,

Dennis
OpenOffice 4.1.2 on CentOS
ddavis1086
 
Posts: 10
Joined: Sun Aug 21, 2016 1:24 am


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 3 guests