Easily write formatted Excel from Python: Start with Excel formatted, use it in Python, and regenerate Excel from Python

Question

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

I start in Excel as it is very easy to format: I write in Excel the model I want, with the good format
I read this from Python
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

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
– penguin359, Commented Mar 29, 2012 at 7:37
You can use COM to create / format xls. For formatting I usually record macro in Excel and than rewrite it to Python. — Fenikso
– Fenikso, Commented Mar 29, 2012 at 7:44

Community · Accepted Answer · 2017-05-23 11:44:15Z

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

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Apr 13, 2012 at 6:46

Exevior

1011 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5457708 Over a year ago

This deserves more attention, thank you for saving me, really. +1

John Machin · Accepted Answer · 2012-04-03 21:23:14Z

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.

answered Apr 3, 2012 at 21:23

John Machin

83.3k12 gold badges147 silver badges193 bronze badges

2 Comments

Exevior Over a year ago

stackoverflow.com/questions/3723793/…

Exevior Over a year ago

with easyxf need to define each format of cells. for instance: format1 = easyxf (xxxx) # for bold font, border just left

Collectives™ on Stack Overflow

Easily write formatted Excel from Python: Start with Excel formatted, use it in Python, and regenerate Excel from Python

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related