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 Previous  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
SergeM
Super User
Super User


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

PostPosted: Sat Oct 09, 2004 7:05 am    Post subject: Reply with quote

Very interesting as usualy : we have to spend hours to study this code. Thank you Danny.
_________________
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: Sat Oct 09, 2004 8:39 am    Post subject: Reply with quote

An example of a directory listing into a Calc spreadsheet.
http://www.oooforum.org/forum/viewtopic.php?p=50977#50977

The program creates a Calc spreadsheet that has five columns.
1. Filename
2. IsFolder?
3. IsReadOnly?
4. Size (bites)
5. Last Modified
_________________
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 Oct 11, 2004 7:32 am    Post subject: Reply with quote

Example of how to clear the formulas of a range of cells, but preserve the values.
http://www.oooforum.org/forum/viewtopic.php?p=51141#51141
_________________
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 Oct 28, 2004 6:19 am    Post subject: Reply with quote

How to find the first or last used cell on a spreadsheet.

Code:
Sub Main
   oDoc = ThisComponent
   oSheet = oDoc.getSheets().getByIndex( 0 )
   
   oLastCell = GetLastUsedCell( oSheet )
   Print "Last cell is ", oLastCell.getRangeAddress().EndColumn, oLastCell.getRangeAddress().EndRow
   
   oFirstCell = GetFirstUsedCell( oSheet )
   Print "First cell is ", oFirstCell.getRangeAddress().EndColumn, oFirstCell.getRangeAddress().EndRow
End Sub



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


The above was adapted from a technique first demonstrated here....
http://www.oooforum.org/forum/viewtopic.php?p=26719#26719
_________________
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: Tue Nov 02, 2004 7:46 am    Post subject: Reply with quote

Here is an example that answers this question.

Put buttons in an entire column for an action on their rows?
http://www.oooforum.org/forum/viewtopic.php?t=13703

In this example, I created a spreadsheet. Put the following code into the macro.

Put a button into Column A of the first row of the spreadsheet that you are interested in. RightClick --> Control Properties on the button. Set the button's name. On the Events tab, under When Initiating, make the button call the ButtonClick() macro below.

Now copy the button, and put it into the other rows in Column A.

Now when any button in column A is clicked, the cells from column E over are shifted one position to the right.


Code:

' This routine is called by multiple buttons on the spreadsheet.
' The buttons are in the same column, but different rows.
' This routine does something to the cells on a single row,
'  that is, the row, that the button lives on.
Sub ButtonClick( oEvent )
   ' Get the control which called this routine.
   oButtonCtrl = oEvent.Source
   ' Get the control's model from the control.
   oButtonModel = oButtonCtrl.getModel()
   
   ' Get the name of the button.
   'cButtonName = oButtonModel.Name
   
   ' Get this document model.
   oDoc = ThisComponent
   ' Get the current controller.
   '  (i.e. the controller associated with whichever window is currently viewing the sheet.)
   ' Note, multiple windows can view the sheet by picking, Window --> New Window.
   oDocCtrl =  oDoc.getCurrentController()
   ' Get the active sheet in the window.
   oSheet = oDocCtrl.getActiveSheet()
   
   ' Go on a wild goose chase for the control shape.
   ' We'll assume that buttons calling this routine are on the Standard form.
   oControlShape = FindControlShape( oSheet, "Standard", oButtonModel )
   
   ' Finally, get the spreadsheet cell that the button is anchored to.
   oCell = oControlShape.getAnchor()
   ' Get the Row of the cell.  This is what we've been wanting the whole time.
   nRow = oCell.getRangeAddress().StartRow
   
   
   ' Now that we finally know the row of the button that called us,
   '  let's do something to that row...
   DoSomethingToRow( oDoc, oSheet, nRow )
End Sub


' Given the control model on a sheet, search for the control's shape.
Function FindControlShape( oSheet, cFormName, oWantedControlModel )
   oDrawPage = oSheet.getDrawPage()
   oForms = oDrawPage.getForms()
   oForm = oForms.getByName( cFormName )
   
   nNumShapes = oDrawPage.getCount()
   For i = 0 To nNumShapes - 1
      oShape = oDrawPage.getByIndex( i )
      If HasUnoInterfaces( oShape, "com.sun.star.drawing.XControlShape" ) Then
         oControlModel = oShape.getControl()
         If EqualUnoObjects( oWantedControlModel, oControlModel ) Then
            FindControlShape = oShape
            Exit Function
         EndIf
      EndIf
   Next
