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
