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 = write_sheetPr(self.sheet_properties) xf.write(pr) views = Element("sheetViews") views.append(self.sheet_view.to_tree()) xf.write(views) xf.write(write_format(self)) cols = write_cols(self) if cols is not None: xf.write(cols) with xf.element("sheetData"): try: while True: r = (yield) xf.write(r) except GeneratorExit: pass af = write_autofilter(self) if af is not None: xf.write(af) if self._comments: comments = Element("legacyDrawing", {"{%s}id" % REL_NS: "commentsvml"}) xf.write(comments)
def _writer(doc): with xmlfile(doc) as xf: with xf.element('sheetData'): try: while True: body = (yield) xf.write(body) except GeneratorExit: pass
def test_write_formula(worksheet, write_rows): ws = worksheet ws.cell('F1').value = 10 ws.cell('F2').value = 32 ws.cell('F3').value = '=F1+F2' ws.cell('A4').value = '=A1+A2+A3' ws.formula_attributes['A4'] = {'t': 'shared', 'ref': 'A4:C4', 'si': '0'} ws.cell('B4').value = '=1' ws.formula_attributes['B4'] = {'t': 'shared', 'si': '0'} ws.cell('C4').value = '=1' ws.formula_attributes['C4'] = {'t': 'shared', 'si': '0'} out = BytesIO() with xmlfile(out) as xf: write_rows(xf, ws) xml = out.getvalue() expected = """ <sheetData> <row r="1" spans="1:6"> <c r="F1" t="n"> <v>10</v> </c> </row> <row r="2" spans="1:6"> <c r="F2" t="n"> <v>32</v> </c> </row> <row r="3" spans="1:6"> <c r="F3"> <f>F1+F2</f> <v></v> </c> </row> <row r="4" spans="1:6"> <c r="A4"> <f ref="A4:C4" si="0" t="shared">A1+A2+A3</f> <v></v> </c> <c r="B4"> <f si="0" t="shared"></f> <v></v> </c> <c r="C4"> <f si="0" t="shared"></f> <v></v> </c> </row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_formula(worksheet, write_rows): ws = worksheet ws.cell("F1").value = 10 ws.cell("F2").value = 32 ws.cell("F3").value = "=F1+F2" ws.cell("A4").value = "=A1+A2+A3" ws.formula_attributes["A4"] = {"t": "shared", "ref": "A4:C4", "si": "0"} ws.cell("B4").value = "=1" ws.formula_attributes["B4"] = {"t": "shared", "si": "0"} ws.cell("C4").value = "=1" ws.formula_attributes["C4"] = {"t": "shared", "si": "0"} out = BytesIO() with xmlfile(out) as xf: write_rows(xf, ws) xml = out.getvalue() expected = """ <sheetData> <row r="1" spans="1:6"> <c r="F1" t="n"> <v>10</v> </c> </row> <row r="2" spans="1:6"> <c r="F2" t="n"> <v>32</v> </c> </row> <row r="3" spans="1:6"> <c r="F3"> <f>F1+F2</f> <v></v> </c> </row> <row r="4" spans="1:6"> <c r="A4"> <f ref="A4:C4" si="0" t="shared">A1+A2+A3</f> <v></v> </c> <c r="B4"> <f si="0" t="shared"></f> <v></v> </c> <c r="C4"> <f si="0" t="shared"></f> <v></v> </c> </row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_comment(worksheet, write_cell_implementation): write_cell = write_cell_implementation from openpyxl.comments import Comment ws = worksheet cell = ws['A1'] cell.comment = Comment("test comment", "test author") out = BytesIO() with xmlfile(out) as xf: write_cell(xf, ws, cell, False) assert len(ws._comments) == 1
def test_write_hyperlink(worksheet, write_cell_implementation): write_cell = write_cell_implementation ws = worksheet cell = ws['A1'] cell.value = "test" cell.hyperlink = "http://www.test.com" out = BytesIO() with xmlfile(out) as xf: write_cell(xf, ws, cell, cell.has_style) assert len(worksheet._hyperlinks) == 1
def _writer(doc): with xmlfile(doc) as xf: with xf.element('sheetData'): try: while True: row = (yield) with xf.element("row"): for cell in row: with xf.element("v"): xf.write(str(cell)) except GeneratorExit: pass
def test_write_cell(worksheet, write_cell_implementation, value, expected): write_cell = write_cell_implementation ws = worksheet cell = ws['A1'] cell.value = value out = BytesIO() with xmlfile(out) as xf: write_cell(xf, ws, cell, cell.has_style) xml = out.getvalue() diff = compare_xml(xml, expected) assert diff is None, diff
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"): try: while True: r = (yield) xf.write(r) except GeneratorExit: pass if self.protection.sheet: xf.write(worksheet.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)
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_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) views = Element('sheetViews') views.append(self.sheet_view.to_tree()) xf.write(views) xf.write(write_format(self)) cols = write_cols(self) if cols is not None: xf.write(cols) with xf.element("sheetData"): try: while True: r = (yield) xf.write(r) except GeneratorExit: pass if self.protection.sheet: xf.write(worksheet.protection.to_tree()) af = write_autofilter(self) if af is not None: xf.write(af) dv = write_datavalidation(self) if dv is not None: xf.write(dv) drawing = write_drawing(self) if drawing is not None: xf.write(drawing) if self._comments: legacyDrawing = Related(id="commentsvml") xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml)
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) views = Element('sheetViews') views.append(self.sheet_view.to_tree()) xf.write(views) xf.write(write_format(self)) cols = write_cols(self) if cols is not None: xf.write(cols) with xf.element("sheetData"): try: while True: r = (yield) xf.write(r) except GeneratorExit: pass if self.protection.sheet: xf.write(worksheet.protection.to_tree()) af = write_autofilter(self) if af is not None: xf.write(af) dv = write_datavalidation(self) if dv is not None: xf.write(dv) 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 write_string_table(string_table): """Write the string table xml.""" out = BytesIO() with xmlfile(out) as xf: with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)): for key in string_table: el = Element('si') text = SubElement(el, 't') text.text = key if key.strip() != key: text.set(PRESERVE_SPACE, 'preserve') xf.write(el) return out.getvalue()
def write_string_table(string_table): """Write the string table xml.""" out = BytesIO() with xmlfile(out) as xf: with xf.element("sst", xmlns=SHEET_MAIN_NS, uniqueCount="%d" % len(string_table)): for key in string_table: el = Element('si') if key.strip() != key: el.set(PRESERVE_SPACE, 'preserve') text = SubElement(el, 't') text.text = key xf.write(el) return out.getvalue()
def test_write_formula(worksheet, write_rows): ws = worksheet ws['F1'] = 10 ws['F2'] = 32 ws['F3'] = '=F1+F2' ws['A4'] = '=A1+A2+A3' ws['B4'] = "=SUM(A10:A14*B10:B14)" ws.formula_attributes['B4'] = {'t': 'array', 'ref': 'B4:B8'} out = BytesIO() with xmlfile(out) as xf: write_rows(xf, ws) xml = out.getvalue() expected = """ <sheetData> <row r="1" spans="1:6"> <c r="F1" t="n"> <v>10</v> </c> </row> <row r="2" spans="1:6"> <c r="F2" t="n"> <v>32</v> </c> </row> <row r="3" spans="1:6"> <c r="F3"> <f>F1+F2</f> <v></v> </c> </row> <row r="4" spans="1:6"> <c r="A4"> <f>A1+A2+A3</f> <v></v> </c> <c r="B4"> <f ref="B4:B8" t="array">SUM(A10:A14*B10:B14)</f> <v></v> </c> </row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_date(worksheet, write_cell_implementation, value, expected, iso_dates): from openpyxl.cell import Cell write_cell = write_cell_implementation ws = worksheet cell = ws['A1'] cell.value = value cell.parent.parent.iso_dates = iso_dates out = BytesIO() with xmlfile(out) as xf: write_cell(xf, ws, cell, cell.has_style) xml = out.getvalue() diff = compare_xml(xml, expected) assert diff is None, diff
def test_whitespace(worksheet, write_cell_implementation): write_cell = write_cell_implementation ws = worksheet cell = ws['A1'] cell.value = " whitespace " out = BytesIO() with xmlfile(out) as xf: write_cell(xf, ws, cell) expected = """ <c t="inlineStr" r="A1"> <is> <t xml:space="preserve"> whitespace </t> </is> </c>""" xml = out.getvalue() diff = compare_xml(xml, expected) assert diff is None, diff
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 = write_sheetPr(self.sheet_properties) xf.write(pr) views = Element('sheetViews') views.append(self.sheet_view.to_tree()) xf.write(views) xf.write(write_format(self)) cols = write_cols(self) if cols is not None: xf.write(cols) with xf.element("sheetData"): try: while True: r = (yield) xf.write(r) except GeneratorExit: pass if self.protection.sheet: prot = Element('sheetProtection', dict(self.protection)) xf.write(prot) af = write_autofilter(self) if af is not None: xf.write(af) dv = write_datavalidation(self) if dv is not None: xf.write(dv) if self._comments: comments = Element('legacyDrawing', {'{%s}id' % REL_NS: 'commentsvml'}) xf.write(comments)
def test_write_height(worksheet, write_rows): ws = worksheet ws.cell("F1").value = 10 ws.row_dimensions[ws.cell("F1").row].height = 30 ws.row_dimensions[ws.cell("F2").row].height = 30 ws._garbage_collect() out = BytesIO() with xmlfile(out) as xf: write_rows(xf, ws) xml = out.getvalue() expected = """ <sheetData> <row customHeight="1" ht="30" r="1" spans="1:6"> <c r="F1" t="n"> <v>10</v> </c> </row> <row customHeight="1" ht="30" r="2" spans="1:6"></row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_height(worksheet, write_rows): ws = worksheet ws.cell('F1').value = 10 ws.row_dimensions[ws.cell('F1').row].height = 30 ws.row_dimensions[ws.cell('F2').row].height = 30 ws._garbage_collect() out = BytesIO() with xmlfile(out) as xf: write_rows(xf, ws) xml = out.getvalue() expected = """ <sheetData> <row customHeight="1" ht="30" r="1" spans="1:6"> <c r="F1" t="n"> <v>10</v> </c> </row> <row customHeight="1" ht="30" r="2" spans="1:6"></row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_write_height(worksheet, write_rows): from openpyxl.worksheet.dimensions import RowDimension ws = worksheet ws['F1'] = 10 ws.row_dimensions[1] = RowDimension(ws, height=30) ws.row_dimensions[2] = RowDimension(ws, height=30) out = BytesIO() with xmlfile(out) as xf: write_rows(xf, ws) xml = out.getvalue() expected = """ <sheetData> <row customHeight="1" ht="30" r="1" spans="1:6"> <c r="F1" t="n"> <v>10</v> </c> </row> <row customHeight="1" ht="30" r="2" spans="1:6"></row> </sheetData> """ diff = compare_xml(xml, expected) assert diff is None, diff
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 write_worksheet(worksheet, shared_strings): """Write a worksheet to an xml file.""" if LXML is True: from .lxml_worksheet import write_cell, write_rows else: from .etree_worksheet import write_cell, write_rows out = BytesIO() with xmlfile(out) as xf: with xf.element('worksheet', xmlns=SHEET_MAIN_NS): props = write_properties(worksheet) xf.write(props) dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()}) xf.write(dim) views = Element('sheetViews') views.append(worksheet.sheet_view.to_tree()) xf.write(views) xf.write(write_format(worksheet)) cols = write_cols(worksheet) if cols is not None: xf.write(cols) write_rows(xf, worksheet) if worksheet.protection.sheet: prot = Element('sheetProtection', dict(worksheet.protection)) xf.write(prot) af = write_autofilter(worksheet) if af is not None: xf.write(af) merge = write_mergecells(worksheet) if merge is not None: xf.write(merge) cfs = write_conditional_formatting(worksheet) for cf in cfs: xf.write(cf) dv = write_datavalidation(worksheet) if dv is not None: xf.write(dv) hyper = write_hyperlinks(worksheet) if hyper is not None: xf.write(hyper) options = worksheet.print_options if len(dict(options)) > 0: new_element = options.write_xml_element() xf.write(new_element) del new_element margins = Element('pageMargins', dict(worksheet.page_margins)) xf.write(margins) del margins setup = worksheet.page_setup if len(dict(setup)) > 0: new_element = setup.write_xml_element() xf.write(new_element) del new_element hf = write_header_footer(worksheet) if hf is not None: xf.write(hf) if worksheet._charts or worksheet._images: drawing = Element('drawing', {'{%s}id' % REL_NS: 'rId1'}) xf.write(drawing) del drawing # If vba is being preserved then add a legacyDrawing element so # that any controls can be drawn. if worksheet.vba_controls is not None: xml = Element("{%s}legacyDrawing" % SHEET_MAIN_NS, {"{%s}id" % REL_NS: worksheet.vba_controls}) xf.write(xml) pb = write_pagebreaks(worksheet) if pb is not None: xf.write(pb) # add a legacyDrawing so that excel can draw comments if worksheet._comment_count > 0: comments = Element('legacyDrawing', {'{%s}id' % REL_NS: 'commentsvml'}) xf.write(comments) xml = out.getvalue() out.close() return xml
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)
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.sheet: 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_worksheet(worksheet, shared_strings): """Write a worksheet to an xml file.""" ws = worksheet ws._rels = [] ws._hyperlinks = [] if LXML is True: from .lxml_worksheet import write_cell, write_rows else: from .etree_worksheet import write_cell, write_rows out = BytesIO() with xmlfile(out) as xf: with xf.element('worksheet', xmlns=SHEET_MAIN_NS): props = worksheet.sheet_properties.to_tree() xf.write(props) dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()}) xf.write(dim) views = Element('sheetViews') views.append(worksheet.sheet_view.to_tree()) xf.write(views) xf.write(write_format(worksheet)) cols = write_cols(worksheet) if cols is not None: xf.write(cols) write_rows(xf, worksheet) if worksheet.protection.sheet: xf.write(worksheet.protection.to_tree()) af = write_autofilter(worksheet) if af is not None: xf.write(af) merge = write_mergecells(worksheet) if merge is not None: xf.write(merge) cfs = write_conditional_formatting(worksheet) for cf in cfs: xf.write(cf) dv = write_datavalidation(worksheet) if dv is not None: xf.write(dv) hyper = write_hyperlinks(worksheet) if hyper is not None: xf.write(hyper) options = worksheet.print_options if dict(options): new_element = options.to_tree() xf.write(new_element) margins = worksheet.page_margins.to_tree() xf.write(margins) setup = worksheet.page_setup if dict(setup): new_element = setup.to_tree() xf.write(new_element) hf = write_header_footer(worksheet) if hf is not None: xf.write(hf) drawing = write_drawing(worksheet) 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 worksheet.legacy_drawing is not None or worksheet._comment_count > 0: legacyDrawing = Related(id="anysvml") xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml) if len(worksheet.page_breaks): xf.write(worksheet.page_breaks.to_tree()) xml = out.getvalue() out.close() return xml
def write_worksheet(worksheet, shared_strings): """Write a worksheet to an xml file.""" if LXML is True: from .lxml_worksheet import write_cell, write_rows else: from .etree_worksheet import write_cell, write_rows out = BytesIO() with xmlfile(out) as xf: with xf.element('worksheet', xmlns=SHEET_MAIN_NS): props = write_properties(worksheet) xf.write(props) dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()}) xf.write(dim) views = Element('sheetViews') views.append(worksheet.sheet_view.to_tree()) xf.write(views) xf.write(write_format(worksheet)) cols = write_cols(worksheet) if cols is not None: xf.write(cols) write_rows(xf, worksheet) if worksheet.protection.sheet: prot = Element('sheetProtection', dict(worksheet.protection)) xf.write(prot) af = write_autofilter(worksheet) if af is not None: xf.write(af) merge = write_mergecells(worksheet) if merge is not None: xf.write(merge) cfs = write_conditional_formatting(worksheet) for cf in cfs: xf.write(cf) dv = write_datavalidation(worksheet) if dv is not None: xf.write(dv) hyper = write_hyperlinks(worksheet) if hyper is not None: xf.write(hyper) options = worksheet.print_options if len(dict(options)) > 0: new_element = options.write_xml_element() xf.write(new_element) del new_element margins = Element('pageMargins', dict(worksheet.page_margins)) xf.write(margins) del margins setup = worksheet.page_setup if len(dict(setup)) > 0: new_element = setup.write_xml_element() xf.write(new_element) del new_element hf = write_header_footer(worksheet) if hf is not None: xf.write(hf) if worksheet._charts or worksheet._images: drawing = Element('drawing', {'{%s}id' % REL_NS: 'rId1'}) xf.write(drawing) del drawing pb = write_pagebreaks(worksheet) if pb is not None: xf.write(pb) # If vba is being preserved then add a legacyDrawing element so # that any controls can be drawn. if worksheet.vba_controls is not None: xml = Element("{%s}legacyDrawing" % SHEET_MAIN_NS, {"{%s}id" % REL_NS : worksheet.vba_controls}) xf.write(xml) elif worksheet._comment_count > 0: # add a legacyDrawing so that excel can draw comments # If a legacyDrawing element has already been added then # we have to hope it already contains the vml for # comments because we cannot add another. comments = Element('{%s}legacyDrawing' % SHEET_MAIN_NS, {'{%s}id' % REL_NS: 'commentsvml'}) xf.write(comments) xml = out.getvalue() out.close() return xml
def write_worksheet(worksheet, shared_strings): """Write a worksheet to an xml file.""" worksheet._rels = [] if LXML is True: from .lxml_worksheet import write_cell, write_rows else: from .etree_worksheet import write_cell, write_rows out = BytesIO() with xmlfile(out) as xf: with xf.element('worksheet', xmlns=SHEET_MAIN_NS): props = worksheet.sheet_properties.to_tree() xf.write(props) dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()}) xf.write(dim) views = Element('sheetViews') views.append(worksheet.sheet_view.to_tree()) xf.write(views) xf.write(write_format(worksheet)) cols = write_cols(worksheet) if cols is not None: xf.write(cols) write_rows(xf, worksheet) if worksheet.protection.sheet: xf.write(worksheet.protection.to_tree()) if worksheet.auto_filter: xf.write(worksheet.auto_filter.to_tree()) if worksheet.sort_state: xf.write(worksheet.sort_state.to_tree()) merge = write_mergecells(worksheet) if merge is not None: xf.write(merge) cfs = write_conditional_formatting(worksheet) for cf in cfs: xf.write(cf) if worksheet.data_validations: xf.write(worksheet.data_validations.to_tree()) hyper = write_hyperlinks(worksheet) if hyper is not None: xf.write(hyper) options = worksheet.print_options if dict(options): new_element = options.to_tree() xf.write(new_element) margins = worksheet.page_margins.to_tree() xf.write(margins) setup = worksheet.page_setup if dict(setup): new_element = setup.to_tree() xf.write(new_element) hf = write_header_footer(worksheet) if hf is not None: xf.write(hf) drawing = write_drawing(worksheet) if drawing is not None: xf.write(drawing) # If vba is being preserved then add a legacyDrawing element so # that any controls can be drawn. if worksheet.vba_controls is not None: legacyDrawing = Related(id=worksheet.vba_controls) xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml) elif worksheet._comment_count > 0: # add a legacyDrawing so that excel can draw comments # If a legacyDrawing element has already been added then # we have to hope it already contains the vml for # comments because we cannot add another. legacyDrawing = Related(id="commentsvml") xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml) if len(worksheet.page_breaks): xf.write(worksheet.page_breaks.to_tree()) xml = out.getvalue() out.close() return xml
def write_worksheet(worksheet, shared_strings): """Write a worksheet to an xml file.""" worksheet._rels = [] if LXML is True: from .lxml_worksheet import write_cell, write_rows else: from .etree_worksheet import write_cell, write_rows out = BytesIO() with xmlfile(out) as xf: with xf.element('worksheet', xmlns=SHEET_MAIN_NS): props = worksheet.sheet_properties.to_tree() xf.write(props) dim = Element('dimension', {'ref': '%s' % worksheet.calculate_dimension()}) xf.write(dim) views = Element('sheetViews') views.append(worksheet.sheet_view.to_tree()) xf.write(views) xf.write(write_format(worksheet)) cols = write_cols(worksheet) if cols is not None: xf.write(cols) write_rows(xf, worksheet) if worksheet.protection.sheet: xf.write(worksheet.protection.to_tree()) af = write_autofilter(worksheet) if af is not None: xf.write(af) merge = write_mergecells(worksheet) if merge is not None: xf.write(merge) cfs = write_conditional_formatting(worksheet) for cf in cfs: xf.write(cf) dv = write_datavalidation(worksheet) if dv is not None: xf.write(dv) hyper = write_hyperlinks(worksheet) if hyper is not None: xf.write(hyper) options = worksheet.print_options if dict(options): new_element = options.to_tree() xf.write(new_element) margins = worksheet.page_margins.to_tree() xf.write(margins) setup = worksheet.page_setup if dict(setup): new_element = setup.to_tree() xf.write(new_element) hf = write_header_footer(worksheet) if hf is not None: xf.write(hf) drawing = write_drawing(worksheet) if drawing is not None: xf.write(drawing) # If vba is being preserved then add a legacyDrawing element so # that any controls can be drawn. if worksheet.vba_controls is not None: legacyDrawing = Related(id=worksheet.vba_controls) xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml) elif worksheet._comment_count > 0: # add a legacyDrawing so that excel can draw comments # If a legacyDrawing element has already been added then # we have to hope it already contains the vml for # comments because we cannot add another. legacyDrawing = Related(id="commentsvml") xml = legacyDrawing.to_tree("legacyDrawing") xf.write(xml) if len(worksheet.page_breaks): xf.write(worksheet.page_breaks.to_tree()) xml = out.getvalue() out.close() return xml
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