End Function


' This subroutine does something to a row of the spreadsheet.
' What it does is up to you.
'
' This particular example, shifts the cells to the right, in column E.
'
Sub DoSomethingToRow( oDoc, oSheet, nRow )
   ' Get one of the document's controllers -- the current one.
   oDocCtrl = oDoc.getCurrentController()
   ' Get the frame that goes with the controller.
   oDocFrame = oDocCtrl.getFrame()
   
   ' Get cell E in the current row.  (E = col 4)
   oCell = oSheet.getCellByPosition( 4, nRow )
   ' Select the cell
   oDocCtrl.select( oCell )
   
   ' Create a dispatch helper.
   oDispatchHelper = createUnoService( "com.sun.star.frame.DispatchHelper" )
   ' Do a dispatch that does the Insert Cells operation.
   oDispatchHelper.executeDispatch( oDocFrame, ".uno:InsertCell",  "", 0, _
      Array( MakePropertyValue( "Flags", ">" ) ) )
   
End Sub



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



Some of the code above was drawn from this earlier related example....
http://www.oooforum.org/forum/viewtopic.php?p=49454#49454
_________________
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: Tue Nov 09, 2004 7:02 am    Post subject: Reply with quote

Here is an interesting discussion about how to detect if the cells in a range are empty.
http://www.oooforum.org/forum/viewtopic.php?t=12879
_________________
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 Nov 15, 2004 10:27 am    Post subject: Reply with quote

Copy data from Writer table to Calc spreadsheet.
http://www.oooforum.org/forum/viewtopic.php?p=55393#55393
_________________
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 Nov 20, 2004 2:41 pm    Post subject: Reply with quote

What follows is the first example in this thread, rewritten in Python. Just to review, what this example does is....
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


In order to make this example work, you need the Danny.OOo.OOoLib module from Danny's Python Modules.

Notice how remarkably similar this code is to the Basic code in the first example. This is no accident. My OOoLib module is intended to make it easy to literally cut and paste Basic code and use it as Python code.

My module provides such goodies as...
* StarDesktop global variable (see it in the first line of the example!)
* ConvertToURL
* Array()
* makePropertyValue()
* ...and many other common Basic doodads

