def save_img(self,file,month_info,send_info):
'''根据每个住户对应的租金，在截图xlsx文件修改数值，然后截图保存
:param file: 截图.xlsx
:param month_info: 所有住户此月的租金信息dict
:param send_info: 全部住户的租金信息(用于微信发送)
:return:
'''
file_name = os.path.abspath(file) # 把相对路径转成绝对路径
pythoncom.CoInitialize() # 开启多线程
excel = DispatchEx('excel.application')# 创建Excel对象
excel.visible = False # 不显示Excel
excel.DisplayAlerts = 0 # 关闭系统警告(保存时不会弹出窗口)
workbook = excel.workbooks.Open(file_name)# 打开截图.xlsx
wSheet = workbook.worksheets['截图']
# 循环每个住户的数据,根据 所选月份，得到具体数据，然后再截图xlxs上改变数字，截图保存，添加到send_info
for the_zuhu,the_month_data in month_info.items():
#该住户此月的租金信息不为空
if type(the_month_data)!=int:
img_name = self.month + '：' + the_zuhu #该住户此月的截图名
self.change_sheet(wSheet, the_month_data) #根据不同住户 改变截图xlsx 里的 每个项目的金额
self.snapshot(excel, wSheet, img_name)#截图，保存
send_info[the_zuhu] = [the_month_data['租户'],the_month_data['合计'], img_name + '.png'] #格式：{住户A：[租户名，合计租金，图片名称],住户B....}
else:
send_info[the_zuhu]=0
workbook.Close(False) # 关闭Excel文件，不保存
excel.Quit() # 退出Excel
pythoncom.CoUninitialize() # 关闭多线程
def excel_catch_screen(os_path,file_name, sheet_name, screen_area,picture_name):
excel = DispatchEx("Excel.Application") #启动excel
excel.Visible = True #可视化
excel.DisplayAlerts = False #是否显示警告
wb = excel.Workbooks.Open(os_path+file_name) #打开excel
ws = wb.Sheets(sheet_name) #选择sheet
#拆分截频区域和图片名称
screen_area=screen_area.split(",")
picture_name=picture_name.split(",")
#循环处理每个截图区域
for i in range (0,len(screen_area)):
ws.Range(screen_area[i]).CopyPicture() #复制图片区域
time.sleep(2)
ws.Paste() #粘贴
excel.Selection.ShapeRange.Name = picture_name[i] #将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(picture_name[i]).Copy() # 选择图片
time.sleep(2)
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img_name = picture_name[i]+ ".PNG" #生成图片的文件名
img.save(os_path+img_name) #保存图片
time.sleep(1)
wb.Close(SaveChanges=0) #关闭工作薄，不保存
excel.Quit() #退出excel
def merge_excels():
pythoncom.CoInitialize()
xlApp = DispatchEx('Excel.Application')
xlApp.Visible = False
xlApp.DisplayAlerts = 0
xlApp.ScreenUpdating = 0
file = xlwt.Workbook()
ta = file.add_sheet('sheet1')
index = 0
try:
excels = os.listdir(".//files")
except:
print 'can not find directory files'
return
for xfile in excels:
indexMark = index
if xfile[0] == '~':
continue
postfix = xfile.split(".")[-1]
if postfix == "xls" or postfix == "xlsx":
print "Merging " + xfile
absPath = os.path.abspath(r"files\\" + xfile)
index = xlrd_merge(index, absPath, ta)
if (index == indexMark):
index = win32com_merge(index, absPath, xlApp, ta)
index = index - 1
file.save("merged.xls")
xlApp.Quit()
pythoncom.CoUninitialize()
class PowerPointHandler(AbstractHandler):
__w = None
__ppt = None
def __init__(self):
AbstractHandler.__init__(self)
self.set_file_type_in_handler("powerpoint")
# makepy.py - i "Microsoft Excel 16.0 Object Library"
# gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 9)
gencache.EnsureDispatch('PowerPoint.Application')
def open(self, init_path_name):
print("PowerPoint open for %s" % init_path_name)
self.__w = DispatchEx("PowerPoint.Application")
try:
self.__ppt = self.__w.Presentations.Open(init_path_name,
ReadOnly=True,
WithWindow=False)
except pywintypes.com_error as e:
raise FileOpenFailedException("Open PowerPoint file failed.")
def output(self, result_path_name):
print("PowerPoint output for %s" % result_path_name)
self.__ppt.ExportAsFixedFormat(result_path_name,
FixedFormatType=2,
PrintRange=None)
def clean(self):
print("clean")
self.__w.Quit()
def force_clean(self):
self.clean()
def Excel_CatchScreen(filename, sheetname, screen_area, img_name=False):
""" 对excel的表格区域进行截图——用例：excel_catch_screen(ur"D:\Desktop\123.xlsx", "Sheet1", "A1:J10")"""
pythoncom.CoInitialize() # excel多线程相关
excel = DispatchEx("Excel.Application") # 启动excel
excel.Visible = True # 可视化
excel.DisplayAlerts = False # 是否显示警告
wb = excel.Workbooks.Open(filename) # 打开excel
ws = wb.Sheets(sheetname) # 选择sheet
ws.Range(screen_area).CopyPicture() # 复制图片区域
ws.Paste() # 粘贴 ws.Paste(ws.Range('B1')) # 将图片移动到具体位置
name = str(uuid.uuid4()) # 重命名唯一值
new_shape_name = name[:6]
excel.Selection.ShapeRange.Name = new_shape_name # 将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(new_shape_name).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
if not img_name:
img_name ='./pic/' + name + ".PNG"
img.save(img_name) # 保存图片
wb.Close(SaveChanges=0) # 关闭工作薄，不保存
excel.Quit() # 退出excel
pythoncom.CoUninitialize()
return name + ".PNG"
#if __name__ == '__main__':
#pass
#ImageName = Excel_CatchScreen("D:/alog/天津仓/Algorithm/Dingtalk/PictureBroadcast/DingtalkChatbot-master/dingtalkchatbot/datareport.xls", "hour", "A1:C25")
def excel_catch_screen(filename, sheetname, screen_area, img_name=False):
""" 对excel的表格区域进行截图——用例：excel_catch_screen(ur"D:\Desktop\123.xlsx", "Sheet1", "A1:J10")"""
pythoncom.CoInitialize() # excel多线程相关
excel = DispatchEx("Excel.Application") # 启动excel
excel.Visible = True # 可视化
excel.DisplayAlerts = False # 是否显示警告
wb = excel.Workbooks.Open(filename) # 打开excel
ws = wb.Sheets(sheetname) # 选择sheet
ws.Range(screen_area).CopyPicture() # 复制图片区域
ws.Paste() # 粘贴 ws.Paste(ws.Range('B1')) # 将图片移动到具体位置
#name = str(uuid.uuid4()) # 重命名唯一值
#new_shape_name = name[:6]
name = 'test' #图片名称
new_shape_name=name
excel.Selection.ShapeRange.Name = new_shape_name # 将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(new_shape_name).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
if not img_name:
img_name = "E:\\cango\\"+name + ".PNG"
img.save(img_name) # 保存图片
wb.Close(SaveChanges=0) # 关闭工作薄，不保存
excel.Quit() # 退出excel
time.sleep(3)
pythoncom.CoUninitialize()
def createIntergateFile(self):
'''
由模板创建工单综合实施文档
'''
self.order_info['ver'] = self.getVer(self.order_info['svn'],
self.order_info['order_name'],
'Aeg2DB_Build')
#print(self.order_info['ver'])
self.intergrate_info['intergrate_file'] = os.path.join(
self.order_info['svn'], 'Aeg2DB_' + self.order_info['ver'] +
'.0_' + self.order_info['order_name'] + '_综合实施文档.docx')
#由模板文件复制成实施文档
shutil.copy(self.intergrate_info['template_file'],
self.intergrate_info['intergrate_file'])
#启动独立的进程
w = DispatchEx('Word.Application')
#后台运行，不显示，不警告
w.Visible = 0
w.DisplayAlerts = 0
#打开新的文档
doc = w.Documents.Open(self.intergrate_info['intergrate_file'])
w.Selection.Find.ClearFormatting()
w.Selection.Find.Replacement.ClearFormatting()
w.Selection.Find.Execute('{ver}', False, False, False, False, False,
True, 1, True, self.order_info['ver'], 2)
w.Selection.Find.Execute('{order_name}', False, False, False, False,
False, True, 1, True,
self.order_info['order_name'], 2)
doc.Close()
w.Quit()
def convert_excel_htm(self, local_file_path, new_file_name, file_path):
"""保存，以htm后缀存取"""
# from win32com.client import Dispatch
from win32com.client import DispatchEx
import pythoncom
# from win32com.client import constants
pythoncom.CoInitialize()
xl = DispatchEx('Excel.Application')
# xl = Dispatch('Excel.Application')
xl.DisplayAlerts = False
wb = xl.Workbooks.Open(local_file_path)
# 创建新的文件夹用来装转后的htm文件(一个网页一个文件夹）
uti = Utillity()
only_id = uti.get_nextval()
file_dir = os.path.join(file_path, str(only_id),
os.path.splitext(new_file_name)[0])
if not os.path.exists(file_dir):
os.makedirs(file_dir)
t_path = os.path.join(file_dir, os.path.splitext(new_file_name)[0])
print(t_path)
wb.SaveAs(t_path, FileFormat=44) # 保存在Z盘 新文件夹下, constants.xlHtml==44
# xl.Application.Run("SaveHTML")
xl.Workbooks.Close()
xl.Quit()
del xl
return file_dir
def ddefunc(datahubname, topic, filename, txtname):
pythoncom.CoInitialize()
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = 0 # 隐藏
xlApp.Application.DisplayAlerts = 0 # 禁止弹出会话
nChan = xlApp.Application.DDEInitiate(datahubname, topic) # datahub名称
arrname = Opentxt(filename).split(",") # tagname
timestamp = timefunc() # timestamp
if(os.path.exists(txtname)):
os.remove(txtname)
for i in arrname:
DDEVALUE = xlApp.DDErequest(nChan, i)
if not DDEVALUE[0]:
linex = 'TAGNAME='+i.replace(".Value", "") + '\n'
liney = 'ITEM=VALUE,VALUE={},TIMESTAMP={},QUALITY=192'.format(
0, timestamp) + '\n'
linez = linex+liney
else:
# dde = DDE.DDEClient(datahubname, topic)
# if(os.path.exists(txtname)):
# os.remove(txtname)
# for i in arrname:
# # repi = i.replace(".value", "")
# DDEVALUE = dde.request(i)
# print(DDEVALUE)
linex = 'TAGNAME='+i.replace(".Value", "") + '\n'
liney = 'ITEM=VALUE,VALUE={},TIMESTAMP={},QUALITY=192'.format(
DDEVALUE[0], timestamp) + '\n'
linez = linex+liney
# print(linez)
with open(txtname, "a+") as f:
f.write(linez)
xlApp.Quit()
pythoncom.CoUninitialize()
def convertExcel (fname):
pythoncom.CoInitialize()
yourExcelFile = fname + '.xlsx'
newFileName = fname + '.html'
xl = DispatchEx('Excel.Application')
wb = xl.Workbooks.Open(yourExcelFile)
wb.SaveAs(newFileName, constants.xlHtml)
wb.Close(SaveChanges=0)
xl.Quit()
del xl
def xls(self, filename):
name = os.path.basename(filename).split('.')[0] + '.pdf'
exportfile = os.path.join(self._export_folder, name)
pythoncom.CoInitialize()
xlApp = DispatchEx("Excel.Application")
pythoncom.CoInitialize()
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
print('保存 PDF 文件：', exportfile)
xlApp.Quit()
def xls(self, filename):
'''
xls 和 xlsx 文件转换
'''
name = os.path.basename(filename).split('.')[0] + '.pdf'
exportfile = os.path.join(self._export_folder, name)
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
logging.info('保存 PDF 文件：%s', exportfile)
xlApp.Quit()
def createPdf(cls, wordPath, pdfPath):
"""
创建主方法 word转pdf
:param wordPath: word文件路径
:param pdfPath: 生成pdf文件路径
"""
word = DispatchEx('Word.Application')
doc = word.Documents.Open(wordPath, ReadOnly=1)
# 开启工作空间
doc.ExportAsFixedFormat(pdfPath,
constants.wdExportFormatPDF,
Item=constants.wdExportDocumentWithMarkup,
CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
word.Quit(constants.wdDoNotSaveChanges)
class Win32ComExcel(object):
"""
利用Win32com操纵Excel表格，可以对原有的Excel表格操作，利用xlsxwriter只能进行覆盖操作
可以截屏Excel
catch_screen()
"""
def __init__(self):
# kill_cilent()
# pythoncom.CoInitialize() # 多线程初始化
self.excel = DispatchEx("Excel.Application") # 启动excel
self.excel.Visible = True # 可视化
self.excel.DisplayAlerts = False # 是否显示警告
self.workbooks = None
self.worksheet = None
def read_workbook(self, filename):
""" 打开Excel """
self.workbooks = self.excel.Workbooks.Open(filename)
def read_worksheet(self, sheetname):
""" 选择sheet """
self.worksheet = self.workbooks.Sheets(sheetname)
def catch_screen(self, screen_area, path, name):
""" Excel截屏操作 """
# 这里使用的时候注意要保证所有EXCEL文件关闭 后台也没有EXCEL进程
self.worksheet.Range(screen_area).CopyPicture() # 复制图片区域
self.worksheet.Paste(self.worksheet.Range(
'F2')) # 粘贴 ws.Paste(ws.Range('B1')) # 将图片移动到具体位置
self.excel.Selection.ShapeRange.Name = name # 将刚刚选择的Shape重命名，避免与已有图片混淆
self.worksheet.Shapes(name).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
print(type(img), img)
img_name = os.path.join(path, name + ".png")
img.save(img_name) # 保存图片
def save(self, root_path, name):
""" 文件另存为 copy.xlsx """
self.workbooks.SaveAs(os.path.join(root_path, name))
def close(self):
""" 关闭工作薄 不保存"""
# 总是不能完全关闭进程
self.workbooks.Close(SaveChanges=0)
self.excel.Quit()
def xls(self, filename):
"""
xls和xlsx文件转换
:param filename:
:return:
"""
name = os.path.basename(filename).split('.')[0] + '.pdf'
exportfile = os.path.join(self._export_folder, name)
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
print('保存PDF文件：', exportfile)
xlApp.Quit()
def write_pdf(self):
pythoncom.CoInitialize()
word = DispatchEx("Word.Application")
pythoncom.CoInitialize()
try:
if os.path.exists(self.pdf_path):
os.remove(self.pdf_path)
worddoc = word.Documents.Open(self.doc_path, ReadOnly=1)
worddoc.SaveAs(self.pdf_path, FileFormat=17)
worddoc.Close()
# return pdf_name
except Exception as e:
# print('aaa')
print(e)
finally:
word.Quit(constants.wdDoNotSaveChanges)
def convert_excel_htm3(t_path, local_file_path):
from win32com.client import DispatchEx
# w = win32com.client.DispatchEx('Word.Application') # 或者使用下面的方法，使用启动独立的进程：
xl = DispatchEx('Excel.Application') # 加载excel文件
# 后台运行，不显示，不警告
xl.Visible = 0
xl.DisplayAlerts = 0
wb = xl.Workbooks.Open(local_file_path) # 打开文件
wb.WebOptions.Encoding = 65001 # 65001编码是utf-8
wb.SaveAs(t_path, FileFormat=44) # 保存在本地 新文件夹下, constants.xlHtml==44
xl.Workbooks.Close()
xl.Quit()
del xl
def conversion(self):
'''转换封面EXCEL为PDF'''
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
for name_list_index, name_list in enumerate(self.doc_code):
print('当前文件转换进度', name_list_index + 1, "/", len(self.doc_code))
exportfile = name_list
filenames = exportfile.split('.')[0] + '.xlsx'
filename = filenames.replace("input", "tmp")
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
print('封面转为PDF文件：', exportfile)
xlApp.Quit()
print('封面转为PDF文件完成')
print("=><=" * 25)
def excel_catch_screen(filename, sheetname, screen_area, picture_name, flag):
pythoncom.CoInitialize() # excel多线程相关
try:
excel = DispatchEx("Excel.Application") # 启动excel
excel.Visible = True # 可视化
excel.DisplayAlerts = False # 是否显示警告
wb = excel.Workbooks.Open(filename) # 打开excel
ws = wb.Sheets(sheetname) # 选择sheet
# 循环处理每个截图区域
for i in range(0, len(screen_area)):
ws.Range(screen_area[i]).CopyPicture() # 复制图片区域
ws.Paste() # 粘贴
excel.Selection.ShapeRange.Name = picture_name[
i] # 将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(picture_name[i]).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img_name = picture_name[i] + ".PNG" # 生成图片的文件名
img.save(img_name) # 保存图片
# 记录已处理的截图区域
x = screen_area[i]
# 将已处理的截图区域从列表移除
area_list.remove(x)
# 记录已处理的截图名称
y = picture_name[i]
# 将已处理的截图名称从列表移除
picture_list.remove(y)
# 打印日志
print('移除区域：' + x + ' 移除图片：' + y)
print('剩余计数：' + str(len(screen_area)) + ' ' +
str(len(picture_name)))
flag = 'N' # 如果程序执行到这里，说明所有截图都正常处理完成，将flag置为N
except Exception as e:
flag = 'Y' # 只要有任一截图异常，退出当前程序，将flag置为Y，等待再次调用此函数
print('error is:', e) # 打印异常日志
finally:
wb.Close(SaveChanges=0) # 关闭工作薄，不保存
excel.Quit() # 退出excel
time.sleep(5)
pythoncom.CoUninitialize()
return flag # 返回flag
def xls2pdf(input, output):
'''
xls 和 xlsx 文件转换
'''
exportfile = output
try:
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(input, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
return True
except Exception as e:
print(e)
return False
finally:
xlApp.Quit()
def xls(self, filename):
'''
xls 和 xlsx 文件转换
'''
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(filename, False)
# 循环保存每一个sheet
for i in range(1, self.sheetnum + 1):
sheetName = books.Sheets(i).Name
xlSheet = books.Worksheets(sheetName)
name = sheetName + '.pdf'
exportfile = os.path.join(self._export_folder, name)
xlSheet.ExportAsFixedFormat(0, exportfile)
print('保存 PDF 文件：', exportfile)
books.Close(False)
xlApp.Quit()
def exportImg(filename, sheet_name, screen_area):
from win32com.client import DispatchEx
import pythoncom
from PIL import ImageGrab
pic_name = filename[:-4] + 'png'
pythoncom.CoInitialize()
excel = DispatchEx("Excel.Application")
excel.Visible = True
excel.DisplayAlerts = False
workbook = excel.Workbooks.Open(filename)
worksheet = workbook.Sheets(sheet_name)
worksheet.Range(screen_area).CopyPicture()
worksheet.Paste()
excel.Selection.ShapeRange.Name = pic_name
worksheet.Shapes(pic_name).Copy()
img = ImageGrab.grabclipboard()
img.save(pic_name)
workbook.Close(SaveChanges=0)
excel.Quit()
pythoncom.CoUninitialize()
def getPdfOutlines(pdfpath,listpath,isList):
'''获取pdf文档的大纲'''
with open(pdfpath,'rb') as file:
doc=PdfFileReader(file)
outlines=doc.getOutlines()
global returnlist
returnList=[]
mylist=getOutline(outlines,isList)
w=DispatchEx('Word.Application')
w.Visible=1
w.DisplayAlerts=0
doc1=w.Documents.Add()
range1=doc1.Range(0,0)
for item in mylist:
range1.InsertAfter(item)
outpath=os.path.join(listpath,'list.docx')
doc1.SaveAs(outpath)
doc1.close()
w.Quit()
return outpath
def getPdfOutlines(pdfpath, listpath, isPage):
with open(pdfpath, "rb") as file:
doc = PdfFileReader(file)
outlines = doc.getOutlines() # 获取大纲
global returnlist # 全局变量，保存大纲的列表
returnlist = [] # 创建一个空列表
mylist = getOutline(outlines, isPage) # 递归获取大纲
w = DispatchEx("Word.Application") # 创建Word文档应用程序对象
w.Visible = 1
w.DisplayAlerts = 0
doc1 = w.Documents.Add() # 添加一个Word文档对象
range1 = doc1.Range(0, 0)
for item in mylist: # 通过循环将获取的目录列表插入到Word文档对象中
range1.InsertAfter(item)
outpath = os.path.join(listpath, 'list.docx') # 连接Word文档路径
doc1.SaveAs(outpath) # 保存文件
doc1.Close() # 关闭Word文档对象
w.Quit() # 退出Word文档应用程序对象
return outpath
def excel_catch_screen(filename, sheetname, screen_area, img_name=False):
'''excel截图'''
filename = os.path.join(os.path.dirname(__file__), filename)
pythoncom.CoInitialize() # excel多线程相关
excel = DispatchEx("Excel.Application") # 启动excel
excel.Visible = True # 可视化
excel.DisplayAlerts = False # 是否显示警告
wb = excel.Workbooks.Open(filename) # 打开excel
ws = wb.Sheets(sheetname) # 选择sheet
ws.Range(screen_area).CopyPicture() # 复制图片区域
ws.Paste() # 粘贴 ws.Paste(ws.Range('B1')) # 将图片移动到具体位置
name = str(uuid.uuid4()) # 重命名唯一值
new_shape_name = name[:6]
excel.Selection.ShapeRange.Name = new_shape_name # 将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(new_shape_name).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img_name = "screenshot.png"
img.save(img_name) # 保存图片
wb.Close(SaveChanges=0) # 关闭工作薄，不保存
excel.Quit() # 退出excel
pythoncom.CoUninitialize()
def xls(self, filename):
'''
xls 和 xlsx 文件转换
'''
# l = len(os.path.basename(filename).split('.'))
# t = os.path.basename(filename).split('.')[l - 1]
# f = os.path.basename(filename).replace(t, "")
name = self.exchange_suffix(filename)
exportfile = os.path.join(self._export_folder, name)
exportfile = str(exportfile)
pythoncom.CoInitialize()
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
logUtil.logger.info('保存 PDF 文件：', exportfile)
xlApp.Quit()
self.pdf_path = self.pdf_url_exchange(exportfile)
class Downloader(object):
def __init__(self,url):
self.url = ""
self.ie = DispatchEx('InternetExplorer.Application')
self.ie.Visible=0
self.url=url
self.ie.Navigate(url)
currentTime=int(time.time())
while self.ie.ReadyState != 4:
if int(time.time()) > currentTime+10:
logging.warning("time out after 10s")
break
time.sleep(1)
self.fileName=""
self.html=""
def __del__(self):
self.ie.Quit()
def get_html(self):
self.html = self.ie.Document.getElementsByTagName('html')[0].outerHTML
return self.html
def write_file(self):
fileName=self.url.replace("http://","")
fileName=fileName.replace("https://","")
if fileName.find("?")>0:
fileName=fileName[0:fileName.find("?")]
fileName=fileName.replace("/","_")+"-"+time.strftime("%H-%M-%S")
self.fileName=fileName
file_obj=open(fileName+".html","wb")
file_obj.write(self.html.encode('utf-8'))
file_obj.close()
def saveHtmlToFile(self,fileName):
file_obj=open(fileName,"wb")
file_obj.write(self.html.encode('utf-8'))
file_obj.close()
def createTestDocx(self):
'''
创建测试报告文档
'''
self.testdocx_info['start_date'] = datetime.date.today().strftime(
'%Y-%m-%d')
self.testdocx_info['end_date'] = (
datetime.date.today() +
datetime.timedelta(days=2)).strftime('%Y-%m-%d')
f = '高频单元测试报告_DB_' + self.order_info['ver'][5:] + '.' + self.order_info[
'order_no'] + '.' + self.order_info['order_name'] + '.docx'
self.testdocx_info['testdocx_file'] = os.path.join(
self.order_info['work_order_path'], f)
shutil.copy(self.testdocx_info['template_file'],
self.testdocx_info['testdocx_file'])
w = DispatchEx('Word.Application')
w.Visible = 0
w.DisplayAlerts = 0
#打开新的文件
doc = w.Documents.Open(self.testdocx_info['testdocx_file'])
w.Selection.Find.ClearFormatting()
w.Selection.Find.Replacement.ClearFormatting()
w.Selection.Find.Execute('{start_date}', False, False, False, False,
False, True, 1, True,
self.testdocx_info['start_date'], 2)
w.Selection.Find.Execute('{end_date}', False, False, False, False,
False, True, 1, True,
self.testdocx_info['end_date'], 2)
w.Selection.Find.Execute('{order_no}', False, False, False, False,
False, True, 1, True,
self.order_info['order_no'], 2)
w.Selection.Find.Execute('{order_name}', False, False, False, False,
False, True, 1, True,
self.order_info['order_name'], 2)
doc.Close()
w.Quit()
def excel_catch_screen():
""" 对excel的表格区域进行截图——用例：excel_catch_screen(r"E:\年周口市区县27日数据.xls", "淮阳县空气质量数据", "A1:J10")"""
# pythoncom.CoInitialize() # excel多线程相关
excel = DispatchEx("Excel.Application") # 启动excel
excel.Visible = True # 可视化
excel.DisplayAlerts = False # 是否显示警告
wb = excel.Workbooks.Open(
r"D:\Program Files\pycharm\机器人发送数据\周口市区县数据排名充填.xlsx") # 打开excel
ws = wb.Sheets("Sheet1") # 选择sheet
ws.Range("A1:L10").CopyPicture() # 复制图片区域
ws.Paste() # 粘贴 ws.Paste(ws.Range('B1')) # 将图片移动到具体位置
name = str(uuid.uuid4()) # 重命名唯一值
new_shape_name = name[:6]
excel.Selection.ShapeRange.Name = new_shape_name # 将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(new_shape_name).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
# if not img_name:
# img_name = name + ".PNG"
# img.save(img_name) # 保存图片
wb.Close(SaveChanges=0) # 关闭工作薄，不保存
excel.Quit() # 退出excel
def ddefunc(datahubname, topic, filename):
pythoncom.CoInitialize()
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = 0 #隐藏
xlApp.Application.DisplayAlerts = 0 #禁止弹出会话
nChan = xlApp.Application.DDEInitiate(datahubname, topic) #datahub名称
arrname = Opentxt(filename).split(",") #tagname
timestamp = timefunc() #timestamp
ValueResult = []
Jsonlist = []
for i in arrname:
repi = i.replace(".value", "")
DDEVALUE = xlApp.DDErequest(nChan, i)
if not DDEVALUE[0]:
pass
else:
ValueResult.append(
Tag(i.replace(".Value", ""), timestamp, str(DDEVALUE[0])))
for tag in ValueResult:
Jsonlist.append(eval(json.dumps(tag.__dict__)))
xlApp.Quit()
pythoncom.CoUninitialize()
return Jsonlist
