Xlsxwriter, formatting just some cells, not whole row or whole column?

user_2831637 0
Viewed 3407

I have a dataframe that I am sending to Excel using the xlsxwriter engine, then applying formatting to a sheet before closing it. How can I only set a bg color for the columns that have data in them?

For example, I am able to add a yellow background color to the second column (below), but it extends past the cells that have data in them. I am doing this with set_column() as below:

worksheet.set_column(1, 1, 18, hilite_format)

I don't see an option to set column format for only certain rows, or to use set_row() to only format certain columns. I've already written the data to the worksheet, so I can't use worksheet.write() or it will overwrite the data that is already in there. At least I don't see any way to use .write() to just apply the formatting without writing data or None into the cells.

Is there a way to just 'paint' some formatting over a range of cells without affecting the values in those cells? I would prefer not to have to use conditional formatting or to re-write the data for these cells just to be able to get the formatting right.

Thank!

enter image description here

xlsxwriter formatting python
2 Answers

Try using the conditional_format(), highlighting the cells that are not blank:

worksheet.conditional_format('B:B', {'type': 'no_blanks',
                                     'format': hilite_format})
user_106420350

Try this code :

  • You put your logic here and use whenever you want in cell.

    merge_format = workbook.add_format({
        'border': 1,
        'align': 'center',
        'valign': 'vcenter'})
    


    worksheet.merge_range('A1:R1', 'AGENCIES', merge_format)

user_91172040
Related