In adapting this example, the biggest things I had to do were....
* Change case of some things, such as MakePropertyValue to makePropertyValue, makeRectangle, etc.
* Change comments from apostrophe (') to a (#) for python.
* Make sure indentation was correct.

There was one nasty issue. See how the oDoc.print() method is used to print the spreadsheet! According to the Python Uno Bridge documentaiton, you cannot call "print", because the word "print" has a special meaning in Python. See how to use the uno.invoke() machinery in the example below.

Code:

from Danny.OOo.OOoLib import *

def CalcExample():
    # create a new Calc spreadsheet.
    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.
    com_sun_star_util_NumberFormat_DATE = uno.getConstantByName( "com.sun.star.util.NumberFormat.DATE" )
    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 = uno.getConstantByName( "com.sun.star.chart.ChartDataCaption.VALUE" )
    oDiagram.DataRowSource = uno.getConstantByName( "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.
    #
    # NOTE: we would do it like this, except the word "print"
    #       has a special meaning in python, and cannot be invoked
    #       as a method.
    #oDoc.print(
    #    Array(
    #        makePropertyValue( "CopyCount", 2 ),
    #        makePropertyValue( "Pages", "1-4;10" ) ) )
    uno.invoke( oDoc, "print", ( Array(
            makePropertyValue( "CopyCount", 2 ),
            makePropertyValue( "Pages", "1-4;10" ) ), ) )

    # Technique 2.
    # Print the document already, without any arguments.
    uno.invoke( oDoc, "print", ( Array(), ) )
    #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 )
    #-------





# import Danny.OOo.Examples.CalcExamples
# reload( Danny.OOo.Examples.CalcExamples ); from Danny.OOo.Examples.CalcExamples import *

# CalcExample()



I have also adapted the above into a self contained program. The following thread has the self contained version along with instructions on how to run it on one computer such that it connects to a running OOo on a different computer to create the spreadsheet there.
http://www.oooforum.org/forum/viewtopic.phtml?p=75067#75067

See Also....

A lot of introductory information / answers about OOo UNO and Python
http://www.oooforum.org/forum/viewtopic.phtml?p=75468#75468
_________________
Want to make OOo Drawings like the colored flower design to the left?


Last edited by DannyB on Sat Apr 23, 2005 9:06 am; edited 2 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: Wed Dec 01, 2004 7:37 am    Post subject: Reply with quote

In answer to this question.....
http://www.oooforum.org/forum/viewtopic.php?t=14518
Here is an example of how to set the font size of text in a spreadsheet cell.

Code:
Sub Main
   ' Get this spreadsheet document
   oDoc = ThisComponent
   ' Get the first sheet.
   oSheet = oDoc.getSheets().getByIndex( 0 )
   ' Get cell A1
   oCell = oSheet.getCellByPosition( 0, 0 )
   
   ' Put some text into the cell.
   oCell.setString( "This is a test" )
   
   ' Get the cell's Text.
   ' You can do many things with this text, like you can do with Writer text.
   oText = oCell.getText()
   ' Get a cursor on the text.
   oCursor = oText.createTextCursor()
   
   ' Select ALL of the text.
   oCursor.gotoStart( False ) ' goto start of text
   oCursor.gotoEnd( True ) ' goto end of text while holding down SHIFT key
   ' Change selected char's height
   oCursor.CharHeight = 34
   
   ' Select the first four characters
   oCursor.gotoStart( False ) ' goto start of text
   oCursor.goRight( 4, True ) ' go right 4 chars, holding down SHIFT key
   ' Change selected char's height
   oCursor.CharHeight = 27
End Sub



If all of the text is of uniform size, and you want to increase the size of the entire text cell, then simply do....
Code:
Sub Main
   ' Get this spreadsheet document
   oDoc = ThisComponent
   ' Get the first sheet.
   oSheet = oDoc.getSheets().getByIndex( 0 )
   ' Get cell A1
   oCell = oSheet.getCellByPosition( 0, 0 )
   
   ' Get the cell's Text.
   ' You can do many things with this text, like you can do with Writer text.
   oText = oCell.getText()
   ' Get a cursor on the text.
   oCursor = oText.createTextCursor()
   
   ' Select ALL of the text.
   oCursor.gotoStart( False ) ' goto start of text
   oCursor.gotoEnd( True ) ' goto end of text while holding down SHIFT key
   ' Change selected char's height
   oCursor.CharHeight = oCursor.CharHeight + 1
End Sub


If the characters are of different sizes, then it is more tricky. One brute force approach that comes to mind is to iterate the cursor over every character of the text, and individually increase the font size of each character.

For more about cursors and text, see this explanation in my Writer Examples thread. Keep in mind that the Text of a Writer document has many features not available on the Text of a spreadsheet cell.

Also see this thread.

Altering CharacterProperties of Text
http://www.oooforum.org/forum/viewtopic.php?t=6195

for an example of the similarities of Text in a Writer doc, spreadsheet Cell, and drawing TextShape.
_________________
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: Fri Dec 03, 2004 7:13 am    Post subject: Reply with quote

The following example is an answer to this question.
http://www.oooforum.org/forum/viewtopic.php?t=14760

What this does is....
1. Underline a row of cells, every Nth row, by setting the cell border bottom
2. Adjust the column widths of a certain range of columns.

Code:
Sub Main
   
   ' Create a new document.
   oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() )
   ' Alternative: Use document that this macro is embedded into.
   'oDoc = ThisComponent
   ' Alternative: Load document from disk
   ' CHANGE ME
   'cFile = "C:\Documents and Settings\dbrewer\Desktop\Something.sxc" ' for Windows
   'cFile = "/home/danny/Desktop/Something.sxc" ' for Linux
   'cUrl = ConvertToUrl( cFile )
   'oDoc = StarDesktop.loadComponentFromURL( cUrl, "_blank", 0, Array() )

   ' Get first sheet of the document -- the zero'th sheet.
   oSheet = oDoc.getSheets().getByIndex( 0 )
   
   UnderlineRows( oSheet )
   AdjustColumnWidths( oSheet )
End Sub


' Pass in a single sheet of a spreadsheet document.
' This will create borders that underline certain cells.
Sub UnderlineRows( oSheet )
   '-----
   ' CHANGE ME
   ' CHANGE ME
   ' You could turn these local variables into parameters.
   nUnderlineFirstRow = 9
   nUnderlineSkipRows = 4
   nUnderlineLastRow = 50
   cUnderlineFirstColumn = "A"
   cUnderlineLastColumn = "I"
   
   'nBorderColor = RGB( 0, 0, 0 ) ' Black
   nBorderColor = RGB( 255, 200, 200 ) ' Pink
   
   nInnerBorderWidth = 0 ' in 1000'th of cm
   'nOuterBorderWidth = 75 ' in 1000'th of cm, means 0.075 cm width
   nOuterBorderWidth = 1/32 * 2540 ' 1/32 inch (2.54 cm = 1 inch)
   '-----
   
   nRow = nUnderlineFirstRow
   Do While nRow <= nUnderlineLastRow
      ' Form a string such as...  "A19:I19"
      cCellRangeName = cUnderlineFirstColumn + CSTR( nRow ) _
                  + ":" + cUnderlineLastColumn + CSTR( nRow )
      
      ' Get the range of cells whose border is to be adjusted.
      oCellRange = oSheet.getCellRangeByName( cCellRangeName )
      
      'oCellRange.LeftBorder   = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 )
      'oCellRange.RightBorder  = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 )
      'oCellRange.TopBorder    = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 )
      oCellRange.BottomBorder = MakeCellBorderLine( nBorderColor, nInnerBorderWidth, nOuterBorderWidth, 0 )
      
      nRow = nRow + nUnderlineSkipRows
   Loop
End Sub


' Pass in a single sheet of a spreadsheet document.
' This will set the column widths of certain cells.
Sub AdjustColumnWidths( oSheet )
   '-----
   ' CHANGE ME
   ' CHANGE ME
   ' You could turn these local variables into parameters.
   cFirstColumn = "A"
   cLastColumn = "I"
   '-----
   
   ' Turn the column names into column numbers.
   nFirstColumn = CalcColumnNameToNumber( oSheet, cFirstColumn )
   nLastColumn = CalcColumnNameToNumber( oSheet, cLastColumn )
   
   ' Get the collection of all columns on the sheet.
   oColumns = oSheet.getColumns()
   
   ' Loop over each column...
   For nCol = nFirstColumn To nLastColumn
      ' Get a single column
      oColumn = oColumns.getByIndex( nCol )
      
      ' Change column width
      oColumn.Width = 2.5 * 2540 ' 2.5 inches * 2.54 cm / inch.
   Next
End Sub


Function MakeCellBorderLine( nColor, nInnerLineWidth, nOuterLineWidth, nLineDistance ) _
         As com.sun.star.table.BorderLine
   oBorderLine = createUnoStruct( "com.sun.star.table.BorderLine" )
   With oBorderLine
      .Color = nColor

      .InnerLineWidth = nInnerLineWidth
      .OuterLineWidth = nOuterLineWidth
      .LineDistance = nLineDistance
   End With
   MakeCellBorderLine = oBorderLine
End Function

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


Be sure that you edit the lines that say CHANGE ME.

The CalcColumnNameToNumber function was first discussed earlier in this thread, here....
http://www.oooforum.org/forum/viewtopic.php?p=25537#25537

The MakeCellBorderLine function was first discussed earlier in this thread, here....
http://www.oooforum.org/forum/viewtopic.php?p=31118#31118
_________________
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 Dec 08, 2004 10:09 am    Post subject: Reply with quote

Scroll / Set first visible column or row displayed in spreadsheet
http://www.oooforum.org/forum/viewtopic.php?p=57982#57982
_________________
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 Dec 09, 2004 10:45 am    Post subject: Reply with quote

See the following article which applies to Draw, Writer and Calc documents.

How to insert a graphic into a document
http://www.oooforum.org/forum/viewtopic.php?t=14979
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
the_dude
General User
General User


Joined: 25 Oct 2004
Posts: 22

PostPosted: Mon Jan 03, 2005 9:53 am    Post subject: Reply with quote

hmm here's one quick question,
when i open a cmd, and type

soffice "-accept=socket,host=localhost,port=2002;urp;"

./python dannyexample.py (i've use a text-editor and import the syntax, named dannyexample.py)



it saids
Import Error: No Module Named Danny.OOo.OOolib

I have it downloaded already and put it in the OOo/program directory.
have i put it in the wrong place? or it should be put somewhere else?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


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

PostPosted: Tue Jan 04, 2005 11:37 am    Post subject: Reply with quote

the_dude wrote:
Import Error: No Module Named Danny.OOo.OOolib


See answer over here.....
http://www.oooforum.org/forum/viewtopic.php?p=60941#60941
_________________
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 Jan 05, 2005 11:08 am    Post subject: Reply with quote

A nice example of how to copy cells within a spreadsheet.....
http://www.oooforum.org/forum/viewtopic.php?p=61083#61083
_________________
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 Previous  1, 2, 3, 4, 5, 6, 7  Next
Page 4 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