def _write_single_physician_chart(self, sheet: openpyxl.worksheet.Worksheet, first_col: int, first_row: int) \ -> None: cell = sheet.cell(column=first_col, row=first_row) cell.value = "satisfaction for phys" cell.offset(column=1).value = 1 chart = self._create_default_chart() chart.x_axis_title = "physician" chart.y_axis_title = "satisfaction" xref = openpyxl.chart.Reference(self.analysis_sheet, min_col=self.start_single_phys_table_col, max_col=self.start_single_phys_table_col, min_row=self.start_single_phys_table_row + 1, max_row=self.start_single_phys_table_row + len(self.results)) chart.set_categories(xref) for mode_cnt, mode in enumerate(self.solver_modes): data_column = self.start_single_phys_table_col + 1 + mode_cnt ref = openpyxl.chart.Reference(self.analysis_sheet, min_col=data_column, max_col=data_column, min_row=self.start_single_phys_table_row + 1, max_row=self.start_single_phys_table_row + len(self.results)) series = openpyxl.chart.Series(ref, title=self.mode_settings[mode].name) chart.append(series) sheet.add_chart(chart, anchor=cell.offset(column=1, row=1).coordinate)
def _add_physician_chart(self, sheet: openpyxl.worksheet.Worksheet, first_col: int, first_row: int, title: str, y_axis: str, data_col_offset: int) -> None: anchor = sheet.cell(column=first_col, row=first_row) cell = sheet.cell(column=first_col, row=first_row) cell.value = title chart = openpyxl.chart.LineChart() chart.style = 1 chart.height = 9 chart.width = 15 chart.legend.position = "b" chart.x_axis.title = "physician" chart.y_axis.title = y_axis xref = openpyxl.chart.Reference(self.analysis_sheet, min_col=self.start_sat_table_col, max_col=self.start_sat_table_col, min_row=self.start_sat_table_row + 1, max_row=self.start_sat_table_row + len(self.physicians)) chart.set_categories(xref) for mode_cnt, mode in enumerate(self.solver_modes): data_column = self.start_sat_table_col + data_col_offset + (len(self.satisfaction_table_cols) * mode_cnt) ref = openpyxl.chart.Reference(self.analysis_sheet, min_col=data_column, max_col=data_column, min_row=self.start_sat_table_row + 1, max_row=self.start_sat_table_row + len(self.physicians)) mode_info = self.mode_settings[mode] series = openpyxl.chart.Series(ref, title=mode_info.name) series.marker.symbol = mode_info.marker series.graphicalProperties.line.noFill = True chart.append(series) sheet.add_chart(chart, anchor=anchor.offset(column=1).coordinate)
def _write_directory_request_table(self, sheet: openpyxl.worksheet.Worksheet, directory: str) -> None: first_col = self.req_first_col first_row = self.req_first_row cell = sheet.cell(column=first_col, row=first_row) cell.value = "g_req_on" self._setBoldFont(cell) cell = sheet.cell(column=first_col + 1, row=first_row) cell.value = "g_req_off" self._setBoldFont(cell) for phys_cnt, phys in enumerate(sorted(self.physicians)): cell = sheet.cell(column=first_col, row=first_row + 1 + phys_cnt) cell.value = self.results[directory][phys]["g_req_on"] cell = sheet.cell(column=first_col + 1, row=first_row + 1 + phys_cnt) cell.value = self.results[directory][phys]["g_req_off"]
def _write_physician_table(self, sheet: openpyxl.worksheet.Worksheet, first_col: int, first_row: int) -> None: row = first_row for phys in sorted(self.physicians): cell = sheet.cell(row=row, column=first_col) cell.value = phys self._setBoldFont(cell) row += 1
def _write_directory_satisfaction_table(self, sheet: openpyxl.worksheet.Worksheet, directory: str) -> None: first_col = self.sat_first_col first_row = self.sat_first_row cell = sheet.cell(column=first_col, row=first_row) cell.value = "phys" self._setBoldFont(cell) self._write_physician_table(sheet, first_col, first_row + 1) for mode_cnt, solver_mode in enumerate(self.solver_modes): first_mode_col = first_col + 1 + (3 * mode_cnt) cell = sheet.cell(column=first_mode_col, row=first_row) cell.value = "sigma " + solver_mode self._setBoldFont(cell) cell = sheet.cell(column=first_mode_col + 1, row=first_row) cell.value = "l " + solver_mode self._setBoldFont(cell) cell = sheet.cell(column=first_mode_col + 2, row=first_row) cell.value = "lambda " + solver_mode self._setBoldFont(cell) row = first_row + 1 for phys in sorted(self.physicians): sheet.cell(row=row, column=first_mode_col).value = \ self.results[directory]["modes"][solver_mode][phys]["satisfaction"] sheet.cell(row=row, column=first_mode_col + 1).value = \ self.results[directory]["modes"][solver_mode][phys]["load"] sheet.cell(row=row, column=first_mode_col + 2).value = \ self.results[directory]["modes"][solver_mode][phys]["lambda"] row += 1
def _write_directory_delta_request_table(self, sheet: openpyxl.worksheet.Worksheet, directory: str) -> None: first_col = self.delta_req_first_col first_row = self.delta_req_first_row for mode_cnt, mode in enumerate(self.solver_modes): req_on_col = first_col + (mode_cnt * 2) req_off_col = req_on_col + 1 cell = sheet.cell(column=req_on_col, row=first_row) cell.value = mode + " delta_req_on" self._setBoldFont(cell) cell = sheet.cell(column=req_off_col, row=first_row) cell.value = mode + " delta_req_off" self._setBoldFont(cell) for phys_cnt, phys in enumerate(sorted(self.physicians)): cell = sheet.cell(column=req_on_col, row=first_row + 1 + phys_cnt) cell.value = self.results[directory]["modes"][mode][phys]["delta_req_on"] cell = sheet.cell(column=req_off_col, row=first_row + 1 + phys_cnt) cell.value = self.results[directory]["modes"][mode][phys]["delta_req_off"]
def _write_single_physician_table(self, sheet: openpyxl.worksheet.Worksheet, first_col: int, first_row: int) \ -> None: physician_data_cell = self.charts_sheet.cell(column=self.single_phys_var_first_col, row=self.single_phys_var_first_row).offset(column=1) for mode_cnt, mode in enumerate(self.solver_modes): cell = sheet.cell(column=first_col + 1 + mode_cnt, row=first_row) cell.value = mode self._setBoldFont(cell) for dir_cnt, directory in enumerate(sorted(self.results)): row = first_row + 1 + dir_cnt header_cell = sheet.cell(row=row, column=first_col) header_cell.value = directory self._setBoldFont(header_cell) for mode_cnt, mode in enumerate(self.solver_modes): col = first_col + 1 + mode_cnt mode_col_letter = chr(ord('B') + mode_cnt) value_cell = sheet.cell(row=row, column=col) value_cell.value = "=INDIRECT(\"'\" & {} & \"'!{}\" & ('{}'!{} + 1))".format( header_cell.coordinate, mode_col_letter, self.charts_sheet.title, physician_data_cell.coordinate)
def _write_request_table(self, sheet: openpyxl.worksheet.Worksheet, first_col: int, first_row: int) -> None: cell = sheet.cell(column=first_col, row=first_row) cell.value = "requests" self._setBoldFont(cell) total_col = first_col + 1 cell = sheet.cell(column=total_col, row=first_row) cell.value = "total (continuous phys.)" self._setBoldFont(cell) overall_col = first_col + 2 cell = sheet.cell(column=overall_col, row=first_row) cell.value = "total (all phys.)" self._setBoldFont(cell) for directory_cnt, directory in enumerate(self.results): dir_row = first_row + 1 + directory_cnt cell = sheet.cell(row=dir_row, column=first_col) cell.value = directory self._setBoldFont(cell) first_req_cell = sheet.cell(row=self.req_first_row + 1, column=self.req_first_col) last_req_cell = sheet.cell(row=self.req_first_row + len(self.physicians), column=self.req_first_col + 1) total_delta_cell = sheet.cell(row=dir_row, column=total_col) total_delta_cell.value = "=SUM('{}'!{}:{})".format(directory, first_req_cell.coordinate, last_req_cell.coordinate) overall_cell = sheet.cell(row=dir_row, column=overall_col) overall_cell.value = self.results[directory]["total"]["g_req_on"] \ + self.results[directory]["total"]["g_req_off"] for mode_cnt, mode in enumerate(self.solver_modes): first_delta_cell = sheet.cell(row=self.delta_req_first_row + 1, column=self.delta_req_first_col + (2 * mode_cnt)) last_delta_cell = sheet.cell(row=self.delta_req_first_row + len(self.physicians), column=self.delta_req_first_col + (2 * mode_cnt) + 1) delta_overall_col = first_col + 3 + mode_cnt delta_cell = sheet.cell(row=dir_row, column=delta_overall_col) delta_cell.value = "=SUM('{}'!{}:{})".format(directory, first_delta_cell.coordinate, last_delta_cell.coordinate) percent_col = delta_overall_col + len(self.solver_modes) percent_cell = sheet.cell(row=dir_row, column=percent_col) percent_cell.value = "={}/{}*100".format(delta_cell.coordinate, total_delta_cell.coordinate) overall_delta_col = percent_col + len(self.solver_modes) overall_delta_cell = sheet.cell(row=dir_row, column=overall_delta_col) overall_delta_cell.value = self.results[directory]["modes"][mode]["total"]["delta_req_on"] \ + self.results[directory]["modes"][mode]["total"]["delta_req_off"] overall_percent_col = overall_delta_col + len(self.solver_modes) overall_percent_cell = sheet.cell(row=dir_row, column=overall_percent_col) overall_percent_cell.value = "={}/{}*100".format(overall_delta_cell.coordinate, overall_cell.coordinate) row = first_row + len(self.results) + 1 cell = sheet.cell(column=first_col, row=row) cell.value = "sum" self._setBoldFont(cell) self._setTopBorder(cell) total_sum_cell = sheet.cell(column=total_col, row=row) total_sum_cell.value = "=SUM({}:{})".format(sheet.cell(row=first_row + 1, column=total_col).coordinate, sheet.cell(row=first_row + len(self.results), column=total_col).coordinate) self._setBoldFont(total_sum_cell) self._setTopBorder(total_sum_cell) overall_sum_cell = sheet.cell(column=overall_col, row=row) overall_sum_cell.value = "=SUM({}:{})".format(sheet.cell(row=first_row + 1, column=overall_col).coordinate, sheet.cell(row=first_row + len(self.results), column=overall_col).coordinate) self._setBoldFont(overall_sum_cell) self._setTopBorder(overall_sum_cell) for mode_cnt, mode in enumerate(self.solver_modes): delta_col = first_col + mode_cnt + 3 cell = sheet.cell(column=delta_col, row=first_row) cell.value = "delta (continuous phys.) " + mode self._setBoldFont(cell) vio_col = delta_col + len(self.solver_modes) cell = sheet.cell(column=vio_col, row=first_row) cell.value = "%vio (continuous phys.) " + mode self._setBoldFont(cell) delta_overall_col = vio_col + len(self.solver_modes) cell = sheet.cell(column=delta_overall_col, row=first_row) cell.value = "delta (all phys.) " + mode self._setBoldFont(cell) vio_overall_col = delta_overall_col + len(self.solver_modes) cell = sheet.cell(column=vio_overall_col, row=first_row) cell.value = "%vio (all phys.) " + mode self._setBoldFont(cell) total_delta_cell = sheet.cell(column=delta_col, row=row) total_delta_cell.value = "=SUM({}:{})".format(sheet.cell(row=first_row + 1, column=delta_col).coordinate, sheet.cell(row=first_row + len(self.results), column=delta_col).coordinate) self._setBoldFont(total_delta_cell) self._setTopBorder(total_delta_cell) percentage_cell = sheet.cell(column=vio_col, row=row) percentage_cell.value = "={}/{}*100".format(total_delta_cell.coordinate, total_sum_cell.coordinate) self._setBoldFont(percentage_cell) self._setTopBorder(percentage_cell) total_delta_overall_cell = sheet.cell(column=delta_overall_col, row=row) total_delta_overall_cell.value = "=SUM({}:{})".format(sheet.cell(row=first_row + 1, column=delta_overall_col).coordinate, sheet.cell(row=first_row + len(self.results), column=delta_overall_col).coordinate) self._setBoldFont(total_delta_overall_cell) self._setTopBorder(total_delta_overall_cell) total_vio_overall_cell = sheet.cell(column=vio_overall_col, row=row) total_vio_overall_cell.value = "={}/{}*100".format(total_delta_overall_cell.coordinate, overall_sum_cell.coordinate) self._setBoldFont(total_vio_overall_cell) self._setTopBorder(total_vio_overall_cell)
def _write_satisfaction_directory_table(self, sheet: openpyxl.worksheet.Worksheet, first_col: int, first_row: int) \ -> None: cell = sheet.cell(column=first_col, row=first_row) cell.value = "plan" self._setBoldFont(cell) row = first_row + 1 for directory in self.results: cell = sheet.cell(column=first_col, row=row) cell.value = directory self._setBoldFont(cell) row += 1 cell = sheet.cell(column=first_col, row=row) cell.value = "average" self._setBoldFont(cell) self._setTopBorder(cell) cell = sheet.cell(column=first_col, row=row + 1) cell.value = "difference" self._setBoldFont(cell) cell = sheet.cell(column=first_col, row=row + 2) cell.value = "diff %" self._setBoldFont(cell) mode_cnt = 1 for solver_mode in self.solver_modes: column = first_col + mode_cnt cell = sheet.cell(column=column, row=first_row) cell.value = solver_mode self._setBoldFont(cell) row = first_row + 1 for directory in self.results: sheet.cell(column=column, row=row).value = "=_xlfn.VAR.P('{}'!{}:{})".format( directory, sheet.cell(row=self.sat_first_row + 1, column=self.sat_first_col + mode_cnt).coordinate, sheet.cell(row=self.sat_first_row + len(self.physicians), column=self.sat_first_col + mode_cnt).coordinate ) row += 1 cell = sheet.cell(column=column, row=row) cell.value = "=AVERAGE({}:{})".format( sheet.cell(row=first_row, column=column).coordinate, sheet.cell(row=row - 1, column=column).coordinate ) self._setBoldFont(cell) self._setTopBorder(cell) cell = sheet.cell(row=row + 1, column=column) cell.value = "={}-{}".format( sheet.cell(row=row, column=column).coordinate, sheet.cell(row=row, column=first_col + 1).coordinate) cell = sheet.cell(row=row + 2, column=column) cell.value = "={}/{}*100".format( sheet.cell(row=row + 1, column=column).coordinate, sheet.cell(row=row, column=first_col + 1).coordinate ) mode_cnt += 1
def _write_satisfaction_table(self, sheet: openpyxl.worksheet.Worksheet, first_col: int, first_row: int) -> None: cell = sheet.cell(column=first_col, row=first_row) cell.value = "phys" self._setBoldFont(cell) self._write_physician_table(sheet, first_col, first_row + 1) cell = sheet.cell(row=first_row + 1 + len(self.physicians), column=first_col) cell.value = "average" self._setBoldFont(cell) self._setTopBorder(cell) cell = sheet.cell(row=first_row + 2 + len(self.physicians), column=first_col) cell.value = "difference" self._setBoldFont(cell) cell = sheet.cell(row=first_row + 3 + len(self.physicians), column=first_col) cell.value = "diff %" self._setBoldFont(cell) cell = sheet.cell(row=first_row + 4 + len(self.physicians), column=first_col) cell.value = "variance" self._setBoldFont(cell) cell = sheet.cell(row=first_row + 5 + len(self.physicians), column=first_col) cell.value = "difference" self._setBoldFont(cell) cell = sheet.cell(row=first_row + 6 + len(self.physicians), column=first_col) cell.value = "diff %" self._setBoldFont(cell) for mode_cnt, solver_mode in enumerate(self.solver_modes): cols = self.satisfaction_table_cols for colinfo in cols: column = first_col + colinfo[0] + (mode_cnt * len(cols)) cell = sheet.cell(row=first_row, column=column) cell.value = colinfo[1] + " " + solver_mode self._setBoldFont(cell) for phys_cnt in range(len(self.physicians)): row = first_row + phys_cnt + 1 cell = sheet.cell(row=row, column=column) dirsheet_row = self.sat_first_row + phys_cnt + 1 dirsheet_col = self.sat_first_col + (3 * mode_cnt) + colinfo[2] dirsheet_cell = sheet.cell(row=dirsheet_row, column=dirsheet_col) cell.value = "={}({})".format(colinfo[3], ",".join(["'{}'!{}".format(sheet_name, dirsheet_cell.coordinate) for sheet_name in self.results])) last_phys_row = first_row + len(self.physicians) cell = sheet.cell(row=last_phys_row + 1, column=column) cell.value = "=AVERAGE({}:{})".format( sheet.cell(row=first_row + 1, column=column).coordinate, sheet.cell(row=last_phys_row, column=column).coordinate) self._setBoldFont(cell) self._setTopBorder(cell) if colinfo[4]: self._setCellBackground(cell, colinfo[4]) if mode_cnt > 0: cell = sheet.cell(row=last_phys_row + 2, column=column) cell.value = "={}-{}".format( sheet.cell(row=last_phys_row + 1, column=column).coordinate, sheet.cell(row=last_phys_row + 1, column=first_col + colinfo[0]).coordinate ) self._setBoldFont(cell) if colinfo[4]: self._setCellBackground(cell, colinfo[4]) cell = sheet.cell(row=last_phys_row + 3, column=column) cell.value = "={}/{}*100".format( sheet.cell(row=last_phys_row + 2, column=column).coordinate, sheet.cell(row=last_phys_row + 1, column=first_col + colinfo[0]).coordinate ) self._setBoldFont(cell) if colinfo[4]: self._setCellBackground(cell, colinfo[4]) cell = sheet.cell(row=last_phys_row + 4, column=column) cell.value = "=_xlfn.VAR.P({}:{})".format( sheet.cell(row=first_row + 1, column=column).coordinate, sheet.cell(row=last_phys_row, column=column).coordinate) self._setBoldFont(cell) if colinfo[5]: self._setCellBackground(cell, colinfo[5]) if mode_cnt > 0: cell = sheet.cell(row=last_phys_row + 5, column=column) cell.value = "={}-{}".format( sheet.cell(row=last_phys_row + 4, column=column).coordinate, sheet.cell(row=last_phys_row + 4, column=first_col + colinfo[0]).coordinate ) self._setBoldFont(cell) if colinfo[5]: self._setCellBackground(cell, colinfo[5]) cell = sheet.cell(row=last_phys_row + 6, column=column) cell.value = "={}/{}*100".format( sheet.cell(row=last_phys_row + 5, column=column).coordinate, sheet.cell(row=last_phys_row + 4, column=first_col + colinfo[0]).coordinate ) self._setBoldFont(cell) if colinfo[5]: self._setCellBackground(cell, colinfo[5]) row = first_row + len(self.physicians) + 8 col = first_col for color in self.colors: cell = sheet.cell(row=row, column=col) cell.value = color self._setCellBackground(cell, self.colors[color]) col += 1