Search code examples
pythonobjectchartspositionxlsxwriter

Create chart using xlsxwriter in Excel that does not move or size with cells in Python

Goal

I want to create a chart using xlsxwriter in Python that does not move or size with cells by having xlsxwriter select the option for "Don't move or size with cells" when inserting the chart into the worksheet.

The option for this in Excel is under: Format Chart Area Chart Options Size & Properties Properties Don't move or size with cells

By default when a chart is created with xlsxwriter the option "Move and size with cells" is selected.

However, this becomes problematic when filtering data. The chart will disappear or resize if the data it is on top of is hidden or filtered.

Help Docs

I read the help docs that show how to do this exact thing on images and they indicate that the same applies to charts. However, I have not been able to get it to work. Help doc link: https://xlsxwriter.readthedocs.io/working_with_object_positioning.html

Minimal reproducible code

chart = workbook.add_chart({'type': 'line'})

chart.add_series({

    'name':       [sheet_name, name_row, name_col],

    'categories': [sheet_name, first_row, x_data_col, max_row, x_data_col],

    'values':     [sheet_name, first_row, col, max_row, col],
})


worksheet.insert_chart(position, chart, {'object_position': 3})

Problem

The charts are still being created properly but the object_position is not being set to 3. For some reason, the charts that are being created are still able to move and size with cells, which corresponds with object_position 1. Am I attempting to set the object_position parameter incorrectly?

Solution

  • You need to make sure that you are using XlsxWriter version >= 1.1.7, when this feature was added.

    After that it should work:

    import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()

chart = workbook.add_chart({'type': 'line'})

worksheet.write_column('A1', [2, 4, 6, 8, 10])

chart.add_series({'values': '=Sheet1!$A$1:$A$5'})

worksheet.insert_chart('A7', chart, {'object_position': 3})

workbook.close()

    Output is option 3 "Don’t move or size with cells":

    enter image description here