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")
