def _write_species(self, file_path):
ws = util_xls.get_or_create_sheet(self.wb, 'Species')
species_ws = load_workbook(file_path).active
col_max = species_ws.get_highest_row()
species_list = [cell.value for (cell,) in list(species_ws.get_squared_range(1, 1, 1, col_max))]
range_ = util_xls.append_column(ws, species_list)
self.wb.create_named_range('species', ws, range_)
return ws
def _write_lookups(self):
"""
Write the lookups as columns in the Lookups spreadsheet.
For each column we register a named range.
The name will be the name of the Lookup table (model) NOT the model field name because some fields refer to the
same lookup tables (ex DisturbanceIndicator and the FeralEvidenceLookup)
:return:
"""
# First gather all the lookups tables
lookups = self._build_lookups_dict() # key=lookup name value=lookup values(list)
# Then for every lookups write a column in Lookup datasheet and register the range
ws = util_xls.get_or_create_sheet(self.wb, 'Lookups')
for name, values in lookups.items():
if len(values) > 0:
range_ = util_xls.append_column(ws, values)
self.wb.create_named_range(name, ws, range_)
return ws
def _populate_site_characteristics(self):
# export the site characteristic data from the Site
mapping = get_mapping_for_model(SiteCharacteristic)
if mapping is not None:
row = [
self.site.underlaying_geology.value if self.site.underlaying_geology else "",
self.site.closest_water_distance,
self.site.closest_water_type.value if self.site.closest_water_type else "",
self.site.landform_pattern.value if self.site.landform_pattern else "",
self.site.landform_element.value if self.site.landform_element else "",
self.site.soil_surface_texture.value if self.site.soil_surface_texture else "",
self.site.soil_colour,
self.site.comments,
]
ws = util_xls.get_or_create_sheet(self.wb, mapping.sheet_name)
top_cell = ws.cell(row=mapping.top_left_row, column=mapping.top_left_column)
start_cell = util_xls.get_cell_neighbour(top_cell, mapping.next_row_direction)
writing_direction = mapping.next_col_direction
util_xls.write_values_from_cell(start_cell, row, writing_direction)
def _write_model(self, mapping):
model = mapping.model
ws = util_xls.get_or_create_sheet(self.wb, mapping.sheet_name)
fields = util_model.get_datasheet_fields_for_model(model)
top_cell = ws.cell(row=mapping.top_left_row, column=mapping.top_left_column)
column_cell = top_cell
# create alignment for transposed header cells
right_alignment = Alignment(horizontal='right')
max_column_width = 0
for field in fields:
# the column header
col_header = util_model.get_datasheet_field_name(field)
column_cell.font = self.column_header_font
column_cell.value = col_header
if mapping.transpose:
column_cell.alignment = right_alignment
# calculate column widths
if util_model.is_species_observation_field(field):
# special case for species column width
ws.column_dimensions[column_cell.column].width = SPECIES_COLUMN_WIDTH
else:
column_width = len(column_cell.value) + COLUMN_WIDTH_BUFFER
if mapping.transpose:
if column_width > max_column_width:
max_column_width = column_width
else:
column_width = max_column_width
ws.column_dimensions[column_cell.column].width = column_width
dv = self._create_data_validation(field)
if dv is not None:
# apply data validation to the row cell
row_cell = util_xls.get_cell_neighbour(column_cell, mapping.next_row_direction)
dv.add(row_cell)
if not mapping.unique:
# it is a multi row model. We need to extend the data validation to other rows
# choose a max row
max_row = self.max_row
for i in range(0, max_row):
row_cell = util_xls.get_cell_neighbour(row_cell, mapping.next_row_direction)
dv.add(row_cell)
ws.add_data_validation(dv)
column_cell = util_xls.get_cell_neighbour(column_cell, mapping.next_col_direction)
# insert formulas
for field_name in mapping.formulas:
field = mapping.model._meta.get_field_by_name(field_name)[0]
field_header_cell = util_xls.find_cell_by_value(ws, util_model.get_datasheet_field_name(field))
field_cell = util_xls.get_cell_neighbour(field_header_cell, mapping.next_row_direction)
parameter_cells = []
for param_field_name in mapping.formulas[field_name]['parameters']:
param_field = mapping.model._meta.get_field_by_name(param_field_name)[0]
param_header_cell = util_xls.find_cell_by_value(ws, util_model.get_datasheet_field_name(param_field))
parameter_cells.append(util_xls.get_cell_neighbour(param_header_cell, mapping.next_row_direction))
field_cell.value = self._create_formula(mapping.formulas[field_name]['formula'], *parameter_cells)
if not mapping.unique:
# it is a multi row model. We need to extend the formula to other rows choose a max row
max_row = self.max_row
for i in range(0, max_row):
field_cell = util_xls.get_cell_neighbour(field_cell, mapping.next_row_direction)
parameter_cells = [util_xls.get_cell_neighbour(cell, mapping.next_row_direction) for cell in
parameter_cells]
field_cell.value = self._create_formula(mapping.formulas[field_name]['formula'], *parameter_cells)