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 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 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