def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" xml_file = StringIO() doc = XMLGenerator(xml_file, 'utf-8') start_tag( doc, 'worksheet', { 'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships' }) start_tag(doc, 'sheetPr') tag( doc, 'outlinePr', { 'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right) }) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_hyperlinks(doc, worksheet) if worksheet._charts: tag(doc, 'drawing', {'r:id': 'rId1'}) end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag( doc, 'worksheet', { 'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships' }) start_tag(doc, 'sheetPr') tag( doc, 'outlinePr', { 'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right) }) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader( ) or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts: tag(doc, 'drawing', {'r:id': 'rId1'}) end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string
def write_header(self): fobj = get_temporary_file(filename=self._fileobj_header_name) doc = XMLGenerator(fobj, 'utf-8') start_tag( doc, 'worksheet', { 'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships' }) start_tag(doc, 'sheetPr') tag(doc, 'outlinePr', {'summaryBelow': '1', 'summaryRight': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': 'A1:%s' % (self.get_dimensions())}) start_tag(doc, 'sheetViews') start_tag(doc, 'sheetView', {'workbookViewId': '0'}) tag(doc, 'selection', {'activeCell': 'A1', 'sqref': 'A1'}) end_tag(doc, 'sheetView') end_tag(doc, 'sheetViews') tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) start_tag(doc, 'sheetData')
def __init__(self, parent_workbook): Worksheet.__init__(self, parent_workbook) self._max_col = 0 self._max_row = 0 self._parent = parent_workbook self._fileobj_header = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.header', delete=False) self._fileobj_content = NamedTemporaryFile(mode='r+', prefix='openpyxl.', suffix='.content', delete=False) self._fileobj = NamedTemporaryFile(mode='w', prefix='openpyxl.', delete=False) self.doc = XMLGenerator(self._fileobj_content, 'utf-8') self.header = XMLGenerator(self._fileobj_header, 'utf-8') self.title = 'Sheet' self._shared_date = SharedDate() self._string_builder = self._parent.strings_table_builder
def _get_content_generator(self): """ XXX: this is ugly, but it allows to resume writing the file even after the handle is closed""" # when I'll recreate the XMLGenerator, it will start writing at the # begining of the file, erasing previously entered rows, so we have # to move to the end of the file before adding new tags handle = get_temporary_file(filename=self._fileobj_content_name) handle.seek(0, 2) doc = XMLGenerator(out=handle) return doc
def write_string_table(string_table): """Write the string table xml.""" temp_buffer = StringIO() doc = XMLGenerator(temp_buffer, 'utf-8') start_tag(doc, 'sst', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'uniqueCount': '%d' % len(string_table)}) strings_to_write = sorted(string_table.iteritems(), key=lambda pair: pair[1]) for key in [pair[0] for pair in strings_to_write]: start_tag(doc, 'si') if key.strip() != key: attr = {'xml:space': 'preserve'} else: attr = {} tag(doc, 't', attr, key) end_tag(doc, 'si') end_tag(doc, 'sst') string_table_xml = temp_buffer.getvalue() temp_buffer.close() return string_table_xml
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) register_namespace( "r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships" ) register_namespace( "", "http://schemas.openxmlformats.org/spreadsheetml/2006/main") else: vba_root = None xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag( doc, 'worksheet', { 'xml:space': 'preserve', 'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships' }) if vba_root is not None: codename = vba_root.find( '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetPr' ).get('codeName', worksheet.title) start_tag(doc, 'sheetPr', {"codeName": codename}) else: start_tag(doc, 'sheetPr') tag( doc, 'outlinePr', { 'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right) }) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader( ) or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id': 'rId1'}) # if the sheet has an xml_source field then the workbook must have # been loaded with keep-vba true and we need to extract any control # elements. if vba_root is not None: for t in ( '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}legacyDrawing', '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}controls' ): for elem in vba_root.findall(t): xml_file.write( re.sub(r' xmlns[^ >]*', '', tostring(elem).decode("utf-8"))) breaks = worksheet.page_breaks if breaks: start_tag(doc, 'rowBreaks', { 'count': str(len(breaks)), 'manualBreakCount': str(len(breaks)) }) for b in breaks: tag(doc, 'brk', { 'id': str(b), 'man': 'true', 'max': '16383', 'min': '0' }) end_tag(doc, 'rowBreaks') end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string
def write_worksheet(worksheet, string_table, style_table): """Write a worksheet to an xml file.""" if worksheet.xml_source: vba_root = fromstring(worksheet.xml_source) else: vba_root = None xml_file = StringIO() doc = XMLGenerator(out=xml_file, encoding='utf-8') start_tag(doc, 'worksheet', {'xmlns': SHEET_MAIN_NS, 'xmlns:r': REL_NS}) if vba_root is not None: el = vba_root.find('{%s}sheetPr' % SHEET_MAIN_NS) if el is not None: codename = el.get('codeName', worksheet.title) start_tag(doc, 'sheetPr', {"codeName": codename}) else: start_tag(doc, 'sheetPr') else: start_tag(doc, 'sheetPr') tag( doc, 'outlinePr', { 'summaryBelow': '%d' % (worksheet.show_summary_below), 'summaryRight': '%d' % (worksheet.show_summary_right) }) if worksheet.page_setup.fitToPage: tag(doc, 'pageSetUpPr', {'fitToPage': '1'}) end_tag(doc, 'sheetPr') tag(doc, 'dimension', {'ref': '%s' % worksheet.calculate_dimension()}) write_worksheet_sheetviews(doc, worksheet) tag(doc, 'sheetFormatPr', {'defaultRowHeight': '15'}) write_worksheet_cols(doc, worksheet, style_table) write_worksheet_data(doc, worksheet, string_table, style_table) if worksheet.auto_filter: tag(doc, 'autoFilter', {'ref': worksheet.auto_filter}) write_worksheet_mergecells(doc, worksheet) write_worksheet_datavalidations(doc, worksheet) write_worksheet_hyperlinks(doc, worksheet) write_worksheet_conditional_formatting(doc, worksheet) options = worksheet.page_setup.options if options: tag(doc, 'printOptions', options) margins = worksheet.page_margins.margins if margins: tag(doc, 'pageMargins', margins) setup = worksheet.page_setup.setup if setup: tag(doc, 'pageSetup', setup) if worksheet.header_footer.hasHeader( ) or worksheet.header_footer.hasFooter(): start_tag(doc, 'headerFooter') if worksheet.header_footer.hasHeader(): tag(doc, 'oddHeader', None, worksheet.header_footer.getHeader()) if worksheet.header_footer.hasFooter(): tag(doc, 'oddFooter', None, worksheet.header_footer.getFooter()) end_tag(doc, 'headerFooter') if worksheet._charts or worksheet._images: tag(doc, 'drawing', {'r:id': 'rId1'}) # If vba is being preserved then add a legacyDrawing element so # that any controls can be drawn. if vba_root is not None: el = vba_root.find('{%s}legacyDrawing' % SHEET_MAIN_NS) if el is not None: rId = el.get('{%s}id' % REL_NS) tag(doc, 'legacyDrawing', {'r:id': rId}) breaks = worksheet.page_breaks if breaks: start_tag(doc, 'rowBreaks', { 'count': str(len(breaks)), 'manualBreakCount': str(len(breaks)) }) for b in breaks: tag(doc, 'brk', { 'id': str(b), 'man': 'true', 'max': '16383', 'min': '0' }) end_tag(doc, 'rowBreaks') # add a legacyDrawing so that excel can draw comments if worksheet._comment_count > 0: tag(doc, 'legacyDrawing', {'r:id': 'commentsvml'}) end_tag(doc, 'worksheet') doc.endDocument() xml_string = xml_file.getvalue() xml_file.close() return xml_string