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?

hello, this was posted in 2013 but do you still have the code to do this by any chance? — grymlin, Feb 28 '23 at 12:38

score 1 · Accepted Answer · edited Dec 03 '18 at 16:36

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)

edited Dec 03 '18 at 16:36

nmpeterson

49
1
10

answered Aug 18 '13 at 05:50

Erxin

1,786
4
19
33

Hello, I have a hard time seeing how this could be done, can you explain some more please? – grymlin Feb 28 '23 at 12:39
Which kinds of problem do you encounter? Suggest to check the official document of xlutils.styles. There are plenty of examples for helping you. If you still can not find out the solution please let me know. Thx. – Erxin Mar 01 '23 at 03:09
thank you for your answer, I have resolved my issue, but i am curious still as to how one might change a style (for example alignement) while keeping the rest all the same (just like in the question), I have checked the documentation but to no avail, would you have an example? – grymlin Mar 02 '23 at 10:52
You can use the xlwt write method to change a single cell style. You can also use the method easy_xf to configure style object and use with Worksheet.write method. The detail can be checked from the xlwt document. – Erxin Mar 03 '23 at 05:27
@grymlin BTW. Now there is another well developed excel manipulation library https://openpyxl.readthedocs.io/en/stable/styles.html. You could check it out. – Erxin Mar 15 '23 at 02:15

Python xlwt: preserve all styles but one

1 Answers1