[Solved] [Python] Changing chart range

Keyboard macros or custom scripts

[Solved] [Python] Changing chart range

Postby kiloran » Mon Jun 22, 2020 12:56 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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?
Last edited by Hagar Delest on Wed Jun 24, 2020 6:08 pm, edited 1 time in total.
Reason: tagged solved
--kiloran
LibreOffice 6 on Windows 10 and Linux Mint
kiloran
 
Posts: 34
Joined: Sun Oct 14, 2012 2:06 pm

Re: [Python] Changing chart range

Postby Villeroy » Mon Jun 22, 2020 1:40 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29707
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Python] Changing chart range

Postby kiloran » Mon Jun 22, 2020 1:48 pm

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
--kiloran
LibreOffice 6 on Windows 10 and Linux Mint
kiloran
 
Posts: 34
Joined: Sun Oct 14, 2012 2:06 pm

Re: [Python] Changing chart range

Postby Villeroy » Mon Jun 22, 2020 2:03 pm

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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29707
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Python] Changing chart range

Postby Villeroy » Mon Jun 22, 2020 8:03 pm

Code: Select all   Expand viewCollapse view
oAddresses.append(newrange)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29707
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Python] Changing chart range

Postby kiloran » Mon Jun 22, 2020 9:07 pm

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
--kiloran
LibreOffice 6 on Windows 10 and Linux Mint
kiloran
 
Posts: 34
Joined: Sun Oct 14, 2012 2:06 pm

Re: [Python] Changing chart range

Postby Villeroy » Mon Jun 22, 2020 9:38 pm

Debug your code. It is faulty. For instance, you can not append an array of range addresses to a collection of charts.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29707
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Python] Changing chart range

Postby Villeroy » Tue Jun 23, 2020 12:20 am

This works
Attachments
ChartRange.ods
(16.97 KiB) Downloaded 126 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29707
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved][Python] Changing chart range

Postby kiloran » Tue Jun 23, 2020 11:33 am

Villeroy wrote: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
--kiloran
LibreOffice 6 on Windows 10 and Linux Mint
kiloran
 
Posts: 34
Joined: Sun Oct 14, 2012 2:06 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests