def column(self, colNo, getValue=True, keepNone=False):
''' 获取sheet中某一列，返回该列所有数据内容组成的tuple
:param colNo: 列号，可填列标或列序号（从1开始）
如：sheet.column(2) 或 sheet.column("B")
'''
try:
if isinstance(colNo, int):
maxRow = self.sheet.max_row
cells = list()
for i in range(maxRow):
cell = self.sheet.cell(row=i + 1, column=colNo)
cells.append(cell)
# if cell.value:
# cells.append(cell)
cells = tuple(cells)
else:
cells = self.sheet[colNo]
if getValue:
cells = self.getValue(cells)
if not keepNone:
cells = self.delNone(cells)
return cells
except Exception as e:
handleErr(e)
raise e
def get_file(self, before_index=1, email_title="", save_path=None):
'''
读取邮件,通过邮件标题判断有效邮件进行下载附件
:param before_index: 从那一封邮件开始读 --<int>
:param email_title: 要读取的邮件标题,部分匹配 --<str>
:param save_path: 如果需要下载附件,传入下载附件的保存路径,不传不下载 --<str>
:return: 返回邮箱里最新的邮件数
'''
try:
self.server = self.connect_server()
index = self.get_index()
du_email_count = index - int(before_index)
for i in range(du_email_count, -1, -1):
print("第%s封邮件" % (index - i))
resp, lines, octets = self.server.retr(index - i)
try:
msg_content = b'\r\n'.join(lines).decode("UTF-8")
except Exception as e:
continue
msg = Parser().parsestr(msg_content)
subject = self.get_title(msg)
print("邮件标题为:", subject)
if email_title in subject:
for part in msg.walk():
file_name = part.get_filename()
if file_name:
data = part.get_payload(decode=True)
if save_path:
self.save_file(file_name, data, save_path)
self.server.quit()
except Exception as e:
handleErr(e)
raise e
def range(self, startRng=None, endRng=None, getValue=True):
'''
获取多个单元格对象，返回tuple类型数据
range("B2", "C3")，获取“B2”到“C3区域”
range("B", "C")，获取“B”列到“C”列
range(2, 3)，获取2-3行
:param startRng: 起始区域
:param endRng: 终止区域
'''
try:
if not startRng and not endRng:
startRng = "A1"
rowCount = self.rowCount()
colCount = self.colCount()
# chr(97)='a'
endRng = "%s%d" % (chr(96 + colCount), rowCount)
if isinstance(startRng, str) and isinstance(endRng, str):
runStr = 'self.sheet["%s":"%s"]' % (startRng, endRng)
else:
runStr = 'self.sheet[%s:%s]' % (startRng, endRng)
cells = eval(runStr)
if getValue:
cells = self.getValue(cells)
return cells
except Exception as e:
handleErr(e)
raise e
def write(self,
content,
coordinate=None,
rowNo=None,
colsNo=None,
style=None):
'''
根据单元格在excel中的编码坐标或者数字索引坐标向单元格中写入数据（写之前确认文件关闭），
下表从1开始，参数style表示字体的颜色名称，如：red、green
:param content: 输入内容
:param coordinate: 单元格坐标（“A3”、"B5"）
:param rowNo: 行号（从1开始）
:param colsNo: 列号（从1开始）
:param style: 字体的颜色名称（“red”、“green”，默认为黑色）
'''
try:
if coordinate is not None:
# coordinate = coordinate.decode('utf-8')
self.sheet[coordinate].value = content
# sheet.cell(coordinate = coordinate).value = content
if style is not None:
self.sheet[coordinate].font = Font(
color=self.RGBDict[style])
# sheet.cell(coordinate = coordinate).font = Font(color = self.RGBDict[style])
elif coordinate == None and rowNo is not None and colsNo is not None:
# sheet.cell(row = rowNo,column = colsNo).value = ""
self.sheet.cell(row=rowNo, column=colsNo).value = content
if style:
self.sheet.cell(
row=rowNo,
column=colsNo).font = Font(color=self.RGBDict[style])
# self.workbook.save(self.excelFile)
except Exception as e:
handleErr(e)
raise e
def paste(self, data, startRng=None):
'''
复制单元格区域
:param data: （tuple）原始数据单元格
:param startRng: 新表中粘贴的起始单元格（不填则与原表位置一致进行粘贴）
'''
try:
initRng = data[0][0]
# 计算偏移量
if not startRng:
rowMove = 0
colMove = 0
else:
rowNum = int(re.findall('\d+', startRng)[0])
# ord("A") = 65
colNum = ord(startRng.replace(str(rowNum), "")) - 64
rowMove = rowNum - initRng.row
colMove = colNum - initRng.column
for i in range(len(data)):
for cell in data[i]:
row = cell.row + rowMove
column = cell.column + colMove
self.sheet.cell(row=row, column=column).value = cell.value
except Exception as e:
handleErr(e)
raise e
def alignment(self,
startRng,
endRng=None,
horizontal="center",
vertical="center"):
'''
设置单个或多个单元格的对齐方式
处理“.xlsm”文件可能导致文件损坏，慎用；处理“.xlsx”正常
sht.alignment("B2", horizontal="right", vertical="top")
sht.alignment("B2", "C3")
sht.alignment(2, 3, horizontal="left", vertical="bottom")
sht.alignment("B", "C", horizontal="right", vertical="top")
:param startRng: 起始区域
:param endRng: 终止区域（选填）
:param horizontal: 水平对齐方式（"general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed"）
:param vertical: 垂直对齐方式（"top", "center", "bottom", "justify", "distributed"）
'''
try:
if endRng:
if isinstance(startRng, str) and isinstance(endRng, str):
runStr = 'self.sheet["%s":"%s"]' % (startRng, endRng)
else:
runStr = 'self.sheet[%s:%s]' % (startRng, endRng)
rng = eval(runStr)
for i in range(len(rng)):
for cell in rng[i]:
cell.alignment = Alignment(horizontal=horizontal,
vertical=vertical)
else:
self.sheet[startRng].alignment = Alignment(
horizontal=horizontal, vertical=vertical)
# self.workbook.save(self.excelFile)
except Exception as e:
handleErr(e)
raise e
def click(self):
''' 点击控件 '''
try:
self.obj.Click()
except Exception as e:
handleErr(e)
raise e
def objControl(self, name):
# def objControl(self, name, conductType, inputStr=None):
sucFlag = False
errInfo = ""
try:
assert self.dict["Windows"].get(name) is not None, \
"本地库中 [Windows] 类型下未找到名为 [%s] 的控件，请检查“log.txt”文件！" %name
info = self.dict["Windows"].get(name).get("Depth")
obj = self.checkBottom(info)
assert obj, "根据本地控件信息未定位到目标控件 [%s]！" % name
# if conductType == "点击":
# obj.Click()
#
# elif conductType == "输入":
# obj.SendKeys(inputStr)
sucFlag = True
return WinElement(obj)
except AssertionError as e:
errInfo = e
raise e
except Exception as e:
errInfo = e
raise e
finally:
if not sucFlag:
handleErr(errInfo)
def sendkeys(self, inputContent):
''' 输入框输值 '''
try:
self.obj.clear()
self.obj.SendKeys(inputContent)
except Exception as e:
handleErr(e)
raise e
def sheets(self):
'''
返回 self.workbook 中所有sheet列表（list）
'''
try:
return self.workbook.worksheets
except Exception as e:
handleErr(e)
raise e
def rowCount(self):
'''
获取sheet中有数据区域的结束行号
'''
try:
return self.sheet.max_row
except Exception as e:
handleErr(e)
raise e
def minColNum(self):
'''
获取sheet中有数据区域的开始列号
'''
try:
return self.sheet.min_column
except Exception as e:
handleErr(e)
raise e
def minRowNum(self):
'''
获取sheet中有数据区域的开始行号
'''
try:
return self.sheet.min_row
except Exception as e:
handleErr(e)
raise e
def colCount(self):
'''
获取sheet中有数据区域的结束列号
'''
try:
return self.sheet.max_column
except Exception as e:
handleErr(e)
raise e
def decode_str(self, s):
'''字符解码'''
try:
value, charset = decode_header(s)[0]
if charset:
value = value.decode(charset)
return value
except Exception as e:
handleErr(e)
raise e
def save(self, savePath=None):
'''
保存文件
'''
try:
path = savePath if savePath else self.excelFile
self.workbook.save(path)
except Exception as e:
handleErr(e)
raise e
def width(self, colNum, width):
'''
设置单列的单元格宽度
:param colNum: 列序号
:param width: 宽度
'''
try:
self.sheet.column_dimensions[colNum].width = width
except Exception as e:
handleErr(e)
raise e
def unmerge(self, startRng, endRng):
'''
拆分单元格
:param startRng: 起始区域
:param endRng: 终止区域
'''
try:
self.sheet.unmerge_cells('%s:%s' % (startRng, endRng))
except Exception as e:
handleErr(e)
raise e
def loadWorkBook(self, filaPath):
''' 将excel文件加载到内存，并获取其workbook对象
:param filaPath: 文件路径
'''
try:
self.workbook = openpyxl.load_workbook(filaPath)
self.excelFile = filaPath
return ParseSheet(self.workbook, self.excelFile)
except Exception as e:
handleErr(e)
raise e
def close(self, save=True, savePath=None):
'''
关闭文件，默认保存
'''
try:
if save:
self.save(savePath)
self.workbook.close()
except Exception as e:
handleErr(e)
raise e
def height(self, rowNum, height):
'''
设置单行的单元格高度
:param rowNum: 行序号（从1开始）
:param height: 高度
'''
try:
self.sheet.row_dimensions[rowNum].height = height
except Exception as e:
handleErr(e)
raise e
def delete_cols(self, idx, amount=1):
'''
删除列
:param idx: 起始列号（数值型）
:param amount: 从idx开始数共删除的列数
'''
try:
self.sheet.delete_cols(idx, amount)
# self.workbook.save(self.excelFile)
except Exception as e:
handleErr(e)
raise e
def delete_rows(self, idx, amount=1):
'''
删除行
:param idx: 起始行号
:param amount: 从idx开始数共删除的行数
'''
try:
self.sheet.delete_rows(idx, amount)
# self.workbook.save(self.excelFile)
except Exception as e:
handleErr(e)
raise e
def create(self, filePath):
''' 创建新工作簿并保存
:param filePath: 文件保存路径
'''
try:
self.workbook = openpyxl.Workbook()
self.excelFile = filePath
self.workbook.save(self.excelFile)
return ParseSheet(self.workbook, self.excelFile)
except Exception as e:
handleErr(e)
raise e
def remove(self, sheetName=None, sheetIndex=None):
''' 删除选定sheet
（若名称不为空，则优先通过名称查找；否则通过索引号查找，默认获取第一个sheet）
:param sheetName: sheet名称
:param sheetIndex: sheet序号
'''
try:
self.getSheet(sheetName, sheetIndex)
self.workbook.remove(self.sheet)
# self.save()
except Exception as e:
handleErr(e)
raise e
def guess_charset(self, msg):
'''获取邮件的字符编码，首先在message中寻找编码，如果没有，就在header的Content-Type中寻找'''
try:
charset = msg.get_charset()
if charset is None:
content_type = msg.get('Content-Type', '').lower()
pos = content_type.find('charset=')
if pos >= 0:
charset = content_type[pos + 8:].strip()
return charset
except Exception as e:
handleErr(e)
raise e
def createSheet(self, title=None, index=None):
''' 新建sheet
:param title: sheet名，选填
:param index: sheet序号
:return:
'''
try:
self.sheet = self.workbook.create_sheet(title=title, index=index)
# self.save()
return ParseCell(self.workbook, self.excelFile, self.sheet)
except Exception as e:
handleErr(e)
raise e
def rename(self, newName, sheetName=None, sheetIndex=None):
''' 重命名选定sheet
（若名称不为空，则优先通过名称查找；否则通过索引号查找，默认获取第一个sheet）
:param newName: 新定义的sheet名称
:param sheetName: sheet名称
:param sheetIndex: sheet序号
'''
try:
self.getSheet(sheetName, sheetIndex)
self.sheet.title = newName
# self.save()
except Exception as e:
handleErr(e)
raise e
def getSheet(self, sheetName=None, sheetIndex=0):
''' 获取sheet对象（若名称不为空，则优先通过名称查找；否则通过索引号查找，默认获取第一个sheet）
:param sheetName: sheet名称
:param sheetIndex: sheet序号
'''
try:
if sheetName:
self.sheet = self.workbook[sheetName]
# sheet = self.workbook.get_sheet_by_name(sheetName)
else:
self.sheet = self.workbook.worksheets[sheetIndex]
return ParseCell(self.workbook, self.excelFile, self.sheet)
except Exception as e:
handleErr(e)
raise e
def runVBA(self, func, visible=True):
''' 在 self.workbook 中运行VBA
:param func: 函数名
:param visible: 运行过程是否可见，默认可见
'''
try:
xlApp = win32com.client.DispatchEx("Excel.Application")
xlApp.Visible = visible
xlApp.DisplayAlerts = 0
xlBook = xlApp.Workbooks.Open(self.excelFile, False)
xlBook.Application.Run(func)
xlBook.Close(True)
xlApp.quit()
except Exception as e:
handleErr(e)
raise e
