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

Question

Simply put, I would like to preserve all of the formatting of one Excel file into another. However, despite using the formatting_info=True flag, the formatting only appears for all of the unchanged cells within the changed row. Any advice?

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

inBook = xlrd.open_workbook(r"path/to/file/format_input.xls", formatting_info=True, on_demand=True)
outBook = xlutils.copy.copy(inBook)

outBook.get_sheet(0).write(0,0,'changed!')
outBook.save(r"path/to/file/format_output.xls")

enter image description here

Answer 1

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