It's easy to write formatted Excel from Python: start by formatting Excel, use it in Python, and recover Excel from Python

I need to create a nicely formatted Excel spreadsheet from Python. I thought about it:

  • I start in Excel as it is very easy to format: I write in Excel the model I want with a good format
  • I read this from Python
  • I am creating from Python an Excel spreadsheet with the same format

In the end, the goal is to build from Python Excel spreadsheets, but formatting with xlwt takes a long time, so I thought about formatting in Excel first to help.

I've researched for easy ways to do this but haven't found one. I can stick with my current working solution using xlwt in Python to create a formatted Excel, but this is pretty awkward to use.

Thanks for any answer


source to share

2 answers

Thank you for your responses. I found what I was looking for in Saving Styles with xlrd, xlwt and xlutils.copy python . The code is below.

import xlrd 
import xlutils.copy 

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

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) 
    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')'output.xls') 




You said:

formatting with xlwt takes a long time


rather awkward to use

Perhaps you are not using easyxf

? If so, check out the tutorial you can access via

and take a look at examples/

in your xlwt installation.



All Articles