def copy_df_xl(df: pd.DataFrame, sheet: str, name: str, copy_columns: bool = False, xl: w32 = None): xl.ScreenUpdating = False try: excel_data = df.to_records(index=False).tolist() if copy_columns: excel_data.insert(0, tuple(df.columns.tolist())) range = get_address(name) row, _ = range.split(":") start_cell = row if copy_columns else xl_add_row(row, 1) add_row = 1 if not copy_columns else 0 end_cell = xl_add_row(start_cell, df.shape[0] - add_row) end_cell = xl_add_column(end_cell, df.shape[1] - 1) write_range = start_cell + ":" + end_cell xl.ActiveWorkbook.Sheets(sheet).Range(write_range).Value = excel_data finally: xl.ScreenUpdating = True
def set_formula( name: str, fn: Callable = None, args: str = None, xl: w32 = None, replace: bool = False, ): """ Set a formula ina given cell. Used to instantiate objects like data_obj_cfg and data_obj_manager Parameters ---------- name: str Excel named range fn: Callable A function to be executed args: str Arguments to the function xl: {None, xl_app} replace: bool Replace if exists flag """ args = "" if not args else args if isinstance(fn, FunctionType): formula = f"={fn.__name__}({args})" else: formula = "" if not check_object_alive(name) or replace: # logging.info(f"Setting {name} as {formula}") xl.Range(name).Formula = formula
def copy_np_xl(array: np.ndarray, sheet: str, name: str, xl: w32 = None): """ 1D array to a excel sheet :param array: :param sheet: :param name: :param xl: :return: """ xl.ScreenUpdating = False try: excel_data = pd.DataFrame(array).to_records(index=False).tolist() range = get_address(name) start_cell, _ = range.split(":") end_cell = xl_add_row(start_cell, array.shape[0] - 1) write_range = start_cell + ":" + end_cell xl.ActiveWorkbook.Sheets(sheet).Range(write_range).Value = excel_data finally: xl.ScreenUpdating = True
def excel_styler(address: str, style_format: str, xl: w32 = None): """ Styles a Excel Range Parameters ---------- address: str Excel Address style_format: str Excel Styling option xl: {None, xl_app} """ xl.Range(address).Style = style_format
def set_value(name, value, xl: w32 = None): """ Sets value in a given range Parameters ---------- name: str Excel named range value: value Value to be placed in the range xl: {None, xl_app} """ # logging.info(f"Setting {name} value") xl.Range(name).Value = value
def get_address(name: str, xl: w32 = None) -> str: """ Address from a named range Parameters ---------- name: str Excel Address xl: {None, xl_app} Returns ------- range address as str """ return xl.Evaluate(name).GetAddress()
def check_name_present(name: str, xl: w32 = None) -> bool: """ Checks if the named range is present in the excel. Parameters ---------- name: str Excel named range xl: {None or xl_app} Returns ------- bool indicating the presence of the named range """ return False if isinstance(xl.Evaluate(name), int) else True
def clear_user_zones(containing_string: str = "state", including_columns: bool = False, xl: w32 = None): """ Clears user zones from the sheet. Very similar to clear_list Parameters ---------- containing_string: str String that group user zones in the project. including_columns: bool Flag to clear columns. xl: {None, xl_app} See Also -------- clear_list """ # TODO: reduce COC xl.ScreenUpdating = False logging.info("Clearing user input zones") remove_list = get_names_with_string(containing_string, xl=xl) try: for entity in remove_list: if including_columns or is_scalar(get_address(entity)): set_formula(entity, fn=None, xl=xl, replace=True) elif count_nrows(get_address(entity)) > 1: current_range = get_address(entity) entity_sheet = get_sheet_of_name(entity) modified_range = xl_add_row(current_range) xl.ActiveWorkbook.Sheets(entity_sheet).Activate() xl.ActiveSheet.Range(modified_range).Formula = "" finally: xl.ScreenUpdating = True
def excel_formatter(address: str, format_option: str, xl: w32 = None): """ Formats a Excel Range Parameters ---------- address: str Excel Address format_option: str Excel Formatting option. For further information, https://support.microsoft.com/en-us/help/264372/how-to-control-and-understand-settings-in-the-format-cells-dialog-box xl: {None, xl_app} """ xl.Range(address).NumberFormat = format_option
def get_value(name: str, xl: w32 = None): """ Returns the value of a excel named range Parameters ---------- name: str Excel named range xl: {None, xl_app} Raises ------ NameMissingInExcel Returns ------- value of the range in a tuple """ try: return xl.Evaluate(name).Value except AttributeError: raise NameMissingInExcel(name)
def excel_data_bar(address: str, xl: w32 = None): xl.Range(address).BarFillType = win32.constants.xlDataBarFillSolid