Preserving Styles Using Python's Xlrd,Xlwt, and Xlutils.Copy

Issues in preserving Excel formatting with Python's xlrd and xlutils

xlwt.write accept style informations as its third argument. Unfortunately, xlrd and xlwt use two very different XF object format. So you cannot directly copy the cell's style from the workbook read by xlrd to the workbook created by xlwt.

The workaround is the use an xlutils.XLWTWriter to copy the file, and then get back the style informations of that object to save the style of the cell you will update.

First you need that patch function by John Machin provided in a very similar question:

from xlutils.filter import process,XLRDReader,XLWTWriter

#
# suggested patch by John Machin
# https://stackoverflow.com/a/5285650/2363712
# 
def copy2(wb):
    w = XLWTWriter()
    process(
        XLRDReader(wb,'unknown.xls'),
        w
        )
    return w.output[0][1], w.style_list

Then in you main code:

import xlrd, xlutils
from xlrd import open_workbook
from xlutils.copy import copy

inBook = xlrd.open_workbook(r"/tmp/format_input.xls", formatting_info=True, on_demand=True)
inSheet = inBook.sheet_by_index(0)

# Copy the workbook, and get back the style
# information in the `xlwt` format
outBook, outStyle = copy2(inBook)

# Get the style of _the_ cell:    
xf_index = inSheet.cell_xf_index(0, 0)
saved_style = outStyle[xf_index]

# Update the cell, using the saved style as third argument of `write`:
outBook.get_sheet(0).write(0,0,'changed!', saved_style)
outBook.save(r"/tmp/format_output.xls")

xlwt/xlutils.copy doesn't preserve cell (which I didn't touch) format

Looks like xlrd doesn't support conditional formatting yet.

You can check out the error logs by passing verbosity=1 to open_workbook function.

rb=xlrd.open_workbook(MY_FILE_PATH, formatting_info=True, verbosity=1)

Alternatively, openpyxl seems to have support for "Conditional Formatting". Can check this package instead.

Xlrd, xlwt, and xlutils: how do they work together

xlrd will load your worksheet file into memory at the time you call the open_workbook() method. Any changes made to the worksheet file after you call open_workbook() are not automatically reflected in the object in memory.

If you take a look at the xlrd source code on Github, in particular the open_workbook() method of the book.py file, you'll see that it loads the contents of the worksheet into memory when the open_workbook() method is called. That means, if you call open_workbook(), then change the original file, the workbook in memory will not reflect those changes.

This is also why, when you use the xlwt module, you must use the write() method - when you modify the contents of the worksheet that is in memory, the changes are not reflected in the original file, until you call write().

---

Related Topics