Increasing the sheet name tab size for libreoffice calc in ubuntu 12.04

In libreoffice calc, the font size (or horizontal height) for the sheet name tab is related to the scrollbar slider width.

Open "/usr/share/thmemes/[a theme used, e.g. Ambiance]/gtk-2.0/gtkrc"

find "GtkScrollbar::slider-width = xx", where xx is a number.

then, change the number xx to a larger value.

Logout and login, or, (this is better) change theme to other one and then return to current theme using ubuntu tweak.

calc python macro: print cell range

## set tmpRange => from (0, 0) to (5, 40)  <= (Col, Row)
tmpRange = oSheet.getCellRangeByPosition(0, 0, 8, 78)  

## oSheet1.setPrintAreas( Array() )
oSheet1.setPrintAreas( (tmpRange.RangeAddress, ) )

## set print margin

oFamilies = oDoc.getStyleFamilies()

oPageStyles = oFamilies.getByName("PageStyles") 

Style = oPageStyles.getByName(oSheet.PageStyle) 

Style.BackColor = &HCCCCFF 
Style.BottomMargin = 1000 
Style.TopMargin = 1000 
Style.LeftMargin = 1000 
Style.RightMargin = 500 

Style.PageScale = 77

Style.HeaderIsOn = False
Style.FooterIsOn = False

python calc macro: create textbox (TextShape) on a sheet

oTextShape = oDoc.createInstance("com.sun.star.drawing.TextShape")  

oSheet.DrawPage.add(oTextShape)  

aa = oSheet.DrawPage.getByindex( n )   


## set string...  
aa.String = "babo hehe"   

## set Size...  
tmpSize = aa.getSize()  
tmpSize.Width = 9999  
aa.setSize(tmpSize)    

## set RotateAngle  
aa.RotateAngle = 9000  ## rotate 90 degree    

## paragraph adjust 
aa.ParaAdjust = 1  ## set right align 

HOWTO sort sheets using python

import uno

def getReady():
 localContext = uno.getComponentContext()
 resolver = localContext.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext )
 ctx = resolver.resolve( "uno:socket,host=localhost,port=8100;urp;StarOffice.ComponentContext" )
 smgr = ctx.ServiceManager
 desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
 oDoc = desktop.getCurrentComponent()
 
 return oDoc

oDoc = getReady()
oSheets = oDoc.getSheets()

## getting all sheet name
sheetNames = []
for loopi in range(oSheets.Count):
 tmpSheet = oSheets.getByIndex(loopi)
 sheetNames.append(tmpSheet.Name)

## get sorting
sheetNames.sort(reverse=True)

for tmpName in sheetNames :
 oSheets.moveByName(tmpName, 0)

Converting a date value read from a calc cell into readable format using python

When a date value, such as "2010-10-12" or etc., is in a cell,

oCell.getValue() and oCellRange.DataArray methods give just a number, not date format,
while oCell.getString() gives a date format string

The code below shows how to convert a date value into date format.

#!/usr/bin/python
import datetime, uno

# .... some lines to get oCell

# cell value shown in calc -> 2010-10-12
dateValue = oCell.getValue()
# get date value from a cell with getValue() method,
# note that I did NOT used the getString() method.

print dateValue # this gives 40463.0 <- This means the elapsed date from 1899-12-30

DateValue = datetime.timedelta(dateValue) + datetime.date(1899, 12, 30) # converting
# DateValue -> datetime.date(2010, 10, 12)

print DateValue # this gives 2010-10-12.

* The starting date 1899-12-30 can be changed in option > openoffice Calc > calculate > date

Control Calc chart using python

ref: http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide/Structure_of_Charts

ref: http://www.oooforum.org/forum/viewtopic.phtml?p=56037


Legend show/hide

oChart = oSheet.Charts.getByIndex(0).EmbeddedObject
oChart.HasLegend = False

Set main title

oChart.HasMainTitle = True
oChart.Title.String = "main-title-string"

Set XAxis title

oDiagram = oChart.Diagram
oDiagram.HasXAxisTitle = True
oDiagram.XAxisTitle.String = "x-title"

Set XAxis label text rotation

oDiagram.XAxis.TextRotation = 90 * 100 # for 90 degree rotation

Set cell border

oBorderLine = createUnoStruct("com.sun.star.table.BorderLine2")
oBorderLine.InnerLineWidth = 2
oBorderLine.Color = 255 # blue

oCell.LeftBorder = oBorderLine
oCell.RightBorder = oBorderLine

Set cellRange border (apply to every cells)

oCellRange = oSheet.getCellRangeByPosition(1, 2, 3, 4)
oCellRange.BottomBorder = oBorderLine

Set cellRange TableBorder (apply to only edge lines not inner lines)

oCellRange.TableBorder -> com.sun.star.table.TableBorder

