def process_sheet1(self): tree = parse(self.tmp_sheet_xml_file) # Removing file from the template. XML structure has been loaded to memory, file will be recreated in the zip # archive when calling the write function os.remove(self.tmp_sheet_xml_file) worksheet = tree.getroot() sheetData = worksheet.find("./" + self.base_ns + "sheetData") # Removing existing cell records from template if any file_rows = sheetData.findall("./" + self.base_ns + "row") if len(file_rows) > 0: for file_row in file_rows: sheetData.remove(file_row) curent_row_number = None curent_cell_ref = None total_cell_number = 0 row = None v = None for key, value in self.dict.items(): row_number = value[4] # New row ? If so, append a new row Element if row_number != curent_row_number: # Append the new row curent_row_number = row_number row = SubElement(sheetData, self.base_ns + "row") row.set("r", row_number) # Determine if the cell is already defined in the file. If so (multiple writing to the same cell), last # writing to the cell (last item of the for loop) will actually be effective if key != curent_cell_ref: # Append the new cell curent_cell_ref = key total_cell_number += 1 c = SubElement(row, self.base_ns + "c") style = value[2] if style is not None: c.set("s", str(style)) c.set("r", key) c.set("t", "s") v = SubElement(c, self.base_ns + "v") v.text = str(value[1]) # Updating sharedstring "count" attribute from the sharedstrings.xml file with the new number of cells # Excel will complain otherwise self.finalize_shared(total_cell_number) # Updating dimension element. Optional, only allows Excel/Open Office to optimize # the horizontal / vertical scroll bar size. Not really time consuming though min_row = list(self.dict.values())[0][4] max_row = list(self.dict.values())[-1][4] tmp_set = set() for cell_data in self.dict.values(): tmp_set.add(cell_data[3]) sorted_columns = sorted(tmp_set) min_column = sorted_columns[0] max_column = sorted_columns[-1] new_dim = min_column + str(min_row) + ":" + max_column + str(max_row) dimension = worksheet.find("./" + self.base_ns + "dimension") if dimension is not None: dimension.set("ref", new_dim) # Adjusting columns width for the columns that are used col_records = None cols = worksheet.find("./" + self.base_ns + "cols") col = None if cols is not None: col_records = cols.findall("./" + self.base_ns + "col") else: cols = SubElement(worksheet, self.base_ns + "cols") if col_records is not None: # Removing existing col elements if any, keeping only the last one to update it for index, col_record in enumerate(col_records): if index < len(col_records)-1: cols.remove(col_record) else: col = col_record if col is None: col = SubElement(cols, self.base_ns + "col") col.set("min", str(self.column_to_index(min_column))) col.set("max", str(self.column_to_index(max_column))) col.set("width", "30") col.set("customWidth", "1") # Writing XML tree to file tree.write(self.tmp_sheet_xml_file) # Writing file to archive self.zout.write(self.tmp_sheet_xml_file, arcname="xl/worksheets/sheet1.xml")