def get_file(self):
fp = BytesIO()
archive = ZipFile(fp, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(self.work_book, archive)
writer.write_data()
archive.close()
fp.flush()
fp.seek(0)
return fp.read()
def dump(obj: Union[Dict, Iterable], file: Union[str, BinaryIO, PathLike]):
"""
Dumps an object hierarchy into an xlsx/xlsm file.
"""
# Create a new workbook and delete the default sheet.
workbook = Workbook()
workbook.remove(workbook.active)
header_font = Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
# Loop through all tables returned by object_hierarchy_to_tables.
for length, table in object_hierarchy_to_tables(obj).items():
# If it's a table of scalar elements (aka. length 0), create
# two columns. The first column has the element name and the
# second one the value.
if length == 0:
worksheet = workbook.create_sheet(f"scalar")
for i, row in enumerate(table.items()):
worksheet.append(row)
worksheet[i+1][0].font = header_font
else:
# For any other length create a header with the element names
# and list the values beneath them.
worksheet = workbook.create_sheet(f"size {str(length)}")
# Create the header and set the font
worksheet.append(tuple(table.keys()))
for i in range(len(table)):
worksheet[1][i].font = header_font
# Loop row by row, and list the values under the headers.
for row in zip(*[values for _, values in table.items()]):
worksheet.append(row)
# Freeze the first row.
worksheet.freeze_panes = "A2"
# Adjust the width of the columns to make sure the headers are visible.
adjust_column_width(worksheet)
# Create zip archive in with to write the excel data.
archive = ZipFile(file, 'w', ZIP_DEFLATED, allowZip64=True)
# Try to write the excel data.
writer = ExcelWriter(workbook, archive)
try:
writer.write_data()
finally:
archive.close()
def get_data(self):
archive = ZipFile(self.output, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(self.wb, archive)
try:
writer.write_data()
writer.save()
finally:
archive.close()
return self.output.getvalue()
def save_virtual_workbook(workbook, ):
"""Return an in-memory workbook, suitable for a Django response."""
temp_buffer = BytesIO()
archive = ZipFile(temp_buffer, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(workbook, archive)
try:
writer.write_data()
finally:
archive.close()
virtual_workbook = temp_buffer.getvalue()
temp_buffer.close()
return virtual_workbook
class XLSXResponse(web.StreamResponse):
def __init__(self,
request,
*args,
fields=None,
head=None,
filename=None,
**kwargs):
if filename:
headers = kwargs.setdefault('headers', {})
v = 'attachment; filename="{}"'.format(filename)
headers['Content-Disposition'] = v
super().__init__(*args, **kwargs)
self.content_type = 'application/xlsx'
self.request = request
self.fields = fields
self.head = head
def prepare_workbook(self):
wb = openpyxl.Workbook()
self.file = io.BytesIO()
self.archive = ZipFile(self.file, 'w', ZIP_DEFLATED)
self.ws = wb.active
self.writer = ExcelWriter(wb, self.archive)
if self.head:
self.ws.append(self.head)
def append(self, data):
if not self.fields:
self.fields = list(data)
if self.head is None:
self.ws.append(self.fields)
self.ws.append([data[i] for i in self.fields])
def write_workbook(self):
self.writer.write_data()
self.archive.close()
async def __aenter__(self):
await self.prepare(self.request)
self.prepare_workbook()
return self
async def __aexit__(self, exc_type, exc, tb):
loop = asyncio.get_event_loop()
await loop.run_in_executor(None, self.write_workbook)
await super().write(self.file.getbuffer())
def to_bytes(self, table, **kargs):
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
# Determine columns. We may need an extra (first) column which 'names' the row
columns = list(map(str, list(table.get_columns())))
ws.append(columns)
# Write rows to worksheet
for row in table.get_rows():
ws.append(tuple(_get_values(table, row)))
writer = ExcelWriter(wb)
# Need to do a little bit more work here, since the openpyxl library only
# supports writing to a filename, while we need a buffer here..
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, 'w', zipfile.ZIP_DEFLATED) as zf:
writer.write_data(zf)
return buffer.getvalue()
def to_bytes(self, table, **kargs):
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
# Determine columns. We may need an extra (first) column which 'names' the row
columns = list(map(str, list(table.get_columns())))
ws.append(columns)
# Write rows to worksheet
for row in table.get_rows():
ws.append(tuple(_get_values(table, row)))
writer = ExcelWriter(wb)
# Need to do a little bit more work here, since the openpyxl library only
# supports writing to a filename, while we need a buffer here..
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, 'w', zipfile.ZIP_DEFLATED) as zf:
writer.write_data(zf)
return buffer.getvalue()
def store_spreadsheet(self,
course_id,
filename,
ws1_title,
ws1_rows,
additional_sheets=None):
"""
Create and store a new spreadsheet and store it in the selected storage.
Args:
course_id: used for storing purposes
filename (str): name of the spreadsheet
ws1_title (str): title of the first worksheet
ws1_rows (list): rows for the first worksheet
additional_sheets (list): a list of additional worksheets. Each item is a
dictionary containing a name and a list of rows
"""
log.info('Creating new spreadsheet')
wb = Workbook()
ws1 = wb.active
ws1.title = ws1_title
for row in ws1_rows:
ws1.append(row)
for additional_sheet in additional_sheets:
additional_ws = wb.create_sheet(additional_sheet.get('title'))
for row in additional_sheet.get('rows'):
additional_ws.append(row)
output_buffer = ContentFile('')
archive = ZipFile(output_buffer, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(wb, archive)
try:
writer.write_data()
finally:
archive.close()
output_buffer.seek(0)
log.info('Saving spreadsheet {}'.format(filename))
self.store(course_id, filename, output_buffer)
class XLSXResponse(web.StreamResponse):
def __init__(self, *args, fields=None, head=None, filename=None, **kwargs):
if filename:
headers = kwargs.setdefault('headers', {})
v = 'attachment; filename="{}"'.format(filename)
headers['Content-Disposition'] = v
super().__init__(*args, **kwargs)
self.content_type = 'application/xlsx'
self.fields = fields
self.head = head
def write(self, data):
super().write(data)
return len(data)
def flush(self):
return super().drain()
def __enter__(self):
wb = openpyxl.Workbook()
self.archive = ZipFile(self, 'w', ZIP_DEFLATED, allowZip64=True)
self.ws = wb.active
self.writer = ExcelWriter(wb, self.archive)
if self.head:
self.ws.append(self.head)
return self
def append(self, data):
if not self.fields:
self.fields = list(data)
if self.head is None:
self.ws.append(self.fields)
self.ws.append([data[i] for i in self.fields])
def __exit__(self, exc_type, exc_val, exc_tb):
self.writer.write_data()
self.archive.close()
