How to access OO/LBO Calc Picture objects?

[g0gcd]

I can't get my head around this. Please be patient with me...

I have a Workbook/Worksheet used as a template, created and managed in Excel 2010. The template contains all of the items that I'm likely to need in my report, including plain text, calculations, charts and images (pictures). I've cracked the creation, copying, charting and formatting elements of Open Office / Libre Office (I'll use LBO for short) but I am struggling to work out how to remove the unwanted objects.

Fortunately, I only have one chart, which I manipulate and then copy to the required position, before doing it and copying again to the next position. I use this pseudo code to access it:

$oCalc  = _OOoCalc_BookOpen($sTemplateFilename, $bHideSheetWork)

    _OOoCalc_SheetActivate($oCalc, $sMainSheetName)         ; work in the template

    ;Prepare chart for manual scaling; assumes only one chart, on active sheet
    $oSheet             = $oCalc.CurrentController.getActiveSheet()
    $oCell              = $oCalc.CurrentSelection
    $oCharts            = $oSheet.getCharts()
    $oChart             = $oCharts.getByIndex(0)        ; Index 0 finds the first (and only) chart on this sheet
    $oEmbeddedObject    = $oChart.getEmbeddedObject()
    $oDiagram           = $oEmbeddedObject.getDiagram()
    
    $oXAxis             = $oDiagram.getXAxis()
    $oXAxis.AutoMax     = False
    $oXAxis.Max         = 3000
    $oXAxis.AutoMin     = False
    $oXAxis.Min         = 0.1
    
    $oYAxis             = $oDiagram.getYAxis()
    $oYAxis.AutoMax     = False
    $oYAxis.Max         = 1
    $oYAxis.AutoMin     = False
    $oYAxis.Min         = -5

I am lucky here because there's only one chart I need to manipulate and that's at index 0. And I know which rows that chart covers, so I can copy/paste the rows, which include the chart. Copies of the chart are considered fixed.That works nicely.

However, when it comes to the images, which in the Template appear to be named Picture1, Picture2 etc. I don't know which rows they are on, or cover. I just need to delete the picture when I delete the underlying rows. Currently I use these commands:

;       Then, remove empty rows
;       Cell "Bn" is blank or contains "keep"

For $x = $iMainLastRow To 1 Step -1
If _OOoCalc_ReadCell($oCalc, "B" & $x) = "" Then _OOoCalc_RowDelete($oCalc, $x - 1)
Next

but, as shown in the attached screenshot, this deletes the rows, shuffling the data up and dragging up the unwanted images into an untidy pile at the bottom.

Is there a way to find the picture's anchor in terms of RC or A1 reference, so that I can pick off the unwanted images under some kind of control. Or a variant of _OOoCalc_RowDelete() that also deletes any objects linked to the row?

Thanks for indulging me!   (I'm so nearly there)

 

Regards

John G0GCD

 

 

 

 

 

[g0gcd]

So, this so often happens that I'm now considering writing my questions to a dummy forum. Once I'd laid the question out, I found a work around.

Instead of deleting the rows where the images are, I use this first:

_OOoCalc_RangeClearContents($oCalc, "C" & $x+1 & ":AZ" & $xEnd, -1, -1, -1, -1, 128) ;128 for objects, 1023 for all types of data)

This clears the offending pictures and now when I collapse the sheet by deleting the redundant rows, they don't pile up at the bottom of the sheet.

Someone else might try the same approach - I hope that I save them some time by sharing.

 

I'd still love to find out how I can access an array of picture objects and query them to establish their anchors and thereby identify them. That would be far safer and more reliable than my clumsy approach.

But, for now, back to programming and testing.

Regards

John

 

[donnyh13]

Hello;

For a tip on getting images, you can get a list of them from the sheet object by doing something like: 

For $i = 0 To $oSheet.DrawPage.Count() - 1
$oShape =  $oSheet.DrawPage.getByIndex($i)
If ($oShape.supportsService("com.sun.star.drawing.GraphicObjectShape") Then ConsoleWrite("yes, its an image!" & @CRLF); it could have been a shape, textbox or other, otherwise.
next

And for the anchor, try

$oShape.Anchor.AbsoluteName()

This will give you this: "$Sheet1.$A$2" if it's anchored to a cell, or this: "$Sheet1.$A$1:$XFD$1048576" if anchored to the page, which wont help much.

Edit: There is also RangeAddress Structure available too, instead of AbsoluteName.

$tAddr = $oShape.Anchor.RangeAddress()
; $tAddr.StartRow()
; $tAddr.StartColumn()
; $tAddr.EndRow()
; $tAddr.EndColumn()
; $tAddr.Sheet()

 

Edited January 14 by donnyh13