Python xlwt: preserve all styles but one

Question

I'm using xlrd and xlwt to go through certain cells and check them for certain criteria. If they meet the criteria I move on, if not, I want to color the text RED. The formatting from cell to cell changes, some have a background color, some are bold, and some are different sizes and all of these differences need to be preserved.

Is there any easy way to do this?

I can duplicate the current format of one of the cells I know fairly easily using easy_xf,

    form = xlwt.easyxf(
             'font: name Gotham Narrow Book, height 140, color red;'
             'borders: left thin, right thin, top thin, bottom thin;'
             'pattern: pattern solid, pattern_fore_colour white, pattern_back_colour white'
             )

but this of course runs into problems since not every cell has that same formatting (as I explained above, some have background colors or no border or different font styles). I looked into preserving the style with this code from another StackOverflow question:

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')

It appears as if the style is held in Cell.xf_idx, but after taking a closer look at this value, I discovered it is an integer, leaving me completely baffled as to how to extract certain attributes of the style from it so that I can change solely the font color.

As I said before, is there any easy way to accomplish this?

Answer 1

You should check out the module xlutils.styles, open the excel with formatting_info=True, and do your logic and change the relative cell style and then save the excel again.

open_workbook(excel_file_full_path, formatting_info=True)