| View previous topic :: View next topic |
| Author |
Message |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
 Posted: Thu Jun 09, 2005 6:15 am Post subject: |
 |
|
Here is a program that....
- Opens a spreadsheet (doc1)
- Select all of the used cells in doc1. (That is, instead of doing SelectAll, it only selects from the first cell used to the last cell used.)
- Copy the selected cells to clipboard.
- Create new spreadsheet (doc2)
- Select cell B27
- Paste. (This pastes the contents of the entire set of used cells from doc1 into doc2, starting at cell B27.)
This code is posted in answer to the question of this thread....
Copy and Paste Problem
http://www.oooforum.org/forum/viewtopic.phtml?t=20330
Here is the code. It should be quite easy to read. But it uses a bunch of high level subroutines. (See below)
| Code: | Sub Main
' Get Doc 1.
' CHANGE ME !!!
' CHANGE ME !!!
' CHANGE ME !!!
' CHANGE ME !!!
cFile1 = "C:\Documents and Settings\dbrewer\Desktop\CalcMaster 20 .xls"
cUrl1 = ConvertToUrl( cFile1 )
oDoc1 = StarDesktop.loadComponentFromURL( cUrl1, "_blank", 0, Array() )
' Create empty Doc 2.
oDoc2 = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() )
'------------approach 1----------
' Select All on document 1.
' SelectAll( oDoc1 )
' Now Copy.
' ClipboardCopy( oDoc1 )
'------------------------------
'------------approach 2----------
' Instead of selecting ALL, let's only select the cells that are actually USED.
' First obtain the first and last cell used.
oSheet1 = oDoc1.getSheets().getByIndex( 0 )
oFirstCellDoc1 = GetFirstUsedCell( oSheet1 )
oLastCellDoc1 = GetLastUsedCell( oSheet1 )
' Now get the *names* of the first and last cells.
cFirstCellName = CellRangeAddressToName( oSheet1, oFirstCellDoc1.getRangeAddress() )
cLastCellName = CellRangeAddressToName( oSheet1, oLastCellDoc1.getRangeAddress() )
' Now select from the first to the last cell used.
' This is more gooder than SelectAll.
CalcSelectCellsByName( oDoc1, cFirstCellName+":"+cLastCellName )
' Now Copy.
ClipboardCopy( oDoc1 )
'------------------------------
' Get cell B27 on document 2.
oSheet2 = oDoc2.getSheets().getByIndex( 0 )
'oSheet = oDoc2.getSheets().getByName( "Sheet223" )
' This gets us a *rectangular-range* of cells B27:B27.
oCells = oSheet2.getCellRangeByName( "B27" )
' This gets a *single-cell*, upper-left, from the rectangular range of cells.
' This gets us the single cell B27.
oCell = oCells.getCellByPosition( 0, 0 )
' Select Cell B27 on document 2.
oDoc2Ctrl = GetDocumentController( oDoc2 )
oDoc2Ctrl.select( oCell )
' Now Paste clipboard into doc2.
ClipboardPaste( oDoc2 )
End Sub
|
In order to make the above code work, you need to snarf a whole bunch of subroutines from Danny's Basic Library.
For the lazy, here are the routines you need. If you add these routines to the above Sub Main, then you form a complete program, ready to run -- just copy, paste, add water, stir.
| Code: | '############################################################
' Sugar coated dispatch routines.
'############################################################
Sub SelectAll( oDocumentFrame )
DocumentDispatch( oDocumentFrame, ".uno:SelectAll" )
' DocumentDispatch( oDocumentFrame, "slot:5723" )
End Sub
Sub ClipboardPaste( oDocumentFrame )
DocumentDispatch( oDocumentFrame, ".uno:Paste" )
' DocumentDispatch( oDocumentFrame, "slot:5712" )
End Sub
Sub ClipboardCopy( oDocumentFrame )
DocumentDispatch( oDocumentFrame, ".uno:Copy" )
' DocumentDispatch( oDocumentFrame, "slot:5711" )
End Sub
Sub ClipboardCut( oDocumentFrame )
DocumentDispatch( oDocumentFrame, ".uno:Cut" )
' DocumentDispatch( oDocumentFrame, "slot:5710" )
End Sub
'############################################################
' Dispatch help
'############################################################
'----------
' A super easy to use Dispatch on an office document.
' Arguments are similar to the args for the com.sun.star.frame.XDispatchHelper
' interface of com.sun.star.frame.DispatchHelper.
' What makes this so easy to use are two things:
' 1. The fact that the oDocumentFrame parameter can actually accept
' either the document model or one of its controllers.
' 2. The optional parameters.
' For an example of how simple this routine is to use, see
' routines below such as ClipboardCopy().
'
' Parameters:
' oDocumentFrame - An office document frame.
' But wait! It could be the document controller
' or the document model. This routine will find
' the document frame from either of these.
' cURL - The dispatch URL.
' Optional:
' cTargetFrameName - Defaults to blank.
' nSearchFlags - Defaults to zero.
' aDispatchArgs - Defaults an an empty sequence.
'
Sub DocumentDispatch( ByVal oDocumentFrame As Object,_
ByVal cURL As String,_
Optional cTargetFrameName,_
Optional nSearchFlags,_
Optional aDispatchArgs )
' If they gave us the wrong parameter...
If Not IsNull( oDocumentFrame ) Then
If Not HasUnoInterfaces( oDocumentFrame, "com.sun.star.frame.XFrame" ) Then
' Be sure that we've got the document frame.
' Someone might have passed us the document model or one of
' its controller's.
oDocumentFrame = GetDocumentFrame( oDocumentFrame )
EndIf
EndIf
If IsMissing( cTargetFrameName ) Then
cTargetFrameName = ""
EndIf
If IsMissing( nSearchFlags ) Then
nSearchFlags = 0
EndIf
If IsMissing( aDispatchArgs ) Then
aDispatchArgs = Array()
EndIf
oDispatchHelper = createUnoService( "com.sun.star.frame.DispatchHelper" )
oDispatchHelper.executeDispatch( oDocumentFrame, cURL, cTargetFrameName, nSearchFlags, aDispatchArgs )
End Sub
'############################################################
' API navigation convenience
'############################################################
'----------
' This will always return the document's controller.
' Pass in any one of...
' * the document's model (subclass of com.sun.star.document.OfficeDocument)
' * the document's controller
' * the document's frame
Function GetDocumentController( oDoc As Object ) As Object
Dim oCtrl As Object
' If the caller gave us the document model...
If oDoc.supportsService( "com.sun.star.document.OfficeDocument" ) Then
' ...then get the controller from that.
oCtrl = oDoc.getCurrentController()
' If the caller gave us a document controller...
ElseIf HasUnoInterfaces( oDoc, "com.sun.star.frame.XController" ) Then
' ...thanks! That's just what we wanted!
oCtrl = oDoc
' If the caller gave us the document frame...
ElseIf HasUnoInterfaces( oDoc, "com.sun.star.frame.XFrame" ) Then
oFrame = oDoc
' ...then get the controller from the frame.
oCtrl = oFrame.getController()
Else
' The caller did not give us what we expected!
MsgBox( "GetDocController called with incorrect parameter." )
EndIf
GetDocumentController() = oCtrl
End Function
'----------
' This will always return the document's frame.
' Pass in any one of...
' * the document's model (subclass of com.sun.star.document.OfficeDocument)
' * the document's controller
' * the document's frame
Function GetDocumentFrame( oDoc As Object ) As Object
Dim oFrame As Object
' If the caller gave us the document model...
If oDoc.supportsService( "com.sun.star.document.OfficeDocument" ) Then
' ...then get the controller from that.
oCtrl = oDoc.getCurrentController()
' ...then get the frame from the controller.
oFrame = oCtrl.getFrame()
' If the caller gave us a document controller...
ElseIf HasUnoInterfaces( oDoc, "com.sun.star.frame.XController" ) Then
oCtrl = oDoc
' ...then get the frame from the controller.
oFrame = oCtrl.getFrame()
' If the caller gave us the document frame...
ElseIf HasUnoInterfaces( oDoc, "com.sun.star.frame.XFrame" ) Then
' ...thanks! That's just what we wanted!
oFrame = oDoc
Else
' The caller did not give us what we expected!
MsgBox( "GetDocumentFrame called with incorrect parameter." )
EndIf
GetDocumentFrame() = oFrame
End Function
'----------
' This will always return the document's model.
' Pass in any one of...
' * the document's model (subclass of com.sun.star.document.OfficeDocument)
' * the document's controller
' * the document's frame
Function GetDocumentModel( oDoc As Object ) As Object
Dim oDocModel As Object
' If the caller gave us the document model...
If oDoc.supportsService( "com.sun.star.document.OfficeDocument" ) Then
' ...thanks! That's just what we wanted!
oDocModel = oDoc
' If the caller gave us a document controller...
ElseIf HasUnoInterfaces( oDoc, "com.sun.star.frame.XController" ) Then
oCtrl = oDoc
' ...then get the model from the controller.
oDocModel = oCtrl.getModel()
' If the caller gave us the document frame...
ElseIf HasUnoInterfaces( oDoc, "com.sun.star.frame.XFrame" ) Then
oFrame = oDoc
' ...then get the controller from the frame.
oCtrl = oFrame.getController()
' ...then get the model from the controller.
oDocModel = oCtrl.getModel()
Else
' The caller did not give us what we expected!
MsgBox( "GetDocumentModel called with incorrect parameter." )
EndIf
GetDocumentModel() = oDocModel
End Function
'############################################################
' Other stuff ripped from Danny's Library.
'############################################################
Function GetLastUsedCell( oSheet As com.sun.star.sheet.Spreadsheet ) As com.sun.star.sheet.SheetCell
' The Spreadsheet interface XSpreadsheet has method createCursor(),
' which returns a SheetCellCursor.
oCellCursor = oSheet.createCursor()
' The SheetCellCursor has interface XUsedAreaCursor, which has method gotoEndOfUsedArea().
oCellCursor.gotoEndOfUsedArea( False )
' The SheetCellCursor includes service SheetCellRange which has interface XCellRangeAddressable
' which has method getRangeAddress(), which returns a struct com.sun.star.table.CellRangeAddress.
tCellRangeAddress = oCellCursor.getRangeAddress()
' Get the last used cell on the spreadsheet.
oCell = oSheet.getCellByPosition( tCellRangeAddress.EndColumn, tCellRangeAddress.EndRow )
GetLastUsedCell = oCell
end function
Function GetFirstUsedCell( oSheet As com.sun.star.sheet.Spreadsheet ) As com.sun.star.sheet.SheetCell
' The Spreadsheet interface XSpreadsheet has method createCursor(),
' which returns a SheetCellCursor.
oCellCursor = oSheet.createCursor()
' The SheetCellCursor has interface XUsedAreaCursor, which has method gotoStartOfUsedArea().
oCellCursor.gotoStartOfUsedArea( False )
' The SheetCellCursor includes service SheetCellRange which has interface XCellRangeAddressable
' which has method getRangeAddress(), which returns a struct com.sun.star.table.CellRangeAddress.
tCellRangeAddress = oCellCursor.getRangeAddress()
' Get the first used cell on the spreadsheet.
oCell = oSheet.getCellByPosition( tCellRangeAddress.EndColumn, tCellRangeAddress.EndRow )
GetFirstUsedCell = oCell
end function
Function CellRangeAddressToName( oSheet As com.sun.star.sheet.Spreadsheet,_
oRangeAddress As com.sun.star.table.CellRangeAddress ) As String
If oRangeAddress.StartColumn = oRangeAddress.EndColumn _
And oRangeAddress.StartRow = oRangeAddress.EndRow Then
CellRangeAddressToName = CalcColumnNumberToName( oSheet, oRangeAddress.StartColumn ) _
+ CSTR( oRangeAddress.StartRow + 1 )
Else
CellRangeAddressToName = CalcColumnNumberToName( oSheet, oRangeAddress.StartColumn ) _
+ CSTR( oRangeAddress.StartRow + 1 ) _
+ ":" _
+ CalcColumnNumberToName( oSheet, oRangeAddress.EndColumn ) _
+ CSTR( oRangeAddress.EndRow + 1 )
EndIf
End Function
' Convert between column number and column name.
' See...
' http://www.oooforum.org/forum/viewtopic.php?p=23013#23013
Function CalcColumnNameToNumber( oSheet As com.sun.star.sheet.Spreadsheet,_
cColumnName As String ) As Long
oColumns = oSheet.getColumns()
oColumn = oColumns.getByName( cColumnName )
oRangeAddress = oColumn.getRangeAddress()
nColumn = oRangeAddress.StartColumn
CalcColumnNameToNumber() = nColumn
End Function
Function CalcColumnNumberToName( oSheet As com.sun.star.sheet.Spreadsheet,_
nColumnNumber As Long ) As String
oColumns = oSheet.getColumns()
oColumn = oColumns.getByIndex( nColumnNumber )
cColumnName = oColumn.getName()
CalcColumnNumberToName() = cColumnName
End Function
' Select some cells on a spreadsheet document.
' Parameters:
' oDocCtrl
' - The document controller.
' Note: is okay if you pass in either the
' document model or frame instead of a controller.
' cCellsName
' - The name of the cell or cell range to select.
' Optional Parameters:
' oSheet
' - The sheet within the document. A com.sun.star.sheet.Spreadsheet.
' If not supplied, then the currently visible sheet is used.
'
Sub CalcSelectCellsByName( ByVal oDocCtrl, ByVal cCellsName As String, Optional oSheet )
' If they gave us the incorrect parameter...
If Not HasUnoInterfaces( oDocCtrl, "com.sun.star.frame.XController" ) Then
' Be sure that we've got the document frame.
' Someone might have passed us the document model or one of
' its controller's.
oDocCtrl = GetDocumentController( oDocCtrl )
EndIf
If IsMissing( oSheet ) Then
oSheet = oDocCtrl.getActiveSheet()
EndIf
oRange = oSheet.getCellRangeByName( cCellsName )
oDocCtrl.select( oRange )
End Sub
|
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
Jeanm1242
General User
Joined: 10 Jun 2005
Posts: 13
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
JRE
Newbie
Joined: 16 Jun 2005
Posts: 3
|
| Posted: Thu Jun 16, 2005 6:27 am Post subject: CALC Help Wanted |
|
|
I want to set a CellRange on an Optimal Width and how can conect cells like A1 and B1?! How can I do this in VFP or VBA?
Thanks for Help |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Thu Jun 16, 2005 7:51 am Post subject: Re: CALC Help Wanted |
|
|
| JRE wrote: | | I want to set a CellRange on an Optimal Width and how can conect cells like A1 and B1?! How can I do this in VFP or VBA? |
I do not understand the "Optimal Width" part or the "connect cells" part. (But, I do not know much about Calc or Writer. I mostly use Draw and Basic/Python.)
You might get better luck by starting a new thread in the Macros and API section. Real spreadsheet users might know.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
JRE
Newbie
Joined: 16 Jun 2005
Posts: 3
|
| Posted: Fri Jun 17, 2005 5:03 am Post subject: |
|
|
I mean to join cells to one cell! I´ve seen you have a lot of examples here linked for VBA and VFP too.
Thanks  |
|
| Back to top |
|
|
poldi112
General User
Joined: 03 Mar 2004
Posts: 12
|
| Posted: Fri Jun 24, 2005 7:30 am Post subject: |
|
|
Hi.
Thank you DannyB. This code of yours is trully great.
However, I am unable to figure out some things. I loop through all files in folder, modify one by one and save them to sxc. But I don't know how to save without .csv in the name. Now I open name.csv and save bo name.csv.sxc instead to name.sxc.
The other problem is how to specify destination folder. I read some pages on api.openoffice.org, but found nothing usefull (and it's pain browsing that pages. My machine tends to freez trying to open that site).
Code that I use to save is:
cURL = ConvertToURL( cFile + ".sxc" )
oDoc.storeAsURL( cURL, Array() ) |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Sat Jun 25, 2005 9:56 am Post subject: |
|
|
| poldi112 wrote: | Code that I use to save is:
cURL = ConvertToURL( cFile + ".sxc" )
oDoc.storeAsURL( cURL, Array() ) |
cUrl can be any url you want. It can have any pathname you want. Any folder. And any file name or suffix.
cUrl = "file:///C:/Documents and Settings/dbrewer/Desktop/Test.meow"
oDoc.storeAsURL( cUrl, Array() )
Will save the current document onto my desktop as "Test.meow". Note the strange file suffix. The file is structurally an OOo document. But because of the strange suffix, Windows or the Finder (or KDE or GNOME) is not going to know what to do with the file when you click on it, nor will they know what icon to display. But the file is still an OOo document. Simply rename it.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
poldi112
General User
Joined: 03 Mar 2004
Posts: 12
|
| Posted: Sat Jun 25, 2005 10:43 am Post subject: |
|
|
Hi.
Thank your for your reply. I guess it was a misunderstanding. I import the file and I want to keep the name. cFile I already have (I read folder content and import files inside) and it's /tmp/p_6-22.csv. What I need is some string manipulation to make it /tmp/scx/p_6-22.scx. Cause now it becomes /tmp/p_6-22.csv.scx.
Aslo, I'm unable to set page / print properties in calc. Landscape, A4, margins, ... I found macro for writer, but it does not work for calc.
Thank you. |
|
| Back to top |
|
|
BillChow
Newbie
Joined: 18 Jul 2005
Posts: 2
|
| Posted: Tue Jul 19, 2005 10:52 pm Post subject: Can convert it using C++? |
|
|
Why did it has more complex operation when I tried to control openoffice using C++?
Can convert it using C++?
Who can help me?
E-mail: billzp2000@yahoo.com.cn
Thanks! |
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
|
McFozzy
Newbie
Joined: 04 Oct 2005
Posts: 2
|
| Posted: Tue Oct 04, 2005 5:28 am Post subject: |
|
|
Great post.
How make text in OpenOffice Calc Bold or Italic? Change colour? Via Visual FoxPro? |
|
| 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
|
