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
+3
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)
# 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')
+5
source to share