| View previous topic :: View next topic |
| Author |
Message |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
 Posted: Sat Jan 17, 2004 10:21 am Post subject: Calc Examples |
 |
|
This is an example program that shows how to work with the Calc spreadsheet.
This example program demonstrates....
1. How to obtain a Calc document by either....
1A. Creating a new one
1B. Opening one from disk (both Linux and Windows examples)
1C. Access the current Calc document, assuming that this macro is embedded into a Calc document rather than some other type of document.
2. Obtain a sheet from the document by either index or by name.
3. How to set formulas, strings, and numeric values in cells of the sheet. You can probably guess how to "get" values. (getFormula(), getString(), getValue()). The example also sets dates into the cells as formulas.
4. Format date cells as Dates.
5. Add a chart to the document.
6. Find out how many sheets are in a calc document, add sheets, or remove sheets, change the name of a sheet.
7. Print a spreadsheet using three different techniques.
8. Save the Calc document in native OOo format.
9. Export the Calc document to... Excel, PDF, CSV, DIF, SYLK, Html
| Code: | Sub Main
' Set oDoc to which spreadsheet document we're working with.
' Three examples of how to set the variable oDoc...
'-----
' Set oDoc to the document that this macro is embedded into.
' This macro must be embedded into a Calc document.
' oDoc = ThisComponent
'-----
'-----
' Use this line to create a NEW calc document
' and assign it to variable oDoc.
oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() )
'-----
'-----
' Use this instead to open an EXISTING calc document,
' and assign it to variable oDoc.
' cFile = "C:\Documents and Settings\danny\Desktop\MyCalc" ' Windows
' cFile = "/home/danny/Desktop/MyCalc.sxc" ' Linux
' cURL = ConvertToURL( cFile + ".sxc" )
' oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, Array() )
'-----
' Here are two ways to get access to one of the various sheets
' in the spreadsheet document.
' Note that these don't make the sheet *vislble*, they merely give
' access to the sheet's content within the program.
oSheet = oDoc.getSheets().getByIndex( 0 ) ' get the zero'th sheet
' oSheet = oDoc.getSheets().getByName( "Sheet3" ) ' get by name
'-----
' Put some sales figures onto the sheet.
oSheet.getCellByPosition( 0, 0 ).setString( "Month" )
oSheet.getCellByPosition( 1, 0 ).setString( "Sales" )
oSheet.getCellByPosition( 2, 0 ).setString( "End Date" )
oSheet.getCellByPosition( 0, 1 ).setString( "Jan" )
oSheet.getCellByPosition( 0, 2 ).setString( "Feb" )
oSheet.getCellByPosition( 0, 3 ).setString( "Mar" )
oSheet.getCellByPosition( 0, 4 ).setString( "Apr" )
oSheet.getCellByPosition( 0, 5 ).setString( "May" )
oSheet.getCellByPosition( 0, 6 ).setString( "Jun" )
oSheet.getCellByPosition( 0, 7 ).setString( "Jul" )
oSheet.getCellByPosition( 0, 8 ).setString( "Aug" )
oSheet.getCellByPosition( 0, 9 ).setString( "Sep" )
oSheet.getCellByPosition( 0, 10 ).setString( "Oct" )
oSheet.getCellByPosition( 0, 11 ).setString( "Nov" )
oSheet.getCellByPosition( 0, 12 ).setString( "Dec" )
oSheet.getCellByPosition( 1, 1 ).setValue( 3826.37 )
oSheet.getCellByPosition( 1, 2 ).setValue( 3504.21 )
oSheet.getCellByPosition( 1, 3 ).setValue( 2961.45 )
oSheet.getCellByPosition( 1, 4 ).setValue( 2504.12 )
oSheet.getCellByPosition( 1, 5 ).setValue( 2713.98 )
oSheet.getCellByPosition( 1, 6 ).setValue( 2248.17 )
oSheet.getCellByPosition( 1, 7 ).setValue( 1802.13 )
oSheet.getCellByPosition( 1, 8 ).setValue( 2003.22 )
oSheet.getCellByPosition( 1, 9 ).setValue( 1502.54 )
oSheet.getCellByPosition( 1, 10 ).setValue( 1207.68 )
oSheet.getCellByPosition( 1, 11 ).setValue( 1319.71 )
oSheet.getCellByPosition( 1, 12 ).setValue( 786.03 )
oSheet.getCellByPosition( 2, 1 ).setFormula( "=DATE(2004;01;31)" )
oSheet.getCellByPosition( 2, 2 ).setFormula( "=DATE(2004;02;29)" )
oSheet.getCellByPosition( 2, 3 ).setFormula( "=DATE(2004;03;31)" )
oSheet.getCellByPosition( 2, 4 ).setFormula( "=DATE(2004;04;30)" )
oSheet.getCellByPosition( 2, 5 ).setFormula( "=DATE(2004;05;31)" )
oSheet.getCellByPosition( 2, 6 ).setFormula( "=DATE(2004;06;30)" )
oSheet.getCellByPosition( 2, 7 ).setFormula( "=DATE(2004;07;31)" )
oSheet.getCellByPosition( 2, 8 ).setFormula( "=DATE(2004;08;31)" )
oSheet.getCellByPosition( 2, 9 ).setFormula( "=DATE(2004;09;30)" )
' Note that these last three dates are not set as DATE() function calls.
oSheet.getCellByPosition( 2, 10 ).setFormula( "10/31/2004" )
oSheet.getCellByPosition( 2, 11 ).setFormula( "11/30/2004" )
oSheet.getCellRangeByName( "C13" ).setFormula( "12/31/2004" )
'-----
'-----
' Format the date cells as dates.
oFormats = oDoc.getNumberFormats()
oLocale = createUnoStruct( "com.sun.star.lang.Locale" )
nDateKey = oFormats.getStandardFormat( com.sun.star.util.NumberFormat.DATE, oLocale )
oCell = oSheet.getCellRangeByName( "C2:C13" )
oCell.NumberFormat = nDateKey
'-----
'-----
' Now add a chart to the spreadsheet.
oCellRangeAddress = oSheet.getCellRangeByName( "A1:B13" ).getRangeAddress()
' oCellRangeAddress = MakeCellRangeAddress( 0, 0, 1, 1, 12 )
' Get the collection of charts from the sheet.
oCharts = oSheet.getCharts()
' Add a new chart with a specific name,
' in a specific rectangle on the drawing page,
' and connected to specific cells of the spreadsheet.
oCharts.addNewByName( "Sales",_
MakeRectangle( 8000, 1000, 16000, 10000 ),_
Array( oCellRangeAddress ),_
True, True )
' From the collection of charts, get the new chart we just created.
oChart = oCharts.getByName( "Sales" )
' Get the chart document model.
oChartDoc = oChart.getEmbeddedObject()
' Get the drawing text shape of the title of the chart.
oTitleTextShape = oChartDoc.getTitle()
' Change the title.
oTitleTextShape.String = "Sales Chart"
' Create a diagram.
oDiagram = oChartDoc.createInstance( "com.sun.star.chart.BarDiagram" )
' Set its parameters.
oDiagram.Vertical = True
' Make the chart use this diagram.
oChartDoc.setDiagram( oDiagram )
' Ask the chart what diagram it is using.
' (Unnecessary, since variable oDiagram already contains this value.)
oDiagram = oChartDoc.getDiagram()
' Make more changes to the diagram.
oDiagram.DataCaption = com.sun.star.chart.ChartDataCaption.VALUE
oDiagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
'
'-----
'-----
' Now demonstrate how to manipulate the sheets.
' Insert six more sheets into the document.
nNumSheetsCurrently = oDoc.getSheets().getCount()
oDoc.getSheets().insertNewByName( "Fred", nNumSheetsCurrently+1 )
oDoc.getSheets().insertNewByName( "Joe", nNumSheetsCurrently+2 )
oDoc.getSheets().insertNewByName( "Bill", nNumSheetsCurrently+3 )
oDoc.getSheets().insertNewByName( "Sam", nNumSheetsCurrently+4 )
oDoc.getSheets().insertNewByName( "Tom", nNumSheetsCurrently+5 )
oDoc.getSheets().insertNewByName( "David", nNumSheetsCurrently+6 )
' Now find a sheet named "Sheet2" and get rid of it.
oDoc.getSheets().removeByName( "Sheet2" )
' Now find the sheet named "Sam" and change its name to "Sheet 37"
oDoc.getSheets().getByName( "Sam" ).Name = "Sheet 37"
'
'-----
'-------
' Now print the document -- three different ways.
' Technique 1.
' Now print the document.
' Print two copies.
' Print pages 1 thru 4, and also page 10.
' oDoc.print( _
' Array( _
' MakePropertyValue( "CopyCount", 2 ),_
' MakePropertyValue( "Pages", "1-4;10" ) ) )
' Technique 2.
' Print the document already, without any arguments.
' oDoc.print( Array() )
' Using technique 1 or 2, be sure not to close the document
' until printing is completed.
' http://www.oooforum.org/forum/viewtopic.php?p=23144#23144
' Technique 3.
' Print the document by bringing up the Print Job dialog box
' for the user to interact with.
' oDocFrame = oDoc.getCurrentController().getFrame()
' oDispatchHelper = createUnoService( "com.sun.star.frame.DispatchHelper" )
' oDispatchHelper.executeDispatch( oDocFrame, ".uno:Print", "", 0, Array() )
' To learn some more about the dispatcher, see these articles...
' http://www.oooforum.org/forum/viewtopic.php?t=5058
' http://www.oooforum.org/forum/viewtopic.php?t=5057
'
'-------
'-------
' Now save the document
' Prepare the filename to save.
' We're going to save the file in several different formats,
' but all based on the same filename.
cFile = "C:\Documents and Settings\dbrewer\Desktop\MyCalc" ' Windows
' cFile = "/home/danny/Desktop/MyCalc.sxc" ' Linux
' Now save the spreadsheet in native OOo Calc format.
cURL = ConvertToURL( cFile + ".sxc" )
oDoc.storeAsURL( cURL, Array() )
' Note the above used storeAsUrl,
' the following use storeToUrl.
' Now save it in Excel format.
cURL = ConvertToURL( cFile + ".xls" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "MS Excel 97" ) ) )
' Now save a PDF.
cURL = ConvertToURL( cFile + ".pdf" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "calc_pdf_Export" ) ) )
' Now save it in CSV format.
cURL = ConvertToURL( cFile + ".csv" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ) ) )
' Now save it in DIF format.
cURL = ConvertToURL( cFile + ".dif" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "DIF" ) ) )
' Now save it in SYLK format.
cURL = ConvertToURL( cFile + ".sylk" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "SYLK" ) ) )
' Now save as HTML.
cURL = ConvertToURL( cFile + ".html" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "HTML (StarCalc)" ) ) )
' A list of some filter names you can use for both loading
' and saving a document can be found here...
' http://www.oooforum.org/forum/viewtopic.php?t=3549
'
'-------
'-------
' Now close the document
' oDoc.close( True )
'-------
End Sub
'----------
' Create and return a new com.sun.star.beans.PropertyValue.
'
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
If Not IsMissing( cName ) Then
oPropertyValue.Name = cName
EndIf
If Not IsMissing( uValue ) Then
oPropertyValue.Value = uValue
EndIf
MakePropertyValue() = oPropertyValue
End Function
'----------
' Create and return a new com.sun.star.awt.Rectangle.
'
Function MakeRectangle( nX, nY, nWidth, nHeight ) As com.sun.star.awt.Rectangle
oRectangle = createUnoStruct( "com.sun.star.awt.Rectangle" )
With oRectangle
.X = nX
.Y = nY
.Width = nWidth
.Height = nHeight
End With
MakeRectangle() = oRectangle
End Function
'----------
' Create and return a new com.sun.star.table.CellRangeAddress.
'
Function MakeCellRangeAddress( nSheetIndex, nStartColumn, nStartRow, nEndColumn, nEndRow ) As com.sun.star.table.CellRangeAddress
oCellRangeAddress = createUnoStruct( "com.sun.star.table.CellRangeAddress" )
With oCellRangeAddress
.Sheet = nSheetIndex
.StartColumn = nStartColumn
.StartRow = nStartRow
.EndColumn = nEndColumn
.EndRow = nEndRow
End With
MakeCellRangeAddress() = oCellRangeAddress
End Function
|
Simply copy and paste the entire above code into OOo Basic's IDE and run it. It is a self contained example.
This example is in OOo Basic.
This example appears later in this thread in other languages....
* Visual Basic
* Visual FoxPro
* Java
* Python
_________________
Want to make OOo Drawings like the colored flower design to the left?
Last edited by DannyB on Sat Nov 20, 2004 2:42 pm; edited 7 times in total |
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3208
Location: Troyes France
|
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3208
Location: Troyes France
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
| Posted: Mon Jan 19, 2004 9:09 am Post subject: |
|
|
| sergeM wrote: | | oSheet.getCellByPosition( 0, 0 ).String = "Month" |
This would work in OOo Basic, but not any other language. "setString()" instead of the psuedo-property "String" communicates better.
Using dot-String suggests that the cell has an actual String property. OOo Basic will humor you in this delusion, but it is not in fact true. There is no "String" property of a cell. Only a setString() method and a getString() method.
Writing it to look like a property, when in fact no property actually exists, makes it more difficult to translate to other programming languages. Since I write so much code that is intended for examples to others, I try to avoid this, so that my code is of greatest benefit to others. A java programmer, for example, won't be scratching her head as to why a "String" property cannot be used here?
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3208
Location: Troyes France
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
| Posted: Tue Jan 20, 2004 11:11 am Post subject: |
|
|
I'm don't want to suggest that using "String" as a property vs. getString() as a method is a bad thing -- assuming that you understand what is going on underneath.
Using "String" like this
1. results in shorter code
2. obscures the reality of what the API truly looks like
Item (1) is definitely a good thing. Item (2) has drawbacks, especially in code examples for others. Item (2) is not a problem if you are the only one reading your code, or if the code is only intended soley for OOo Basic users.
A line such as
| Code: | | oDrawPage = oDrawDoc.DrawPages( 5 ) |
looks better than
| Code: | | oDrawPage = oDrawDoc.getDrawPages().getByIndex( 5 ) |
especially for a newbie. It looks simpler and shorter. In fact, if you didn't know better, you might never realize that DrawPages( 5 ) is in fact, invoking two (2) method calls! It invokes getDrawPages(), and then getByIndex().
Nothing wrong with using "String". I just want to write clearly, with the code reflecting the API, both for readability and to promote understanding of the API when someone tries to compare my code to the API docs or against the output of Xray. Someone not finding "String" in Xray might not realize to look at getString().
I also have a goal to make my code to be easy to translate to other languages. This goal is not furthered by using tricky special features provided by any one single language. Some features, such as ThisComponent have no (present day) counterpart in any other language, so I feel free to use it. (But no telling what the Scripting Framework dudes may do.) Other shortcuts I use such as StarDesktop.loadComponentFromURL() are obvious enough that I don't think anyone is confused. Clearly, you must have the Desktop object. Getting the ServiceManager and Desktop are the two fundamental things you must master in any language in order to drive OOo.
I tend to favor smallish subroutines that eare easy to analyze on their own, and yet promote expressive power, ala Lisp style, in how they are used. My best example is when you combine my MakePropertyValue() function with the built in Array() function.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
| Posted: Wed Mar 17, 2004 7:39 am Post subject: |
|
|
Here is a bit more info for Calc users...
Here is an example of how to remove columns from a spreadsheet. (This looks more complicated than it is. If you remove all of the comments, there is very little code here.)
| Code: | Sub Main
' The document we want to work with is the document that this macro
' is embedded into.
oDoc = ThisComponent
' You could uncomment and use this line instead to create a new Calc document.
' oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() )
' You could uncomment and use one of these lines instead to open a document.
' For more information see...
' http://www.oooforum.org/forum/viewtopic.php?t=5252
' oDoc = StarDesktop.loadComponentFromURL( ConvertToURL( "C:\test.sxc" ), "_blank", 0, Array() )
' oDoc = StarDesktop.loadComponentFromURL( ConvertToURL( "/home/danny/test.sxc" ), "_blank", 0, Array() )
' oDoc = StarDesktop.loadComponentFromURL( "ftp://myname:mypassword@someserver.com/some/path/test.xsc", "_blank", 0, Array() )
' oDoc = StarDesktop.loadComponentFromURL( "http://@someserver.com/some/path/test.xsc", "_blank", 0, Array() )
' From the document, get the collection of all sheets,
' then get the zero'th sheet.
oSheet = oDoc.getSheets().getByIndex( 0 )
' Or get a sheet by its name...
' oSheet = oDoc.getSheets().getByName( "Sheet32" )
' From the sheet, get the collection of columns.
oColumns = oSheet.getColumns()
' Remove column zero. (which was originally column A.
oColumns.removeByIndex( 0, 1 )
' Remove what is NOW column 1,2,3.
' (which was originally column C,D,E, presently column B,C,D.)
' Remove 3 columns.
oColumns.removeByIndex( 1, 3 )
End Sub
|
So how did I learn about how to use removeByIndex() ?
First I started at the API Reference. From there I went to the sheet module. Then I looked at the Spreadsheet service. Note that this service is for a single sheet, not the entire multi-sheet document. Notice that a Spreadsheet service includes the SheetCellRange service. You can also clearly see that among the numerous interfaces implemented by this service, the SheetCellRange service happens to implement the XColumnRowRange interface. This service has the getColumns() method and the getRows() method. Calling getColumns() returns an XTableColumns which is an implementation of TableColumns service, and which also has the removeByIndex() method. (The observant will also notice that it has the insertByIndex() method as well.)
In fact, since XTableColumns inherits from XIndexAccess, you can conveniently also use the getCount() method to determine how many columns there are, and the getByIndex() method to return a particular column.
You might also notice that the TableColumns service implements the XNameAccess interface as well, and therefore, on the group of columns, you can call getByName() to get a certian column, or hasByName() to check if a certian column name exists, or getElementNames() to return an array of strings which are the column names.
The last four paragraphs have been a brief tutorial on how to navigate the API docs.
Here are some functions to convert between column name and column index.
| Code: | ' 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
|
See this related past article.
http://www.oooforum.org/forum/viewtopic.php?p=23013#23013
Here are some functions to convert between sheet name and sheet index.
| Code: | Function SheetNumberToName( ByVal nSheetNumber As Long ) As String
oSheets = ThisComponent.getSheets()
' aSheetNames is an Array() of String. (The sheet names).
aSheetNames = oSheets.getElementNames()
cSheetName = aSheetNames( nSheetNumber )
SheetNumberToName() = cSheetName
End Function
' Given the name of a sheet, return it's index.
' Return -1 if not found.
Function SheetNameToNumber( ByVal cSheetName As String ) As Long
oSheets = ThisComponent.getSheets()
' aSheetNames is an Array() of String. (The sheet names).
aSheetNames = oSheets.getElementNames()
For i = LBound( aSheetNames ) To UBound( aSheetNames )
cSheetName2 = aSheetNames( i ) ' get string from array
If cSheetName = cSheetName2 Then
SheetNameToNumber() = i
Exit Function
EndIf
Next
SheetNameToNumber() = -1
End Function
|
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
| Posted: Thu Apr 08, 2004 7:30 am Post subject: |
|
|
Here is a very similar Visual FoxPro example that.....
1. Creates an OOo Calc spreadsheet
2. Puts some strings into it.
3. Puts some numbers into it.
4. Puts some dates into it (as formulas)
5. Saves it in Calc format (as C:\Example.sxw)
6. Saves it in Excel format (as C:\Example.xls)
7. Closes the document -- but this line is commented to leave the spreadsheet open in OOo
| Code: | VfpOOoCalcExample()
PROCEDURE VfpOOoCalcExample()
* Create a spreadsheet.
LOCAL oDoc
oDoc = OOoOpenURL( "private:factory/scalc" )
* Get first sheet
LOCAL oSheet
oSheet = oDoc.getSheets().getByIndex( 0 )
*-----
* Put some sales figures onto the sheet.
oSheet.getCellByPosition( 0, 0 ).setString( "Month" )
oSheet.getCellByPosition( 1, 0 ).setString( "Sales" )
oSheet.getCellByPosition( 2, 0 ).setString( "End Date" )
oSheet.getCellByPosition( 0, 1 ).setString( "Jan" )
oSheet.getCellByPosition( 0, 2 ).setString( "Feb" )
oSheet.getCellByPosition( 0, 3 ).setString( "Mar" )
oSheet.getCellByPosition( 0, 4 ).setString( "Apr" )
oSheet.getCellByPosition( 0, 5 ).setString( "May" )
oSheet.getCellByPosition( 0, 6 ).setString( "Jun" )
oSheet.getCellByPosition( 0, 7 ).setString( "Jul" )
oSheet.getCellByPosition( 0, 8 ).setString( "Aug" )
oSheet.getCellByPosition( 0, 9 ).setString( "Sep" )
oSheet.getCellByPosition( 0, 10 ).setString( "Oct" )
oSheet.getCellByPosition( 0, 11 ).setString( "Nov" )
oSheet.getCellByPosition( 0, 12 ).setString( "Dec" )
oSheet.getCellByPosition( 1, 1 ).setValue( 3826.37 )
oSheet.getCellByPosition( 1, 2 ).setValue( 3504.21 )
oSheet.getCellByPosition( 1, 3 ).setValue( 2961.45 )
oSheet.getCellByPosition( 1, 4 ).setValue( 2504.12 )
oSheet.getCellByPosition( 1, 5 ).setValue( 2713.98 )
oSheet.getCellByPosition( 1, 6 ).setValue( 2448.17 )
oSheet.getCellByPosition( 1, 7 ).setValue( 1802.13 )
oSheet.getCellByPosition( 1, 8 ).setValue( 2203.22 )
oSheet.getCellByPosition( 1, 9 ).setValue( 1502.54 )
oSheet.getCellByPosition( 1, 10 ).setValue( 1207.68 )
oSheet.getCellByPosition( 1, 11 ).setValue( 1819.71 )
oSheet.getCellByPosition( 1, 12 ).setValue( 986.03 )
oSheet.getCellByPosition( 2, 1 ).setFormula( "=DATE(2004;01;31)" )
oSheet.getCellByPosition( 2, 2 ).setFormula( "=DATE(2004;02;29)" )
oSheet.getCellByPosition( 2, 3 ).setFormula( "=DATE(2004;03;31)" )
oSheet.getCellByPosition( 2, 4 ).setFormula( "=DATE(2004;04;30)" )
oSheet.getCellByPosition( 2, 5 ).setFormula( "=DATE(2004;05;31)" )
oSheet.getCellByPosition( 2, 6 ).setFormula( "=DATE(2004;06;30)" )
oSheet.getCellByPosition( 2, 7 ).setFormula( "=DATE(2004;07;31)" )
oSheet.getCellByPosition( 2, 8 ).setFormula( "=DATE(2004;08;31)" )
oSheet.getCellByPosition( 2, 9 ).setFormula( "=DATE(2004;09;30)" )
* Note that these last three dates are not set as DATE() function calls.
oSheet.getCellByPosition( 2, 10 ).setFormula( "10/31/2004" )
oSheet.getCellByPosition( 2, 11 ).setFormula( "11/30/2004" )
oSheet.getCellRangeByName( "C13" ).setFormula( "12/31/2004" )
*-----
* Format the date cells as dates.
oFormats = oDoc.getNumberFormats()
oLocale = OOoCreateStruct( "com.sun.star.lang.Locale" )
* com.sun.star.util.NumberFormat.DATE = 2
nDateKey = oFormats.getStandardFormat( 2, oLocale )
oCell = oSheet.getCellRangeByName( "C2:C13" )
oCell.NumberFormat = nDateKey
LOCAL ARRAY aOneArg[1]
LOCAL cFile, cURL
* cFile = GetDesktopFolderPathname()+"example"
cFile = "c:\example"
* Now save the spreadsheet.
cURL = OOoConvertToURL( cFile + ".sxw" )
aOneArg[1] = OOoMakePropertyValue( "Overwrite", .T. )
oDoc.storeToUrl( cURL, @ aOneArg )
* Now save it as Excel
cURL = OOoConvertToURL( cFile + ".xls" )
aOneArg[1] = OOoMakePropertyValue( "FilterName", "MS Excel 97" )
oDoc.storeToUrl( cURL, @ aOneArg )
* Close the document.
* oDoc.close( 1 ) && TRUE
ENDPROC
* Open or Create a document from it's URL.
* New documents are created by URL's such as:
* private:factory/sdraw
* private:factory/swriter
* private:factory/scalc
* private:factory/simpress
FUNCTION OOoOpenURL( cURL )
* LOCAL oPropertyValue
* oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" )
* LOCAL ARRAY aNoArgs[1]
* aNoArgs[1] = oPropertyValue
* aNoArgs[1].Name = "ReadOnly"
* aNoArgs[1].Value = .F.
* These two lines replace the alternate version above,
* which are left commented for the insight they provide.
LOCAL ARRAY aNoArgs[1]
aNoArgs[1] = OOoMakePropertyValue( "Hidden", .F. )
LOCAL oDesktop
oDesktop = OOoGetDesktop()
LOCAL oDoc
oDoc = oDesktop.LoadComponentFromUrl( cURL, "_blank", 0, @ aNoargs )
* Make sure that arrays passed to this document are passed zero based.
COMARRAY( oDoc, 10 )
RETURN oDoc
ENDFUNC
* Create a com.sun.star.beans.PropertyValue struct and return it.
FUNCTION OOoMakePropertyValue( cName, uValue, nHandle, nState )
LOCAL oPropertyValue
oPropertyValue = OOoCreateStruct( "com.sun.star.beans.PropertyValue" )
oPropertyValue.Name = cName
oPropertyValue.Value = uValue
IF TYPE([nHandle])="N"
oPropertyValue.Handle = nHandle
ENDIF
IF TYPE([nState])="N"
oPropertyValue.State = nState
ENDIF
RETURN oPropertyValue
ENDFUNC
* Sugar coated routine to create any UNO struct.
* Use the Bridge_GetStruct() feature of the OLE-UNO bridge.
FUNCTION OOoCreateStruct( cTypeName )
LOCAL oServiceManager
oServiceManager = OOoGetServiceManager()
LOCAL oStruct
oStruct = .NULL.
LOCAL cOldErrHandler
cOldErrHandler = ON( "ERROR" )
ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
ON ERROR &cOldErrHandler
IF ISNULL( oStruct )
=__OOoReleaseCachedVars()
oServiceManager = OOoGetServiceManager()
oStruct = oServiceManager.Bridge_GetStruct( cTypeName )
ENDIF
RETURN oStruct
ENDFUNC
* Return the OpenOffice.org desktop object.
* Cache it in a global variable.
* Create it if not already cached.
FUNCTION OOoGetDesktop()
IF (TYPE([goOOoDesktop])!="O") OR ISNULL( goOOoDesktop )
PUBLIC goOOoDesktop
goOOoDesktop = OOoServiceManager_CreateInstance( "com.sun.star.frame.Desktop" )
COMARRAY( goOOoDesktop, 10 )
ENDIF
RETURN goOOoDesktop
ENDFUNC
* Return the OpenOffice.org service manager object.
* Cache it in a global variable.
* Create it if not already cached.
FUNCTION OOoGetServiceManager()
IF (TYPE([goOOoServiceManager])!="O") OR ISNULL( goOOoServiceManager )
PUBLIC goOOoServiceManager
goOOoServiceManager = CREATEOBJECT( "com.sun.star.ServiceManager" )
ENDIF
RETURN goOOoServiceManager
ENDFUNC
* Sugar coated routine to ask the service manager to
* create you an instance of some other OpenOffice.org UNO object.
FUNCTION OOoServiceManager_CreateInstance( cServiceName )
LOCAL oServiceManager
oServiceManager = OOoGetServiceManager()
LOCAL oInstance
oInstance = .NULL.
LOCAL cOldErrHandler
cOldErrHandler = ON( "ERROR" )
ON ERROR = DoNothing__ErrorHandler( ERROR(), MESSAGE(), LINENO(), SYS(16), PROGRAM(), SYS(2018) )
oInstance = oServiceManager.createInstance( cServiceName )
ON ERROR &cOldErrHandler
IF ISNULL( oInstance )
=__OOoReleaseCachedVars()
oServiceManager = OOoGetServiceManager()
oInstance = oServiceManager.createInstance( cServiceName )
ENDIF
RETURN oInstance
ENDFUNC
PROCEDURE DoNothing__ErrorHandler( pnError, pcErrMessage, pnLineNo, pcProgramFileSys16, pcProgram, pcErrorParamSys2018 )
ENDPROC
PROCEDURE __OOoReleaseCachedVars()
RELEASE goOOoServiceManager, goOOoDesktop, goOOoCoreReflection
ENDPROC
* Convert a local filename to an OOo URL.
FUNCTION OOoConvertToURL( cFilename )
* Ensure leading slash.
IF LEFT( cFilename, 1 ) != "/"
cFileName = "/" + cFileName
ENDIF
LOCAL cURL
cURL = CHRTRAN( cFilename, "\", "/" ) && change backslashes to forward slashes.
cURL = "file://" + cURL
RETURN cURL
ENDFUNC
|
The function VfpOOoCalcExample() is actually the heart of the example.
Everything else after VfpOOoCalcExample() is just stuff ripped from my general purpose OOo Library in VFP.
This was tested in VFP 8 and VFP 7. It should work in VFP 6, but not tested. It may work in VFP 5, but I have reason to believe that it might not. It will definitely not work in VFP 3.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
| Posted: Sat Apr 10, 2004 9:12 am Post subject: |
|
|
I have updated the Calc example, the first message in this thread, with a more sophisticated example.
The new example demonstrates how to add a simple bar chart to a spreadsheet.
Other trivial matters are added to the example, such as how to print (using 3 techniques), save the document to more formats (DIF, SYLK, etc.) and how to close the document.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| 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
|
)