oTableBorder = createUnoStruct("com.sun.star.table.TableBorder")

# there are 2 types: BorderLine and BorderLine2. Confusing, be careful to choose.
oBorderLine = createUnoStruct("com.sun.star.table.BorderLine")
#oBorderLine2 = createUnoStruct("com.sun.star.table.BorderLine2")

# make oTableBorder object
oTableBorder.BottomLine = oBorderLine
oTableBorder.TopLine = oBorderLine
oTableBorder.LeftLine = oBorderLine
oTableBorder.RightLine = oBorderLine

# ... something like this ...
oTableBorder.IsTopLineValid = True
oTableBorder.IsBottomLineValid = True
oTableBorder.IsLeftLineValid = True
oTableBorder.IsRightLineValid = True

# set
oCellRange.TableBorder = oTableBorder
### Sometimes the code above does not work.
### Be sure that "oTableBorder.IsTopLineValid = True" line is correct.

.....

openoffice calc macro : Copy a chart to GDI meta format by python macro

def myAddChart(oDataRangeAddress, oSheet, oChartPositionCell):        
        tmpChartName = "tmpChartName"
        #--------- make a chart
        oCharts = oSheet.Charts
                
        X = oChartPositionCell.Position.X
        Y = oChartPositionCell.Position.Y
                
        oCharts.addNewByName(tmpChartName, makeRectangle( X, Y, 8000, 3500 ), Array( oDataRangeAddress ), True, True)
        oChart = oCharts.getByName(tmpChartName)
        
        #==========================================================
        #--------- Copy the Chart to GDI meta image into the position on where the chart is
        #==========================================================
        oDoc = getReady()
        oController = oDoc.getCurrentController()

        oDrawPage = oSheet.getDrawPage() 
        nNumShapes = oDrawPage.getCount() 
        
        ### select the chart. 
        ### No way for direct selecting a chart. So, get shapes and find the chart from shapes.
        for loopi in range(nNumShapes) :
                oShape = oDrawPage.getByIndex( loopi ) 
                if "CLSID" in dir(oShape) : # only charts have the CLSID attribute
                        if oShape.CLSID == "12DCAE26-281F-416F-a234-c3086127382e"  : # this is the CLSID of charts
                                oController.select( oShape ) 
                                break
                                
        # PropertyValue format=3 means the PasteSpecial format to be the GDI meta file format
        PropertyValue = uno.createUnoStruct("com.sun.star.beans.PropertyValue")
        PropertyValue.Name = "Format"
        PropertyValue.Value = 3
        
        serviceMgr = uno.getComponentContext().ServiceManager
        dp = serviceMgr.createInstance('com.sun.star.frame.DispatchHelper')
        #--- copy the selected object (chart)
        dp.executeDispatch( oController.getFrame(), ".uno:Copy", "", 0, ())
        #--- remove the selected object
        oCharts.removeByName(tmpChartName)
        #--- now select the cell where the GDI meta image will be on
        oController.select(oChartPositionCell)
        #--- paste
        dp.executeDispatch( oController.getFrame(), ".uno:PasteSpecial", "", 0, (PropertyValue,))   # <- The last (PropertyValue,) option is important.
                

..

python code snippets for openoffice calc

getReady() -> return oDoc (calc document)
gotoOffset -> similar to Range.Offset(x, y) in excel VBA
gotoLeftEnd -> Range.End(xlToLeft) in VBA for excel

import uno

def gotoOffset(oCell, coffset, roffset):
        currentColumn = oCell.getCellAddress().Column
        currentRow = oCell.getCellAddress().Row
        
        newColumn = currentColumn + coffset
        newRow = currentRow + roffset
        
        if newColumn < 0 : newColumn = 0
        if newRow < 0 : newRow = 0
        
        tmpCell = oCell.getSpreadsheet().getCellByPosition(newColumn, newRow)
        return tmpCell
        
        
def gotoRightEnd(oCell):
        tmpCell = oCell
        while True :
                tmpCell = gotoOffset(tmpCell, 1, 0)
                if tmpCell.getString() == '' :
                        tmpCell = gotoOffset(tmpCell, -1, 0)
                        break
        return tmpCell

def gotoLeftEnd(oCell):
        tmpCell = oCell
        while True :
                tmpCell = gotoOffset(tmpCell, -1, 0)
                if tmpCell.getRangeAddress().StartColumn == 0 :
                        break
                if tmpCell.getString() == '' :
                        tmpCell = gotoOffset(tmpCell, 1, 0)
                        break
        return tmpCell

def gotoTop(oCell):
        tmpCell = oCell
        while True :
                tmpCell = gotoOffset(tmpCell, 0, -1)
                if tmpCell.getRangeAddress().StartRow == 0 :
                        break
                if tmpCell.getString() == '' :
                        tmpCell = gotoOffset(tmpCell, 0, 1)
                        break
        return tmpCell

