def create_excel_table_from_df(df: pd.DataFrame, sheet_: Worksheet, row_ini: 1, table_name): """Crea tabla de Excel en la hoja indicada a partir de un pandas DataFrame. Parametros: df: pandas DataFrame row_ini: fila inicial, por default 1 sheet_: Worksheet object openpyxl table_name: nombre de la tabla""" col_last = get_excel_style(1, df.shape[1])[:-1] # Crear tabla de Excel tabla_excel = Table( displayName=table_name, ref=f"A{row_ini}:{col_last}{df.shape[0] + row_ini}") # nombre y tamaño # declarar estilos a la tabla style = TableStyleInfo(name="TableStyleMedium2", showRowStripes=False) # asignar el estilo tabla_excel.tableStyleInfo = style # agregar tabla a la hoja sheet_.add_table(tabla_excel)
def remove_empty_worksheet_rows( *, ws: Worksheet, empty_rows: List[int] ) -> Worksheet: """ Delete empty worksheet rows. Parameters ---------- ws : Worksheet A worksheet from a workbook. empty_rows : List[int] List of row numbers. Returns ------- ws : Worksheet A worksheet from a workbook. Example ------- Remove empty rows found. >>> import datasense as ds >>> ws = ds.remove_empty_worksheet_rows( >>> ws=ws, >>> empty_rows=empty_rows >>> ) """ for row_idx in reversed(empty_rows): ws.delete_rows( idx=row_idx, amount=1 ) return ws
def df_to_excel(wb: Workbook, df: pd.DataFrame, sheet_: Worksheet, row_ini: 1, as_table: False, **kwargs): """Agregar pandas DataFrame a hoja de Excel. Parametros: df: pandas DataFrame sheet_: Worksheet object openpyxl row_ini: fila inicial, por default 1 as_table: boolean, crear Tabla de Excel""" # Agregar dataframe de Python a Excel rows = dataframe_to_rows(df, index=False, header=True) # agregar filas a Excel for r_idx, row in enumerate(rows, row_ini): for c_idx, value in enumerate(row, 1): sheet_.cell(row=r_idx, column=c_idx, value=value) if as_table: try: table_name = kwargs['table_name'] create_excel_table_from_df(df, sheet_, row_ini, table_name) except KeyError: raise ValueError( 'A table name must be specified if as_table is True.') try: for sheet in ['Sheet', 'Hoja', 'Hoja1']: wb.remove(wb[sheet]) except KeyError: pass
def __parseMonthly(worksheet: Worksheet) -> list: res = [] # Timestamp of the data. data_timestamp = datetime.now().isoformat() minRowNum = FIRST_DATA_LINE_NUMBER maxRowNum = len(worksheet['B']) for rownum in range(minRowNum, maxRowNum + 1): row = {} if worksheet.cell(column=2, row=rownum).value is not None: DataFileParser.__fillRow(row, PropertyName.TIME_PERIOD.value, worksheet.cell(column=2, row=rownum), False) DataFileParser.__fillRow(row, PropertyName.VOLUME.value, worksheet.cell(column=3, row=rownum), True) DataFileParser.__fillRow(row, PropertyName.ENERGY.value, worksheet.cell(column=4, row=rownum), True) row[PropertyName.TIMESTAMP.value] = data_timestamp res.append(row) DataFileParser.logger.debug( f"Monthly data read successfully between row #{minRowNum} and row #{maxRowNum}" ) return res
def populate(self, ws: Worksheet, cg: ConnectGroup): ws.title = cg.name self.create_column_headers(ws) for person in sorted( cg.members, key=lambda p: p.personal_attributes[PERSONAL_ATTRIBUTE_NAME] ): ws.append(self.person_as_row_values(person))
def read_von_neumann_excel(sheet: Worksheet, start_row: int, start_column: int, cells_states: List[Tuple[str, str]]) -> List[int]: zero_color = sheet.cell(start_row + 1, start_column).fill.start_color.value zero_color = "#" + zero_color[2:] zero_type = 0 for i in range(len(cells_states)): if cells_states[i][1] == zero_color: zero_type = i one_color = sheet.cell(start_row, start_column + 1).fill.start_color.value one_color = "#" + one_color[2:] one_type = 0 for i in range(len(cells_states)): if cells_states[i][1] == one_color: one_type = i two_color = sheet.cell(start_row + 1, start_column + 1).fill.start_color.value two_color = "#" + two_color[2:] two_type = 0 for i in range(len(cells_states)): if cells_states[i][1] == two_color: two_type = i three_color = sheet.cell(start_row + 2, start_column + 1).fill.start_color.value three_color = "#" + three_color[2:] three_type = 0 for i in range(len(cells_states)): if cells_states[i][1] == three_color: three_type = i four_color = sheet.cell(start_row + 1, start_column + 2).fill.start_color.value four_color = "#" + four_color[2:] four_type = 0 for i in range(len(cells_states)): if cells_states[i][1] == four_color: four_type = i rule = [zero_type, one_type, two_type, three_type, four_type] return rule
def first_difference(left: Worksheet, right: Worksheet) -> Optional[Tuple[Diff, int]]: """ Compare two sheets and return the first difference found, if any, plus the index (starting from 1) of the row that differed. :param left: The left-hand sheet :param right: The right-hand sheet :return: The first difference and index or None """ left_rows = left.iter_rows() right_rows = right.iter_rows() left_row: Union[Row, None] right_row: Union[Row, None] row_index = 0 while True: left_row = next(left_rows, None) right_row = next(right_rows, None) row_index += 1 if left_row is None and right_row is None: return None if left_row is None: return (None, right_row), row_index if right_row is None: return (left_row, None), row_index if not rows_match(left_row, right_row): return (left_row, right_row), row_index
def remove_worksheet_rows( *, ws: Worksheet, rows_to_remove: List[int] ) -> Worksheet: """ Remove worksheet rows. Parameters ---------- ws : Worksheet A worksheet from a workbook. rows_to_remove: List[int] The list of row numbers to remove. Returns ------- ws : Worksheet A worksheet from a workbook. Example ------- >>> import datasense as ds >>> ws = ds.remove_worksheet_rows( >>> ws=ws, >>> rows_to_remove=rows_to_remove >>> ) """ for row in reversed(rows_to_remove): ws.delete_rows( idx=row, amount=1 ) return ws
def load_initial_schedules(ws: Worksheet, monitor_dict: dict): """ 指定シートからあらかじめ代入されている予定を読み取り、各監視者のスケジュールを初期化する。 :param ws: ワークシート :param monitor_dict: 監視者の辞書(key:=name, item:=Monitor) :return: 監視者のlatestシートにおける列インデックスの辞書(key:=name, item:=column index), 日付の辞書(key:=行番号, item:=datetime) """ monitor_column_dict = create_monitor_col_dict(ws, monitor_dict) num_of_monitors = len(monitor_dict) weekday_dict = {} holiday_col = find_col_idx_by_val(ws, HEADER_ROW_IDX, 'Holiday') for row_idx, row in enumerate( ws.iter_rows(min_row=DATA_START_ROW_IDX, max_col=num_of_monitors + 2), DATA_START_ROW_IDX): day = row[0].value if not day: break if not is_weekday(day, ws.cell(row=row_idx, column=holiday_col)): continue weekday_dict[row_idx] = day for idx, monitor in enumerate(monitor_dict.values(), 1): if val := row[idx].value: role = convert_val_to_role(val) monitor.schedule[day] = role
def _add_values_to_sheet(tag: SongTag, song: Song, sheet: Worksheet, second_sheet: Union[Worksheet, None] = None): """Add the values from a speicifc tag to the indicated sheet. If a second sheet is added, add the second values from the tag. Parameters ---------- tag : SongTag - Which tag the data should be received from\n song : Song - Which song the data should be receieved from\n sheet : Worksheet - Which sheet the data should be added to\n second_sheet : Union[Worksheet, None], optional - Second sheet where data should be added, by default None """ values = tag.get_tag(song.id3) if values is None: return for value in values: if tag.value_length == 1: value = cast(str, value) sheet.append([value]) elif tag.value_length == 2: value = cast(List[str], value) role, person = value sheet.append([role]) if second_sheet is not None: second_sheet.append([person])
def merge_region(sheet: Worksheet, min_row, max_row, min_col, max_col): assert max_row >= min_row > 0 and max_col >= min_col > 0 merged_region = ( f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}" ) sheet.merge_cells(merged_region)
def write_samples_sheet(sheet: Worksheet, df_samples: pd.DataFrame, min_coverage: int): """ Write and format samples sheet in workbook """ # write data for r in dataframe_to_rows(df_samples, header=True, index=False): sheet.append(r) # apply conditional filling to perc_covered column perc_column = next(x[0].column_letter for x in sheet.columns if x[0].value == "perc_covered") for i, row in enumerate(df_samples.itertuples(), start=2): if pd.isna(row.perc_covered) or round(row.perc_covered) < min_coverage: color = "fbc1c1" else: color = "cefbc1" sheet[f"{perc_column}{i}"].fill = PatternFill(start_color=color, fill_type="solid") # improve style for col in sheet.columns: col[0].font = Font(name="Calibri", bold=True) col[0].border = Border( bottom=Side(border_style="medium", color="000000")) col[0].alignment = Alignment(horizontal="center") sheet.column_dimensions[col[0].column_letter].width = 13
def parse_xlsx_product_option(ws: Worksheet, product_options_sheet: ProductOptionsSheet, options_rows: List[int]) -> List[ProductOption]: res = list() for index, row_index in enumerate(options_rows): name = ws.cell(row=row_index, column=product_options_sheet.name_column_index).value if isinstance(name, int): name = str(name) else: name.strip() price = float( ws.cell(row=row_index, column=product_options_sheet.price_column_index).value) res.append( ProductOption( name=name, price=price, index=index, is_default=index == 0, xlsx_row=row_index, )) return res
def saveActiveProjectTaskMapping(worksheet: Worksheet, startRow: int = 1, startColumn: int = 1): """Saves the active project list and active task list data. The projects are stored in one row, the tasks corresponding to the project are saved in the same column as the project Arguments: worksheet {Worksheet} -- [description] startRow {int} -- starting row form where the data should be stored startColumn {int} -- starting column form where the data should be stored """ projectList = excelDatabase.getActiveProjects() if projectList is None: return for project in projectList: taskList = excelDatabase.getActiveTasksByProjectId(project.projectId) if taskList is None: taskList = [] task = Task() task.taskName = "-" taskList.append(task) project.taskList = taskList defaultProject = getDefaultProjects() projectList.extend(defaultProject) column = startColumn for project in projectList: row = startRow worksheet.cell(row=row, column=column).value = project.projectName for task in project.taskList: row += 1 worksheet.cell(row=row, column=column).value = task.taskName column += 1
def create_all_cards(sheet: Worksheet, index: ScryfallDataIndex) -> None: """Create all cards sheet from card_db.""" sheet.title = "All Cards" sheet.append(ALL_CARDS_SHEET_HEADER) for name in sorted(index.name_to_cards): row = [name, get_references(index, name)] sheet.append(row)
def _fill_neat_settings(self, sheet: Worksheet, col_to_paste: int, row_to_paste: int, neat_settings: list) -> tuple: next_row = row_to_paste section: dict end_column_index = col_to_paste for section in neat_settings: should_render = section.get('show') if not should_render: continue translate: str = ReportPredictionResultsExcelMaker.neat_settings_translations[section.get('header')] sheet.cell(row=next_row, column=col_to_paste, value=translate) next_row += 1 section_params = section.get('params') param: dict for param in section_params: should_render = param.get('showInGui') if not should_render: continue translate: str = ReportPredictionResultsExcelMaker.neat_settings_translations[param.get('name')] sheet.cell(row=next_row, column=col_to_paste, value=translate) value = param.get('value') if type(value) is list: for i in range(len(value)): end_column_index = max(end_column_index, col_to_paste + i + 1) sheet.cell(row=next_row, column=col_to_paste + i + 1, value=ReportPredictionResultsExcelMaker.neat_settings_translations[value[i]]) elif type(value) is bool: sheet.cell(row=next_row, column=col_to_paste + 1, value='Да' if value is True else 'Нет') else: sheet.cell(row=next_row, column=col_to_paste + 1, value=value) next_row += 1 return next_row, end_column_index
def cell_fill_down( *, ws: Worksheet, min_row: int, max_row: int, min_col: int, max_col: int ) -> Worksheet: """ Fill empty cell with the value from the cell above Parameters ---------- ws : Worksheet The worksheet in which to change the case of column(s). min_row : int The first row in the range to change. max_row : int The last row in the range to change. min_col : int The first column in the range to change. max_col : int The last column in the range to change. Returns ------- ws : Worksheet The worksheet in which cells were modified. Example ------- >>> for column in fill_down_columns: >>> ws = ds.cell_fill_down( >>> ws=ws, >>> min_row=row_below_labels, >>> max_row=ws.max_row, >>> min_col=column_names_numbers[column], >>> max_col=column_names_numbers[column] >>> ) """ row_count = 0 for row in ws.iter_rows( min_col=min_col, max_col=max_col ): for cell in row: if cell.value: row_count += 1 if row_count > 0: for row in ws.iter_rows( min_row=min_row + 1, # start one row below the 'start' row max_row=max_row, min_col=min_col, max_col=max_col ): for cell in row: if cell.value in [None, 'None', '']: cell.value = ws[cell.row - 1][min_col - 1].value return ws
def _insert_sheet_title(self, sheet: Worksheet, month_name_and_year: str): current_row = str(self.__CURRENT_ROW) start_cell = self.__LEFT_COLUMNS[0] + str(current_row) end_cell = self.__RIGHT_COLUMNS[3] + str(current_row) sheet[start_cell] = self.language_pack.meeting_name + ' – ' + month_name_and_year sheet.merge_cells(start_cell + ':' + end_cell) self.__CURRENT_ROW += 2 self._style_sheet_title(sheet)
def _get_last_cell(ws: Worksheet, row=-1, col=1) -> Tuple[int, datetime.date]: row_count = 1 while ws.cell(row_count + 1, col).value: row_count += 1 row = max(row_count + row + 1, 1) return row, ws.cell(row, col)
def copy_sheet_header(main_ws: Worksheet, dst_ws: Worksheet): # determine max column #dst_ws.merge_cells('A1:M7') dst_ws.merged_cells.ranges = main_ws.merged_cells.ranges for r in main_ws.iter_rows(1, NUM_ROW_HEADER): for c in r: #dst_ws.cell(c.row, c.column, c.value) dst_ws.copy_cell(c.row, c.column, c)
def parse_metadata_command(sh: Worksheet, area: AreaTupleType, name: str = None) -> IssuesLabelContentTripleType: """ Most "parse" methods are mostly syntactic (as opposed to semantic). They do not check existence of names. But in this case, the valid field names are fixed beforehand, so they are checked at this time. Some of the fields will be controlled also, according to some :param sh: Input worksheet :param area: Tuple (top, bottom, left, right) representing the rectangular area of the input worksheet where the command is present :return: list of issues (issue_type, message), command label, command content """ some_error = False issues = [] controlled = create_dictionary() mandatory = create_dictionary() keys = create_dictionary() for t in metadata_fields: controlled[t[4]] = t[3] mandatory[t[4]] = t[2] keys[t[0]] = t[4] # Scan the sheet, the first column must be one of the keys of "k_list", following # columns can contain repeating values # Map key to a list of values content = {} # Dictionary of lists, one per metadata key for r in range(area[0], area[1]): label = sh.cell(row=r, column=area[2]).value if label in keys: key = keys[label] for c in range(area[2]+1, area[3]): value = sh.cell(row=r, column=c).value if value: value = str(value).strip() if controlled[key]: # Control "value" if the field is controllable cl = {"dimensions": ["water", "energy", "food", "land", "climate"], "subject_topic_keywords": None, "geographical_level": ["local", "regional", "region", "country", "europe", "global", "sectoral", "sector"], "geographical_situation": None, # TODO Read the list of all geographical regions (A long list!!) "restriction_level": ["internal", "confidential", "public"], "language": None, # TODO Read the list of ALL languages (or just "English"??) } if cl[key] and value.lower() not in cl[key]: issues.append((3, "The key '"+key+"' should be one of: "+",".join(cl[key]))) if key not in content: content[key] = [] content[key].append(value) else: issues.append((2, "Row "+str(r)+": unknown metadata label '"+label+"'")) for key in keys.values(): if mandatory[key] and key not in content: some_error = True issues.append((3, "The value '"+key+"' is mandatory in the definition of the metadata")) return issues, None, content
def __writeCurveChartData(sheet: Worksheet, tests: Iterable[str], order: Iterable[str], data: Dict[str, dict]) -> dict: from .TestSuite import _PSNR, _KBS, _TIME, parseSheetLayer to_write_data = [] out_ref = {} col = 2 ref_len = 4 def toRefFunc(sheet, cells): return [ "=" + __SR_FORMAT.format(sheet=parseSheetLayer(sheet)[0], cell=cell) for cell in cells ] for seq in order: to_write_data.append([ seq, ]) out_ref[seq] = {} to_write_data.append([ "Data Type", "Test", "Data Point 1", "Data Point 2", "Data Point 3", "Data Point 4" ]) for test in tests: out_ref[seq][test] = {} to_write_data.append( [_KBS, test, *toRefFunc(test, data[test][seq][_KBS])]) to_write_data.append( [_PSNR, test, *toRefFunc(test, data[test][seq][_PSNR])]) to_write_data.append( [_TIME, test, *toRefFunc(test, data[test][seq][_TIME])]) row = len(to_write_data) out_ref[seq][test][__RATE] = Reference(sheet, min_col=col, max_col=col + ref_len, min_row=row - 2) out_ref[seq][test][__PSNR] = Reference(sheet, min_col=col, max_col=col + ref_len, min_row=row - 1) out_ref[seq][test][__TIME] = Reference(sheet, min_col=col, max_col=col + ref_len, min_row=row) for row in to_write_data: sheet.append(row) # hide chart data sheet.column_dimensions.group('A', 'F', hidden=True) return out_ref
def _fill_sheet(data, sheet: Worksheet, fields): if sheet.max_row == 1: for col, field in enumerate(fields, 1): sheet.cell(1, col, field["name"]) row = sheet.max_row + 1 for col, field in enumerate(fields, 1): value = data.get(field["name"]) if isinstance(value, (list, dict)): value = json.dumps(value) sheet.cell(row, col, value)
def collect_table(worksheet: Worksheet, year: int, fund_name, row: int, column: int) -> object: """ Start to run on the fields and look for the anchor cell which from there the data lay out. """ data = {} for iterated_row in range(row, row + 21): row_label: str = worksheet.cell(row=iterated_row, column=column).value if not row_label: # Some of the years has less than 21 fields which mean we'll fail # with older files. Skipping on an empty row label. continue row_data = {} for iterated_column in range(column + 1, column + 1 + 24): # Getting the matching month of the current cell and resetting # the cell value. month_index = int(iterated_column / 2) if month_index not in months: continue month_name = months[month_index] if month_name not in row_data: row_data[month_name] = { 'התרומה לתשואה': '', 'שיעור מסך הנכסים': '', 'year': year, 'fund': fund_name, } cell_kwargs = {'row': iterated_row, 'column': iterated_column} cell_value = worksheet.cell(**cell_kwargs).value if cell_value: if type(cell_value) is not str: cell_value = format(cell_value * 100, '.2f') # Values in cell with even index number have a label and odd # indexed-cell have a different label. if iterated_column % 2 == 0: key = 'התרומה לתשואה' else: key = 'שיעור מסך הנכסים' row_data[month_name][key] = cell_value data[row_label.strip()] = row_data return data
def find_or_create_row(self, rls_date: datetime.date, ws: Worksheet): row = 1 while True: row += 1 date = ws.cell(row, 1).value if date == rls_date: return row if not date or date > rls_date: ws.insert_rows(row) ws.cell(row, 1, rls_date) return row
def add_headers_and_title(sheet: Worksheet): headers = ('序号', '考试日期', '考试时间', '考试名称', '开课院系', '考试地点', '班级', '考生人数', '考生院系') for index, header in enumerate(headers): sheet.cell(2, index + 1, header) title = '东北大学考试日程表' sheet.merge_cells(start_row=1, end_row=1, start_column=1, end_column=9) cell = sheet['A1'] cell.value = title cell.alignment = Alignment(horizontal="center", vertical="center")
def _find_free_cell(self, col_index: int, row_index: int, sheet: Worksheet) -> Tuple[Cell, int]: target_cell = sheet.cell(row_index + 1, col_index + 1) while True: if isinstance(target_cell, MergedCell): col_index += 1 target_cell = sheet.cell(row_index + 1, col_index + 1) else: break return target_cell, col_index
def _finalize_styling(self, sheet: Worksheet): sheet.page_setup.paperSize = Worksheet.PAPERSIZE_A4 sheet.sheet_properties.pageSetUpPr.fitToPage = True sheet.page_margins = PageMargins(left=self.excel_config.MARGIN_LENGTH, right=self.excel_config.MARGIN_LENGTH) sheet.column_dimensions[self.__LEFT_COLUMNS[0]].width = 4 sheet.column_dimensions[self.__LEFT_COLUMNS[1]].width = 42 sheet.column_dimensions[self.__RIGHT_COLUMNS[0]].width = 4 sheet.column_dimensions[self.__RIGHT_COLUMNS[1]].width = 42 for rows in sheet.iter_rows(min_row=3): for row in rows: sheet.row_dimensions[row.row].height = self.excel_config.ROW_HEIGHT
def add_sale_data(sheet: Worksheet, sale_record: InvestmentRecord, recs_and_quants_to_sell: list): """Returns the number of rows added for subquantities. """ net_capital_gain_formula = "=(" + str(sale_record.quantity) + "*" \ + cell.get_column_letter(COLUMNS["Average price"]) \ + str(sale_record.row_idx) + ")" num_recs_and_quants = len(recs_and_quants_to_sell) using_subquantities = True if num_recs_and_quants > 1 else False current_row_idx = sale_record.row_idx + 1 if using_subquantities else sale_record.row_idx quantity_column_to_use = COLUMNS[ "Subquantity"] if using_subquantities else COLUMNS["Quantity"] for rec, quant in recs_and_quants_to_sell: if using_subquantities: sheet.cell(current_row_idx, quantity_column_to_use).value = quant # Else we assume the Quantity of the investment record was written earlier cost_base_formula = "=" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \ + "*" + cell.get_column_letter(COLUMNS["Average price"]) + str(rec.row_idx) \ + "+(" + cell.get_column_letter(COLUMNS["Brokerage"]) + str(rec.row_idx) \ + "*" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \ + "/" + cell.get_column_letter(COLUMNS["Quantity"]) + str(rec.row_idx) + ")" \ + "+(" + cell.get_column_letter(COLUMNS["Brokerage"]) + str(sale_record.row_idx) \ + "*" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \ + "/" + cell.get_column_letter(COLUMNS["Quantity"]) + str(sale_record.row_idx) + ")" sheet.cell(current_row_idx, COLUMNS["Cost base"]).value = cost_base_formula capital_gain_formula = "=(" + cell.get_column_letter(quantity_column_to_use) + str(current_row_idx) \ + "*" + cell.get_column_letter(COLUMNS["Average price"]) + str(sale_record.row_idx) \ + ")-" + cell.get_column_letter(COLUMNS["Cost base"]) + str(current_row_idx) if sale_record.trade_date > rec.trade_date + timedelta(days=365): capital_gain_column_to_use = COLUMNS["Capital gain > 1 year"] else: capital_gain_column_to_use = COLUMNS["Capital gain <= 1 year"] sheet.cell(current_row_idx, capital_gain_column_to_use).value = capital_gain_formula history = sheet.cell(rec.row_idx, COLUMNS["History"]).value new_history = "Sold " + str( quant) + " on " + sale_record.trade_date.strftime("%d/%m/%Y. ") sheet.cell(rec.row_idx, COLUMNS["History"]).value = (history + new_history if history else new_history) current_row_idx += 1 return num_recs_and_quants if using_subquantities else 0
def __writeCharts(sheet: Worksheet, tests: Iterable[str], order: Iterable[str], charts: Iterable[Tuple[str, str]], data: Dict[str, dict]) -> None: row = 1 bound_scale = 10 def updateBounds(bounds, x_data, y_data): from statistics import mean if not bounds: bounds = [0, 0, 0, 0, 0, 0, 0] bounds[0] = min(bounds[0], *x_data) bounds[1] = max(bounds[1], *x_data) bounds[2] = mean(bounds[2], *x_data) bounds[3] = min(bounds[3], *y_data) bounds[4] = max(bounds[4], *y_data) bounds[5] = mean(bounds[5], *y_data) return bounds for seq in order: col = 0 for (typeX, typeY) in charts: chart = ScatterChart(scatterStyle='lineMarker') chart.title = seq chart.x_axis.title = typeX chart.y_axis.title = typeY chart.visible_cells_only = False bounds = None for test in tests: #bounds = updateBounds(bounds, data[seq][test][__DATA][typeX], data[seq][test][__DATA][typeY]) rX = data[seq][test][typeX] rY = data[seq][test][typeY] series = Series(Reference(sheet, min_col=rY.min_col, max_col=rY.max_col, min_row=rY.min_row), Reference(sheet, min_col=rX.min_col + 1, max_col=rX.max_col, min_row=rX.min_row), title_from_data=True) series.marker.symbol = 'auto' chart.series.append(series) if bounds: sheet.x_axis.scaling.min = max( bounds[0] - bounds[2] / bound_scale, 0) sheet.x_axis.scaling.max = bounds[1] + bounds[2] / bound_scale sheet.y_axis.scaling.min = max( bounds[3] - bounds[5] / bound_scale, 0) sheet.y_axis.scaling.max = bounds[4] + bounds[5] / bound_scale sheet.add_chart(chart, get_column_letter(7 + col) + str(row)) col += 9 row += 15