def _write_chartsheets(self, archive):
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.worksheet.drawing import Drawing
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
if sheet._charts:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels())
)
rel = Relationship(type="drawing", target="/" + drawingpath)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
sheet.drawing.id = "rId{0}".format(len(rels))
archive.writestr(PACKAGE_CHARTSHEETS +
'/_rels/sheet%d.xml.rels' % idx, tostring(tree)
)
xml = tostring(sheet.to_tree())
archive.writestr(PACKAGE_CHARTSHEETS + '/sheet%d.xml' % idx, xml)
def read_worksheets(self):
for sheet, rel in self.parser.find_sheets():
if rel.target not in self.valid_files:
continue
if "chartsheet" in rel.Type:
self.read_chartsheet(sheet, rel)
continue
rels_path = get_rels_path(rel.target)
rels = RelationshipList()
if rels_path in self.valid_files:
rels = get_dependents(self.archive, rels_path)
if self.read_only:
ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target, self.shared_strings)
self.wb._sheets.append(ws)
continue
else:
fh = self.archive.open(rel.target)
ws = self.wb.create_sheet(sheet.name)
ws._rels = rels
ws_parser = WorksheetReader(ws, fh, self.shared_strings, self.data_only)
ws_parser.bind_all()
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = self.archive.read(r.target)
comment_sheet = CommentSheet.from_tree(fromstring(src))
for ref, comment in comment_sheet.comments:
ws[ref].comment = comment
# preserve link to VML file if VBA
if self.wb.vba_archive and ws.legacy_drawing:
ws.legacy_drawing = rels[ws.legacy_drawing].target
for t in ws_parser.tables:
src = self.archive.read(t)
xml = fromstring(src)
table = Table.from_tree(xml)
ws.add_table(table)
drawings = rels.find(SpreadsheetDrawing._rel_type)
for rel in drawings:
charts, images = find_images(self.archive, rel.target)
for c in charts:
ws.add_chart(c, c.anchor)
for im in images:
ws.add_image(im, im.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = self.archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = self.parser.pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state
def _write_chartsheets(self, archive):
from openpyxl.packaging.relationship import Relationship, RelationshipList
from openpyxl.worksheet.drawing import Drawing
for idx, sheet in enumerate(self.workbook.chartsheets, 1):
if sheet._charts:
drawing = SpreadsheetDrawing()
drawing.charts = sheet._charts
self.workbook._drawings.append(drawing)
drawing_id = len(self.workbook._drawings)
drawingpath = "{0}/drawing{1}.xml".format(
PACKAGE_DRAWINGS, drawing_id)
archive.writestr(drawingpath, tostring(drawing._write()))
archive.writestr(
"{0}/_rels/drawing{1}.xml.rels".format(
PACKAGE_DRAWINGS, drawing_id),
tostring(drawing._write_rels()))
rel = Relationship(type="drawing", target="/" + drawingpath)
rels = RelationshipList()
rels.append(rel)
tree = rels.to_tree()
sheet.drawing.id = "rId{0}".format(len(rels))
archive.writestr(
PACKAGE_CHARTSHEETS + '/_rels/sheet%d.xml.rels' % idx,
tostring(tree))
xml = tostring(sheet.to_tree())
archive.writestr(PACKAGE_CHARTSHEETS + '/sheet%d.xml' % idx, xml)
def __init__(self, ws, out=None):
self.ws = ws
if out is None:
out = create_temporary_file()
self.out = out
self._rels = RelationshipList()
self.xf = self.get_stream()
next(self.xf) # start generator
def write_rels(worksheet, comments_id=None):
"""Write relationships for the worksheet to xml."""
rels = RelationshipList(worksheet._rels)
# If there is an existing vml file that is preserved or extended then
# create its relation.
if worksheet.legacy_drawing is not None:
rel = Relationship("vmlDrawing",
id="anysvml",
target='/' + worksheet.legacy_drawing)
rels.append(rel)
# Comments
if worksheet._comment_count > 0:
rel = Relationship(type="comments",
id="comments",
target='/xl/comments%s.xml' % comments_id)
rels.append(rel)
if worksheet.legacy_drawing is None:
rel = Relationship(type="vmlDrawing",
id="anysvml",
target='/xl/drawings/commentsDrawing%s.vml' %
comments_id)
rels.append(rel)
return rels.to_tree()
def test_external_hyperlinks(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_hyperlinks()
assert ws['A1'].hyperlink.target == "../"
def test_tables(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="table", Id="rId1", Target="../tables/table1.xml")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_tables()
assert reader.tables == ["../tables/table1.xml"]
def test_internal_hyperlinks(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_hyperlinks()
assert ws['B4'].hyperlink.location == "'STP nn000TL-10, PKG 2.52'!A1"
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 _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 _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._path = "{0}{1}.xml".format(link._rel_type, idx)
arc_path = "{0}/{1}s/{2}".format(PACKAGE_XL, link._rel_type, link._path)
rels_path = get_rels_path(arc_path)
xml = link.to_tree()
archive.writestr(arc_path, tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
archive.writestr(rels_path, tostring(rels.to_tree()))
def __init__(
self,
write_only=False,
):
self._sheets = []
self._active_sheet_index = 0
self.defined_names = DefinedNameList()
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = []
self._drawings = []
self._charts = []
self._images = []
self.code_name = None
self.excel_base_date = CALENDAR_WINDOWS_1900
self.encoding = "utf-8"
if not self.write_only:
self._sheets.append(Worksheet(self))
self.rels = RelationshipList()
def __init__(
self,
write_only=False,
iso_dates=False,
):
self._sheets = []
self._pivots = []
self._active_sheet_index = 0
self.defined_names = DefinedNameList()
self._external_links = []
self.properties = DocumentProperties()
self.security = DocumentSecurity()
self.__write_only = write_only
self.shared_strings = IndexedList()
self._setup_styles()
self.loaded_theme = None
self.vba_archive = None
self.is_template = False
self._differential_styles = DifferentialStyleList()
self.code_name = None
self.epoch = CALENDAR_WINDOWS_1900
self.encoding = "utf-8"
self.iso_dates = iso_dates
if not self.write_only:
self._sheets.append(Worksheet(self))
self.rels = RelationshipList()
self.calculation = CalcProperties()
self.views = [BookView()]
def test_merged_hyperlinks(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_merged_cells()
reader.bind_hyperlinks()
assert ws.merged_cells == "G18:H18 G23:H24 A18:B18"
assert ws['A18'].hyperlink.display == 'http://test.com'
assert ws['B18'].hyperlink is None
def _setup(self):
self.row_dimensions = BoundDictionary("index", self._add_row)
self.column_dimensions = DimensionHolder(
worksheet=self, default_factory=self._add_column)
self.page_breaks = PageBreak()
self._charts = []
self._images = []
self._rels = RelationshipList()
self._drawing = None
self._comments = []
self._merged_cells = []
self._tables = []
self.data_validations = DataValidationList()
self._hyperlinks = []
self.sheet_state = 'visible'
self.page_setup = PrintPageSetup(worksheet=self)
self.print_options = PrintOptions()
self._print_rows = None
self._print_cols = None
self._print_area = None
self.page_margins = PageMargins()
self.views = SheetViewList()
self.protection = SheetProtection()
self._current_row = 0
self.auto_filter = AutoFilter()
self.sort_state = SortState()
self.paper_size = None
self.formula_attributes = {}
self.orientation = None
self.conditional_formatting = ConditionalFormattingList()
self.legacy_drawing = None
self.sheet_properties = WorksheetProperties()
self.sheet_format = SheetFormatProperties()
def _write_external_links(self, archive):
"""Write links to external workbooks"""
wb = self.workbook
for idx, link in enumerate(wb._external_links, 1):
link._path = "{0}{1}.xml".format(link._rel_type, idx)
arc_path = "{0}/{1}s/{2}".format(PACKAGE_XL, link._rel_type,
link._path)
rels_path = get_rels_path(arc_path)
xml = link.to_tree()
archive.writestr(arc_path, tostring(xml))
rels = RelationshipList()
rels.append(link.file_link)
archive.writestr(rels_path, tostring(rels.to_tree()))
def _write_rels(self, archive, manifest):
"""
Write the relevant child objects and add links
"""
if self.cache is None:
return
rels = RelationshipList()
r = Relationship(Type=self.cache.rel_type, Target=self.cache.path)
rels.append(r)
self.id = r.id
if self.cache.path[1:] not in archive.namelist():
self.cache._write(archive, manifest)
path = get_rels_path(self.path)
xml = tostring(rels.to_tree())
archive.writestr(path[1:], xml)
def _write_rels(self, archive, manifest):
"""
Write the relevant child objects and add links
"""
if self.records is None:
return
rels = RelationshipList()
r = Relationship(Type=self.records.rel_type, Target=self.records.path)
rels.append(r)
self.id = r.id
self.records._id = self._id
self.records._write(archive, manifest)
path = get_rels_path(self.path)
xml = tostring(rels.to_tree())
archive.writestr(path[1:], xml)
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 _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 test_external_hyperlinks(WorkSheetParser):
src = """
<sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<hyperlink xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
display="http://test.com" r:id="rId1" ref="A1"/>
</sheet>
"""
from openpyxl.packaging.relationship import Relationship, RelationshipList
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
parser = WorkSheetParser
parser.source = src
parser.ws._rels = rels
parser.parse()
assert parser.ws['A1'].hyperlink.target == "../"
def test_tables(WorkSheetParser):
src = """
<sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<tableParts count="1">
<tablePart r:id="rId1"/>
</tableParts>
</sheet>
"""
parser = WorkSheetParser
r = Relationship(type="table", Id="rId1", Target="../tables/table1.xml")
rels = RelationshipList()
rels.append(r)
parser.ws._rels = rels
parser.source = src
parser.parse()
assert parser.tables == ["../tables/table1.xml"]
def test_merged_hyperlinks(self, PrimedWorksheetReader):
reader = PrimedWorksheetReader
reader.bind_cells()
ws = reader.ws
r = Relationship(type="hyperlink", Id="rId1", Target="../")
rels = RelationshipList()
rels.append(r)
ws._rels = rels
reader.bind_merged_cells()
reader.bind_hyperlinks()
assert ws.merged_cells == "G18:H18 G23:H24 A18:B18"
assert ws['A18'].hyperlink.display == 'http://test.com'
assert ws['B18'].hyperlink is None
# Link referencing H24 should be placed on G23 because H24 is a merged cell
# and G23 is the top-left cell in the merged range
assert ws["G23"].hyperlink.tooltip == "openpyxl"
assert ws["H24"].hyperlink is None
def write_rels(worksheet, comments_id=None, vba_controls_id=None):
"""Write relationships for the worksheet to xml."""
rels = RelationshipList(worksheet._rels)
# VBA
if worksheet.vba_controls is not None:
rel = Relationship("vmlDrawing", id=worksheet.vba_controls,
target='/xl/drawings/vmlDrawing%s.vml' % vba_controls_id)
rels.append(rel)
# Comments
if worksheet._comment_count > 0:
rel = Relationship(type="comments", id="comments",
target='/xl/comments%s.xml' % comments_id)
rels.append(rel)
if worksheet.vba_controls is None:
rel = Relationship(type="vmlDrawing", id="commentsvml",
target='/xl/drawings/commentsDrawing%s.vml' % comments_id)
rels.append(rel)
return rels.to_tree()
def write_rels(worksheet, comments_id=None):
"""Write relationships for the worksheet to xml."""
rels = RelationshipList(worksheet._rels)
# If there is an existing vml file that is preserved or extended then
# create its relation.
if worksheet.legacy_drawing is not None:
rel = Relationship(type="vmlDrawing", Id="anysvml", Target='/' + worksheet.legacy_drawing)
rels.append(rel)
# Comments
if worksheet._comments:
rel = Relationship(type="comments", Id="comments",
Target='/xl/comments%s.xml' % comments_id)
rels.append(rel)
if worksheet.legacy_drawing is None:
rel = Relationship(type="vmlDrawing", Id="anysvml",
Target='/xl/drawings/commentsDrawing%s.vml' % comments_id)
rels.append(rel)
return rels.to_tree()
class WorksheetWriter:
def __init__(self, ws, out=None):
self.ws = ws
self.ws._hyperlinks = []
self.ws._comments = []
if out is None:
out = create_temporary_file()
self.out = out
self._rels = RelationshipList()
self.xf = self.get_stream()
next(self.xf) # start generator
def write_properties(self):
props = self.ws.sheet_properties
self.xf.send(props.to_tree())
def write_dimensions(self):
"""
Write worksheet size if known
"""
ref = getattr(self.ws, 'calculate_dimension', None)
if ref:
dim = SheetDimension(ref())
self.xf.send(dim.to_tree())
def write_format(self):
self.ws.sheet_format.outlineLevelCol = self.ws.column_dimensions.max_outline
fmt = self.ws.sheet_format
self.xf.send(fmt.to_tree())
def write_views(self):
views = self.ws.views
self.xf.send(views.to_tree())
def write_cols(self):
cols = self.ws.column_dimensions
self.xf.send(cols.to_tree())
def write_top(self):
"""
Write all elements up to rows:
properties
dimensions
views
format
cols
"""
self.write_properties()
self.write_dimensions()
self.write_views()
self.write_format()
self.write_cols()
def rows(self):
"""Return all rows, and any cells that they contain"""
# order cells by row
rows = defaultdict(list)
for (row, col), cell in sorted(self.ws._cells.items()):
rows[row].append(cell)
# add empty rows if styling has been applied
for row in self.ws.row_dimensions.keys() - rows.keys():
rows[row] = []
return sorted(rows.items())
def write_rows(self):
xf = self.xf.send(True)
with xf.element("sheetData"):
for row_idx, row in self.rows():
self.write_row(xf, row, row_idx)
self.xf.send(None) # return control to generator
def write_row(self, xf, row, row_idx):
attrs = {'r': f"{row_idx}"}
dims = self.ws.row_dimensions
attrs.update(dims.get(row_idx, {}))
with xf.element("row", attrs):
for cell in row:
if cell._comment is not None:
comment = CommentRecord.from_cell(cell)
self.ws._comments.append(comment)
if (cell._value is None and not cell.has_style
and not cell._comment):
continue
write_cell(xf, self.ws, cell, cell.has_style)
def write_protection(self):
prot = self.ws.protection
if prot:
self.xf.send(prot.to_tree())
def write_scenarios(self):
scenarios = self.ws.scenarios
if scenarios:
self.xf.send(scenarios.to_tree())
def write_filter(self):
flt = self.ws.auto_filter
if flt:
self.xf.send(flt.to_tree())
def write_sort(self):
"""
As per discusion with the OOXML Working Group global sort state is not required.
openpyxl never reads it from existing files
"""
pass
def write_merged_cells(self):
merged = self.ws.merged_cells
if merged:
cells = [MergeCell(str(ref)) for ref in self.ws.merged_cells]
self.xf.send(MergeCells(mergeCell=cells).to_tree())
def write_formatting(self):
df = DifferentialStyle()
wb = self.ws.parent
for cf in self.ws.conditional_formatting:
for rule in cf.rules:
if rule.dxf and rule.dxf != df:
rule.dxfId = wb._differential_styles.add(rule.dxf)
self.xf.send(cf.to_tree())
def write_validations(self):
dv = self.ws.data_validations
if dv:
self.xf.send(dv.to_tree())
def write_hyperlinks(self):
links = HyperlinkList()
for link in self.ws._hyperlinks:
if link.target:
rel = Relationship(type="hyperlink",
TargetMode="External",
Target=link.target)
self._rels.append(rel)
link.id = rel.id
links.hyperlink.append(link)
if links:
self.xf.send(links.to_tree())
def write_print(self):
print_options = self.ws.print_options
if print_options:
self.xf.send(print_options.to_tree())
def write_margins(self):
margins = self.ws.page_margins
if margins:
self.xf.send(margins.to_tree())
def write_page(self):
setup = self.ws.page_setup
if setup:
self.xf.send(setup.to_tree())
def write_header(self):
hf = self.ws.HeaderFooter
if hf:
self.xf.send(hf.to_tree())
def write_breaks(self):
brks = (self.ws.row_breaks, self.ws.col_breaks)
for brk in brks:
if brk:
self.xf.send(brk.to_tree())
def write_drawings(self):
if self.ws._charts or self.ws._images:
rel = Relationship(type="drawing", Target="")
self._rels.append(rel)
drawing = Related()
drawing.id = rel.id
self.xf.send(drawing.to_tree("drawing"))
def write_legacy(self):
"""
Comments & VBA controls use VML and require an additional element
that is no longer in the specification.
"""
if (self.ws.legacy_drawing is not None or self.ws._comments):
legacy = Related(id="anysvml")
self.xf.send(legacy.to_tree("legacyDrawing"))
def write_tables(self):
tables = TablePartList()
for table in self.ws._tables.values():
if not table.tableColumns:
table._initialise_columns()
if table.headerRowCount:
try:
row = self.ws[table.ref][0]
for cell, col in zip(row, table.tableColumns):
if cell.data_type != "s":
warn(
"File may not be readable: column headings must be strings."
)
col.name = str(cell.value)
except TypeError:
warn(
"Column headings are missing, file may not be readable"
)
rel = Relationship(Type=table._rel_type, Target="")
self._rels.append(rel)
table._rel_id = rel.Id
tables.append(Related(id=rel.Id))
if tables:
self.xf.send(tables.to_tree())
def get_stream(self):
with xmlfile(self.out) as xf:
with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
try:
while True:
el = (yield)
if el is True:
yield xf
elif el is None: # et_xmlfile chokes
continue
else:
xf.write(el)
except GeneratorExit:
pass
def write_tail(self):
"""
Write all elements after the rows
calc properties
protection
protected ranges #
scenarios
filters
sorts # always ignored
data consolidation #
custom views #
merged cells
phonetic properties #
conditional formatting
data validation
hyperlinks
print options
page margins
page setup
header
row breaks
col breaks
custom properties #
cell watches #
ignored errors #
smart tags #
drawing
drawingHF #
background #
OLE objects #
controls #
web publishing #
tables
"""
self.write_protection()
self.write_scenarios()
self.write_filter()
self.write_merged_cells()
self.write_formatting()
self.write_validations()
self.write_hyperlinks()
self.write_print()
self.write_margins()
self.write_page()
self.write_header()
self.write_breaks()
self.write_drawings()
self.write_legacy()
self.write_tables()
def write(self):
"""
High level
"""
self.write_top()
self.write_rows()
self.write_tail()
self.close()
def close(self):
"""
Close the context manager
"""
if self.xf:
self.xf.close()
def read(self):
"""
Close the context manager and return serialised XML
"""
self.close()
if isinstance(self.out, BytesIO):
return self.out.getvalue()
with open(self.out, "rb") as src:
out = src.read()
return out
def cleanup(self):
"""
Remove tempfile
"""
os.remove(self.out)
ALL_TEMP_FILES.remove(self.out)
class WorkbookWriter:
def __init__(self, wb):
self.wb = wb
self.rels = RelationshipList()
self.package = WorkbookPackage()
self.package.workbookProtection = wb.security
self.package.calcPr = wb.calculation
def write_properties(self):
props = WorkbookProperties(
) # needs a mapping to the workbook for preservation
if self.wb.code_name is not None:
props.codeName = self.wb.code_name
if self.wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
self.package.workbookPr = props
def write_worksheets(self):
for idx, sheet in enumerate(self.wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title,
sheetId=idx,
id="rId{0}".format(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 write_refs(self):
for link in self.wb._external_links:
# need to match a counter with a workbook's relations
rId = len(self.wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
self.rels.append(rel)
ext = ExternalReference(id=rel.id)
self.package.externalReferences.append(ext)
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"{0}!{1}".format(
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"{0}!{1}".format(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"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area
])
defined_names.append(name)
self.package.definedNames = defined_names
def write_pivots(self):
pivot_caches = set()
for pivot in self.wb._pivots:
if pivot.cache not in pivot_caches:
pivot_caches.add(pivot.cache)
c = PivotCache(cacheId=pivot.cacheId)
self.package.pivotCaches.append(c)
rel = Relationship(Type=pivot.cache.rel_type,
Target=pivot.cache.path)
self.rels.append(rel)
c.id = rel.id
#self.wb._pivots = [] # reset
def write_views(self):
active = get_active_sheet(self.wb)
if self.wb.views:
self.wb.views[0].activeTab = active
self.package.bookViews = self.wb.views
def write(self):
"""Write the core workbook xml."""
self.write_properties()
self.write_worksheets()
self.write_names()
self.write_pivots()
self.write_views()
self.write_refs()
return tostring(self.package.to_tree())
def write_rels(self):
"""Write the workbook relationships xml."""
styles = Relationship(type='styles', Target='styles.xml')
self.rels.append(styles)
theme = Relationship(type='theme', Target='theme/theme1.xml')
self.rels.append(theme)
if self.wb.vba_archive:
vba = Relationship(type='', Target='vbaProject.bin')
vba.Type = 'http://schemas.microsoft.com/office/2006/relationships/vbaProject'
self.rels.append(vba)
return tostring(self.rels.to_tree())
def write_root_rels(self):
"""Write the package relationships"""
rels = RelationshipList()
rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
rels.append(rel)
rel = Relationship(Type="%s/metadata/core-properties" % PKG_REL_NS,
Target=ARC_CORE)
rels.append(rel)
rel = Relationship(type="extended-properties", Target=ARC_APP)
rels.append(rel)
if self.wb.vba_archive is not None:
# See if there was a customUI relation and reuse it
xml = fromstring(self.wb.vba_archive.read(ARC_ROOT_RELS))
root_rels = RelationshipList.from_tree(xml)
for rel in root_rels.find(CUSTOMUI_NS):
rels.append(rel)
return tostring(rels.to_tree())
def write_root_rels(self):
"""Write the package relationships"""
rels = RelationshipList()
rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
rels.append(rel)
rel = Relationship(Type="%s/metadata/core-properties" % PKG_REL_NS,
Target=ARC_CORE)
rels.append(rel)
rel = Relationship(type="extended-properties", Target=ARC_APP)
rels.append(rel)
if self.wb.vba_archive is not None:
# See if there was a customUI relation and reuse it
xml = fromstring(self.wb.vba_archive.read(ARC_ROOT_RELS))
root_rels = RelationshipList.from_tree(xml)
for rel in root_rels.find(CUSTOMUI_NS):
rels.append(rel)
return tostring(rels.to_tree())
def write_root_rels(workbook):
"""Write the relationships xml."""
rels = RelationshipList()
rel = Relationship(type="officeDocument", Target=ARC_WORKBOOK)
rels.append(rel)
rel = Relationship(Target=ARC_CORE, Type="%s/metadata/core-properties" % PKG_REL_NS)
rels.append(rel)
rel = Relationship(type="extended-properties", Target=ARC_APP)
rels.append(rel)
if workbook.vba_archive is not None:
# See if there was a customUI relation and reuse it
xml = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
root_rels = RelationshipList.from_tree(xml)
for rel in root_rels.find(CUSTOMUI_NS):
rels.append(rel)
return tostring(rels.to_tree())
def write_root_rels(workbook):
"""Write the relationships xml."""
rels = RelationshipList()
rel = Relationship(type="officeDocument", target=ARC_WORKBOOK, id="rId1")
rels.append(rel)
rel = Relationship("", target=ARC_CORE, id='rId2',)
rel.type = "%s/metadata/core-properties" % PKG_REL_NS
rels.append(rel)
rel = Relationship("extended-properties", target=ARC_APP, id='rId3')
rels.append(rel)
if workbook.vba_archive is not None:
relation_tag = '{%s}Relationship' % PKG_REL_NS
# See if there was a customUI relation and reuse its id
arc = fromstring(workbook.vba_archive.read(ARC_ROOT_RELS))
rel_tags = arc.findall(relation_tag)
rId = None
for rel in rel_tags:
if rel.get('Target') == ARC_CUSTOM_UI:
rId = rel.get('Id')
break
if rId is not None:
vba = Relationship("", target=ARC_CUSTOM_UI, id=rId)
vba.type = CUSTOMUI_NS
rels.append(vba)
return tostring(rels.to_tree())
def write_workbook_rels(workbook):
"""Write the workbook relationships xml."""
rels = RelationshipList()
rId = 0
for idx, _ in enumerate(workbook.worksheets, 1):
rId += 1
rel = Relationship(type='worksheet', target='worksheets/sheet%s.xml' % idx, id='rId%d' % rId)
rels.append(rel)
for idx, _ in enumerate(workbook.chartsheets, 1):
rId += 1
rel = Relationship(type='chartsheet', target='chartsheets/sheet%s.xml' % idx, id='rId%d' % rId)
rels.append(rel)
rId += 1
strings = Relationship(type='sharedStrings', target='sharedStrings.xml', id='rId%d' % rId)
rels.append(strings)
rId += 1
styles = Relationship(type='styles', target='styles.xml', id='rId%d' % rId)
rels.append(styles)
rId += 1
theme = Relationship(type='theme', target='theme/theme1.xml', id='rId%d' % rId)
rels.append(theme)
if workbook.vba_archive:
rId += 1
vba = Relationship(type='vbaProject', target='vbaProject.bin', id='rId%d' % rId)
vba.type ='http://schemas.microsoft.com/office/2006/relationships/vbaProject'
rels.append(vba)
external_links = workbook._external_links
if external_links:
for idx, link in enumerate(external_links, 1):
ext = Relationship(type='externalLink',
target='externalLinks/externalLink%d.xml' % idx,
id='rId%d' % (rId +idx))
rels.append(ext)
return tostring(rels.to_tree())
def __init__(self, wb):
self.wb = wb
self.rels = RelationshipList()
self.package = WorkbookPackage()
self.package.workbookProtection = wb.security
self.package.calcPr = wb.calculation
def _write_rels(self):
rels = RelationshipList()
rels.Relationship = self._rels
return rels.to_tree()
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties(
) # needs a mapping to the workbook for preservation
if wb.code_name is not None:
props.codeName = wb.code_name
if wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
root.workbookPr = props
# book views
active = get_active_sheet(wb)
view = BookView(activeTab=active)
root.bookViews = [view]
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title,
sheetId=idx,
id="rId{0}".format(idx))
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError(
"The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
for link in wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
wb.rels.append(rel)
ext = ExternalReference(id=rel.id)
root.externalReferences.append(ext)
# Defined names
defined_names = copy(
wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase',
localSheetId=idx,
hidden=True)
name.value = u"{0}!{1}".format(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"{0}!{1}".format(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"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area
])
defined_names.append(name)
root.definedNames = defined_names
root.calcPr = CalcProperties(calcId=124519, fullCalcOnLoad=True)
return tostring(root.to_tree())
def write_worksheet(worksheet):
"""Write a worksheet to an xml file."""
ws = worksheet
ws._rels = RelationshipList()
ws._hyperlinks = []
out = BytesIO()
with xmlfile(out) as xf:
with xf.element('worksheet', xmlns=SHEET_MAIN_NS):
props = ws.sheet_properties.to_tree()
xf.write(props)
dim = SheetDimension(ref=ws.calculate_dimension())
xf.write(dim.to_tree())
xf.write(ws.views.to_tree())
cols = ws.column_dimensions.to_tree()
ws.sheet_format.outlineLevelCol = ws.column_dimensions.max_outline
xf.write(ws.sheet_format.to_tree())
if cols is not None:
xf.write(cols)
# write data
write_rows(xf, ws)
if ws.protection:
xf.write(ws.protection.to_tree())
if ws.auto_filter:
xf.write(ws.auto_filter.to_tree())
if ws.sort_state:
xf.write(ws.sort_state.to_tree())
merge = write_mergecells(ws)
if merge is not None:
xf.write(merge)
cfs = write_conditional_formatting(ws)
for cf in cfs:
xf.write(cf)
if ws.data_validations:
xf.write(ws.data_validations.to_tree())
hyper = write_hyperlinks(ws)
if hyper:
xf.write(hyper.to_tree())
options = ws.print_options
if dict(options):
new_element = options.to_tree()
xf.write(new_element)
margins = ws.page_margins.to_tree()
xf.write(margins)
setup = ws.page_setup
if dict(setup):
new_element = setup.to_tree()
xf.write(new_element)
if bool(ws.HeaderFooter):
xf.write(ws.HeaderFooter.to_tree())
if ws.page_breaks:
xf.write(ws.page_breaks.to_tree())
drawing = write_drawing(ws)
if drawing is not None:
xf.write(drawing)
# if there is an existing vml file associated with this sheet or if there
# are any comments we need to add a legacyDrawing relation to the vml file.
if (ws.legacy_drawing is not None or ws._comments):
legacyDrawing = Related(id="anysvml")
xml = legacyDrawing.to_tree("legacyDrawing")
xf.write(xml)
tables = _add_table_headers(ws)
if tables:
xf.write(tables.to_tree())
xml = out.getvalue()
out.close()
return xml
def _write_rels(self):
rels = RelationshipList()
rels.Relationship = self._rels
return rels.to_tree()
def read_worksheets(self):
comment_warning = """Cell '{0}':{1} is part of a merged range but has a comment which will be removed because merged cells cannot contain any data."""
for sheet, rel in self.parser.find_sheets():
if rel.target not in self.valid_files:
continue
if "chartsheet" in rel.Type:
self.read_chartsheet(sheet, rel)
continue
rels_path = get_rels_path(rel.target)
rels = RelationshipList()
if rels_path in self.valid_files:
rels = get_dependents(self.archive, rels_path)
# Used Worksheet and WorksheetReader for all scenarios,
# not distinguishing between read_only or not
fh = self.archive.open(rel.target)
ws = self.wb.create_sheet(sheet.name)
ws._rels = rels
ws_parser = WorksheetReader(ws, fh, self.shared_strings,
self.data_only, self.read_only)
ws_parser.bind_all()
# assign any comments to cells
for r in rels.find(COMMENTS_NS):
src = self.archive.read(r.target)
comment_sheet = CommentSheet.from_tree(fromstring(src))
for ref, comment in comment_sheet.comments:
try:
ws[ref].comment = comment
except AttributeError:
c = ws[ref]
if isinstance(c, MergedCell):
warnings.warn(
comment_warning.format(ws.title, c.coordinate))
continue
# preserve link to VML file if VBA
if self.wb.vba_archive and ws.legacy_drawing:
ws.legacy_drawing = rels[ws.legacy_drawing].target
for t in ws_parser.tables:
src = self.archive.read(t)
xml = fromstring(src)
table = Table.from_tree(xml)
ws.add_table(table)
drawings = rels.find(SpreadsheetDrawing._rel_type)
for rel in drawings:
charts, images = find_images(self.archive, rel.target)
for c in charts:
ws.add_chart(c, c.anchor)
for im in images:
ws.add_image(im, im.anchor)
pivot_rel = rels.find(TableDefinition.rel_type)
for r in pivot_rel:
pivot_path = r.Target
src = self.archive.read(pivot_path)
tree = fromstring(src)
pivot = TableDefinition.from_tree(tree)
pivot.cache = self.parser.pivot_caches[pivot.cacheId]
ws.add_pivot(pivot)
ws.sheet_state = sheet.state