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"
$