title: Conditionally formatting text strings from Excel using python
body: <p>I would like to format a spreadsheet (xls or xlsx) so that any cells containing a word or ending with a certain string are formatted by filling the background with a specific color.</p>
  
  <p>For example, if the cell contains the word 'deleted', fill it black and paint the text white.
  if the cell ends with '.pf', paint the cell red.</p>
  
  <p>I found a similar question from several years ago that suggested the following:</p>
  
  <pre><code>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 the 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 to retain cell style. 
      previousCell = _getOutCell(outSheet, col, row) 
      # END HACK, PART I 
      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') 
  </code></pre>
  
  <p>While this does copy the values from input.xls to output.xls, this does not seem to transfer the formatting (the test values from input.xls are no longer formatted when opening output.xls, nor are the conditional formatting rules present under "manage rules" in excel.</p>
  
  <p>"if" statements for number values seem to work, but again, I am looking for a way to format cells containing certain strings. thanks! </p>