Retrieve error-bar values from a chart in a spreadsheet

[e.tews]

Hi

I have a chart in an spreadsheet document which contains error-bars. I would like to retrieve the data for those error-bars from a macro or just in general from the API. From the documentation, I assume that those values should be part of the DataSequences of the corresponding diagram. However they only contain the labels and the y-values, but no data for the error-bars.

I have written a minimalistic python script to illustrate the issue. The corresponding files can be found at:

• Spreadsheet
• Python Script

The output I get from that script is:

range: (com.sun.star.table.CellRangeAddress){ Sheet = (short)0x0, StartColumn = (long)0x0, StartRow = (long)0x0, EndColumn = (long)0x2, EndRow = (long)0x1 }
Title: A test for error-bars
role: categories, data: ('First', 'Second')
role: values-y, data: ('30', '34')

Here is the full script:

#!/usr/bin/env python3

# Corresponding document is at: https://cloud.datenzone.de/seafile/f/0c4e2d9acfad452190f4/?dl=1

# start using: soffice --accept="socket,host=localhost,port=2002;urp;" --norestore --nologo --nodefault --headless
import uno
import os


# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
# create the UnoUrlResolver
resolver = 	localContext.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext)
# connect to the running office
ctx = resolver.resolve("uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext")
smgr = ctx.ServiceManager
# get the central desktop object
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
url = uno.systemPathToFileUrl(os.path.abspath("errorbartest.ods"))
document = desktop.loadComponentFromURL(url, '_blank', 0, ())

# Here it gets interesting
sheets = document.getSheets()
sheetNames = sheets.getElementNames()
for i in sheetNames:
    # There should be only one sheet
	currentSheet = sheets.getByName(i)
	charts = currentSheet.getCharts()
	for c in charts:
        # There should be only one chart
		ranges = c.getRanges()
		for r in ranges:
			print("range: " + str(r))
		o = c.getEmbeddedObject()
		title = o.getTitle()
		print("Title: " + str(title.String))
		sequences = o.getDataSequences()
		for seq in sequences:
			values = seq.getValues()
			print("role: " + str(values.Role) + ", data: " + str(seq.getValues().getTextualData()))

'''
Current output:
range: (com.sun.star.table.CellRangeAddress){ Sheet = (short)0x0, StartColumn = (long)0x0, StartRow = (long)0x0, EndColumn = (long)0x2, EndRow = (long)0x1 }
Title: A test for error-bars
role: categories, data: ('First', 'Second')
role: values-y, data: ('30', '34')
'''

Does anyone know whether this is maybe a bug in LibreOffice or whether my assumption that the error-bar values should be part of the DataSequences is just wrong?

Erik

[Ratslinger]

Hello @e.tews,

The error-bar values are NOT part of the data sequences. You cannot get the values directly but you can obtain the cell ranges. They are in two locations: when getting chart, instead of Ranges, it is part of ‘RelatedCellRanges’; and in EmbeddedObject in ‘UsedRangeRepresentations’. Each of these includes the location of Categories, then for each series is the location of ‘Range Name’, ‘Y-Values’, ‘Y Error Bar Positive Values’ and ‘Y Error Bar Negative Values’.

Don’t see direct access to the actual values.