I am trying to create an Excel workbook where I can auto-set, or auto-adjust the widths of the columns before saving the workbook.
I have been reading the Python-Excel tutorial in hopes of finding some functions in xlwt that emulate xlrd ones (such as sheet_names()
, cellname(row, col)
, cell_type
, cell_value
, and so on...) For example, suppose I have the following:
from xlwt import Workbook
wb = Workbook()
sh1 = wb.add_sheet('sheet1' , cell_overwrite_ok = True)
sh2 = wb.get_sheet(0)
wb.get_sheet(0)
is similar to the rb.sheet_by_index(0)
function offered in xlrd, except that the former allows you to modify the contents (provided the user has set cell_overwrite_ok = True
)
Assuming xlwt DOES offer the functions I am looking for, I was planning on going through every worksheet again, but this time keeping track of the content that takes the most space for a particular column, and set the column width based on that. Of course, I can also keep track of the max width for a specific column as I write to the sheet, but I feel like it would be cleaner to set the widths after all the data has been already written.
Does anyone know if I can do this? If not, what do you recommend doing in order to adjust the column widths?
I just implemented a wrapper class that tracks the widths of items as you enter them. It seems to work pretty well.
import arial10
class FitSheetWrapper(object):
"""Try to fit columns to max size of any entry.
To use, wrap this around a worksheet returned from the
workbook's add_sheet method, like follows:
sheet = FitSheetWrapper(book.add_sheet(sheet_name))
The worksheet interface remains the same: this is a drop-in wrapper
for auto-sizing columns.
"""
def __init__(self, sheet):
self.sheet = sheet
self.widths = dict()
def write(self, r, c, label='', *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
width = arial10.fitwidth(label)
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
def __getattr__(self, attr):
return getattr(self.sheet, attr)
All the magic is in John Yeung's arial10
module. This has good widths for Arial 10, which is the default Excel font. If you want to write worksheets using other fonts, you'll need to change the fitwidth function, ideally taking into account the style
argument passed to FitSheetWrapper.write
.