CODEWITHSUNDEEP
pythonexcelxlsxwriter
Ivan Bilan
Ivan Bilan

Reputation: 2439

How to use color scale to the whole column in xlsxwriter

According to this example (http://xlsxwriter.readthedocs.io/working_with_conditional_formats.html) you can use color scales on columns.

The example from the docs:

caption = 'Examples of color scales and data bars. Default colors.'

data = range(1, 13)
worksheet7.write('A1', caption)
worksheet7.write('B2', "3 Color Scale")

for row, row_data in enumerate(data):
    worksheet7.write(row + 2, 1, row_data)

worksheet7.conditional_format('B3:B14', {'type': '3_color_scale'})

You can see that the formatting is applied to a certain B column range of 1 to 14. I want to apply the format to the whole B column regardless of it's length.

When I do this:

caption = 'Examples of color scales and data bars. Default colors.'

 data = range(1, 13)
 worksheet7.write('A1', caption)
 worksheet7.write('B2', "3 Color Scale")

 for row, row_data in enumerate(data):
     worksheet7.write(row + 2, 1, row_data)

 worksheet7.conditional_format('B:B', {'type': '3_color_scale'})
 # or this worksheet7.conditional_format('$B:$B', {'type': '3_color_scale'})

I get the following error: 

File "build\bdist.win-amd64\egg\xlsxwriter\worksheet.py", line 85, in cell_wrapper
  File "build\bdist.win-amd64\egg\xlsxwriter\utility.py", line 108, in xl_cell_to_rowcol
AttributeError: 'NoneType' object has no attribute 'group'

What is the proper way to do this?

Upvotes: 2

Views: 2554

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

In general in XlsxWriter you can set a 2D range to the maximum column values to get a Col:Col style range. This is also (generally) what happens internally in Excel.

So the following should work in your case:

worksheet7.conditional_format('B1:B1048576', {'type': '3_color_scale'})

# Or with row, col notation:
worksheet7.conditional_format(0, 1, 1048575, 1, {'type': '2_color_scale'})

Within Excel the range will be displayed as B:B.

Upvotes: 2

Related Questions