xlrd - Format individual characters in a single Excel cell with python

Question

I am using xlrd, xlwt, and xlutils on the Windows Vista OS with Python 2.7. I have a set of DNA sequences in an excel worksheet that are 100 characters long, with each sequence in a single cell. I am trying to highlight characters at specific positions within each of these sequences in excel (bold them or change color), but have not found a way to format individual characters within a cell. Applying a style applies it to the entire cell to my knowledge. Therefore I am trying to break the sequences down into individual components where some parts of the sequence will be modified with a style while others won't, and to then collate these back together into a single string in a single cell.

Code:

    rb = open_workbook('Mybook', formatting_info=True)
    rs = rb.sheet_by_index(0)
    wb = copy(rb)
    ws = wb.get_sheet(0)

    minus35style = xlwt.easyxf('font: bold 1') # style I'd like for just one character
    for b in range(0, 368, 1):
        rscellin = rs.cell(b,9)
        f = rscellin.value
        tominus35 = str(f[0:34])
        minus35 = str(f[35:36])
        ws.write(b, 14, tominus35)
        ws.write(b, 14, minus35, minus35style)
    wb.save('Mybook')

My problem is that adding a style changes the whole cell, and I want just certain characters to be modified. Additionally, subsequent writing to the same cell overwrites what was there previously. Is there a way to modify individual characters in a single cell, or to add differently formatted text to an existing cell that already has text in it?

Please let me know if you require additional information that I've overlooked. I appreciate your time in advance.

Brett

Answer 1

Recent versions of xlwt include the ability to use Rich Text within a cell. Where normally you would use ws.write, use ws.write_rich_text instead. The first two parameters are the row index and column index, as usual; but the next parameter is a sequence of components. Each component can either be a "naked" text value or a (text, font) pair. The naked text values will use the font from the cell's overall style, which can be specified using the optional fourth parameter.

For the (text, font) pairs, it is simplest to generate fonts using the new easyfont feature, which is kind of like easyxf but only for fonts. Here is an example:

import xlwt

wb = xlwt.Workbook()
ws = wb.add_sheet('Sheet1')

font0 = xlwt.easyfont('')
font1 = xlwt.easyfont('bold true')
font2 = xlwt.easyfont('color_index red')
style = xlwt.easyxf('font: color_index blue')

seg1 = ('bold', font1)
seg2 = ('red', font2)
seg3 = ('plain', font0)
seg4 = ('boldagain', font1)

ws.write_rich_text(2, 5, (seg1, seg2, seg3, seg4))
ws.write_rich_text(4, 1, ('xyz', seg2, seg3, '123'), style)

wb.save('rich_text.xls')

You should be able to adapt the above for your purposes. Note that you still have to write or overwrite the whole cell at once; you can't go back and update only part of a cell later.