XlsxWriter set_column limit rows

Question

I have a pandas dataframe that I'm writing to a excel file with XlsxWriter. I'm setting the cell format with

worksheet.set_column(first_index, last_index, None, cell_format)

On a few of my columns. By doing this however not only the cells with values in my excel file gets the format applied, but seemingly infinite rows get the cell format applied.

How can I limit the cell format to a set of rows?

Answer 1

You can use conditional formatting:

worksheet.conditional_format(first_index, last_index, {'type': 'no_blanks',
                                                       'format': cell_format})

This works if the other rows are blank. I don't know if that's the case for you.

Answer 2

By doing this however not only the cells with values in my excel file gets the format applied, but seemingly infinite rows get the cell format applied.

That is how column formatting works in Excel.

How can I limit the cell format to a set of rows?

You can set row formatting with the set_row() method but from the overall question it sounds like you want to limit the formatting to a range of cells.

The only way to do that in Excel, or XlsxWriter, is to format the cells individually (apart from solutions like using conditional formatting or worksheet tables that can be applied to a range).

In order to do that with a dataframe you would need to avoid df.to_excel() and write the data cell by cell using XlsxWriter methods.