xlsxwriter and vertical reference lines in charts

Question

I am using xlsxwriter to generate excel sheets and specifically line charts, which I've otherwise had excellent results with.

I would like to add a vertical line as a reference marker in this same line chart.

Here are two examples of this being achieved in excel:

https://peltiertech.com/Excel/Charts/AddLineVertSeries.html

http://www.storytellingwithdata.com/blog/2018/8/7/tactical-tip-embedding-a-vertical-reference-line-in-excel

I am not working with dates, but the examples are otherwise essentially the same.

In the most ideal case, I would like it to behave as such:

• Multiple vertical lines
• The X position of the vertical line I would like to be able reference by the same scale as the X-Axis (important).
• I would like to be able to control the height of the vertical line, but on a separate scale from the rest of the line chart (so, say, 0-100) (non-essential, but nice)
• I don't particularly want references to this line in the legend, but being able to place a label onto this line (say, to the side of it) would be very nice.

It seems what is being done in these examples is that the chart type just for the series is being modified to be an xy scatter type with subtype of line, no markers.

Unfortunately, in xlsxwriter, the chart type only seems to be settable per chart, and not per series (as in the example)

I have explored other ways of achieving this (such as using .combine() to combine two charts), but have run into issues with the X-Axis being referenced improperly.

I am also aware of y2_axis as being useful, but between these, I have not been able to come up with a working combination.

Any help would be greatly appreciated. I can't recommend xlsxwriter enough -- it has been excellent for everything I've otherwise tried to do with it, and recognize this is a bit of an unusual thing to do even in excel itself.

Below is an example of the code I am running, which produces something very close to what I need. The exception is that in cell D16 I use a value of "600" when I'd really love to be able to reference the X-Axis as it is (so, get the same result by using roughly 0.0735 instead of 600. Frankly, I'm not sure why 600 is what it takes to make the line appear there.

Beyond that, I'd love to get rid of the reference to "vlines" in the legend, and ideally, have a "data label" at the top of that line.

The y-scaling is not essential, but I think that part is actually easily doable.

#!/bin/python3
import xlsxwriter
import os             #lets us execute shell commands in windows (to open the created excel document)

workbook = xlsxwriter.Workbook("test.xlsx")

interval=14.7

bins=12

wk_data =workbook.add_worksheet("Raw Data")
wk_chart =workbook.add_worksheet("Chart")

wk_data.write(0, 0, "Bin")
wk_data.write(0, 1, "Int")
wk_data.write(0, 2, "Sample")
wk_data.write(0, 2, "Thing1")
wk_data.write(0, 3, "Thing2")

wk_data.write(1, 2, 2)
wk_data.write(2, 2, 35)
wk_data.write(3, 2, 486)
wk_data.write(4, 2, 36)
wk_data.write(5, 2, 3)
wk_data.write(6, 2, 2)
wk_data.write(7, 2, 3)
wk_data.write(8, 2, 47)
wk_data.write(9, 2, 237)
wk_data.write(10, 2, 50)
wk_data.write(11, 2, 13)
wk_data.write(12, 2, 2)

wk_data.write(1, 3, 2)
wk_data.write(2, 3, 5)
wk_data.write(3, 3, 3)
wk_data.write(4, 3, 6)
wk_data.write(5, 3, 35)
wk_data.write(6, 3, 89)
wk_data.write(7, 3, 523)
wk_data.write(8, 3, 90)
wk_data.write(9, 3, 23)
wk_data.write(10, 3, 4)
wk_data.write(11, 3, 2)
wk_data.write(12, 3, 4)

wk_data.write(14, 2, 6)
wk_data.write(15, 2, 6)
wk_data.write(14, 3, 0)
wk_data.write(15, 3, 600)

for bin in range(0,bins):
    wk_data.write(bin+1, 0, bin) #Column with raw bin number
    wk_data.write(bin+1, 1, interval * bin / 1000) #Column with the x-values we are interested in

data_chart = workbook.add_chart({'type': 'line'})
vline_chart = workbook.add_chart({'type': 'scatter', 'subtype': 'straight'})

data_chart.add_series({
    'name':       "thing1",
    'categories': ['Raw Data', 1, 1, 12, 1],
    'values':     ['Raw Data', 1, 2, 12, 2],
    'line': {
        'width': 0.25,
    },
})

data_chart.add_series({
    'name':       "thing2",
    'categories': ['Raw Data', 1, 1, 12, 1],
    'values':     ['Raw Data', 1, 3, 12, 3],
    'line': {
        'width': 0.25,
    },
})

vline_chart.add_series({
    'name':       "vlines",
    'categories': ['Raw Data', 14, 2, 15, 2],
    'values':     ['Raw Data', 14, 3, 15, 3],

    'line': {
        'width': 4,
    }
    })

data_chart.combine(vline_chart)

data_chart.set_title ({'name': 'Turtles'})
data_chart.set_y_axis({'name': 'Count'})
data_chart.set_x_axis({'name': 'interval'})
data_chart.set_size({ 'width': 1500, 'height': 800})

wk_chart.insert_chart('A1', data_chart, {'x_offset': 0, 'y_offset': 0})

workbook.close()
os.system('"test.xlsx"')

Answer 1

To add the vertical reference lines to the chart on a secondary axis as a scatter, you only need a small change from your posted code that I found after much research on the different doc pages.

The relevant change you need to make from your posted code is to add the 'y2_axis': True parameter to the vline_chart.add_series definition, as so:

vline_chart.add_series({
    'name':       "vlines",
    'categories': ['Raw Data', 14, 2, 15, 2],
    'values':     ['Raw Data', 14, 3, 15, 3],
    **'y2_axis': True,**
    'line': {
        'width': 4,
    }
})

The need for the y2_axis parameter, along with some example code is discussed in the documentation here