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

by

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.

More Related Contents:

  1. writing to existing workbook using xlwt [closed]
  2. Python xlwt – accessing existing cell content, auto-adjust column width
  3. Insert row into Excel spreadsheet using openpyxl in Python
  4. Edit existing excel workbooks and sheets with xlrd and xlwt
  5. GroupBy results to dictionary of lists
  6. Create a .csv file with values from a Python list
  7. Pandas: Looking up the list of sheets in an excel file
  8. python xlrd unsupported format, or corrupt file.
  9. How to copy over an Excel sheet to another workbook in Python
  10. Reading Excel File using Python, how do I get the values of a specific column with indicated column name?
  11. Get formula from Excel cell with python xlrd
  12. Error: Unsupported format, or corrupt file: Expected BOF record
  13. Convert date from excel in number format to date format python [duplicate]
  14. How to obtain sheet names from XLS files without loading the whole file?
  15. xlrd.biffh.XLRDError: Excel xlsx file; not supported [duplicate]
  16. Python Creating Dictionary from excel data
  17. Identifying Excel Sheet cell color code using XLRD package
  18. django excel xlwt
  19. python: update dataframe to existing excel sheet without overwriting contents on the same sheet and other sheets
  20. Integers from excel files become floats?
  21. Interleave multiple lists of the same length in Python
  22. Is it possible to dereference variable id’s?
  23. python: get the print output in an exec statement
  24. Why doesn’t Python have static variables?
  25. Subclassing dict: should dict.__init__() be called?
  26. How to remove axis, legends, and white padding
  27. What is the meaning of list[:] in this code? [duplicate]
  28. Numpy – Replace a number with NaN
  29. dynamically loading django apps at runtime
  30. exec() not working inside function python3.x

Leave a Comment