How to define last row in xlsxwriter while using add_chart

user_9293918 0
Viewed 1399

I running a script to take rows and columns from a worksheet and use it to make charts. The problem that I am running into is that since the rows in the MSEXCEL sheet are changing, I want to add the last row in the add_series argument so that it will always take the limit of the chart data from row 2 till the last row.

This is the code that I am using currently (that I want to change):

chart1 = workbook.add_chart({'type': 'column'})
chart1.add_series({
    'values': "='Sheet1'!$B$2:$B$126",
    'categories': "='Sheet1'!$A$2:$A$126",
    'data_labels': {'value': False, 'categories': False, 'series': False}
})

I have modified the above code to the following, but now the charts are not getting any data whatsoever.

chart1 = workbook.add_chart({'type': 'column'})
chart1.add_series({
    'values': "='Sheet1'!$B$2:($B$ + str(last_row_number + 1)",
    'categories': "='Sheet1'!$A$2:($A$ + str(last_row_number + 1)",
    'data_labels': {'value': False, 'categories': True, 'series': False}

last_row_number in this case is a variable [<type 'int'>] calculated by enumerating through the worksheet column.

Thanks in advance.

xlsxwriter python-2.7
1 Answers

Almost every interface in XlsxWriter supports (row, col) notation as well as A1 range notation.

In the case of add_series() you can use lists like [sheetname, first_row, first_col, last_row, last_col]:

chart.add_series({
    'categories': ['Sheet1', 1, 0, 125, 0],
    'values':     ['Sheet1', 1, 1, 125, 1],
    # ...
})

# Or:

last_row = 125

chart.add_series({
    'categories': ['Sheet1', 1, 0, last_row, 0],
    'values':     ['Sheet1', 1, 1, last_row, 1],
    # ...
})

See the docs for add_series().

user_102380
Related