Share style information across cells / Slowness setting style information per cell
Created originally on Bitbucket by chuckel (Charlie Huckel)
I've been comparing using xlwt (with xlutils) vs openpyxl (v1.6.1). One weakness I've found in openpyxl is the ability to set a style on a large number of cells efficiently. I put together a sample program which simply writes a 200x200 cell area with a yellow background, borders, and the row,col coordinates as its contents. This takes about 16x as long with openpyxl as xlwt. From what I can tell this is primarily due to rewriting the style information each time. With this performance difference, I am opting for xlwt for now. Suggestions on how to do this efficiently?
Test program with openpyxl:
#!python
#!/usr/bin/env python
from openpyxl import Workbook
from openpyxl.style import *
wb = Workbook()
ws = wb.get_active_sheet()
for row in xrange(0,200):
for col in xrange(0,200):
cell = ws.cell(row=row, column=col)
cell.style.borders.bottom.border_style = Border.BORDER_THIN
cell.style.borders.right.border_style = Border.BORDER_THIN
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = Color.YELLOW
cell.value = "%s, %s" % (row, col)
wb.save('openpyxl.xlsx')
Test program with xlst:
#!python
#!/usr/bin/env python
import xlwt
from xlwt.Workbook import *
from xlwt.Style import *
wb = Workbook()
ws = wb.add_sheet('0')
mystyle = xlwt.easyxf('borders: right thin, bottom thin; pattern: pattern solid, fore-color yellow;')
for row in xrange(0,200):
for col in xrange(0,200):
ws.write(row, col, "%s, %s" % (row, col), mystyle)
wb.save('xlwt.xls')
The timing to see comparatively how this looks.
#!bash
$ time ./testxlwt.py
real 0m1.211s
user 0m1.177s
sys 0m0.031s
$ time ./testopenpyxl.py
real 0m19.430s
user 0m19.203s
sys 0m0.187s
I would imagine a solution which somehow shared style information rather than storing it and writing it out per cell, might look something like this to a user:
#!python
mystyle = Style()
mystyle.fill.fill_type = Fill.FILL_SOLID
mystyle.fill.start_color.index = Color.YELLOW
...
for cell in some_cells:
cell.style = mystyle