Make data range dynamic in xlsxwriter add_series in Python?

user_15171387 0
Viewed 517

I have creates the following function to insert a chart into a excel sheet. However, the ranges here are constant (e.g., !$C$1:$CT$1"). My question is how can I change the range to be dynamic?

import xlsxwriter

outputs = xlsxwriter.Workbook(output_path)
def draw_charts(sheet_name, data_sheet):
    """Draw the excel charts."""

    # Create a new chart object.
    chart = outputs.add_chart({"type": "column", "subtype": "stacked"})

    # create a worksheet that only holds a chart
     chartsheet = outputs.add_chartsheet(sheet_name)

    # Add a series to the chart.
    chart.add_series(
    {
      "name": "=" + data_sheet + "!$A$2:$B$2",
      "categories": "=" + data_sheet + "!$C$1:$CT$1",
      "values": "==" + data_sheet + "!$C$2:$CT$2",
     }
     )
      
     # Insert the chart into the worksheet.
     chartsheet.set_chart(chart)
xlsxwriter python
2 Answers

The position is controlled by the string you put.

To make it dynamic:

my_index = 10
"name": "=" + data_sheet + "!$A$2:$B$%d" %my_index,

Now you can use my_index to control it.

user_128384030

Throughout the XlsxWriter you can use (row, col) or (row1, col1, row2, col2) notation instead of range notation like A1 or A1:C5.

For the add_series() method you can add a list of values and then set the row/col numbers programatically, like this:

chart.add_series({
    'name':       ['Sheet1', 0, 2],
    'categories': ['Sheet1', 1, 0, 6, 0],
    'values':     ['Sheet1', 1, 2, 6, 2],
})

See the docs for the add_series() method.

user_102380
Related