def _write_col_order_weight_row(val_sheet, row_index,
index_and_range_tuple_by_header_dict):
"""
:type val_sheet: ValidationWorksheet
"""
# write col_order_weight row: =INT(AQ13:BW13)*100000+AQ14:BW14
col_order_weight_formula_str = _format_order_weight_formula(
index_and_range_tuple_by_header_dict, val_sheet.IS_VALID_COL_HEADER,
val_sheet.COL_IN_METADATA_HEADER)
return xlsxbasics.format_and_write_array_formula(
val_sheet, row_index, col_order_weight_formula_str, write_col=False)
def _write_row_order_weight_col(val_sheet, col_index,
index_and_range_tuple_by_header_dict):
"""
:type val_sheet: ValidationWorksheet
"""
# write row_order_weight column: e.g., =INT(AM2:AM11)*100000+AL2:AL11
row_order_weight_formula_str = _format_order_weight_formula(
index_and_range_tuple_by_header_dict, val_sheet.IS_VALID_ROW_HEADER,
val_sheet.ROW_IN_METADATA_HEADER)
return xlsxbasics.format_and_write_array_formula(
val_sheet, col_index, row_order_weight_formula_str, write_col=True)
def _write_col_in_metadata_row(val_sheet, row_index,
index_and_range_tuple_by_header_dict):
"""
:type val_sheet: ValidationWorksheet
"""
# write col_in_metadata row: =COLUMN(metadata!B2:AH2)
metadata_single_row_range = xlsxbasics.format_single_data_grid_row_range(
val_sheet, row_index, sheet_name=val_sheet.metadata_sheet_name)
return xlsxbasics.format_and_write_array_formula(
val_sheet,
row_index,
"COLUMN({cell})",
write_col=False,
cell_range_str=metadata_single_row_range)
def _write_col_rank_row(val_sheet, row_index,
index_and_range_tuple_by_header_dict):
"""
:type val_sheet: ValidationWorksheet
"""
# write col_rank row: =COUNTIF($AQ15:$BW15,"<="&AQ$15:BW15)
col_rank_formula_str = _format_rank_formula(
val_sheet,
index_and_range_tuple_by_header_dict,
val_sheet.COL_ORDER_WEIGHT_HEADER,
write_col=False)
return xlsxbasics.format_and_write_array_formula(val_sheet,
row_index,
col_rank_formula_str,
write_col=False)
def _write_row_rank_col(val_sheet, col_index,
index_and_range_tuple_by_header_dict):
"""
:type val_sheet: ValidationWorksheet
"""
# write row_rank column: e.g., =COUNTIF(AK$2:AK$11,"<="&$AK2:AK11)
row_rank_formula_str = _format_rank_formula(
val_sheet,
index_and_range_tuple_by_header_dict,
val_sheet.ROW_ORDER_WEIGHT_HEADER,
write_col=True)
return xlsxbasics.format_and_write_array_formula(val_sheet,
col_index,
row_rank_formula_str,
write_col=True)
def _write_row_in_metadata_col(val_sheet, col_index,
index_and_range_tuple_by_header_dict):
"""
:type val_sheet: ValidationWorksheet
"""
# write row_in_metadata column: e.g., =ROW(metadata!A2:A11)
metadata_sample_id_range_str = xlsxbasics.format_single_col_range(
val_sheet,
val_sheet.first_data_col_index,
sheet_name=val_sheet.metadata_sheet_name)
return xlsxbasics.format_and_write_array_formula(
val_sheet,
col_index,
"ROW({cell})",
write_col=True,
cell_range_str=metadata_sample_id_range_str)
def _write_static_name_col(val_sheet, col_index,
index_and_range_tuple_by_header_dict):
"""
:type val_sheet: ValidationWorksheet
"""
# write sample_name column: e.g., =IF(metadata!B2:B11="","",metadata!B2:B11)
# TODO: someday: +1 is a hack to get it to show name rather than sample id ... think of less fragile way
metadata_name_data_range = xlsxbasics.format_single_col_range(
val_sheet,
val_sheet.sample_id_col_index + 1,
sheet_name=val_sheet.metadata_sheet_name)
return xlsxbasics.format_and_write_array_formula(
val_sheet,
col_index,
"IF({cell}=\"\",\"\",{cell})",
write_col=True,
cell_range_str=metadata_name_data_range)