| View previous topic :: View next topic |
| Author |
Message |
carpatic
Power User
Joined: 04 May 2005
Posts: 80
|
 Posted: Wed Aug 24, 2005 3:11 am Post subject: copy cell range from one calc to another |
 |
|
I open 2 calc files, copy a range of cels from one (from a specific sheet) and want to paste them in the other file, on the first sheet (getByIndex(0)).
I get no errors, but it does not copy them.
Strange, if I copy/paste from one sheet to antoher in the same document it works fine.
Bellow is the code (python):
| Code: | oDoc = openURL(pathnameToUrl(path+"/temporar.sxc" ) ,(createPropertyValue("Hidden",1==1),) )
oCreated=openURL(pathnameToUrl(path+"/temp.sxc" ) ,(createPropertyValue("Hidden",1==1),) )
oNewSheet=oCreated.getSheets().getByIndex(0)
oSheet1=oDoc.getSheets().getByIndex(0)
oFromRangeAddress = oSheet1.getCellRangeByPosition(0,0,4,4).getRangeAddress()
oToCellAddress = oNewSheet.getCellByPosition(1,1).getCellAddress()
oNewSheet.copyRange(oToCellAddress,oFromRangeAddress)
oCreated.storeToURL( pathnameToUrl(path+"/temp.sxc" ), (createPropertyValue("Hidden",1==1),))
oCreated.dispose()
oDoc.dispose() |
|
|
| Back to top |
|
 |
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Wed Aug 24, 2005 9:31 am Post subject: |
|
|
I wrote the following:
| Quote: | Use the copyRange(CellAddress, CellRangeAddress) method to copy a range of cells to the location specified by the cell address. The top-left cell in the cell range address is positioned at the specified cell address when the range is copied. The net effect of the copyRange() method is the same as copying a range of cells to the clipboard, positioning
the cursor at the specified cell, and then pasting the cells into place (see Listing 33). |
What I did not explicitly point out is that neither a CellAddress, nor a CessRangeAddress specify the containing document; they only specify the sheet and location in the sheet. In other words, you can not use copyRange() from one document to another. You will need to either use the clipboard, or use the routines getDataArray() and setDataArray() depending on what you really want to copy (as in only data or formatting, formulas, etc....)
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php |
|
| Back to top |
|
|
carpatic
Power User
Joined: 04 May 2005
Posts: 80
|
| Posted: Thu Aug 25, 2005 3:10 am Post subject: |
|
|
Ok, I have done it with the dispatcher, BUT I'd like to copy only the values, not all the relationships. All the values in the selection are computed, with formulas, based on other values in other sheets of the original document, so of course I end up, in the new document, with a bunch of #NAME? cells.
How can I copy only the result values, with the dispatcher ?
Thanks. |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Thu Aug 25, 2005 10:50 am Post subject: |
|
|
| Quote: | | How can I copy only the result values, with the dispatcher ? |
I do not know. My macro document has code to use a dispatch to copy with the clipboard. if not, then lookup how to use getDataArray() and setDataArray() and use that.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php |
|
| Back to top |
|
|
carpatic
Power User
Joined: 04 May 2005
Posts: 80
|
| Posted: Thu Aug 25, 2005 9:05 pm Post subject: |
|
|
The dispatch somehow looses the relations that refer to other sheets (that are not copyied, of course), but the formating (style colors, , nested tables, spans...) are kept correctly.
On the other hand, I've tried to iterate through every cell that I need to get, read the value, string and formula and enter that info in the new document. But, even though I get the string value correctly (the value that resulted after the operations, which I could not get with the dispatcher), I can not paste the format of the cells correctly.
So, given that, I've done the obvoius: combine the 2 methods - first, use dispatch cu copy the data, including formationg, spans, etc., then iterate and replace the cell values where it is needed. Unfortunatelly this process takes very long time - on large calc files (>2M) stays over 5 minutes ! |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
| Posted: Fri Aug 26, 2005 5:53 am Post subject: |
|
|
getDataArray() and setDataArray()
if you only need to copy values, there NOTHING is faster (at least I think so). have you tried that?
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3618
Location: Columbus, Ohio, USA
|
|
| Back to top |
|
|
carpatic
Power User
Joined: 04 May 2005
Posts: 80
|
| Posted: Fri Aug 26, 2005 7:18 am Post subject: |
|
|
| Well, I DO need formating information, like font weight, justification, font color, etc. |
|
| Back to top |
|
|
Danad
OOo Advocate
Joined: 22 Feb 2004
Posts: 293
Location: Brasil
|
| Posted: Fri Aug 26, 2005 11:50 am Post subject: |
|
|
Others ways to do that:
1) Copy the range to clipboard and PASTE SPECIAL (uncheck Formulas). The macro recorder works fine here.
| Code: |
sub paste_espec
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(5) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Flags"
args1(0).Value = "SVDNT"
args1(1).Name = "FormulaCommand"
args1(1).Value = 0
args1(2).Name = "SkipEmptyCells"
args1(2).Value = false
args1(3).Name = "Transpose"
args1(3).Value = false
args1(4).Name = "AsLink"
args1(4).Value = false
args1(5).Name = "MoveMode"
args1(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1())
end sub
|
2) Use SheetLinks to link *one* sheet from another Calc doc. Here, if you want, you can copy only values. After all remove the link. See here:
http://www.oooforum.org/forum/viewtopic.phtml?t=15678
3) Use AreaLinks to link a range from another Calc doc. Here you copy the range as it is. After all remove the link. See code:
| Code: |
Sub ex_Area_Link
Dim oDoc As Object
Dim oPlan As Object
Dim oLinks As Object
Dim aPos As New com.sun.star.table.CellAddress
oDoc = ThisComponent
oPlan = oDoc.getSheets().getByIndex(1)
sURL = "file:///C:/My Documents/ooo/test.sxc"
sFonte = "Planilha2.A1:E5"
sFiltro = "StarOffice XML (Calc)"
sOpcoes = ""
aPos.Sheet = 1
aPos.Column = 0
aPos.Row = 0
oLinks = oDoc.AreaLinks
oLinks.insertAtPosition(aPos, sURL, sFonte, sFiltro, sOpcoes)
oLinks.removeByIndex(oLinks.Count - 1)
End Sub
|
All that ways preserve formatting info.
HTH |
|
| Back to top |
|
|
hazabaral
Power User
Joined: 16 Apr 2008
Posts: 69
|
| Posted: Fri May 23, 2008 5:31 am Post subject: |
|
|
| Do you have any idea how can I import the csv in the row as increasing instead of in the spreadsheet column? Any suggession? |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
