def set_row_data(cell_lis, ws, row, data_lis, string_lis):
for index, data in enumerate(data_lis):
col = index * 3 + 4
cell_num = WriteOnlyCell(ws, data)
cell_num.number_format = num_format # 单元格值设置为数字
# ws[f"{string_lis[col - 1]}{row}"].number_format = num_format # 单元格值设置为数字
# ws[f"{string_lis[col - 1]}{row}"].value = data # 单元格值
cell_pdf = WriteOnlyCell(
ws, f"={string_lis[col - 1]}{row}/{string_lis[col - 1]}2")
cell_pdf.number_format = pdf_format # 当前所占百分比设置为百分数
# ws[f"{string_lis[col]}{row}"].number_format = pdf_format # 当前所占百分比设置为百分数
# ws[f"{string_lis[col]}{row}"].value = f"={string_lis[col - 1]}{row}/{string_lis[col - 1]}2" # 当前所占百分比
# ws[f"{string_lis[col + 1]}{row}"].number_format = pdf_format # 累计百分比设置为百分数
if row == 3:
cell_cdf = WriteOnlyCell(ws, f"={string_lis[col]}{row}")
# ws[f"{string_lis[col + 1]}{row}"].value = f"={string_lis[col]}{row}" # 第一条数据的累计百分比
else:
cell_cdf = WriteOnlyCell(
ws, f"={string_lis[col]}{row}+{string_lis[col + 1]}{row - 1}")
# ws[
# f"{string_lis[col + 1]}{row}"].value = f"={string_lis[col]}{row}+{string_lis[col + 1]}{row - 1}" # 当前累计百分比
cell_cdf.number_format = pdf_format # 当前所占百分比设置为百分数
cell_lis.append(cell_num)
cell_lis.append(cell_pdf)
cell_lis.append(cell_cdf)
ws.append(cell_lis)
def _write_row(self, sheet_index, row):
from couchexport.export import FormattedRow
sheet = self.tables[sheet_index]
cells = []
for col_ind, val in enumerate(row):
skip_formatting_on_row = (isinstance(row, FormattedRow)
and col_ind in row.skip_excel_formatting)
if (self.use_formatted_cells
and not skip_formatting_on_row
and not self.format_as_text):
excel_format, val_fmt = get_excel_format_value(val)
cell = WriteOnlyCell(sheet, val_fmt)
cell.number_format = excel_format
else:
cell = WriteOnlyCell(sheet, get_legacy_excel_safe_value(val))
if self.format_as_text:
cell.number_format = numbers.FORMAT_TEXT
cells.append(cell)
if isinstance(row, FormattedRow):
for hyperlink_column_index in row.hyperlink_column_indices:
cells[hyperlink_column_index].hyperlink = cells[hyperlink_column_index].value
cells[hyperlink_column_index].style = 'Hyperlink'
sheet.append(cells)
def format_output(val, eachformat, eachstyle, xls_sheet):
"""Returns a excel cell with the data formated as specified in the template table"""
new_cell = WriteOnlyCell(xls_sheet, value="init")
new_cell.style = eachstyle
if val == None:
val = "None"
elif eachformat in [None, "OLE"]:
pass
elif eachformat.startswith("OLE:"):
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().startswith("lookup-"):
lookup_name = eachformat.split("-")[1]
if lookup_name in template_lookups:
lookup_table = template_lookups.get(lookup_name, {})
val = lookup_table.get(val, val)
elif eachformat.lower() == "lookup_id":
val = id_table.get(val, "No match in srum lookup table for %s" % (val))
elif eachformat.lower() == "lookup_luid":
inttype = struct.unpack(">H6B",
codecs.decode(format(val, '016x'), 'hex'))[0]
val = template_lookups.get("LUID Interfaces", {}).get(inttype, "")
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) == int:
val = hex(val)
else:
val = format(val, "08x")
elif eachformat.lower() == "base2":
if type(val) == int:
val = format(val, "032b")
else:
try:
val = int(str(val), 2)
except:
val = val
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
else:
val = val
new_cell.value = val
return new_cell
def read_only():
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet()
cell_total = WriteOnlyCell(ws, "=SUM(A2:A1048576)")
cell_total.number_format = num_format
cell_str = WriteOnlyCell(ws, "")
ws.append([cell_total, cell_str])
for i in range(2**20 - 1):
cell_cur = WriteOnlyCell(ws, i)
cell_cur.number_format = num_format
cell_pdf = WriteOnlyCell(ws, F"=A{i + 2}/A1")
cell_pdf.number_format = pdf_format
ws.append([cell_cur, cell_pdf])
wb.save("ohmygod.xlsx")
def create_pdf_readonly_xlsx():
import string
string_lis = list(string.ascii_uppercase)
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet()
header = [
"序号", "callid", "enid", "参数一", "PDF", "CDF", "参数二", "PDF", "CDF",
"参数三", "PDF", "CDF", "参数死四", "PDF", "CDF"
]
ws.append(header)
# ws.cell(2, 1).value = "汇总"
# ws.merge_cells("A2:C2")
row_1 = ["汇总", "", ""]
for i in range(4):
col = i * 3 + 4
total_cell = WriteOnlyCell(
ws, f"=SUM({string_lis[col - 1]}3:{string_lis[col - 1]}1048576)")
total_cell.number_format = num_format # 总和格式
total_pdf_cell = WriteOnlyCell(ws, 1)
total_pdf_cell.number_format = pdf_format # 总和百分比
total_cdf_cell = WriteOnlyCell(ws, 1)
total_cdf_cell.number_format = pdf_format # 总累计百分比
row_1.append(total_cell)
row_1.append(total_pdf_cell)
row_1.append(total_cdf_cell)
ws.append(row_1)
for i in range(2**20):
data = [i, i + 1, i + 2, i + 3]
row = i + 3
cell_lis = ['', f"callid_{i + 1}", f"ENid_{i + 1}"]
set_row_data(cell_lis, ws, row, data, string_lis)
# nrow = ws.max_row
# for i in range(4):
# col = i * 3 + 4
# ws[f"{string_lis[col - 1]}2"].value = f"=SUM({string_lis[col - 1]}3:{string_lis[col - 1]}{nrow})" # 更新总和值
wb.save("excel_ouput/pdf_xlsx.xlsx")
def _get_cell(self, value, cell_format):
cell = WriteOnlyCell(self._sheet, value)
cell.number_format = cell_format
return cell
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
"Skipping corrupt row in the %s table. The last good row was %s."
% (each_sheet, row_num))
continue
if ese_row == None:
break
#The row is retrieved now use the template to figure out which ones you want and format them
xls_row = []
row_num += 1
for eachcolumn, eachformat, eachstyle in zip(ese_template_fields,
ese_template_formats,
ese_template_styles):
if eachcolumn == "#XLS_COLUMN#":
val = eachformat.replace("#ROW_NUM#", str(row_num))
val = WriteOnlyCell(xls_sheet, value=val)
val.style = eachstyle.style
val.number_format = eachstyle.number_format
else:
val = ese_row.get(eachcolumn.encode(),
"UNABLETORETRIEVECOLUMN")
if val == "UNABLETORETRIEVECOLUMN":
val = "WARNING: Invalid Column Name " + eachcolumn + " - Try one of these:" + str(
ese_template_fields) + str(
eachcolumn in ese_template_fields)
val = WriteOnlyCell(xls_sheet, value=val)
val.style = eachstyle.style
else:
val = format_output(val, eachformat, eachstyle.style)
#print dir(new_cell.style.font)
xls_row.append(val)
xls_sheet.append(xls_row)
def xlsx(net, out_path='.', overwrite=False):
"""
Export :class:`Qiber3D.Network` as Excel file (:file:`.xlsx`).
:param Qiber3D.Network net: network to export
:param out_path: file or folder path where to save the network
:type out_path: str, Path
:param bool overwrite: allow file overwrite
:return: path to saved file
:rtype: Path
"""
out_path, needs_unlink = helper.out_path_check(out_path,
network=net,
prefix='',
suffix='.xlsx',
overwrite=overwrite,
logger=net.logger)
if out_path is None:
return
net_properties = {
'average_radius': 'Average radius',
'max_radius': 'Max radius',
'cylinder_radius': 'Equal cylinder radius',
'length': 'Length',
'volume': 'Volume',
'bbox_volume': 'Bounding box volume',
'bbox': 'Bounding box',
'bbox_size': 'Bounding box size',
'center': 'Bounding box center'
}
fiber_seg_properties = {
'average_radius': 'Average radius',
'max_radius': 'Max radius',
'cylinder_radius': 'Equal cylinder radius',
'length': 'Length',
'volume': 'Volume',
# 'raster_volume',
}
wb = Workbook(write_only=True)
ws = wb.create_sheet('Network')
title = WriteOnlyCell(ws, f'{net.name}')
ws.column_dimensions['A'].width = 21
ws.column_dimensions['B'].width = 21
title.style = 'Title'
ws.append([title])
ws.append([config.app_name, config.version])
ws.append([])
subtitle = WriteOnlyCell(ws, 'Metadata')
empty_subtitle = WriteOnlyCell(ws, '')
subtitle.style = 'Headline 3'
empty_subtitle.style = 'Headline 3'
ws.append([subtitle, empty_subtitle])
if isinstance(net.input_file, Path):
ws.append(['Source file', str(net.input_file.absolute())])
else:
ws.append(['Source file', '-'])
ws.append(['Creation date', datetime.now()])
ws.append([])
subtitle = WriteOnlyCell(ws, 'Network measurements')
empty_subtitle = WriteOnlyCell(ws, '')
subtitle.style = 'Headline 3'
empty_subtitle.style = 'Headline 3'
ws.append([subtitle, empty_subtitle])
ws.append(['Number of fibers', len(net.fiber)])
ws.append(['Number of segments', len(net.segment)])
ws.append(['Number of points', len(net.point)])
ws.append(['Number of branch points', len(net.cross_point_dict)])
for key, desciption in net_properties.items():
value = getattr(net, key)
if type(value) == np.ndarray:
value = str(value.tolist())
if isinstance(value, (np.floating, float)):
value = WriteOnlyCell(ws, value=value)
value.number_format = '0.00'
ws.append([desciption, value])
ws = wb.create_sheet('Fibers')
ws.column_dimensions['A'].width = 21
ws.column_dimensions['B'].width = 21
for fid, fiber in net.fiber.items():
subtitle = WriteOnlyCell(ws, f'Fiber {fid} measurements')
empty_subtitle = WriteOnlyCell(ws, '')
subtitle.style = 'Headline 3'
empty_subtitle.style = 'Headline 3'
ws.append([subtitle, empty_subtitle])
ws.append(['Number of segments', len(fiber.segment)])
ws.append([
'Number of points',
sum([len(seg) for seg in fiber.segment.values()])
])
branch_points_raw = sum((list(a) for a in fiber.graph.edges),
[])
check = []
bp_set = set()
for bp in branch_points_raw:
if bp in check:
bp_set.add(bp)
else:
check.append(bp)
ws.append(['Number of branch points', len(bp_set)])
for key, desciption in fiber_seg_properties.items():
value = getattr(net, key)
if type(value) == np.ndarray:
value = str(value.tolist())
if isinstance(value, (np.floating, float)):
value = WriteOnlyCell(ws, value=value)
value.number_format = '0.00'
ws.append([desciption, value])
ws.append(['Segment list'] +
[sid for sid in fiber.segment.keys()])
ws.append([])
ws = wb.create_sheet('Segments')
ws.column_dimensions['A'].width = 21
ws.column_dimensions['B'].width = 21
for sid, segment in net.segment.items():
subtitle = WriteOnlyCell(ws, f'Segment {sid} measurements')
empty_subtitle = WriteOnlyCell(ws, '')
subtitle.style = 'Headline 3'
empty_subtitle.style = 'Headline 3'
ws.append([subtitle, empty_subtitle])
ws.append(['Number of points', len(segment)])
for key, desciption in fiber_seg_properties.items():
value = getattr(net, key)
if type(value) == np.ndarray:
value = str(value.tolist())
if isinstance(value, (np.floating, float)):
value = WriteOnlyCell(ws, value=value)
value.number_format = '0.00'
ws.append([desciption, value])
ws.append([])
ws = wb.create_sheet('Points')
ws.append(['FID', 'SID', 'X', 'Y', 'Z', 'Radius'])
for fid, fiber in net.fiber.items():
for sid, segment in fiber.segment.items():
for n, (x, y, z) in enumerate(segment.point):
x = WriteOnlyCell(ws, value=x)
x.number_format = '0.000'
y = WriteOnlyCell(ws, value=y)
y.number_format = '0.000'
z = WriteOnlyCell(ws, value=z)
z.number_format = '0.000'
r = WriteOnlyCell(ws, value=segment.radius[n])
r.number_format = '0.000'
ws.append([fid, sid, x, y, z, r])
wb.save(out_path)
return out_path
