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 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()