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

score 6 · Accepted Answer · edited May 23 '17 at 12:19

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

Sylvain Leroux - sorry for the late reply. This is exactly what I needed. Thank you! — user1185790, Jan 30 '15 at 21:21

score 2 · Answer 2 · answered Jan 30 '15 at 16:49

I had similar issues while using openpyxl - for some reason this just doesn't seem to handled very well in the modules available. I ended up just restyling the cells as needed after I had input my data, using the syntax below:

#Formatting
from openpyxl.styles import Style, Color, PatternFill, Alignment, Font, NumberFormat
#Allows for conditional formatting
from openpyxl.formatting import CellIsRule #Allows for Conditional Formatting

for cell in changed_cells:
    cell.style = Style(fill=PatternFill(patternType='solid', fgColor=Color('FFff8888')), 
                         font=Font(name="Arial",size=11), 
                         alignment=Alignment(horizontal="center"))

Info on the syntax for implementing this kind of thing with xlrd can be found here.

I appreciate the help, Alecg_O. I'm glad you pointed out the means of going about reformatting a cell. I'll keep this in mind going forward, but Sylvain Leroux offered a solution without the need of reformatting - which is exactly what I was looking for. Thanks again! — user1185790, Jan 30 '15 at 21:23

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

2 Answers2