def _write(self):
"""
create required structure and the serialise
"""
anchors = []
for idx, obj in enumerate(self.charts + self.images, 1):
anchor = _check_anchor(obj)
if isinstance(obj, ChartBase):
rel = Relationship(type="chart", Target=obj.path)
anchor.graphicFrame = self._chart_frame(idx)
elif isinstance(obj, Image):
rel = Relationship(type="image", Target=obj.path)
child = anchor.pic or anchor.groupShape and anchor.groupShape.pic
if not child:
anchor.pic = self._picture_frame(idx)
else:
child.blipFill.blip.embed = "rId%s" % idx
anchors.append(anchor)
self._rels.append(rel)
for a in anchors:
if isinstance(a, OneCellAnchor):
self.oneCellAnchor.append(a)
elif isinstance(a, TwoCellAnchor):
self.twoCellAnchor.append(a)
else:
self.absoluteAnchor.append(a)
tree = self.to_tree()
tree.set('xmlns', SHEET_DRAWING_NS)
return tree
def write_stylesheet(wb):
stylesheet = Stylesheet()
stylesheet.fonts = wb._fonts
stylesheet.fills = wb._fills
stylesheet.borders = wb._borders
stylesheet.dxfs = wb._differential_styles.styles
from .numbers import NumberFormat
fmts = []
for idx, code in enumerate(wb._number_formats, 164):
fmt = NumberFormat(idx, code)
fmts.append(fmt)
stylesheet.numFmts.numFmt = fmts
xfs = []
for style in wb._cell_styles:
xf = CellStyle.from_array(style)
if style.alignmentId:
xf.alignment = wb._alignments[style.alignmentId]
if style.protectionId:
xf.protection = wb._protections[style.protectionId]
xfs.append(xf)
stylesheet.cellXfs = CellStyleList(xf=xfs)
stylesheet._split_named_styles(wb)
stylesheet.tableStyles = wb._table_styles
return stylesheet.to_tree()
def write_names(self):
defined_names = copy(self.wb.defined_names)
# Defined names -> autoFilter
for idx, sheet in enumerate(self.wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
name.value = u"%s!%s" % (quote_sheetname(sheet.title),
absolute_coordinate(auto_filter) )
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([u"%s!%s" % (quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([u"%s!%s" % (quote_sheetname(sheet.title), r)
for r in sheet.print_area])
defined_names.append(name)
self.package.definedNames = defined_names
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 __init__(self, iterable=None):
self.clean = True
self._dict = {}
if iterable is not None:
self.clean = False
for idx, val in enumerate(iterable):
self._dict[val] = idx
list.append(self, val)
def delete(self, name, scope=None):
"""
Delete a name assigned to a specific or global
"""
for idx, defn in enumerate(self.definedName):
if defn.name == name and defn.localSheetId == scope:
del self.definedName[idx]
return True
def to_tree(self):
tree = Element("Relationships", xmlns=PKG_REL_NS)
for idx, rel in enumerate(self.Relationship, 1):
if not rel.Id:
rel.Id = "rId%s" % idx
tree.append(rel.to_tree())
return tree
def __getitem__(self, key):
if isinstance(key, int):
return super(NamedStyleList, self).__getitem__(key)
names = self.names
if key not in names:
raise KeyError("No named style with the name %s exists" % key)
for idx, name in enumerate(names):
if name == key:
return self[idx]
def write_worksheets(self):
for idx, sheet in enumerate(self.wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId%s" % idx)
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
self.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(self.wb._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
self.package.sheets.append(sheet_node)
def to_tree(self, tagname, obj, namespace=None):
"""
Convert the sequence represented by the descriptor to an XML element
"""
for idx, v in enumerate(obj, self.idx_base):
if hasattr(v, "to_tree"):
el = v.to_tree(tagname, idx)
else:
tagname = namespaced(obj, tagname, namespace)
el = Element(tagname)
el.text = safe_string(v)
yield el
def _write_external_links(self):
# delegate to object
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._id = idx
rels_path = get_rels_path(link.path[1:])
xml = link.to_tree()
self._archive.writestr(link.path[1:], tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
self._archive.writestr(rels_path, tostring(rels.to_tree()))
self.manifest.append(link)
def dataframe_to_rows(df, index=True, header=True):
"""
Convert a Pandas dataframe into something suitable for passing into a worksheet.
If index is True then the index will be included, starting one row below the header.
If header is True then column headers will be included starting one column to the right.
Formatting should be done by client code.
"""
import numpy
from pandas import Timestamp
blocks = df._data.blocks
ncols = sum(b.shape[0] for b in blocks)
data = [None] * ncols
for b in blocks:
values = b.values
if b.dtype.type == numpy.datetime64:
values = numpy.array([Timestamp(v) for v in values.ravel()])
values = values.reshape(b.shape)
result = values.tolist()
for col_loc, col in zip(b.mgr_locs, result):
data[col_loc] = col
if header:
if df.columns.nlevels > 1:
rows = expand_levels(df.columns.levels, df.columns.labels)
else:
rows = [list(df.columns.values)]
for row in rows:
n = []
for v in row:
if isinstance(v, numpy.datetime64):
v = Timestamp(v)
n.append(v)
row = n
if index:
row = [None] * df.index.nlevels + row
yield row
if index:
yield df.index.names
for idx, v in enumerate(df.index):
row = [data[j][idx] for j in range(ncols)]
if index:
row = [v] + row
yield row
def _write_worksheets(self):
pivot_caches = set()
for idx, ws in enumerate(self.workbook.worksheets, 1):
ws._id = idx
self.write_worksheet(ws)
if ws._drawing:
self._write_drawing(ws._drawing)
for r in ws._rels.Relationship:
if "drawing" in r.Type:
r.Target = ws._drawing.path
if ws._comments:
self._write_comment(ws)
if ws.legacy_drawing is not None:
shape_rel = Relationship(type="vmlDrawing",
Id="anysvml",
Target="/" + ws.legacy_drawing)
ws._rels.append(shape_rel)
for t in ws._tables:
self._tables.append(t)
t.id = len(self._tables)
t._write(self._archive)
self.manifest.append(t)
ws._rels[t._rel_id].Target = t.path
for p in ws._pivots:
if p.cache not in pivot_caches:
pivot_caches.add(p.cache)
p.cache._id = len(pivot_caches)
self._pivots.append(p)
p._id = len(self._pivots)
p._write(self._archive, self.manifest)
self.workbook._pivots.append(p)
r = Relationship(Type=p.rel_type, Target=p.path)
ws._rels.append(r)
if ws._rels:
tree = ws._rels.to_tree()
rels_path = get_rels_path(ws.path)[1:]
self._archive.writestr(rels_path, tostring(tree))
def append(self, iterable):
"""Appends a group of values at the bottom of the current sheet.
* If it's a list: all values are added in order, starting from the first column
* If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)
:param iterable: list, range or generator, or dict containing values to append
:type iterable: list|tuple|range|generator or dict
Usage:
* append(['This is A1', 'This is B1', 'This is C1'])
* **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
* **or** append({1 : 'This is A1', 3 : 'This is C1'})
:raise: TypeError when iterable is neither a list/tuple nor a dict
"""
row_idx = self._current_row + 1
if (isinstance(iterable, (list, tuple, range))
or isgenerator(iterable)):
for col_idx, content in enumerate(iterable, 1):
if isinstance(content, Cell):
# compatible with write-only mode
cell = content
if cell.parent and cell.parent != self:
raise ValueError("Cells cannot be copied from other worksheets")
cell.parent = self
cell.column = col_idx
cell.row = row_idx
else:
cell = Cell(self, row=row_idx, column=col_idx, value=content)
self._cells[(row_idx, col_idx)] = cell
elif isinstance(iterable, dict):
for col_idx, content in iterable.items():
if isinstance(col_idx, basestring):
col_idx = column_index_from_string(col_idx)
cell = Cell(self, row=row_idx, column=col_idx, value=content)
self._cells[(row_idx, col_idx)] = cell
else:
self._invalid_row(iterable)
self._current_row = row_idx
def write(self, root):
if not hasattr(root, "findall"):
root = Element("xml")
# Remove any existing comment shapes
comments = root.findall("{%s}shape[@type='#_x0000_t202']" % vmlns)
for c in comments:
root.remove(c)
# check whether comments shape type already exists
shape_types = root.find("{%s}shapetype[@id='_x0000_t202']" % vmlns)
if not shape_types:
self.add_comment_shapetype(root)
for idx, (coord, comment) in enumerate(self.comments, 1026):
self.add_comment_shape(root, idx, coord, comment.height, comment.width)
return tostring(root)
def get_active_sheet(wb):
"""
Return the index of the active sheet.
If the sheet set to active is hidden return the next visible sheet or None
"""
visible_sheets = [idx for idx, sheet in enumerate(wb._sheets) if sheet.sheet_state == "visible"]
if not visible_sheets:
raise IndexError("At least one sheet must be visible")
idx = wb._active_sheet_index
sheet = wb.active
if sheet and sheet.sheet_state == "visible":
return idx
for idx in visible_sheets[idx:]:
wb.active = idx
return idx
return None
def _write_chartsheets(self):
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
sheet._id = idx
xml = tostring(sheet.to_tree())
self._archive.writestr(sheet.path[1:], xml)
self.manifest.append(sheet)
if sheet._drawing:
self._write_drawing(sheet._drawing)
rel = Relationship(type="drawing", Target=sheet._drawing.path)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
rels_path = get_rels_path(sheet.path[1:])
self._archive.writestr(rels_path, tostring(tree))
def hash_password(plaintext_password=''):
"""
Create a password hash from a given string for protecting a worksheet
only. This will not work for encrypting a workbook.
This method is based on the algorithm provided by
Daniel Rentz of OpenOffice and the PEAR package
Spreadsheet_Excel_Writer by Xavier Noguer <*****@*****.**>.
See also http://blogs.msdn.com/b/ericwhite/archive/2008/02/23/the-legacy-hashing-algorithm-in-open-xml.aspx
"""
password = 0x0000
for idx, char in enumerate(plaintext_password, 1):
value = ord(char) << idx
rotated_bits = value >> 15
value &= 0x7fff
password ^= (value | rotated_bits)
password ^= len(plaintext_password)
password ^= 0xCE4B
return str(hex(password)).upper()[2:]
def _normalise_numbers(self):
"""
Rebase custom numFmtIds with a floor of 164 when reading stylesheet
And index datetime formats
"""
date_formats = set()
custom = self.custom_formats
formats = self.number_formats
for idx, style in enumerate(self.cell_styles):
if style.numFmtId in custom:
fmt = custom[style.numFmtId]
if fmt in BUILTIN_FORMATS_REVERSE: # remove builtins
style.numFmtId = BUILTIN_FORMATS_REVERSE[fmt]
else:
style.numFmtId = formats.add(fmt) + 164
else:
fmt = builtin_format_code(style.numFmtId)
if is_date_format(fmt):
# Create an index of which styles refer to datetimes
date_formats.add(idx)
self.date_formats = date_formats
