Preserving styles using python's xlrd,xlwt, and xlutils.copy

Question

I'm using xlrd, xlutils.copy, and xlwt to open up a template file, copy it, fill it with new values, and save it.

However, there doesn't seem to be any easy way to preserve the formatting of the cells; it always gets blown away and set to blank. Is there any simple way I can do this?

Thanks! /YGA

A sample script:

from xlrd import open_workbook
from xlutils.copy import copy
rb = open_workbook('output_template.xls',formatting_info=True)
rs = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)
for i,cell in enumerate(rs.col(8)):
    if not i:
        continue
    ws.write(i,2,22,plain)
wb.save('output.xls')

Versions:

• xlrd: 0.7.1
• xlwt: 0.7.2

Answer 1

There are two parts to this.

First, you must enable the reading of formatting info when opening the source workbook. The copy operation will then copy the formatting over.

import xlrd
import xlutils.copy

inBook = xlrd.open_workbook('input.xls', formatting_info=True)
outBook = xlutils.copy.copy(inBook)

Secondly, you must deal with the fact that changing a cell value resets the formatting of that cell.

This is less pretty; I use the following hack where I manually copy the formatting index (xf_idx) over:

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

This preserves almost all formatting. Cell comments are not copied, though.