Column widths in optimized_write=True mode
Created originally on Bitbucket by tuttle (Vlada Macek)
This project is great!
My client is satisfied with XLSX created using openpyxl 2.0.3 optimized_write=True from PyPy mode. I was able to store different types of values and make the header bold.
The only thing I suspect the DumpWorksheet is unable to do is storing the element where column widths are defined. It is desired to have columns with specific widths according to their content, but I don't want to leave the optimized_write=True mode.
So far I ended up with this hack (by the end of the write_header):
#!python
class WidthsDumpWorksheet(DumpWorksheet):
"""
This is a hack to get the <cols>...</cols> element to optimized writer.
"""
def write_header(self):
fobj = self.get_temporary_file(filename=self._fileobj_header_name)
doc = XMLGenerator(fobj)
start_tag(doc, 'worksheet',
{
'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
start_tag(doc, 'sheetPr')
tag(doc, 'outlinePr',
{'summaryBelow': '1',
'summaryRight': '1'})
end_tag(doc, 'sheetPr')
tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())})
start_tag(doc, 'sheetViews')
start_tag(doc, 'sheetView', {'workbookViewId': '0'})
tag(doc, 'selection', {'activeCell': 'A1',
'sqref': 'A1'})
end_tag(doc, 'sheetView')
end_tag(doc, 'sheetViews')
tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'})
# csv2xlsx inserts cols elements
start_tag(doc, 'cols')
for col, width in self.col_widths.items():
tag(doc, 'col', dict(min=str(col), max=str(col), width=width, customWidth="1"))
end_tag(doc, 'cols')
# /csv2xlsx
start_tag(doc, 'sheetData')
...
WidthsDumpWorksheet.col_widths = {}
for width_spec in self.args.col_width or ():
col, width = width_spec.split(',', 1)
col = column_index_from_string(col)
WidthsDumpWorksheet.col_widths[col] = width
wb = Workbook(optimized_write=True, optimized_worksheet_class=WidthsDumpWorksheet)
To keep less code copied, it would be very helpful when the "start_tag(doc, 'sheetData')" is NOT part of the method... Setting col widths this way works on Excel in Windows and Mac. Mac needs the customWidth="1", Windows does not :-P.
Would you consider adding support for setting the in optimized write mode? Or at least wouldn't you mind to refactor the write_header() by moving the last line out? The 'sheetData' is not part of the header anyway. :-)