Conditionally formatting text strings from Excel using python -

i format spreadsheet (xls or xlsx) cells containing word or ending string formatted filling background specific color.

for example, if cell contains word 'deleted', fill black , paint text white. if cell ends '.pf', paint cell red.

i found similar question several years ago suggested following:

import xlrd  import xlutils.copy   inbook = xlrd.open_workbook('input.xls', formatting_info=true)  outbook = xlutils.copy.copy(inbook)   def _getoutcell(outsheet, colindex, rowindex):      """ hack: extract internal xlwt cell representation. """      row = outsheet._worksheet__rows.get(rowindex)      if not row: return none      cell = row._row__cells.get(colindex)      return cell   def setoutcell(outsheet, col, row, value):      """ change cell value without changing formatting. """      # hack retain cell style.      previouscell = _getoutcell(outsheet, col, row)      # end hack, part      outsheet.write(row, col, value)      # hack, part ii       if previouscell:          newcell = _getoutcell(outsheet, col, row)      if newcell:         newcell.xf_idx = previouscell.xf_idx      # end hack    outsheet = outbook.get_sheet(0)  setoutcell(outsheet, 5, 5, 'test')  outbook.save('output.xls')  

while copy values input.xls output.xls, not seem transfer formatting (the test values input.xls no longer formatted when opening output.xls, nor conditional formatting rules present under "manage rules" in excel.

"if" statements number values seem work, again, looking way format cells containing strings. thanks!

preserve original input.xls formatting when open it:

from xlrd import open_workbook  input_wb = open_workbook('input.xls', formatting_info=true) 

create new workbook based on template:

from xlutils.copy import copy copy_workbook  output_wb = copy_workbook(input_wb) 

define new cell styles:

from xlwt import easyxf  red_background = easyxf("pattern: pattern solid, fore_color red;") black_with_white_font = easyxf('pattern: pattern solid, fore_color black; font: color-index white, bold on;") 

evaluate , modify cells:

input_ws = input_wb.sheet_by_name('stackoverflow') output_ws = output_wb.get_sheet(0)  rindex in range(0, input_ws.nrows):    cindex in range(0, input_ws.ncols):        input_cell = input_ws.cell(rindex, cindex)        if input_cell.value[ input_cell.value.rfind('.'): ] == 'pf':            output_ws.write(rindex, cindex, input_cell.value, red_background)        elif input_cell.value.find('deleted') >= 0:            output_ws.write(rindex, cindex, input_cell.value, black_with_white_font)        else:            pass  # don't need modify 

save new workbook

output_wb.save('output.xls') 

---

using above example, unmodified cells should have original formatting intact.

should need alter cell content , preserve original formatting (i.e. not use custom easyxf instance), may use snippet:

def changecell(worksheet, row, col, text):     """ changes worksheet cell text while preserving formatting """     # adapted https://stackoverflow.com/a/7686555/1545769     previouscell = worksheet._worksheet__rows.get(row)._row__cells.get(col)     worksheet.write(row, col, text)     newcell = worksheet._worksheet__rows.get(row)._row__cells.get(col)     newcell.xf_idx = previouscell.xf_idx  # ...  changecell(worksheet_instance, 155, 2, "new value") 

---

for comparisons, can use string methods find , rfind (which searches right). return index of position of substring within string. return -1 if substring not found. ergo, see above input_cell.value.find('deleted') >= 0 evaluate whether or not substring 'deleted' exists. .pf comparison, used rfind in python called slicing.