[Solved] [Python] Changing chart range

[kiloran]

I have a very simple LibreOffice sheet with a chart...... data is in columns A and B. The number of rows of data is variable and set by a Python script. The chart for this data is a simple bar chart.

In Basic, I can change the chart range with the following:

Code: Select all

Sub sector_chart
	DialogLibraries.loadLibrary("Standard")

	oSheets = ThisComponent.Sheets
	sectorSheet = oSheets.getByName("Sheet1")

	chartCollection = sectorSheet.getCharts() 

	Dim newrange as new com.sun.star.table.CellRangeAddress 
	Dim oAddresses(0) as new com.sun.star.table.CellRangeAddress 

	' create a cellrange 
	newrange.sheet= 0
	newrange.startcolumn = 0
	newrange.endcolumn = 1 
	newrange.startrow = 0
	newrange.endrow = 4
	
	oAddresses(0)=newrange  ' add cell range to array 
	chartCollection(0).setranges(oAddresses()) 'set the cell range for the chart 
	
End Sub

I need to do this in Python. I tried the following

Code: Select all

import uno, unohelper
import sys

ctx = uno.getComponentContext()
oDoc = XSCRIPTCONTEXT.getDocument()
oSheets = oDoc.getSheets()

def Start():
    sectorSheet =  oSheets.getByName('Sheet1')
    
    try:
        # Set chart range
        chartCollection = sectorSheet.getCharts() 
        oAddresses = []
        newrange = uno.createUnoStruct('com.sun.star.table.CellRangeAddress') 
        oAddresses = uno.createUnoStruct('com.sun.star.table.CellRangeAddress')

        # create a cellrange 
        newrange.Sheet= 0
        newrange.StartColumn = 0
        newrange.EndColumn = 1 
        newrange.StartRow = 0 
        newrange.EndRow = 4
    
        oAddresses[0] = newrange
        chartCollection[0].setRanges(oAddresses) 
    except Exception as e:
        with open('C:/Users/kiloran/Downloads/pydump.txt','a') as logfile:
            logfile.write('Error on line: ' + str(sys.exc_info()[-1].tb_lineno) + " " + str(type(e)) + " " + str(e) + "\n")
    
g_exportedScripts = Start,

I get the error <class 'TypeError'> 'com.sun.star.table.CellRangeAddress' object does not support item assignment
This error occurs on the line: oAddresses[0] = newrange

I've tried all sorts of variations such as oAddresses = newRange but still get errors. Clearly my aged brain is lacking the required Python skills.

Can anyone point me in the right direction?

[Villeroy]

It would be possible to automatially adjust the data range without a single line of silly coding IF you would pull data from a data source. A data source CAN be a Calc sheet, although I would nor recommend Calc as a data source.
Why fumbling with spreadsheets anyway? As a Python programmer you have the whole world of professional data analysis at your fingertips.

[kiloran]

Thanks but that does not really solve my problem. The problem is part of a much bigger Calc document which is used by maybe 100 people, who need the data in spreadsheet format.
I know that Python can do almost anything. All I need is a bit of guidance why my inadequate python skills cannot implement what is clearly possible in Basic

[Villeroy]

Any professional solution would certainly involve a database server. From a database server hundreds of people can read and write even simultaniously and we would not have to write that code.
If the Basic code works for you and you are not a Python programmer, why don't you use the Basic code?

[Villeroy]

Code: Select all

oAddresses.append(newrange)

[kiloran]

Many thanks for your efforts, Villeroy, but that was one of the alternatives I had tried without success.
Changing the code to oAddresses.append(newrange) results in the error on that line Error on line: 31 <class 'AttributeError'> append

I appreciate that you are trying to help with your other comments, but if I have a punctured tyre on my bike, it doesn't help to be told that I should use a Rolls Royce instead

Since the Basic code works (and I have good reasons to prefer to implement in python), there just HAS to be a solution to the Python problem. I'm convinced it's just due to my insufficient python skills

[Villeroy]

Debug your code. It is faulty. For instance, you can not append an array of range addresses to a collection of charts.

[Villeroy]

This works

[kiloran]

This works

Brilliant!
Many thanks Villeroy. Not only have you pointed me in the right direction, you've provided a working solution and educated me in the process.

It's much appreciated