def del_row(self, sht_name, row, cnt=-1):
"""
删除行
:param sht_name:sheet页名称
:param row:删除的起始行
:param cnt:删除的行数，-1表示删除从row开始到结束的所有行
:return:True：成功，False：失败
"""
if row < 1:
LogUtils.debug_print(f"{row} <1 return")
return False
if cnt == -1:
row_e = max(self.get_max_row(sht_name), row)
else:
row_e = row + cnt - 1
if row > row_e:
tmp = row
row = row_e
row_e = tmp
try:
self.get_op_sheet(sht_name).range(
(row, 1), (row_e, 1)).api.EntireRow.Delete()
return True
except AttributeError:
LogUtils.debug_print(
f"del_row error! sheet = {sht_name} ,row = {row} row_e ={row_e}!"
)
return False
def click(self, pos):
try:
self.dev.click(pos[0], pos[1])
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def screen_on(self):
try:
self.dev.screen_on()
return True
except Exception as err:
LogUtils.debug_print(err)
return False
def rmdir(path):
"""
删除目录，同时删除目录下的文件夹和文件
:param path: 文件目录
:return: None
"""
# 去除首位空格
path = path.strip()
# 去除尾部 \ 符号
path = path.rstrip("\\")
# 判断路径是否存在
# 存在 True
# 不存在 False
is_exist = os.path.exists(path)
if not is_exist:
LogUtils.debug_print(f"{path} 不存在，无需删除")
return
try:
# os.rmdir(path)
shutil.rmtree(path)
except Exception as err:
LogUtils.error_print(err)
def del_col(self, sht_name, col, cnt=-1):
"""
删除列
:return:
:param sht_name:sheet页名称
:param col:删除的；；列数
:param cnt:删除的列数，-1表示删除从col开始到结束的所有列
:return:True：成功，False：失败
"""
if col < 1:
print(f"{col} <1 return")
return False
if cnt == -1:
col_e = max(self.get_max_row(sht_name), col)
else:
col_e = col + cnt - 1
if col > col_e:
tmp = col
col = col_e
col_e = tmp
try:
self.get_op_sheet(sht_name).range(
(col, 1), (col_e, 1)).api.EntireColumn.Delete()
return True
except AttributeError:
LogUtils.debug_print(
f"del_row error! sheet = {sht_name} ,row = {col} row_e ={col_e}!"
)
return False
def double_click(self, click_pos: [], duration=0.05):
try:
self.dev.double_click(click_pos[0], click_pos[1], duration)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def long_click(self, pos, duration: float = 3):
try:
self.dev.long_click(pos[0], pos[1], duration)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def screen_shot(self, file_path):
try:
self.dev.screenshot(file_path)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def read_range_list(self, sht_name, row, col, row_e, col_e):
"""
强制读取必须返回list格式，通过多读取两行的方式，保证读取的是list，然后返回值中再去掉2行数据
:param sht_name:sheet页名称
:param row:开始行号
:param col:开始的列号
:param row_e:结束的行号，为-1时，获取最大行
:param col_e:结束的列号，为-1时，获取最大列
:return:返回读取的值
"""
if row_e == -1:
row_e = max(self.get_max_row(sht_name), row) + 2
else:
row_e += 2
if col_e == -1:
col_e = max(self.get_max_col(sht_name), col)
try:
info_list = self.get_op_sheet(sht_name).range(
(row, col), (row_e, col_e)).options(transpot=True).value
return info_list[:-2]
except AttributeError:
LogUtils.debug_print(
f"read range error! sheet = {sht_name} ,"
f"row = {row}, col = {col} ,row_end = {row_e}, col_end = {col_e} error!"
)
return None
def read_range(self, sht_name, row, col, row_e=-1, col_e=-1):
"""
读取指定区域的内容
结束行 -1：表示选择最后一行
结束列 -1：表示选择最后一列
:param sht_name:sheet页名称
:param row:开始行号
:param col:开始的列号
:param row_e:结束的行号，为-1时，获取最大行
:param col_e:结束的列号，为-1时，获取最大列
:return:返回读取的值
"""
if row_e == -1:
row_e = max(self.get_max_row(sht_name), row)
if col_e == -1:
col_e = max(self.get_max_col(sht_name), col)
try:
return self.get_op_sheet(sht_name).range(
(row, col), (row_e, col_e)).options(transpot=True).value
except AttributeError:
LogUtils.debug_print(
f"read range error! sheet = {sht_name} ,"
f"row = {row}, col = {col} ,row_end = {row_e}, col_end = {col_e} error!"
)
return None
def drag(self, fx, fy, tx, ty):
try:
self.dev.drag(fx, fy, tx, ty)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def stop_app(self, app_name):
try:
self.dev.app_stop(app_name)
time.sleep(APP_ACTION_WAIT)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def pinch_out(self, percent=30, steps=100):
try:
LogUtils.info_print(f"放大{percent}", 4)
self.dev().pinch_out(percent=percent, steps=steps)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def ph_connect(self, ph_id):
try:
self.dev = u2.connect(ph_id)
self.phone_id = ph_id
LogUtils.debug_print(self.dev.info)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def press_back(self):
try:
# 点击back
self.dev.press("back")
# 点击提示框
self.dev.click(0.443, 0.673)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def get_active_workbook():
"""
获取当前激活的excel文件的全路径文件名
:return:文件名
"""
active_book = None
try:
active_book = xw.books.active.fullname
except Exception as err:
LogUtils.debug_print(f"not find active book:{err}", 5)
return active_book
def get_active_sheet():
"""
获取当前激活的excel文件的激活的sheet表
:return:
"""
active_sheet = None
try:
active_sheet = xw.sheets.active.name
except Exception as err:
LogUtils.debug_print(f"not find active sheet:{err}")
return active_sheet
def get_max_col(self, sht_name=''):
"""
获取当前excel sheet也的最大列数
:param sht_name:sheet页名称
:return:最大列数
"""
try:
return self.get_op_sheet(sht_name).used_range.last_cell.column
except AttributeError:
LogUtils.debug_print(
f"read sheet{sht_name} used_range.last_cell.column error!")
return -1
def sheet_rename(self, old_name, new_name):
"""
更改sheet表名称
:param old_name:原sheet表名称
:param new_name:新sheet表名称
:return:
"""
try:
self.get_op_sheet(old_name).name = new_name
except AttributeError:
LogUtils.debug_print(
f"sheet_rename error, old_name = {old_name}, new_name = {new_name}!"
)
return False
def read_cell(self, sht_name, row, col):
"""
读取指定单元格的内容
:param sht_name: sheet页名称
:param row: 行号
:param col: 列号
:return:返回的值，读取出错，返货None
"""
try:
return self.get_op_sheet(sht_name).range(row, col).value
except AttributeError:
LogUtils.debug_print(
f"read cell error, sheet = {sht_name} ,row = {row}, col = {col} error!"
)
return None
def get_op_sheet(self, sht_name=''):
"""
获取需要操作的sheet的变量
:param sht_name:sheet的名称，如果未空则为之前设置的默认名称
:return:成功返回指定值，不成功则返回空
"""
if sht_name == '':
sht_name = self.dft_sh
try:
return self.wb.sheets[sht_name]
except Exception as err:
LogUtils.debug_print(
f"Try get file {self.file_path} no '{sht_name}' error={err}!")
return ''
def start_app(self,
package_name: str,
activity: Optional[str] = None,
wait: bool = False,
stop: bool = False,
use_monkey: bool = False):
try:
if package_name is None or package_name == '':
return False
if self.dev.info['currentPackageName'] == package_name:
return True
self.dev.app_start(package_name, activity, wait, stop, use_monkey)
return True
except Exception as error:
LogUtils.debug_print(error)
return False
def write_cell(self, sht_name, row, col, value):
"""
写指定单元格的内容
:param sht_name: sheet页名称
:param row: 行号
:param col: 列号
:param value: 具体的值
:return: True：成功，False：失败
"""
try:
self.get_op_sheet(sht_name).range(row, col).value = value
return True
except AttributeError:
LogUtils.debug_print(
f"write cell error! sheet = {sht_name} ,row = {row}, col = {col} error!"
)
return False
def write_col_range(self, sht_name, row, col, value):
"""
写指定区域的内容，数组以一列内容为第二维数据
:param sht_name: sheet页名称
:param row: 行号
:param col: 列号
:param value:具体的值
:return:True：成功，False：失败
"""
try:
self.get_op_sheet(sht_name).range(row, col).options(
transpose=True, expand='table').value = value
return True
except AttributeError:
LogUtils.debug_print(
f"write col range error! sheet = {sht_name} ,"
f"row = {row}, col = {col} error! \nvalue:{value}")
return False
def set_default_sheet(self, sht_name):
"""
设置默认需要操作的sheet页，对于部分操作一般会在一个sheet页操作时比较有用
:param sht_name: sheet页的名称
:return:True：设置成功，False：设置失败
"""
if self.wb == '':
LogUtils.debug_print(f"No file opened by python")
return False
for idx in range(self.wb.sheets.count):
if self.wb.sheets[idx].name == sht_name:
self.dft_sh = sht_name
return True
LogUtils.debug_print(
f"File '{self.file_path}' has no '{sht_name}' sheet")
return False
def write_hyperlink(self, sht_name, row, col, hyperlink, disp):
"""
写指定单元格的内容
:param sht_name: sheet页名称
:param row: 行号
:param col: 列号
:param hyperlink: 超链接的地址
:param disp: 显示的内容
:return: True：成功，False：失败
"""
try:
self.get_op_sheet(sht_name).range(row, col).add_hyperlink(
hyperlink, disp)
return True
except AttributeError:
LogUtils.debug_print(
f"write cell error! sheet = {sht_name} ,row = {row}, col = {col} error!"
)
return False
def mkdir(path, reset=False):
"""
创建文件夹
:param path:文件夹路径
:param reset:如果存在，需要先删除
:return:
"""
# 去除首位空格
path = path.strip()
# 去除尾部 \ 符号
path = path.rstrip("\\").rstrip('/')
# 需要复位，则先删除
if reset:
try:
rmdir(path)
time.sleep(1)
os.makedirs(path)
LogUtils.info_print(f"{path} 创建成功!", 5)
except Exception as err:
LogUtils.error_print(err)
return
# 判断路径是否存在
# 存在 True
# 不存在 False
is_exist = os.path.exists(path)
# 判断结果
if not is_exist:
# 如果不存在则创建目录
# 创建目录操作函数
os.makedirs(path)
LogUtils.info_print(f" {path} 创建成功!", 5)
return True
else:
# 如果目录存在则不创建，并提示目录已存在
LogUtils.debug_print(f" {path} 目录已存在!", 5)
return False
def insert_col(self, sht_name, col, cnt=1):
"""
插入列
:param sht_name:sheet页名称
:param col:在哪一列后面插入
:param cnt:插入多少列
:return:True：成功，False：失败
"""
if cnt < 1:
print(f"cnt:{cnt} <1, no need insert")
return False
col_e = col + cnt - 1
try:
self.get_op_sheet(sht_name).api.Rows(str(col) + ':' +
str(col_e)).Insert()
return True
except AttributeError:
LogUtils.debug_print(
f"insert_col error! sheet = {sht_name} ,col = {col} cnt ={cnt}!"
)
return False
def __del__(self):
"""
对象删除时，恢复显示及提示处理
如果文件通过程序自动打开，则再自动关闭
:return:
"""
if self.app is None:
return
if self.auto_open and self.wb is not None:
LogUtils.debug_print(
f"File {self.file_path} opened by Python, begin to close... ",
5)
self.wb.close()
self.wb = None
try:
self.app.display_alerts = True
self.app.screen_updating = True
self.app.quit()
except Exception as err:
LogUtils.error_print(err)
self.app = None
def insert_row(self, sht_name, row, cnt=1):
"""
插入行
:param sht_name:sheet页名称
:param row:需要在哪一行前面插入行
:param cnt:插入的行数
:return:True：成功，False：失败
"""
if cnt < 1:
print(f"cnt:{cnt} <1, no need insert")
return False
row_e = row + cnt - 1
try:
self.get_op_sheet(sht_name).api.Rows(str(row) + ':' +
str(row_e)).Insert()
return True
except AttributeError:
LogUtils.debug_print(
f"insert_row error! sheet = {sht_name} ,row = {row} cnt ={cnt}!"
)
return False