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.

.....

ubuntu nautilus dirs config

$ cat ~/.config/user-dirs.dirs
# This file is written by xdg-user-dirs-update
# If you want to change or add directories, just edit the line you're
# interested in. All local changes will be retained on the next run
# Format is XDG_xxx_DIR="$HOME/yyy", where yyy is a shell-escaped
# homedir-relative path, or XDG_xxx_DIR="/yyy", where /yyy is an
# absolute path. No other format is supported.
#
XDG_DESKTOP_DIR="$HOME/바탕화면"
XDG_DOWNLOAD_DIR="$HOME/다운로드"
XDG_TEMPLATES_DIR="$HOME/서식"
XDG_PUBLICSHARE_DIR="$HOME/공개"
XDG_DOCUMENTS_DIR="$HOME/문서"
XDG_MUSIC_DIR="$HOME/음악"
XDG_PICTURES_DIR="$HOME/"
XDG_VIDEOS_DIR="$HOME/"

python encoding error solution

/usr/lib/python2.6/site.py

find 'ascii' and change to 'utf-8'

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