OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calc Examples
Goto page 1, 2, 3, 4, 5, 6, 7  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Jan 17, 2004 10:21 am    Post subject: Calc Examples Reply with quote

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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Jan 17, 2004 2:28 pm    Post subject: Reply with quote

In the past, I posted additional information here.

Very similar example....
http://www.oooforum.org/forum/viewtopic.php?t=3834
Additional information with links to the API...
http://www.oooforum.org/forum/viewtopic.php?t=4315

How to check if a sheet exists...
http://www.oooforum.org/forum/viewtopic.php?t=3613
_________________
Want to make OOo Drawings like the colored flower design to the left?


Last edited by DannyB on Mon Jan 19, 2004 10:47 am; edited 1 time in total
Back to top
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Mon Jan 19, 2004 2:05 am    Post subject: Reply with quote

OK it works.
I would write
oSheet.getCellByPosition( 0, 0 ).setString( "Month" )
instead of
oSheet.getCellByPosition( 0, 0 ).setFormula( "Month" )
but it works like that.
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Mon Jan 19, 2004 2:12 am    Post subject: Reply with quote

or oSheet.getCellByPosition( 0, 0 ).String = "Month"
....
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Jan 19, 2004 9:09 am    Post subject: Reply with quote

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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Jan 19, 2004 10:44 am    Post subject: Reply with quote

Here are similar Calc examples in Visual Basic

http://www.oooforum.org/forum/viewtopic.php?p=14408#14408
http://www.oooforum.org/forum/viewtopic.php?t=3554
_________________
Want to make OOo Drawings like the colored flower design to the left?


Last edited by DannyB on Mon Jan 19, 2004 11:45 am; edited 1 time in total
Back to top
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Mon Jan 19, 2004 11:42 am    Post subject: Reply with quote

I completly agree with your advise not to use the "string" property. (I use it instead frequently Embarassed )
What I want to say is only that i believed before your post that a formula is a string which begin with "=" and cannot begin with an other character. But I was wrong.
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Jan 20, 2004 11:11 am    Post subject: Reply with quote

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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Jan 31, 2004 10:35 am    Post subject: Reply with quote

Here is a related message that is potentially useful.
http://www.oooforum.org/forum/viewtopic.php?p=20309#20309
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Wed Feb 18, 2004 5:22 am    Post subject: Reply with quote

Here is a companion example for Writer....
http://www.oooforum.org/forum/viewtopic.php?t=6049

It shows the same overall techniques.
1. How to create or load a Writer document (load from several formats).
2. How to insert some simple content into document.
3. How to print document.
4. How to save document in several formats.
5. How to close document.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Feb 23, 2004 11:23 am    Post subject: Reply with quote

Here is an example that shows manipulating the Text of a spreadsheet Cell, by changing the text, then changing a portion of the cell text to be Red and Bold.

Altering CharacterProperties of Text
http://www.oooforum.org/forum/viewtopic.php?t=6195
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Wed Mar 03, 2004 7:47 am    Post subject: Reply with quote

Here is an example showing...
1. how to get the text of a cell
2. how to change the cell's text color and background color

http://www.oooforum.org/forum/viewtopic.php?t=3911
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Wed Mar 17, 2004 7:39 am    Post subject: Reply with quote

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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Thu Apr 08, 2004 7:30 am    Post subject: Reply with quote

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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Sat Apr 10, 2004 9:12 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Goto page 1, 2, 3, 4, 5, 6, 7  Next
Page 1 of 7

 
Jump to:  
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