def get_donor_cell(self): size = 9 if self.has_acceptor: cell = WriteOnlyCell(ws_result, value='OK') cell.fill = PatternFill(start_color='FFdff0d8', end_color='FFdff0d8', fill_type='solid') cell.font = Font(name=self.font, size=size, color='3c763d', bold=True) cell.comment = Comment(text="Good =)", author=self.author) else: cell = WriteOnlyCell(ws_result, value='NO') cell.fill = PatternFill(start_color='FFf2dede', end_color='FFf2dede', fill_type='solid') cell.font = Font(name=self.font, size=size, color='a94442', bold=True) cell.comment = Comment( text="There are no acceptor ({}) on the site: {}".format( self.acceptor, self.donor), author=self.author) return cell
def get_status_cell(self): size = 8 if self.url_status < 400: cell = WriteOnlyCell(ws_result, value=self.status_reason) color = '3c763d' if self.has_acceptor else 'a94442' cell.font = Font(name=self.font, size=size, color=color, bold=True) cell.comment = Comment(text="Good =)", author=self.author) else: cell = WriteOnlyCell(ws_result, value="Site Access Problem") cell.font = Font(name=self.font, size=size, color='a94442', bold=True) cell.comment = Comment( text="Seems we have some troubles with the site: {}".format( self.acceptor), author=self.author) return cell
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs): # Create a workbook wb = Workbook(write_only=True) ws = wb.create_sheet(title=report.name) # Create a named style for the header row readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle") readlonlyheaderstyle.fill = PatternFill(fill_type="solid", fgColor="d0ebfb") wb.add_named_style(readlonlyheaderstyle) # Run the query conn = None try: conn = create_connection(request.database) comment = CellComment(force_text(_("Read only")), "Author", height=20, width=80) with conn.cursor() as cursor: sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole, )) cursor.execute(sql=cls.getSQL(report.sql)) if cursor.description: # Write header row header = [] for f in cursor.description: cell = WriteOnlyCell(ws, value=f[0]) cell.style = "readlonlyheaderstyle" cell.comment = comment header.append(cell) ws.append(header) # Add an auto-filter to the table ws.auto_filter.ref = "A1:%s1048576" % get_column_letter( len(header)) # Write all output rows for result in cursor.fetchall(): ws.append( [_getCellValue(i, request=request) for i in result]) # Write the spreadsheet wb.save(out) finally: if conn: conn.close()
def exportWorkbook(request): # Create a workbook wb = Workbook(write_only=True) # Create a named style for the header row headerstyle = NamedStyle(name="headerstyle") headerstyle.fill = PatternFill(fill_type="solid", fgColor="70c4f4") wb.add_named_style(headerstyle) readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle") readlonlyheaderstyle.fill = PatternFill(fill_type="solid", fgColor="d0ebfb") wb.add_named_style(readlonlyheaderstyle) # Loop over all selected entity types exportConfig = {"anonymous": request.POST.get("anonymous", False)} ok = False for entity_name in request.POST.getlist("entities"): try: # Initialize (app_label, model_label) = entity_name.split(".") model = apps.get_model(app_label, model_label) # Verify access rights permname = get_permission_codename("change", model._meta) if not request.user.has_perm("%s.%s" % (app_label, permname)): continue # Never export some special administrative models if model in EXCLUDE_FROM_BULK_OPERATIONS: continue # Create sheet ok = True ws = wb.create_sheet(title=force_text(model._meta.verbose_name)) # Build a list of fields and properties fields = [] modelfields = [] header = [] source = False lastmodified = False owner = False comment = None try: # The admin model of the class can define some fields to exclude from the export exclude = data_site._registry[model].exclude except Exception: exclude = None for i in model._meta.fields: if i.name in ["lft", "rght", "lvl"]: continue # Skip some fields of HierarchyModel elif i.name == "source": source = i # Put the source field at the end elif i.name == "lastmodified": lastmodified = i # Put the last-modified field at the very end elif not (exclude and i.name in exclude): fields.append(i.column) modelfields.append(i) cell = WriteOnlyCell(ws, value=force_text( i.verbose_name).title()) if i.editable: cell.style = "headerstyle" if isinstance(i, ForeignKey): cell.comment = CellComment( force_text( _("Values in this field must exist in the %s table" ) % force_text(i.remote_field.model. _meta.verbose_name)), "Author", ) elif i.choices: cell.comment = CellComment( force_text( _("Accepted values are: %s") % ", ".join([c[0] for c in i.choices])), "Author", ) else: cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment( force_text(_("Read only")), "Author", height=20, width=80, ) cell.comment = comment header.append(cell) if i.name == "owner": owner = True if hasattr(model, "propertyFields"): if callable(model.propertyFields): props = model.propertyFields(request) else: props = model.propertyFields for i in props: if i.export: fields.append(i.name) cell = WriteOnlyCell(ws, value=force_text( i.verbose_name).title()) if i.editable: cell.style = "headerstyle" if isinstance(i, ForeignKey): cell.comment = CellComment( force_text( _("Values in this field must exist in the %s table" ) % force_text(i.remote_field.model. _meta.verbose_name)), "Author", ) elif i.choices: cell.comment = CellComment( force_text( _("Accepted values are: %s") % ", ".join([c[0] for c in i.choices])), "Author", ) else: cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment( force_text(_("Read only")), "Author", height=20, width=80, ) cell.comment = comment header.append(cell) modelfields.append(i) if source: fields.append("source") cell = WriteOnlyCell(ws, value=force_text(_("source")).title()) cell.style = "headerstyle" header.append(cell) modelfields.append(source) if lastmodified: fields.append("lastmodified") cell = WriteOnlyCell(ws, value=force_text( _("last modified")).title()) cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment(force_text(_("Read only")), "Author", height=20, width=80) cell.comment = comment header.append(cell) modelfields.append(lastmodified) # Write a formatted header row ws.append(header) # Add an auto-filter to the table ws.auto_filter.ref = "A1:%s1048576" % get_column_letter( len(header)) # Use the default manager if issubclass(model, HierarchyModel): model.rebuildHierarchy(database=request.database) query = (model.objects.all().using(request.database).order_by( "lvl", "pk")) elif owner: # First export records with empty owner field query = (model.objects.all().using(request.database).order_by( "-owner", "pk")) else: query = model.objects.all().using( request.database).order_by("pk") # Special annotation of the export query if hasattr(model, "export_objects"): query = model.export_objects(query, request) # Loop over all records for rec in query.values_list(*fields): cells = [] fld = 0 for f in rec: cells.append( _getCellValue(f, field=modelfields[fld], exportConfig=exportConfig)) fld += 1 ws.append(cells) except Exception: pass # Silently ignore the error and move on to the next entity. # Not a single entity to export if not ok: raise Exception(_("Nothing to export")) # Write the excel from memory to a string and then to a HTTP response output = BytesIO() wb.save(output) response = HttpResponse( content_type= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", content=output.getvalue(), ) response["Content-Disposition"] = 'attachment; filename="frepple.xlsx"' response["Cache-Control"] = "no-cache, no-store" return response
def test1(): current_dir = path.dirname(path.abspath(__file__)) file_path = f'{current_dir}{sep}openpyxl.xlsx' # 创建空表格 wb = Workbook() # 读取表格 # wb = load_workbook(file_path) # grab the active worksheet ws = wb.active # ws2 = wb.create_sheet("Sheet2") # insert at the end (default) # ws3 = wb.create_sheet("Sheet3") # insert at the end (default) # ws = wb.create_sheet("Mysheet", 0) # insert at first position # ws = wb.create_sheet("Mysheet", -1) # insert at the penultimate position # target = wb.copy_worksheet(ws) # sheet 标题 ws.title = "Sheet Title" # 通过标题获取 Sheet 对象 # ws3 = wb["Sheet Title"] # sheet 标题的背景色 ws.sheet_properties.tabColor = "FF0000" print('\n ==============输出工作表名称================\n') print(wb.sheetnames) for sheet in wb: print(sheet.title, sheet.max_row, sheet.max_column) # Data can be assigned directly to cells ws['A1'] = 42 ws['B1'] = 43 ws['C1'] = 44 print('\n================ 写数据 =========================\n') # Rows can also be appended ws.append([1, 2, 3]) value = ws.cell(4, 4, 44) print("Cell 对象: ", value) # Python types will automatically be converted ws['A3'] = datetime.now() print('number_format: ', ws['A3'].number_format) special_cell = WriteOnlyCell(ws, value="hello world") special_cell.font = Font(name='Courier', size=12) special_cell.comment = Comment(text="A comment", author="Author's Name") ws.append([special_cell, '张三', None]) # 在第 4 行上插入一行,第 4 行变成空行 # ws.insert_rows(4) ws.delete_rows(4) # Save the file print(f'文件保存路径:{current_dir}') # This operation will overwrite existing files without warning. wb.save(file_path) print('\n================ 读数据 =========================\n') cell_range = ws['A1':'C2'] print('获取 A1 - C2 之间的数据:') for cell in cell_range: print(cell) print() # 访问一组单元格 # colC = ws['C'] # col_range_C_D = ws['C:D'] # row10 = ws[10] # row_range_5_10 = ws[5:10] """ for row in ws.iter_rows(min_row=1, max_col=3, max_row=2): for cell in row: print(cell) """ # For performance reasons the Worksheet.iter_cols() method is not available in read-only mode. for col in ws.iter_cols(min_row=1, max_col=3, max_row=2): list = [] for cell in col: list.append(cell.value) print(list) print() """ print('获取所有的行数据:') for cell in ws.rows: print(cell) print() """ # For performance reasons the Worksheet.columns property is not available in read-only mode. """ print('获取所有的列数据:') for cell in ws.columns: print(cell) print() """ cell_values = ws.values for row in cell_values: list = [] for col in row: list.append(col) print(list) wb.close
def format_output(val, eachformat, eachstyle): "Returns a excel cell with the data formated as specified" new_cell = WriteOnlyCell(xls_sheet, value="init") new_cell.style = eachstyle if val == None: val = "None" elif eachformat == None: pass elif eachformat == "OLE": val = ole_timestamp(val) new_cell.number_format = 'YYYY MMM DD' elif eachformat.startswith("OLE:"): val = ole_timestamp(val) val = val.strftime(eachformat[4:]) elif eachformat == "FILE": val = file_timestamp(val) new_cell.number_format = 'YYYY MMM DD' elif eachformat.startswith("FILE:"): val = file_timestamp(val) val = val.strftime(eachformat[5:]) elif eachformat.lower() == "lookup_id": val = id_table.get(val, "No match in srum lookup table for %s" % (val)) elif eachformat.lower() == "lookup_luid": val = lookup_luid(val) elif eachformat.lower() == "lookup_sid": val = "%s (%s)" % (val, lookup_sid(val)) elif eachformat.lower() == "seconds": val = val / 86400.0 new_cell.number_format = 'dd hh:mm:ss' elif eachformat.lower() == "md5": val = hashlib.md5(str(val)).hexdigest() elif eachformat.lower() == "sha1": val = hashlib.sha1(str(val)).hexdigest() elif eachformat.lower() == "sha256": val = hashlib.sha256(str(val)).hexdigest() elif eachformat.lower() == "base16": if type(val) == "<type 'int'>": val = hex(val) else: val = str(val).encode("hex") elif eachformat.lower() == "base2": if type(val) == int: val = bin(val) else: try: val = int(str(val), 2) except: val = val new_cell.comment = Comment( "Warning: Unable to convert value %s to binary." % (val), "srum_dump") elif eachformat.lower() == "interface_id" and options.reghive: val = interface_table.get(str(val), "") elif eachformat.lower() == "interface_id" and not options.reghive: val = val new_cell.comment = Comment( "WARNING: Ignoring interface_id format command because the --REG_HIVE was not specified.", "srum_dump") else: val = val new_cell.comment = Comment( "WARNING: I'm not sure what to do with the format command %s. It was ignored." % (eachformat), "srum_dump") new_cell.value = val return new_cell