def _create_meta(self, visit_name, site_code):
# TODO: use the download.utils.SiteVisitDatasheetWriter to write the metadata
wb = Workbook()
mapping = DATASHEET_META_MAPPING
meta_ws = wb.active
# rewrite the sheet name in case of the 'Sheet' selection.
meta_ws.title = mapping.sheet_name
col_headers = [util_model.get_field_verbose_name(Visit, 'name'),
util_model.get_field_verbose_name(Site, 'site_code')]
# write column headers
top_cell = meta_ws.cell(row=mapping.top_left_row, column=mapping.top_left_column)
writing_direction = mapping.next_col_direction
util_xls.write_values_from_cell(top_cell, col_headers, writing_direction)
# write values
values = [visit_name, site_code]
top_cell = util_xls.get_cell_neighbour(top_cell, mapping.next_row_direction)
util_xls.write_values_from_cell(top_cell, values, writing_direction)
return wb
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_meta(self):
mapping = DATASHEET_META_MAPPING
meta_ws = util_xls.get_sheet(self.wb, mapping.sheet_name) or util_xls.get_sheet(self.wb, 'Sheet')
# rewrite the sheet name in case of the 'Sheet' selection.
meta_ws.title = mapping.sheet_name
if meta_ws is None:
meta_ws = self.wb.create_sheet(0, mapping.sheet_name)
# write the visit name
# populate Meta with visit/site details (very rough)
col_headers = [util_model.get_field_verbose_name(Visit, 'name'),
util_model.get_field_verbose_name(Site, 'site_code')]
# write column headers
top_cell = meta_ws.cell(row=mapping.top_left_row, column=mapping.top_left_column)
writing_direction = mapping.next_col_direction
util_xls.write_values_from_cell(top_cell, col_headers, writing_direction, self.column_header_font)
# write values
if self.visit and self.site:
values = [self.visit.name, self.site.site_code]
top_cell = util_xls.get_cell_neighbour(top_cell, mapping.next_row_direction)
util_xls.write_values_from_cell(top_cell, values, writing_direction)
return meta_ws
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)