Creating a chart in python 3.5 using XlsxWriter where series values are based on a loop
<p>I'm using Python 3.5 on Windows 10. Hopefully I can articulate what I'm trying to accomplish while permitting as little confusion as possible... </p>

<p>I've written a Python script that performs several tasks, some of which include creating an Excel workbook via XlsxWriter based on data generated from code in the earlier portions of the script. I'm attempting to have my script also create a chart, once again using XlsxWriter, based on this data. I've seen several examples available online and, while useful, there is one specific difference between the examples and my own personal code that make me unsure as to how I could proceed. </p>

<p>My issue arises when trying to add a series to a chart. When configuring a series, the few examples that I've come across include something like this (note: I'm not actually using this exact code):</p>

<pre><code>chart.add_series({
'name': '=Sheet1!$A$2:$A$7 '
})
</code></pre>

<p>Because of the nature of my script which involves looping and variable lengths, the data generated could be populated throughout various columns and rows, so I can't assign something like 'name' a fixed reference like 'Sheet1!$A$2:$A$7' because although all the data generated from my script will exist on the same sheet, it will never consistently be only in column 'A' and only between cells 2 through 7. </p>

<p>So, how can I get around this? Again, due to variable items and such, the way I've told XlsxWriter to populate cells is by creating variables <code>row_1 = 0</code> and <code>col_1 = 0</code> and incrementing them as necessary. I am able to write something like </p>

<pre><code>chart.add_series({
'name': '=Sheet1!row:col+7'
})
</code></pre>

<p>through XlsxWriter? </p>

<p><strong>EDIT</strong>: So I just found out I can use alternative indexing which appears to be my workaround to adding a series to the chart. However, I get the following error message: </p>

<pre><code>UserWarning: Must specify 'values' in add_series()
warn("Must specify 'values' in add_series()")
</code></pre>

<p>Based on this bit of code:</p>

<pre><code>chart.add_series({
'Subscribers': ['Sheet1', row_1 + 2, col_1, 
               2 + len(sb_subCount_list_clean), col_1] 
})
</code></pre>

<p>Is this because I'm using variables as my indexes? sb_subCount_list_clean is a list that contains the data I'm using to create the chart. The column will be of this length plus 2 because I have some headers occupying the first two cells. </p>

<p>In almost all parts of the XlsxWriter API, anywhere there is a Cell reference like <code>A1</code> or a range like <code>=Sheet1!$A$1</code> you can use a tuple or a list of values. For charts you can use a list of values like this:</p>

<pre><code># String interface. This is good for static ranges.
chart.add_series({
    'name':       '=Sheet1!$A$1',
    'categories': '=Sheet1!$B$1:$B$5',
    'values':     '=Sheet1!$C$1:$C$5',
})

# Or using a list of values instead of name/category/value formulas:
#     [sheetname, first_row, first_col, last_row, last_col]
# This is better for generating programmatically.
chart.add_series({
    'name':       ['Sheet1', 0, 0      ],
    'categories': ['Sheet1', 0, 1, 4, 1],
    'values':     ['Sheet1', 0, 2, 4, 2],
})
</code></pre>

<p>Refer to the <a href="https://xlsxwriter.readthedocs.io/chart.html#chart-class" rel="nofollow">docs</a>.</p>

<p>As for the error you are getting (after the edit): <code>Subscribers</code> isn't a XlsxWriter parameter, you probably mean<code>values</code>:</p>

<pre><code> chart.add_series({
      'values': ['Sheet1', row_1 + 2, col_1, 
                 2 + len(sb_subCount_list_clean), col_1] 
 })
</code></pre>

python excel graph charts xlsxwriter