How to add error bars to a chart using a macro

English
, , ,
#1

Pretty much the title. Using LibreOffice Calc 7.5.5.2 on Linux. I’ve created a bar chart in a spreadsheet, and I would like to add error bars using data from the sheet. At the moment this is basically what I’ve got:

' Create charts
	Charts = Doc.Sheets(4+I).Charts
	
	Rect1.X = 0
	Rect1.Y = 9931
	Rect1.Width = 10160
	Rect1.Height = 10160
	
	RangeAddress1(0).Sheet = 4+I
	RangeAddress1(0).StartColumn = 0
	RangeAddress1(0).StartRow = 39
	RangeAddress1(0).EndColumn = 1
	RangeAddress1(0).EndRow = 41

	Charts.addNewByName(Cell.String & "_1", Rect1, RangeAddress1(), False, True)
	Chart1 = Charts.getByName(Cell.String & "_1").EmbeddedObject
	Chart1.HasMainTitle = True
	Chart1.Title.String = "Control vs ARAC"
	Chart1.HasLegend = False
	Chart1.Diagram.HasYAxisTitle = True
	Chart1.Diagram.YAxisTitle.String = "Fold Difference"
' Add in error bars; HOW TF DOES THIS WORK
	Chart1.setPropertyValue(ErrorBarRangePositive, "D40:D42")
	Chart1.setPropertyValue(ErrorBarRangeNegative, "C40:C42")
	Chart1.setPropertyValue(ErrorBarStyle, com.sun.star.chart.ErrorBarStyle.FROM_DATA)

The last three lines are the part where I’m running into trouble. It’s not causing any errors, but there is no sign of any effect whatsoever when I add/remove those lines. Clearly I’m missing something here, but I’ve been staring at this for 2 days and I’m totally stumped. If anyone has any insights into what I’m doing wrong, it would be greatly appreciated

#3

Did you already inspect/analyze a chart where you had created the error bars via the UI?
It may show how the respective properties need to be set.
Try something like (my testcase used here)

chartModel.Diagram.ErrorBarRangePositive="Sheet2.D2:Sheet2.D22"

and don’t omit the sheet address.
You may need to refresh the diagram explicitly after a change.

1 Like
#4

Thanks for the help, this has gotten me much closer! I’m now using the following:

' Add in error bars; HOW TF DOES THIS WORK
	Chart1.Diagram.ErrorBarRangeNegative = Cell.String & ".C40:" & Cell.String & ".C42"
	Chart1.Diagram.ErrorBarRangePositive = Cell.String & ".D40:" & Cell.String & ".D42"
	Chart1.Diagram.ErrorIndicator = com.sun.star.chart.ChartErrorIndicatorType.TOP_AND_BOTTOM
	Chart1.Diagram.ErrorBarStyle = com.sun.star.chart.ErrorBarStyle.FROM_DATA

and it very nearly works, but I’m running into another weird problem. For some reason, only the first error bar range that I define is working (i.e. the above code results in only negative error bars, and if I swap the top two lines then it will only have positive error bars). This has really got me scratching my head now, but it’s definitely much better than it was before!

#5

This example fully works:
disask92334ErrorBarTampering.ods (43.5 KB)
But so far it only cares for the positive errors.
Completion obvious.

#6

Still pretty stuck, so I’m gonna upload a copy of what I’m working on including some example data so people can take a look if so inclined. Here’s the sheet/macro I’m running into trouble with:
Macro Test.ods (57.2 KB)
I added in some msgboxes to see if the properties are being set correctly, and it looks like the properties for both error bar ranges are being set without issue, but for some reason only the first one is actually used for the chart. At this point I honestly have no clue if this is an intended behavior I’m not understanding, or if I’m just running into a bug in libreoffice BASIC. Whatever the cause is, it’s certainly causing me quite a headache, but I’m confident there must be some solution or workaround out there.

#7

I will attach a completed example. There you can choose two alternative pairs of ranges, each giving first the range for the negative error bars then the range for the positive error bars.
Concerning your own attempts: The chart component was bloated a lot, and I wouldn’t try to create any chart “from zero”, but always use the wizard to create a kind of template for the chart type I want to get, then study it, and finally adapt it to my case by user code.
Anyway I don’t see a good reason to study and comment your code as long as you didn’t study mine. In consequence of my italicized remark above, I also am not eager to study any code trying to create a chart without the help of the wizard. Too confusing.
disask92334ErrorBarTamperingEnhanced.ods (39.6 KB)

#8

No clue so far. See my new example. It works for both sides.

#9

An afterthought: Don’t use the wrong sign for the errors. chartModel.Diagram.ErrorBarRangeNegative expects a range with positive values for downward errors. Negative values in that range are interpreted upwards for the chart.
(I can’t test with your example.)

#10

I’ve tried to redesign things to use a premade chart on the template sheet, and this does make the error bars work as intended, but the data series displayed in the chart stays attached to the original sheet rather than adjusting to the copy it is a part of. This wouldn’t be a big deal but I can’t seem to find any clear way to change the cell range of that data series, so now I’m stuck with a chart that has error bars, but no data. I’m sure there must be a way to adjust where a chart is getting its data from, but the documentation regarding charts is thoroughly brain-melting and I can’t seem to find anything that works.
IMO it feels cleaner to create the charts from scratch in code, but at this point whichever method I can get working first is gonna have to be the move since apparently it’s a bit of a nightmare either way.

Here are a couple pages that seemed promising, but I have yet to successfully apply any of this and I honestly couldn’t say with any confidence whether it’s relevant:
https://api.libreoffice.org/docs/idl/ref/structcom_1_1sun_1_1star_1_1chart_1_1ChartSeriesAddress.html
https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1chart_1_1ChartDataArray.html

Sorry to keep coming back with more questions, but it seems like every step forward here leads to a new and even more confusing problem. I swear I’m normally a much less annoying asker, but apparently charts in libreoffice basic are my kryptonite. I have poked around at the code you uploaded; just haven’t found anything worth noting here yet. I shall definitely update if I make any more useful progress, and thanks a ton for your help so far!

#11

Sorry. I’m not in the mood to study pages you found promising, but…
I’m afraid, user programming for Charts without profound knowlege about the implementation of charts won’t lead anywhere.
I was curious, and tried a solution, but even one that works a first time can’t be expected to work the next time again.
There is not a 1:1 relation between the chart as it is shown and its API-representation. The “chart-code” may collect garbage or change an internal structure for other reasons. You thus can’t know if a part of a structure (or an index into a sequence) next time will be usable for the “same purpose”. And there is no unambiguous naming for the API insofar.
Let it be.
There may be a good reason for the fact that nobody else stepped in here.

#12

I finally managed to get my charts working, although it’s still kinda sloppy and I have a more complex chart that I honestly don’t even know how to approach. A word of warning: if you have a similar issue, I recommend turning back now before it’s too late. You’ll waste less time just making the charts manually lol. That being said, here’s my creation with some example data pre-loaded so all you have to do is hit the big green button:

Macro Test Done(ish).ods (55.7 KB)

In summary, I ended up using premade charts on the templates since the error bars just refused to work properly on charts made using code. To set the data range I used the DataProvider’s createDataSource() method, and attached it using the chart diagram’s setDiagramData()

I’m setting this as the solution because I did technically fix the subject of my original post, but it honestly feels like a pretty shaky solution, and I imagine there must be a more elegant and/or robust way of doing this. Even if there is, I certainly won’t be the one to find it, and with any luck I’ll never make another chart for the rest of my life.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.