def _values_to_row(self, values, row_idx):
"""
Convert whatever has been appended into a form suitable for work_rows
"""
cell = WriteOnlyCell(self)
for col_idx, value in enumerate(values, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.column = col_idx
cell.row = row_idx
if cell.hyperlink is not None:
cell.hyperlink.ref = cell.coordinate
yield cell
# reset cell if style applied
if cell.has_style or cell.hyperlink:
cell = WriteOnlyCell(self)
def openpyxl_stream(df):
"""
Write a dataframe straight to disk
"""
wb = Workbook(write_only=True)
ws = wb.create_sheet()
cell = WriteOnlyCell(ws)
cell.style = 'Pandas'
def format_first_row(row, cell):
for c in row:
cell.value = c
yield cell
rows = dataframe_to_rows(df)
first_row = format_first_row(next(rows), cell)
ws.append(first_row)
for row in rows:
row = list(row)
cell.value = row[0]
row[0] = cell
ws.append(row)
wb.save("openpyxl_stream.xlsx")
def write_dataframe_values_to_excel(worksheet, dataframe, start_row=None, wrap_text=True):
"""Write DataFrame values to Excel worksheet
:param worksheet: target Excel worksheet
:type worksheet: openpyxl.worksheet.worksheet.Worksheet
:param dataframe: source dataframe
:type dataframe: pandas.DataFrame
:param start_row: start row index for writing data
:param wrap_text: wrap text in cell
:type wrap_text: boolean
:return: None
"""
# init styles for values
border_side = Side(border_style='thin', color='000000')
border_style = Border(left=border_side, right=border_side, top=border_side, bottom=border_side)
alignment = Alignment(wrap_text=wrap_text)
for row_idx, row_data in enumerate(dataframe.itertuples()):
values_list = []
row_cells = []
for val in row_data:
# value in dataframe may be a tuple in case of existing multiindex and it cannot be correctly written to
# excel worksheet; so convert the tuple into a list and merge it with values list
if isinstance(val, tuple):
values_list += list(val)
else:
values_list.append(val)
for col_idx, val in enumerate(values_list):
cell = WriteOnlyCell(worksheet)
cell.border = border_style
if isinstance(val, Iterable) and not isinstance(val, string_types):
cell.value = ', '.join([str(v) for v in val])
else:
cell.value = None if isnull(val) else val
cell.alignment = alignment
row_cells.append(cell)
if start_row is None:
worksheet.append(row_cells)
else:
worksheet.insert_rows(start_row, amount=1)
for col_idx, cell in enumerate(row_cells):
new_cell = worksheet.cell(row=start_row, column=col_idx+1)
new_cell.value = cell.value
new_cell.border = border_style
new_cell.alignment = alignment
start_row += 1
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 append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
if (not isgenerator(row) and
not isinstance(row, (list, tuple, range))
):
self._invalid_row(row)
cell = WriteOnlyCell(self) # singleton
self._max_row += 1
row_idx = self._max_row
if self.writer is None:
self.writer = self._write_header()
next(self.writer)
el = Element("row", r='%d' % self._max_row)
col_idx = None
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
tree = write_cell(self, cell, styled)
el.append(tree)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
if col_idx:
self._max_col = max(self._max_col, col_idx)
el.set('spans', '1:%d' % col_idx)
try:
self.writer.send(el)
except StopIteration:
self._already_saved()
def append(self, row):
"""
:param row: iterable containing values to append
:type row: iterable
"""
if (not isgenerator(row) and not isinstance(row,
(list, tuple, range))):
self._invalid_row(row)
cell = WriteOnlyCell(self) # singleton
self._max_row += 1
row_idx = self._max_row
if self.writer is None:
self.writer = self._write_header()
next(self.writer)
el = Element("row", r='%d' % self._max_row)
col_idx = None
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
tree = write_cell(self, cell, styled)
el.append(tree)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
if col_idx:
self._max_col = max(self._max_col, col_idx)
el.set('spans', '1:%d' % col_idx)
try:
self.writer.send(el)
except StopIteration:
self._already_saved()
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
cell = WriteOnlyCell(self)
try:
while True:
row = (yield)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx}
if row_idx in self.row_dimensions:
dim = self.row_dimensions[row_idx]
attrs.update(dict(dim))
with xf.element("row", attrs):
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
write_cell(xf, self, cell, styled)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(self.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.conditional_formatting:
cfs = write_conditional_formatting(self)
for cf in cfs:
xf.write(cf)
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
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
def _write_header(self):
"""
Generator that creates the XML file and the sheet header
"""
with xmlfile(self.filename) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
if self.sheet_properties:
pr = self.sheet_properties.to_tree()
xf.write(pr)
xf.write(self.views.to_tree())
cols = self.column_dimensions.to_tree()
self.sheet_format.outlineLevelCol = self.column_dimensions.max_outline
xf.write(self.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
with xf.element("sheetData"):
cell = WriteOnlyCell(self)
try:
while True:
row = (yield)
row_idx = self._max_row
attrs = {'r': '%d' % row_idx}
if row_idx in self.row_dimensions:
dim = self.row_dimensions[row_idx]
attrs.update(dict(dim))
with xf.element("row", attrs):
for col_idx, value in enumerate(row, 1):
if value is None:
continue
try:
cell.value = value
except ValueError:
if isinstance(value, Cell):
cell = value
else:
raise ValueError
cell.col_idx = col_idx
cell.row = row_idx
styled = cell.has_style
write_cell(xf, self, cell, styled)
if styled: # styled cell or datetime
cell = WriteOnlyCell(self)
except GeneratorExit:
pass
if self.protection.sheet:
xf.write(self.protection.to_tree())
if self.auto_filter.ref:
xf.write(self.auto_filter.to_tree())
if self.sort_state.ref:
xf.write(self.sort_state.to_tree())
if self.data_validations.count:
xf.write(self.data_validations.to_tree())
drawing = write_drawing(self)
if drawing is not None:
xf.write(drawing)
if self._comments:
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
