| View previous topic :: View next topic |
| Author |
Message |
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
 Posted: Thu Oct 28, 2004 6:19 am Post subject: |
 |
|
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 |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Tue Nov 02, 2004 7:46 am Post subject: |
|
|
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 |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Sat Nov 20, 2004 2:41 pm Post subject: |
|
|
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 |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Wed Dec 01, 2004 7:37 am Post subject: |
|
|
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 |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Fri Dec 03, 2004 7:13 am Post subject: |
|
|
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 |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
the_dude
General User
Joined: 25 Oct 2004
Posts: 22
|
| Posted: Mon Jan 03, 2005 9:53 am Post subject: |
|
|
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 |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
|
|
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
|
