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