3

I have to create Excel spreadsheet with nice format from Python. I thought of doing it by:

  1. I start in Excel as it is very easy to format: I write in Excel the model I want, with the good format
  2. I read this from Python
  3. I create from Python an Excel spreadsheet with the same format

In the end, the purpose is to create from Python Excel spreadsheets, but formatting with xlwt takes a lot of time, so I thought of formatting first in Excel to help.

I have researched for easy ways to doing this but haven't found any. I can stick to my current working solution, using xlwt in Python to create formatted Excel, but it is quite awkward to use.

Thanks for any reply

John Y
  • 14,123
  • 2
  • 48
  • 72
Exevior
  • 101
  • 1
  • 5
  • 1
    You probably need more than this, but if you just need to get data in and out of Excel, there is always CSV. It won't allow you to control how it looks in Excel, but it a great to move data around and a whole lot simpler than messing with XLWT. – penguin359 Mar 29 '12 at 07:37
  • 2
    You can use COM to create / format xls. For formatting I usually record macro in Excel and than rewrite it to Python. – Fenikso Mar 29 '12 at 07:44

2 Answers2

4

Thanks for your replies. I've found what I was searching for at Preserving styles using python's xlrd,xlwt, and xlutils.copy. 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) 
    # 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')
Community
  • 1
  • 1
Exevior
  • 101
  • 1
  • 5
0

You said:

formatting with xlwt takes a lot of time

and

it is quite awkward to use

Perhaps you are not using easyxf? If so, check out the tutorial that you can access via www.python-excel.org, and have a look at examples/xlwt_easyxf_simple_demo.py in your xlwt installation.

John Machin
  • 81,303
  • 11
  • 141
  • 189
  • http://stackoverflow.com/questions/3723793/preserving-styles-using-pythons-xlrd-xlwt-and-xlutils-copy – Exevior Apr 13 '12 at 06:32
  • with easyxf need to define each format of cells. for instance: format1 = easyxf (xxxx) # for bold font, border just left – Exevior Apr 19 '12 at 10:19