def rows_to_xlsx(data):
"""
Can pass in either a 2D array ... or a list of dicts with the keys "rows"
and "name" that will be turned into individual worksheets.
"""
from pyexcelerate import Workbook
import io
import datetime
from decimal import Decimal
if not data:
sheets = [{'rows': [[]]}]
elif not isinstance(data[0], dict):
sheets = [{'rows': data}]
else:
sheets = data
wb = Workbook()
for j, sheet in enumerate(sheets):
def fixup_value(v):
if v is None:
return ''
if isinstance(v, datetime.datetime):
return str(v)
if isinstance(v, Decimal):
return float(v)
if isinstance(v, bool):
return int(v)
return v
rows = [list(map(fixup_value, row)) for row in sheet['rows']]
wb.new_sheet(sheet.get('name', 'Sheet%d' % (j + 1)), data=rows)
f = io.BytesIO()
wb._save(f)
return f.getvalue()
class ExcelWriter:
"""
Writes an Excel XLSX file.
:param filename_or_stream: The path to write the XLSX file or any stream
to receive the file contents.
:param str sheet_name: The name of the sheet to write to.
"""
def __init__(self, filename_or_stream, sheet_name='Sheet1'):
self._stream = filename_or_stream
self._workbook = Workbook()
if sheet_name is not None:
self._sheet = self._workbook.new_sheet(sheet_name)
self._default_style = None
self._rowcount = 0
@staticmethod
def content_type():
return 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
def __enter__(self):
return self
def __exit__(self, exc_type, exc_value, traceback):
if exc_type is None:
self.close()
def add_sheet(self, sheet_name):
self._sheet = self._workbook.new_sheet(sheet_name)
self._rowcount = 0
def num_rows(self):
return self._rowcount
def set_default_style(self, style):
"""
Sets the default style to be used by cells with no specified style.
"""
self._default_style = style
def set_column_style(self, index, number_format=None, width=None):
"""
Sets the width and/or number format of a single column in the sheet.
NOTE: Seems to be a bug setting the number format for a column to
a date format.
:param int index: The 0-based index of the column.
:param int width: The 'em' widths for the column.
:param str number_format: The excel number format, eg '0.0%'
"""
current = self._sheet.get_col_style(index + 1)
style = {
'format': current.format,
'size': current.size,
}
if number_format is not None:
style['format'] = Format(number_format)
if width is not None:
style['size'] = width * 2
self._sheet.set_col_style(index + 1, Style(**style))
def set_row_style(self, index, number_format=None, height=None):
"""
Sets the height and/or number format of a single row in the sheet.
:param int index: The 0-based index of the row.
:param int height: The 'em' height for the row.
:param str number_format: The excel number format, eg '0.0%'
"""
current = self._sheet.get_row_style(index + 1)
style = {
'format': current.format,
'size': current.size,
}
if number_format is not None:
style['format'] = Format(number_format)
if height is not None:
style['size'] = height * 2
self._sheet.set_row_style(index + 1, Style(**style))
def set_all_column_formats(self, formats):
"""
Sets the number format of each column in the sheet.
:param list formats: A list of format strings, or None to skip.
"""
for i, fmt in enumerate(formats):
if fmt is not None:
self.set_column_style(i, number_format=fmt)
def set_all_column_widths(self, widths):
"""
Sets the width of each column in the sheet.
:param list widths: A list of 'em' widths for each column.
"""
for i, width in enumerate(widths):
if width is not None:
self.set_column_style(i, width=width)
def writerow(self, rowdata, style=None):
"""
Writes a single row to the Excel sheet.
:param list rowdata: A list of values for each column in the row.
:param ExcelStyle style: List of styles for each column, or single style for all columns.
If not specified uses default style, if set. Can use None in the list to leave
a cell unstyled.
"""
self._rowcount += 1
i = self._rowcount
if isinstance(style, ExcelStyle):
merges = [style.colspan] * len(rowdata)
style = [style.get_excel_style()] * len(rowdata)
elif style is not None:
merges = [s.colspan if s is not None else None for s in style]
style = [
s.get_excel_style() if s is not None else None for s in style
]
elif self._default_style is not None:
merges = ()
style = [self._default_style.get_excel_style()] * len(rowdata)
else:
merges = ()
style = ()
for j, val in enumerate(rowdata):
# Strip tzinfo from datetime objects. They
# need to be localized before writing.
if isinstance(val, datetime):
val = val.replace(tzinfo=None)
# Coerce bool to int, excel can't format bools
# You can use this number_format for coerced value:
# '"Yes";"Yes";"No"'
elif isinstance(val, bool):
val = 1 if val else 0
self._sheet.set_cell_value(i, j + 1, val)
if val is not None and j < len(style) and style[j] is not None:
self._sheet.set_cell_style(i, j + 1, style[j])
# Merge any cells to effect "colspan"
if val is not None and j < len(merges) and merges[j] is not None:
self._sheet.range((i, j + 1), (i, j + merges[j])).merge()
def writerows(self, rows):
"""
Writes a list of row data to the Excel sheet, with default styling.
"""
for row in rows:
self.writerow(row)
def freeze_pane(self, col_idx=None, row_idx=None):
"""
Freezes the specified column and/or row panes.
"""
self._sheet.panes = Panes(x=col_idx, y=row_idx, freeze=True)
def close(self):
"""
Writes out the Excel data to the file/stream and
invalidates this instance.
"""
if isinstance(self._stream, str):
self._workbook.save(self._stream)
else:
self._workbook._save(self._stream)