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 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 _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 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 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 _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 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 _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 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 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_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 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 getIndexBysheet(sheet: Worksheet): index: dict = {} print("sheet的名称是:", sheet.title, ",共有列数是:", sheet.max_column, ",第一行最右边的单元格值是:", sheet.cell(row=1, column=sheet.max_column).value) for i in range(1, sheet.max_column + 1): cell: Cell = sheet.cell(row=1, column=i) cellValue: str = cell.value if cellValue is not None and "姓名".__contains__(cellValue): index.__setitem__("nameIndex", i) elif cellValue is not None and "项目".__contains__(cellValue): index.__setitem__("projectIndex", i) elif cellValue is not None and "实际工时数 (小时)".__contains__(cellValue): index.__setitem__("hourIndex", i) return index
def __writeAnchorListData(sheet: Worksheet, ref: Dict[str, dict], sub_def: AnchorSubType, row: int, col: int, *, data_func: Callable[[Dict[str, dict], str], Union[float, int]], data_format: str, number_format: Union[None, str] = None, number_style: str = 'Percent', abs_format: Union[None, str] = None, abs_style: str = 'Comma') -> None: from .TestSuite import parseSheetLayer #final_r = row+len(data.keys()) #final_c = col+len(data.keys()) c = col for (test, anchors) in sub_def.items(): for anchor in anchors: value_format = data_format value_style = number_style anchor_res = [] if not anchor: if abs_format: value_format = abs_format if abs_style: value_style = abs_style else: continue else: anchor_res = [ __SR_FORMAT.format(sheet=parseSheetLayer(anchor)[0], cell=cl) for cl in data_func(ref, anchor) ] test_res = [ __SR_FORMAT.format(sheet=parseSheetLayer(test)[0], cell=cl) for cl in data_func(ref, test) ] sheet.cell(row=row, column=c).value = value_format.format(*(anchor_res + test_res)) sheet.cell(row=row, column=c).style = value_style if number_format: sheet.cell(row=row, column=c).number_format = number_format sheet.cell( row=row, column=c).alignment = xl.styles.Alignment(horizontal='center') c += 1
def load_manual_remote_max(ws: Worksheet, monitor_dict: dict, monitor_column_dict: dict): """ 手動で入力された在宅勤務数の上限を読み込み、MonitorScheduleに設定する。 :param ws: 読み込むシート :param monitor_dict: 監視者の辞書(key:=name, item:=Monitor) :param monitor_column_dict: 監視者のlatestシートにおける列インデックスの辞書(key:=name, item:=column index) :return: None """ for monitor in monitor_dict.values(): if remote_max := ws.cell( row=REMOTE_MAX_ROW_IDX, column=monitor_column_dict[monitor.name]).value: monitor.role_max[ERole.R] = remote_max
def _write_cell_if_value_provided(self, worksheet: Worksheet, column: int, row: int, value: str): if value: # Scale the size of the column with the input value if necessary. By default width is None. if worksheet.column_dimensions[get_column_letter( column)].width is not None: worksheet.column_dimensions[get_column_letter( column)].width = max( worksheet.column_dimensions[get_column_letter( column)].width, len(value)) else: worksheet.column_dimensions[get_column_letter( column)].width = DEFAULT_COL_WIDTH worksheet.cell(column=column, row=row, value=value)
def __writeAnchorListHeader(sheet: Worksheet, sub_def: AnchorSubType, row: int, col: int, allow_none: bool = True) -> None: #Write horizontal headers/test names tmp_col = col for (test, anchors) in sub_def.items(): for anchor in anchors: if not allow_none and not anchor: continue sheet.cell(row=row, column=tmp_col).value = test sheet.cell(row=row + 1, column=tmp_col).value = anchor tmp_col += 1 return tmp_col - 1
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 new_line(ws: Worksheet, space: int = 1, init_row: int = 1, col: int = 1) -> int: row = init_row while True: if ws.cell(row=row, column=col).value is None: break row += space return row
def excel_load_sheet(sheet: Worksheet) -> List[Dict]: print("Processing sheet '{}'".format(sheet.title)) if sheet.max_row <= EXCEL_DATA_START_ROW: print("WARNING: sheet '{}' contains too little data".format( sheet.title)) data_names = excel_get_data_names(sheet) r = [] for row_i in range(EXCEL_DATA_START_ROW, sheet.max_row + 1): row_data = {} for col_i in range(1, len(data_names) + 1): data_name_i = col_i - 1 cell_value = sheet.cell(row=row_i, column=col_i).value if isinstance(cell_value, str): cell_value = many_spaces_re.sub(' ', cell_value.strip()) row_data[data_names[data_name_i]] = cell_value r.append(row_data) print("{} rows successfully loaded from sheet {}".format( len(r), sheet.title)) return r
def set_cell_value(sheet: Worksheet, row_index: int, column_index: int, val: Any, config: Optional[CellConfig] = None): if config is None: config = CellConfig() c = sheet.cell(row_index + 1, column_index + 1) if isinstance(val, Decimal): c.number_format = config.decimal_format c.alignment = config.number_alignment elif isinstance(val, int): c.number_format = config.int_format c.alignment = config.number_alignment elif isinstance(val, float): c.number_format = config.float_format c.alignment = config.number_alignment elif isinstance(val, (datetime, date)): c.style = config.date_style if isinstance(val, datetime): val = val.replace(tzinfo=None) else: val = strip_tags(str(val if val else "")) c.value = val return c
def __writeSummaryMatrixHeader(sheet: Worksheet, tests: List[str], row: int, col: int) -> Tuple[int, int]: d_row = row + 1 d_col = col + 1 #Write horizontal headers/test names tmp_col = col + 1 for test in tests: sheet.cell(row=row, column=tmp_col).value = test tmp_col += 1 #Write vertical tmp_row = row + 1 for test in tests: sheet.cell(row=tmp_row, column=col).value = test tmp_row += 1 return d_row, d_col
def find_row(column_name: str, value: str, ws: Worksheet) -> int: column_index = get_columns_names(ws).index(column_name) + 1 for i in range(2, ws.max_row): if ws.cell(row=i, column=column_index).value == value: return i return -1
def parse_cell(cell: Cell, row_nr: int, column_nr: int, worksheet: Worksheet) -> Expenditure: # noqa: D103 date = worksheet.cell(row=row_nr, column=COLUMN_DATE).value return Expenditure( value=float(cell.value), date=date, category=COLUMNS[column_nr], comment=sanitize_comment(cell.comment), )
def output_schedules(ws: Worksheet, monitor_dict: dict, weekday_dict: dict, monitor_column_dict: dict): monitor_name_st_col = find_col_idx_by_val(ws, HEADER_ROW_IDX, ERole.AM1.name) monitor_name_cols = { ERole.AM1: monitor_name_st_col, ERole.AM2: monitor_name_st_col + 1, ERole.PM: monitor_name_st_col + 2 } for row_idx, weekday in weekday_dict.items(): for monitor in monitor_dict.values(): if (role := monitor.schedule.get(weekday)) and role in OUTPUT_ROLES: ws.cell(row=row_idx, column=monitor_column_dict[monitor.name], value=role.name) if col_idx := monitor_name_cols.get(role): ws.cell(row=row_idx, column=col_idx, value=monitor.name)
def get_cell_name(sheet: Worksheet, row: int, col: int) -> str: """ get cell name "A1" ... :param sheet: sheet get name :param row: row index :param col: column index :return: string name cell """ return sheet.cell(row=row + 1, column=col + 1).coordinate
def get_cell(sheet: Worksheet, row: int, col: int) -> Cell: """ get cell data :param sheet: sheet get info cell :param row: index row :param col: index col :return: cell info data """ return sheet.cell(row=row + 1, column=col + 1)
def _export_xlsx_data_rows(ws: Worksheet, amendement_dicts: Iterable[dict]) -> Counter: counter = Counter({"amendements": 0}) for amend_dict in amendement_dicts: for column, value in enumerate(HEADERS, 1): cell = ws.cell(row=counter["amendements"] + 2, column=column) cell.value = amend_dict[value] counter["amendements"] += 1 return counter