Odtnhj

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!

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

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

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)