def gotoBottom(oCell):
        tmpCell = oCell
        while True :
                tmpCell = gotoOffset(tmpCell, 0, 1)
                if tmpCell.getString() == '' :
                        tmpCell = gotoOffset(tmpCell, 0, -1)
                        break
        return tmpCell

        
        
def getReady():
        localContext = uno.getComponentContext()
        resolver = localContext.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext )
        ctx = resolver.resolve( "uno:socket,host=localhost,port=8100;urp;StarOffice.ComponentContext" )
        smgr = ctx.ServiceManager
        desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
        oDoc = desktop.getCurrentComponent()
        
        return oDoc

Searching a text in openoffice calc sheet using python code

sd = oSheet.createSearchDescriptor()
sd.setSearchString("SearchString")

fc = oSheet.findFirst(sd)

while True :
       if fc == None : break
       fc.CellBackColor = 12341234
       fc = oSheet.findNext(fc, sd)

Set optimal width for all columns of a sheet in openoffice calc with python script code

oSheet.Columns.OptimalWidth = True

.

set image size in openoffice calc with python code

1. resizing selected images

def ResizingSelectedImages(height = 10000, width = 8000):
    oDoc = getReady()
    cSelection = oDoc.getCurrentSelection()
    for loopi in range(cSelection.Count) :
        tmpImage = cSelection.getByIndex(loopi)
        tmpSize = tmpImage.Size
        tmpSize.Width = width
        tmpSize.Height = height
        
        tmpImage.setSize(tmpSize)

2. Resizing the first images (getByIndex(0)) in the selected sheet.

oSheet = oDoc.getSheets().getByIndex(0)
oDraw = oSheet.getDrawPage()
oImage = oDraw.getByIndex(0)

newSize = oImage.Size
newSize.Height = 20000
newSize.Width = 20000

oImage.setSize(newSize)

select (activate) a sheet in openoffice calc by python code

import uno
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext )
# note the port number is 8100, where the default value is 2002
ctx = resolver.resolve( "uno:socket,host=localhost,port=8100;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
oDoc = desktop.getCurrentComponent()

# sheet to be activated
oSheet = oDoc.getSheets().getByIndex(2)

# activate the sheet
oController = oDoc.getCurrentController()
oController.setActiveSheet(oSheet)

# set first visible row
oController.setFirstVisibleRow(3)

openoffice start option enable python macro

openoffice calc should be run with the command

$ ooffice -calc --accept="socket,host=localhost,port=8100;urp;StarOffice.ServiceManager"

or edit /usr/bin/ooffice (in ubuntu)

$ cat /usr/bin/ooffice
#!/bin/sh
/usr/lib/openoffice/program/soffice "$@" --accept="socket,host=localhost,port=8100;urp;StarOffice.ServiceManager"
$

Openoffice calc function script giving a grade from a rank

The following function returns a degree (e.g., A+ or A or ... etc) from a rank data

Function getdegree(V)
'--------------------------------------------------
' Parameters must be set according to each class
'--------------------------------------------------

total = 31 ' total student number
Ap_percent = 15 * 0.01 ' percentage of A+ degree= 15%
A_percent = 30 * 0.01 ' accumulated percentage 0f A degree
Bp_percent = 45 * 0.01
B_percent = 60 * 0.01
Cp_percent = 85 * 0.01
C_percent = 100 * 0.01
Dp_percent = 0 * 0.01
D_percent = 0 * 0.01

'---------------------------------------------------

Ap = int(total * Ap_percent)
A = int(total * A_percent)
Bp = int(total * Bp_percent)
B = int(total * B_percent)
Cp = int(total * Cp_percent)
C = int(total * C_percent)
Dp = int(total * Dp_percent)
D = int(total * D_percent)



select case V
case 1 to Ap
getdegree = "A+"
case Ap to A
getdegree = "A"
case A to Bp
getdegree = "B+"
case Bp to B
getdegree = "B"
case B to Cp
getdegree = "C+"
case Cp to C
getdegree = "C"
case C to Dp
getdegree = "D+"
case Dp to D
getdegree = "D"
case else
getdegree = "F"
end select

End Function

How to : use VBA in openoffice calc

insert "Option VBASupport 1" at the first line of macro module.
for example,

Option VBASupport 1

sub main()

msgbox "Now testing VBA macro..."
range("A1").select
selection.value = "This is A1"
selection.offset(1, 0).select
selection.value = "Offset (1, 0) from A1"
selection.offset(1, 2).value = "Offset(1, 2) from selection"
msgbox "VBA testing's completed"

end sub

It works.
Maybe some basic & simple VBA code will do.
More complicated codes wont work I guess.