def __init__(self, ast):
GenericASTTraversal.__init__(self, ast)
msword = DispatchEx('Word.Application')
msword.Visible = 1 # 1表示要顯示畫面，若為0則不顯示畫面。
self.word = msword
self.doc = msword.Documents.Add() # 開啟一個新的文件。
self.range = self.doc.Range()
# 設定字型為標楷體
self.range.Font.Name = u"標楷體".encode('cp950')
self.preorder()
ti = 1 # title line number
# Format Title
if ast.title:
para = self.doc.Paragraphs.First
para.Format.Alignment = 1 # center
para.Range.Select()
msword.Selection.Font.Size = 18
msword.Selection.Font.Bold = 1
if ast.title.count('\n') == 1:
para = self.doc.Paragraphs(2)
para.Format.Alignment = 1 # center
para.Range.Select()
msword.Selection.Font.Size = 18
msword.Selection.Font.Bold = 1
ti = 2
try:
history = ast.attrs[u'訂定'] + u'函訂定'
para = self.doc.Paragraphs(ti + 1)
para.Format.Alignment = 2 # center
para.Range.Select()
msword.Selection.Font.Size = 10
except KeyError, k:
pass
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 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 mainProcess(year, province, target_area, cwd):
# 打开Excel应用程序
excel = DispatchEx('Excel.Application')
excel.Visible = False
# 打开文件，即Excel工作薄
charts_origin = ''.join([cwd, u'/data/', u'公报图表模板.xlsx'])
charts = ''.join([cwd, u'/temp/', province, '/', year, '/', target_area, '.gdb/',
year, target_area, u'公报统计图表.xlsx'])
shutil.copy2(charts_origin, charts)
workbook = excel.Workbooks.Open(charts)
# 打开word文档
EnsureDispatch('Word.Application')
word = DispatchEx('Word.Application')
word.Visible = False
doc_origin = ''.join([cwd, u'/data/', u'公报文档模板_%s.docx' % target_area])
doc = ''.join([cwd, u'/temp/', province, '/', year, '/', target_area, '.gdb/',
year, target_area, u'公报文档.docx'])
# if not os.path.exists(doc):
shutil.copy2(doc_origin, doc)
doc = word.Documents.Open(doc)
try:
query_results = sqlQuery(workbook, year, province, target_area, cwd)
docProcess(word, doc, workbook, query_results, year, province, target_area, cwd)
finally:
doc.Save()
doc.Close()
word.Quit()
workbook.Save() # 保存EXCEL工作薄
workbook.Close() # 关闭工作薄文件
excel.Quit() # 关闭EXCEL应用程序
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(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 parseExcel(file_path):
pythoncom.CoInitialize()
excelObj = DispatchEx('Excel.Application')
excelObj.Visible = False
workBook = excelObj.Workbooks.open(file_path)
excelSheet = workBook.Sheets(1)
items = DBSession.query(FeeItem).filter(and_(FeeItem.active == 0, FeeItem.import_excel_row != None)).order_by(FeeItem.order)
teams = DBSession.query(LogicTeam).filter(and_(LogicTeam.active == 0, LogicTeam.import_excel_column != None)).order_by(LogicTeam.order)
data = []
y_header = []
def _f(v):
try:
return int(v)
except:
return None
for item in items:
y_header.append(item.label)
data.append([(team.id, item.id, _f(excelSheet.Cells(item.import_excel_row , team.import_excel_column).Value)) for team in teams])
result = {
'x_header' : [team.label for team in teams],
'y_header' : y_header,
'data' : data
}
workBook.Close()
return result
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 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 excel2pdf(input, output):
global w
w = DispatchEx("Excel.Application")
w.Visible = False
w.DisplayAlerts = False
doc = w.Workbooks.Open(input, ReadOnly=1)
doc.ExportAsFixedFormat(0, output)
def doc2pdf(input, output):
global w
w = DispatchEx("Word.Application")
w.Visible = False
w.DisplayAlerts = False
doc = w.Documents.Open(input, ReadOnly=1)
doc.ExportAsFixedFormat(output, 17, False, 1)
def __init__(self, ast):
GenericASTTraversal.__init__(self, ast)
msword = DispatchEx('Word.Application')
msword.Visible = 1 # 1表示要顯示畫面，若為0則不顯示畫面。
self.word = msword
self.doc = msword.Documents.Add() # 開啟一個新的文件。
self.range = self.doc.Range()
# 設定字型為標楷體
self.range.Font.Name = u"標楷體".encode('cp950')
self.preorder()
ti = 1 # title line number
# Format Title
if ast.title:
para = self.doc.Paragraphs.First
para.Format.Alignment = 1 # center
para.Range.Select()
msword.Selection.Font.Size = 18
msword.Selection.Font.Bold = 1
if ast.title.count('\n') == 1:
para = self.doc.Paragraphs(2)
para.Format.Alignment = 1 # center
para.Range.Select()
msword.Selection.Font.Size = 18
msword.Selection.Font.Bold = 1
ti = 2
try:
history = ast.attrs[u'訂定'] + u'函訂定'
para = self.doc.Paragraphs(ti + 1)
para.Format.Alignment = 2 # center
para.Range.Select()
msword.Selection.Font.Size = 10
except KeyError, k:
pass
def __init__(self,
canalyzer_config_path,
canape_config_path,
baud_rate,
a2l_path,
capl_path=None,
channel=2,
device_type="CCP",
logger=None):
self.logger = logger or logging.getLogger(__name__)
self._capl_funcs = {}
self._capl_path = capl_path
if self._capl_path is not None:
self._capl_names = self._get_capl_fun_names()
self._CANalyzer = DispatchEx('CANalyzer.Application')
self._CANalyzer.Open(canalyzer_config_path)
self._CANalyzer.CAPL.Compile()
global CANalyzer
CANalyzer = self
event_handler = win32com.client.WithEvents(self._CANalyzer.Measurement,
MeasEvents)
self._CANalyzer.UI.Write.Output('measurement starting...')
self._CANalyzer.Measurement.Start()
while (not self._CANalyzer.Measurement.Running):
time.sleep(1)
self._CANalyzer.UI.Write.Output('measurement started...')
self.logger.info("Canalyzer measurement started.")
# init the CANape
self._Canape = DispatchEx('CANape.Application')
self._Canape.Open1(canape_config_path, 1, baud_rate, True)
self._CanapeDev = self._Canape.Devices.Add(device_type, a2l_path,
device_type, channel)
pass
def __init__(self, server, file):
print('init mail client')
# self.session = DispatchEx('Notes.NotesSession')
# # self.server = self.session.GetEnvironmentString("MailServer", True)
# self.db = self.session.GetDatabase(server, file)
# if not self.db.IsOpen:
# print('open mail db')
# try:
# self.db.OPENMAIL
# except Exception as e:
# print(str(e))
# print( 'could not open database: {}'.format('\mail\张丽华') )
# for notes 8.5 maybe
self.session = DispatchEx('Notes.NotesSession')
self.server = self.session.GetEnvironmentString("MailServer", True)
self.db = self.session.GetDatebase(server, file)
self.db.OPENMAIL
print("11111111111111111111")
print("2222222222222222")
print(server)
print(file)
if not self.db.IsOpen:
print("3333333333333333333333")
try:
self.db.Open()
except pywintypes.com_error:
print('could not open database: ')
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()
class Aspen():
def __init__(self, aspenFile):
'''
Parameters
file: str, excel file
'''
CoInitialize()
self.file = aspenFile
self.COM = DispatchEx('Apwn.Document')
self.COM.InitFromArchive2(self.file)
def get_value(self, aspenPath):
'''
Parameters
aspenPath: str, path in ASPEN tree
Returns
value: num or str, value in ASPEN tree node
'''
value = self.COM.Tree.FindNode(aspenPath).Value
return value
def set_value(self, aspenPath, value, ifFortran):
'''
Parameters
aspenPath: str, path in ASPEN tree
value: float or str, value to set
ifFortran: bool, whether it is a Fortran variable
'''
if ifFortran:
oldValue = self.COM.Tree.FindNode(aspenPath).Value
self.COM.Tree.FindNode(aspenPath).Value = re.sub(
r'(?<==).+', str(value), oldValue)
else:
self.COM.Tree.FindNode(aspenPath).Value = float(value)
def run_model(self):
self.COM.Reinit()
self.COM.Engine.Run2()
def save_model(self, saveFile):
'''
Parameters
saveFile: str, file name to save (.bkp)
'''
self.COM.SaveAs(saveFile)
def close(self):
self.COM.Close()
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 __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 __init__(self, excelFile):
'''
Parameters
file: str, excel file
'''
CoInitialize()
self.excelCOM = DispatchEx('Excel.Application')
self.excelBook = self.excelCOM.Workbooks.Open(excelFile)
def send_mail(subject,
body_text,
sendto,
copyto=None,
blindcopyto=None,
attach=None):
# Get credentials
mailServer = 'Your Server'
mailPath = 'Your database'
mailPassword = '******'
# Connect
notesSession = DispatchEx('Lotus.NotesSession')
try:
notesSession.Initialize(mailPassword)
notesDatabase = notesSession.GetDatabase(mailServer, mailPath)
except pywintypes.com_error:
raise Exception('Cannot access mail using %s on %s' %
(mailPath, mailServer))
# print('Title:' + notesDatabase.Title)
# Get a list of folders
# for view in notesDatabase.Views:
# if view.IsFolder:
# print('view : ' + view.Name)
document = notesDatabase.CreateDocument()
document.ReplaceItemValue("Form", "Memo")
document.ReplaceItemValue("Subject", subject)
# assign random uid because sometimes Lotus Notes tries to reuse the same one
uid = str(uuid.uuid4().hex)
document.ReplaceItemValue('UNIVERSALID', uid)
# "SendTo" MUST be populated otherwise you get this error:
# 'No recipient list for Send operation'
document.ReplaceItemValue("SendTo", sendto)
if copyto is not None:
document.ReplaceItemValue("CopyTo", copyto)
if blindcopyto is not None:
document.ReplaceItemValue("BlindCopyTo", blindcopyto)
# body
body = document.CreateRichTextItem("Body")
body.AppendText(body_text)
# attachment
if attach is not None:
attachment = document.CreateRichTextItem("Attachment")
for att in attach:
attachment.EmbedObject(1454, "", att, "Attachment")
# save in `Sent` view; default is False
document.SaveMessageOnSend = True
document.Send(False)
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 __init__(self, aspenFile):
'''
Parameters
file: str, excel file
'''
CoInitialize()
self.file = aspenFile
self.COM = DispatchEx('Apwn.Document')
self.COM.InitFromArchive2(self.file)
def __init__(self, templatefile=None):
self.filename = templatefile
self.wordApp = DispatchEx('Word.Application')
if templatefile == None:
self.wordDoc = self.wordApp.Documents.Add()
else:
self.wordDoc = self.wordApp.Documents.open(templatefile)
#set up the selection
self.wordDoc.Range(0, 0).Select()
self.wordSel = self.wordApp.Selection
def excel2pdf(input, output):
w = DispatchEx("Excel.Application")
w.Visible = False
try:
doc = w.Workbooks.Open(input, ReadOnly=1)
doc.ExportAsFixedFormat(0, output)
return 0
except:
return 1
finally:
w.Quit()
def __init__(self, server, file):
print('init mail client')
self.session = DispatchEx('Notes.NotesSession')
self.db = self.session.GetDatabase(server, file)
if not self.db.IsOpen:
print('open mail db')
try:
self.db.OPENMAIL
except Exception as e:
print(str(e))
print('could not open database: {}'.format(db_name))
def doc2pdf(input, output):
w = DispatchEx("Word.Application")
w.Visible = False
try:
doc = w.Documents.Open(input, ReadOnly=1)
doc.ExportAsFixedFormat(output, 17, False, 1)
return 0
except:
return 1
finally:
w.Quit()
def __init__(self, server, file):
"""Initialize
@param server
Server's name of Notes
@param file
Your data file, usually ends with '.nsf'
"""
self.session = DispatchEx('Notes.NotesSession')
self.server = self.session.GetEnvironmentString("MailServer", True)
self.db = self.session.GetDatabase(server, file)
self.db.OPENMAIL
self.myviews = []
def vsd2pdf(input, output):
w = DispatchEx("Visio.Application")
w.Visible = False
try:
doc = w.Documents.Open(input)
doc.ExportAsFixedFormat(1, output, 1, 0)
return 0
except Exception as inst:
print(type(inst), inst.args, inst)
return 1
finally:
w.Quit()
def __init__(self, ast):
GenericASTTraversal.__init__(self, ast)
msword = DispatchEx('Word.Application')
msword.Visible = 1 # 1表示要顯示畫面，若為0則不顯示畫面。
self.word = msword
self.doc = msword.Documents.Add() # 開啟一個新的文件。
pageSetup = self.doc.PageSetup
pageSetup.TopMargin = 64
pageSetup.BottomMargin = 64
#With ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary)
# .PageNumbers.Add PageNumberAlignment:=wdAlignPageNumberRight
#End With
section = self.doc.Sections(1)
self.range = self.doc.Range()
# 設定字型為標楷體
self.range.Font.Name = u"標楷體".encode('cp950')
self.range.Font.Size = 14
self.range.ParagraphFormat.LineSpacingRule = 4
self.range.ParagraphFormat.LineSpacing = 22
#import pdb; pdb.set_trace()
#self.range.paragraphs.LineSpacingRule = 4 #constants.WdLineSpaceExactly
#self.range.LineSpacing = 22
self.preorder()
ti = 1 # title line number
# Format Title
if ast.title:
para = self.doc.Paragraphs.First
para.Format.Alignment = 1 # center
para.Range.Font.Size = 18
para.Range.Font.Bold = 1
if ast.title.count('\n') == 1:
para = self.doc.Paragraphs(2)
para.Format.Alignment = 1 # center
para.Range.Select()
msword.Selection.Font.Size = 18
msword.Selection.Font.Bold = 1
ti = 2
try:
history = ast.attrs[u'訂定'] + u'函訂定'
para = self.doc.Paragraphs(ti + 1)
para.Format.Alignment = 2 # center
para.Range.Select()
msword.Selection.Font.Size = 10
except KeyError, k:
pass
def open(self):
'''
open IE
'''
print(u'打开登陆账号文件和信息文件')
self.result = open(self.result_file)
self.usr = open(self.usr_file)
print(u'绑定IE')
self.__ie = DispatchEx('InternetExplorer.Application')
self.__ie.visible = 1
self.__ie.navigate(self.url)
self.wait()
self.document = self.__ie.Document
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 __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 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)
def __init__(self, server, file):
print('init mail client')
self.session = DispatchEx('Notes.NotesSession')
# self.server = self.session.GetEnvironmentString("MailServer", True)
#self.server = 'MACEDD25N/SERVERS/CEDD/HKSARG'
#self.file = 'C:\\Users\\Desktop\\ppcslo.nsf'
self.db = self.session.GetDatabase(server, file)
if not self.db.IsOpen:
print('open mail db')
try:
self.db.OPENMAIL
except Exception as e:
print(str(e))
print('could not open database: {}'.format(self.db))
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 __init__(self, ast):
GenericASTTraversal.__init__(self, ast)
msword = DispatchEx('Word.Application')
msword.Visible = 1 # 1表示要顯示畫面，若為0則不顯示畫面。
self.word = msword
self.doc = msword.Documents.Add() # 開啟一個新的文件。
pageSetup = self.doc.PageSetup
pageSetup.TopMargin = 64
pageSetup.BottomMargin = 64
#With ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary)
# .PageNumbers.Add PageNumberAlignment:=wdAlignPageNumberRight
#End With
section = self.doc.Sections(1)
self.range = self.doc.Range()
# 設定字型為標楷體
self.range.Font.Name = u"標楷體".encode('cp950')
self.range.Font.Size = 14
self.range.ParagraphFormat.LineSpacingRule = 4
self.range.ParagraphFormat.LineSpacing = 22
#import pdb; pdb.set_trace()
#self.range.paragraphs.LineSpacingRule = 4 #constants.WdLineSpaceExactly
#self.range.LineSpacing = 22
self.preorder()
ti = 1 # title line number
# Format Title
if ast.title:
para = self.doc.Paragraphs.First
para.Format.Alignment = 1 # center
para.Range.Font.Size = 18
para.Range.Font.Bold = 1
if ast.title.count('\n') == 1:
para = self.doc.Paragraphs(2)
para.Format.Alignment = 1 # center
para.Range.Select()
msword.Selection.Font.Size = 18
msword.Selection.Font.Bold = 1
ti = 2
try:
history = ast.attrs[u'訂定'] + u'函訂定'
para = self.doc.Paragraphs(ti + 1)
para.Format.Alignment = 2 # center
para.Range.Select()
msword.Selection.Font.Size = 10
except KeyError, k:
pass
def send_mail(subject,
body_text,
sendto,
copyto=None,
blindcopyto=None,
attach=None):
session = DispatchEx('Lotus.NotesSession')
session.Initialize('your_password')
server_name = 'your/server'
db_name = 'your/database.nsf'
db = session.getDatabase(server_name, db_name)
if not db.IsOpen:
try:
db.Open()
except pywintypes.com_error:
print('could not open database: {}'.format(db_name))
doc = db.CreateDocument()
doc.ReplaceItemValue("Form", "Memo")
doc.ReplaceItemValue("Subject", subject)
# assign random uid because sometimes Lotus Notes tries to reuse the same one
uid = str(uuid.uuid4().hex)
doc.ReplaceItemValue('UNIVERSALID', uid)
# "SendTo" MUST be populated otherwise you get this error:
# 'No recipient list for Send operation'
doc.ReplaceItemValue("SendTo", sendto)
if copyto is not None:
doc.ReplaceItemValue("CopyTo", copyto)
if blindcopyto is not None:
doc.ReplaceItemValue("BlindCopyTo", blindcopyto)
# body
body = doc.CreateRichTextItem("Body")
body.AppendText(body_text)
# attachment
if attach is not None:
attachment = doc.CreateRichTextItem("Attachment")
for att in attach:
attachment.EmbedObject(1454, "", att, "Attachment")
# save in `Sent` view; default is False
doc.SaveMessageOnSend = True
doc.Send(False)
def send_mail(subject,body_text,sendto,copyto=None,blindcopyto=None,
attach=None):
session = DispatchEx('Lotus.NotesSession')
session.Initialize('your_password')
server_name = 'your/server'
db_name = 'your/database.nsf'
db = session.getDatabase(server_name, db_name)
if not db.IsOpen:
try:
db.Open()
except pywintypes.com_error:
print( 'could not open database: {}'.format(db_name) )
doc = db.CreateDocument()
doc.ReplaceItemValue("Form","Memo")
doc.ReplaceItemValue("Subject",subject)
# assign random uid because sometimes Lotus Notes tries to reuse the same one
uid = str(uuid.uuid4().hex)
doc.ReplaceItemValue('UNIVERSALID',uid)
# "SendTo" MUST be populated otherwise you get this error:
# 'No recipient list for Send operation'
doc.ReplaceItemValue("SendTo", sendto)
if copyto is not None:
doc.ReplaceItemValue("CopyTo", copyto)
if blindcopyto is not None:
doc.ReplaceItemValue("BlindCopyTo", blindcopyto)
# body
body = doc.CreateRichTextItem("Body")
body.AppendText(body_text)
# attachment
if attach is not None:
attachment = doc.CreateRichTextItem("Attachment")
for att in attach:
attachment.EmbedObject(1454, "", att, "Attachment")
# save in `Sent` view; default is False
doc.SaveMessageOnSend = True
doc.Send(False)
def open(self):
'''
open IE
'''
print(u'打开登陆账号文件和信息文件')
self.result = open(self.result_file)
self.usr = open(self.usr_file)
print(u'绑定IE')
self.__ie = DispatchEx('InternetExplorer.Application')
self.__ie.visible = 1
self.__ie.navigate(self.url)
self.wait()
self.document = self.__ie.Document
def createShip(self, **kw):
if request.method != 'POST' or not hasattr(kw.get('sifile'), 'file'):
redirect('/uploadsi/new')
data = kw.get('sifile').file.read()
if len(data) > 1048576: # 1M
flash("File must be less than 1MB!", "warn")
redirect('/uploadsi/new')
ush = DBSession.query(UploadSiHeader).filter(and_(UploadSiHeader.active == 0,
UploadSiHeader.filename == kw.get('sifile').filename)).first()
if ush:
flash("This file already uploaded!", "warn")
redirect('/uploadsi/new')
current = dt.now()
dateStr = current.strftime("%Y%m%d")
timeStr = current.strftime("%Y%m%d%H%M%S")
fileDir = os.path.join(config.get("public_dir"), "uploadsireport", dateStr)
if not os.path.exists(fileDir):
os.makedirs(fileDir)
sifile = kw.get('sifile')
filename, ext = os.path.splitext(sifile.filename)
newFilename = '%s%s' % (timeStr, ext)
savefile = os.path.join(fileDir, newFilename)
with open(savefile, 'wb') as f:
f.write(data)
try:
pythoncom.CoInitialize()
xls = DispatchEx("Excel.Application")
# print dir(xls)
xls.Visible = 0
xls.DisplayAlerts = 0
wb = xls.Workbooks.open(savefile)
sheet = wb.Sheets[0]
rows = sheet.UsedRange.Rows
fields = ['InvoiceNo.', 'SONo.', 'SOOtherRef', 'InvoiceIssueDate', 'DeliveryDate', 'Remark', 'ItemCode', 'InvoiceQty']
values = ['invoiceNo', 'internalPO', 'rpacNo', 'invoiceDate', 'shipDate', 'remark', 'itemCode', 'shipQty']
positions = {}
fieldDict = dict(zip(fields, values))
# print fieldDict
siHeader = UploadSiHeader()
siHeader.filename = '%s%s' % (filename, ext)
siHeader.filepath = '/uploadsireport/%s/%s' % (dateStr, newFilename)
siHeader.issuedBy = request.identity["user"]
siHeader.lastModifyBy = request.identity["user"]
# DBSession.add(siHeader)
# DBSession.flush()
for index, row in enumerate(rows):
# print row.Value[0]
tmpRow = [r.strip() if isinstance(r, basestring) else r for r in row.Value[0]]
if not positions:
tmp = self._strFilter(tmpRow[0])
if tmp in fields:
listRow = [self._strFilter(v) for v in tmpRow]
for field in fields:
positions[fieldDict.get(field)] = listRow.index(field)
continue
else:
continue
invoiceNo = tmpRow[positions.get('invoiceNo')]
internalPO = tmpRow[positions.get('internalPO')]
rpacNo = tmpRow[positions.get('rpacNo')]
invoiceDate = self._time2date(tmpRow[positions.get('invoiceDate')])
strInvoiceDate = Date2Text(invoiceDate, '%d-%m-%Y')
# print Date2Text(dt.fromtimestamp((int(invoiceDate))), '%d-%m-%Y')
# print strInvoiceDate
strShipDate = tmpRow[positions.get('shipDate')] if tmpRow[positions.get('shipDate')] else ''
shipDate = self._str2date(strShipDate.split(',')[0])
remark = tmpRow[positions.get('remark')]
itemCode = tmpRow[positions.get('itemCode')]
shipQty = self._str2Int(tmpRow[positions.get('shipQty')])
# invoiceAmount = row.Value[0][42]
# if rpacNo == 'GAP2012052900003':
# print 'index', 'invoiceNo', 'internalPO', 'rpacNo', 'shipDate', 'itemCode', 'shipQty', 'invoiceAmount'
# print index, invoiceNo, internalPO, rpacNo, shipDate, itemCode, shipQty, invoiceAmount
# break
siDetail = UploadSiDetail()
siDetail.orderNO = rpacNo
siDetail.itemNumber = itemCode
siDetail.invoiceNumber = invoiceNo
siDetail.internalPO = internalPO
if isinstance(shipQty, int):
siDetail.shipQty = shipQty
siDetail.invoiceDate = strInvoiceDate
siDetail.deliveryDate = strShipDate
siDetail.header = siHeader
siDetail.issuedBy = request.identity["user"]
siDetail.lastModifyBy = request.identity["user"]
siDetail.type = 1
# check duplicate
tmpSiDetail = DBSession.query(UploadSiDetail).filter(and_(UploadSiDetail.type == 0, UploadSiDetail.active == 0,
UploadSiDetail.orderNO == rpacNo, UploadSiDetail.itemNumber == itemCode,
UploadSiDetail.invoiceNumber == invoiceNo, UploadSiDetail.internalPO == internalPO,
UploadSiDetail.shipQty == shipQty, UploadSiDetail.deliveryDate == strShipDate)).first()
# print '*************** %s' % tmpSiDetail
if tmpSiDetail or not invoiceNo or not rpacNo or not strShipDate or not itemCode \
or not isinstance(shipQty, int) or shipQty < 1 or not strInvoiceDate:
DBSession.add(siDetail)
DBSession.flush()
# print '-----------'
continue
# order detail
oDetail = OrderInfoDetail.getByItem(itemCode, rpacNo)
if oDetail:
orderHeader = oDetail.header
warehouse = orderHeader.region.region_warehouse[0]
if shipQty <= (oDetail.qty - oDetail.qtyShipped) \
and shipQty <= oDetail.item.availableQtyByWarehouse(warehouse.id):
shipItem = ShipItem()
shipItem.qty = shipQty
shipItem.internalPO = internalPO
shipItem.item = oDetail.item
shipItem.warehouse = warehouse
shipItem.orderID = orderHeader.id
shipItem.orderDetail = oDetail
shipItem.createTime = shipDate
shipItem.invoiceDate = invoiceDate
shipItem.issuedBy = request.identity["user"]
shipItem.lastModifyBy = request.identity["user"]
# query ship header
shipHeader = ShipItemHeader.getBySi(invoiceNo, orderHeader.id, '%s%s' % (filename, ext))
if not shipHeader:
shipHeader = ShipItemHeader()
shipHeader.no = 'SHI%s' % dt.now().strftime('%Y%m%d')
shipHeader.invoiceNumber = invoiceNo
# shipHeader.remark = remark
shipHeader.filename = '%s%s' % (filename, ext)
shipHeader.filepath = '/uploadsireport/%s/%s' % (dateStr, newFilename)
shipHeader.warehouse = warehouse
shipHeader.orderHeader = orderHeader
shipHeader.createTime = shipDate
shipHeader.issuedBy = request.identity["user"]
shipHeader.lastModifyBy = request.identity["user"]
DBSession.add(shipHeader)
DBSession.flush()
shipHeader.no = '%s%05d' % (shipHeader.no, shipHeader.id)
# else:
# shipHeader.remark = ' '.join(shipHeader.remark, remark) if shipHeader.remark else remark
shipHeader.remark = remark
shipItem.header = shipHeader
siDetail.type = 0
DBSession.add(shipItem)
DBSession.flush()
if orderHeader.is_shipped_complete:
orderHeader.status = SHIPPED_COMPLETE
else:
orderHeader.status = SHIPPED_PART
DBSession.add(siDetail)
DBSession.flush()
except:
transaction.doom()
traceback.print_exc()
flash("The service is not avaiable now!", "warn")
redirect('/uploadsi/new')
else:
DBSession.flush()
# print siHeader.id
if siHeader.id:
flash("Upload Successfully!")
redirect('/uploadsi/view?ushid=%s' % siHeader.id)
else:
flash("The excel format is wrong!")
redirect('/uploadsi/new')
finally:
if wb:
wb.Close(SaveChanges=0)
if xls:
xls.Quit()
class Wcontent:
def __init__(self):
self.ie = DispatchEx('InternetExplorer.Application')
self.ie.Visible = 1
time.sleep(0.5)
def openFullPage(self,url):
self.ie.Navigate(url)
while self.ie.Busy:
time.sleep(0.5)
print self.ie.LocationName, self.ie.LocationURL
def openXHRFull(self,url):
'''
get complete page for html
for XHR content page
'''
self.ie.Navigate(url)
while self.ie.Busy:
time.sleep(0.1)
print self.ie.LocationName, self.ie.LocationURL
t0 = time.time()
while self.currentHtml().find('page=')==-1:
self.ie.Document.parentWindow.execScript('window.scrollTo(0,document.body.scrollHeight-500)')
time.sleep(0.1)
if time.time() - t0 > 5:
self.ie.refresh()
def initSet(self):
'''init target/done id set from file'''
self.preUrl = 'http://weibo.com/u/'
self.data_dir = 'F:\\IS10\\grad\\data\\'
self.uid_set = set()
self.done_content_set = set()
self.loadIdFilesToSet(['collected.ids'],'done.content.ids')
def backupDoneSet(self):
'''save done id set to file'''
print 'saving done set to file'
self.bf = open(self.data_dir+'done.content.ids','a')
self.bf.write(' '+' '.join(str(ele) for ele in self.done_content_set))
self.bf.close()
def currentHtml(self):
return self.ie.Document.body.outerHTML
def getPageText(self,url):
'''
return plain Weibo text from given url
using div=WB_text to filter
'''
self.openXHRFull(url)
html = self.currentHtml()
txt = re.compile(r'WB_text(.*?)div').findall(html)
if txt:
for i,line in enumerate(txt):
txt[i] = ''.join(re.compile(r'>(.*?)<').findall(line)).split('//')[0]
return '\n'.join(txt)
else:
return ''
def getIdContentUrl(self,uid,page=1):
return ''.join([self.preUrl,str(uid),'?page=',str(page)])
def pageCnts(self,html):
'''return Weibo owner nick name and counts'''
fname = re.compile(r'fname=(.*?)&').search(html)
if fname != None:
wb_cnt = re.compile(r'tagweibo[^>]*>[^>]*>(\d+)<').search(html).group(1)
fans_cnt = re.compile(r'tagfans[^>]*>[^>]*>(\d+)<').search(html).group(1)
follow_cnt = re.compile(r'tagfollow[^>]*>[^>]*>(\d+)<').search(html).group(1)
return fname.group(1),follow_cnt,fans_cnt,wb_cnt
'''
To e.weibo & media.weibo page cnts
fname = re.compile(r'title_big..[\n](.*?)[\n]').search(html)
if fname != None:
cseg = re.compile(r'strong>(\d+)<').findall(html)
self.preUrl = 'http://e.weibo.com/'
return '#e#'+fname.group(1),cseg[0],cseg[2],cseg[4]
'''
#retry
return '##',0,0,0
def getIdContent(self,uid):
'''
Main portal to content crawling over uid
allow controlling by wb_cnt of given uid
'''
url = self.getIdContentUrl(uid)
self.openFullPage(url)
if self.ie.LocationURL.find('media.weibo')==7 or self.ie.LocationURL.find('e.weibo')==7:
return (self.ie.LocationName,0,0,0),''
html = self.currentHtml()
#id_cnts = (fname,follow_cnt,fans_cnt,wb_cnt)
id_cnts = self.pageCnts(html)
print id_cnts
wb_cnt = id_cnts[3]
if int(wb_cnt) < 45:
return (id_cnts,'')
if int(wb_cnt) > 900:
#self.getIdOriContent(uid,id_cnts)
wb_cnt = 2000
content = ''
for page in range(1,int(wb_cnt)/45+2):
url = self.getIdContentUrl(uid,page)
#page_content = self.browser.get_text('class=WB_feed')
page_content = self.getPageText(url)
content = content + page_content
return (id_cnts,content)
def getIdOriContentUrl(self,uid,page=1):
return ''.join([self.preUrl,str(uid),'?ori=1','&page=',str(page)])
def getIdOriContent(self,uid,id_cnts):
'''
When the user is too noisy
crawler her original content pages
'''
url = self.getIdOriContentUrl(uid)
self.openXHRFull(url)
time.sleep(1)
html = self.currentHtml()
page = self.getMaxPage(html)
print page
content = self.getPageText(url)
for page in range(2,int(page)+1):
url = self.getIdOriContentUrl(uid,page)
page_content = self.getPageText(url)
content = content + page_content
return (id_cnts,content)
def getMaxPage(self,html):
maxi = re.compile(r'page=(/d+)').search(html)
if maxi:
page = maxi.group(0)
else:
page = 1
return page
def loadIdFilesToSet(self,uid_files,done_file):
uid_file_paths = [self.data_dir+f for f in uid_files]
done_file_hdl = open(d+done_file,'r')
print 'Time before load to set:',str(time.ctime())
for file_path in uid_file_paths:
file_handle = open(file_path,'r')
t_set = set()
for ele in file_handle.read().split(' '):
t_set.add(ele)
self.uid_set = self.uid_set.union(t_set)
file_handle.close()
for ele in done_file_hdl.read().split(' '):
self.done_content_set.add(ele)
done_file_hdl.close()
print 'Time after loaded to set:',str(time.ctime())
print len(self.uid_set)
def writeIdContent(self,uid'):
fh = open(self.data_dir+str(uid),'w')
cnt,text = self.getIdContent(uid)
fh.writelines(cnt[0].encode('utf-8')+' '+' '.join(str(c) for c in cnt[1:]))
fh.write(text.encode('utf-8'))
fh.close()
self.done_content_set.add(uid)#''.join([str(uid),'||',cnt[0].encode('utf-8')]))
s4.graphicalProperties.line.width = 30050
s5 = chart.series[4]
s5.graphicalProperties.line.width = 30050
sheet.add_chart(chart, chart_position)
for index in range(0,len(infile_names)):
for run_name in run_names:
print("Working on " + infile_names[index] + " and run " + run_name)
infile = os.path.join(wd, run_name, "output", infile_names[index])
outfile = os.path.join(wd, "compare", outfile_names[index])
#outfile1 = os.path.join(wd, "compare", "test.xlsx")
print('Reopen file and save again')
xl = DispatchEx('Excel.Application')
xl.Visible = False
wb = xl.Workbooks.Open(infile)
wb.Close(True)
inworkbook = xlrd.open_workbook(infile)
if (infile_names[index]=="TripDestination.xlsm"):
outworkbook = openpyxl.load_workbook(outfile, keep_vba=True) #, keep_vba=True
for purp in purpose:
print(purp)
sheet_name = "TLFD_" + purp
insheet = inworkbook.sheet_by_name(sheet_name)
nrows = insheet.nrows
region_area = {u'越城区':498.0,u'柯桥区':1041.0,u'上虞区':1403.0,u'诸暨市':2311.0,u'嵊州市':1790.0,u'新昌县':1213.0}
#todo 各地区面积要包括海域面积
cwd = os.getcwd() # 获取当前工作目录，便于程序移植
# 链接数据库
database = ''.join([cwd, '/bulletinTemp/',str(year),'/SQL.mdb;'])
db = pyodbc.connect(''.join(['DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};',
'DBQ=', database])) # Uid=Admin;Pwd=;')
cursor = db.cursor()
data_table = ''.join(['data', str(year), '年']) # sql查询语句不用使用Unicode
# 打开Excel应用程序
excel = DispatchEx('Excel.Application')
excel.Visible = False
# 打开文件，即Excel工作薄
workbook = excel.Workbooks.Open(''.join([cwd, u'/Data/公报图表模板.xlsx']))
EnsureDispatch('Word.Application')
word = DispatchEx('Word.Application')
word.Visible = False
doc = word.Documents.Open(''.join([cwd, u'/Data/公报模板.docx']))
try:
# ************浙江分地区统计**********
sql = """
SELECT count(*) AS num, Region
FROM %s
WHERE Province='浙江省'
def __init__(self):
self.ie = DispatchEx('InternetExplorer.Application')
self.ie.Visible = 1
time.sleep(0.5)
'''
Created on 2015年8月27日
@author: Administrator
'''
from win32com.client import DispatchEx
import time
ie=DispatchEx("InternetExplorer.Application")
ie.Navigate("http://hi.baidu.com/mirguest/creat/blog/")
ie.Visible=1
while ie.Busy:
time.sleep(1)
body=ie.Document.body
# header
for i in body.getElementsByTagName("input"):
if str(i.getAttribute("id"))=="spBlogTitle":
print "Find title"
i.value="AutoCreatedByPython"
break
# editor
for i in body.getElementsByTagName("iframe"):
print "Find iframe"
if str(i.getAttribute("id"))=="tangram_editor_iframe_TANGRAM__1":
print "Find"
break
iframe=i
iframe.click()
sondoc=iframe.contentWindow.Document;
print sondoc
sonbody=sondoc.body
##New and Old file month to determine which tab to use
Old_file_month = time.strftime('%B',time.strptime(Oldfile_date,'%Y.%m.%d'))
Newfile_month = time.strftime('%B',time.strptime(Newfile_date,'%Y.%m.%d'))
NewFile_address = Location_front + Newfile_date + '.xlsx'
OldFile_address = Location_front + Oldfile_date + '.xlsx'
print 'Oldfile date is: ' + Oldfile_date
print 'Newfile date is: ' + Newfile_date
print 'NewFile Month is: ' + Newfile_month
print 'OldFile Month is: ' + Old_file_month
excel = DispatchEx('Excel.Application')
wbG=excel.Workbooks.Open(OldFile_address)
wbP=excel.Workbooks.Open(Write_file_address)
excel.visible = 0
# note altered sheet name; also .Select is not required
wbG.Worksheets(Old_file_month).Copy(Before=wbP.Worksheets('CombinedData'))
wbP.Worksheets(Old_file_month).Name = 'OLDDATA'
wbX=excel.Workbooks.Open(NewFile_address)
wbX.Worksheets(Newfile_month).Copy(Before=wbP.Worksheets('CombinedData'))
wbP.Worksheets(Newfile_month).Name = 'NEWDATA'
## no running out display alets
excel.DisplayAlerts = 0
class IECom():
"""open IE and use IE to auto lock bill"""
url = 'http://ifeidai.com/EDaiWeb/AgentLoan/Login'
fast_ur = 'http://ifeidai.com/EDaiWeb/AgentLogon/MySaleApplyBill?Length=10'
result_file = 'result.txt'
usr_file = 'login.txt'
city_name = [u'青岛', u'济南', u'唐山', u'无锡', u'上海', u'扬州',
u'深圳', u'武汉', u'石家庄', u'北京', u'南昌', u'海口',
u'惠州', u'成都', u'南京', u'广州', u'中山', u'郑州',
u'南宁', u'佛山', u'长沙', u'重庆', u'西安', u'贵阳',
u'厦门', u'青岛市', u'济南市', u'唐山市', u'无锡市',
u'上海市', u'扬州市', u'深圳市', u'武汉市', u'石家庄市',
u'北京市', u'南昌市', u'海口市', u'惠州市', u'成都市',
u'南京市', u'广州市', u'中山市', u'郑州市', u'南宁市',
u'佛山市', u'长沙市', u'重庆市', u'西安市', u'贵阳市',
u'厦门市']
def __init__(self):
'''
init open IE
'''
code = getnode()
self.code_license = code % 3721 + code * 9997 % 997
self.root = cur_file_dir()
try:
lic = open(self.root + os.sep + 'lic')
line = lic.readline()
lic.close()
except:
line = u'请输入激活码'
self.main_window = tk.Tk()
self.main_window.minsize(300, 300)
self.main_window.title(u'锁单神器')
self.main_window.protocol('WM_DELETE_WINDOW', self.quit)
win = tk.Frame()
self.entry_var = tk.StringVar()
self.entry_var.set(line)
self.result_var = tk.StringVar()
self.result_var.set(u'信息文件路径： ' + self.result_file)
self.mac_var = tk.StringVar()
self.mac_var.set(u'机器码: ' + str(code))
self.usr_var = tk.StringVar()
self.usr_var.set(u'账户文件路径： ' + self.usr_file)
self.info_str = u'''
锁单神器v7：自动将信息文件中的数据锁到账户文件中的账户中。
信息文件格式：姓名,身份证号,手机号,城市名
例如：张三,100000000000000000,11100000000,北京
账户文件格式：账户名,密码
例如：100000000000000000,a111111
Copyright (C) 2015 Yeoman Zhuang
'''
self.info_var = tk.StringVar()
self.info_var.set(self.info_str)
win.pack()
label_mac = tk.Entry(win, textvariable=self.mac_var)
label_mac.pack(side=tk.TOP)
self.entry_en = tk.Entry(win, textvariable=self.entry_var)
self.entry_en.pack(side=tk.TOP)
label_result = tk.Label(win, textvariable=self.result_var)
label_result.pack(side=tk.TOP)
label_usr = tk.Label(win, textvariable=self.usr_var)
label_usr.pack(side=tk.TOP)
label_info = tk.Label(win, textvariable=self.info_var)
label_info.pack(side=tk.BOTTOM)
button_act = tk.Button(win, text=u'激活', command=self.act)
button_act.pack(side=tk.LEFT)
self.button_set_result = tk.Button(
win, text=u'打开信息文件', command=self.set_result, state=tk.DISABLED)
self.button_set_result.pack(side=tk.LEFT)
self.button_set_usr = tk.Button(
win, text=u'打开账户文件', command=self.set_info, state=tk.DISABLED)
self.button_set_usr.pack(side=tk.LEFT)
# self.button_open = tk.Button(
# win, text=u'打开IE', command=self.open, state=tk.DISABLED)
# self.button_open.pack(side=tk.LEFT)
self.button_start = tk.Button(
win, text=u'开始锁单', command=self.open_auto, state=tk.DISABLED)
self.button_start.pack(side=tk.LEFT)
win.mainloop()
def open_auto(self):
self.open()
self.auto()
def act(self):
if str(self.code_license) == self.entry_en.get():
self.button_start['state'] = tk.NORMAL
self.button_set_usr['state'] = tk.NORMAL
self.button_set_result['state'] = tk.NORMAL
# self.button_open['state'] = tk.NORMAL
lic = open(self.root + os.sep + 'lic', 'w')
lic.write(str(self.code_license))
lic.close()
self.entry_var.set(u'激活成功')
else:
self.entry_var.set(u'激活失败，请重新输入')
def set_result(self):
self.result_file = tkFileDialog.askopenfilename(initialdir='D:/')
self.result_var.set(u'信息文件路径： ' + self.result_file)
def set_info(self):
self.usr_file = tkFileDialog.askopenfilename(initialdir='D:/')
self.usr_var.set(u'账户文件路径： ' + self.usr_file)
def open(self):
'''
open IE
'''
print(u'打开登陆账号文件和信息文件')
self.result = open(self.result_file)
self.usr = open(self.usr_file)
print(u'绑定IE')
self.__ie = DispatchEx('InternetExplorer.Application')
self.__ie.visible = 1
self.__ie.navigate(self.url)
self.wait()
self.document = self.__ie.Document
def login(self, usr_name, usr_pwd):
'''
login success return 1
'''
print('Login:'******'输入账户密码')
while True:
try:
self.document.Body.getElementsByTagName(
'input')[0].value = usr_name
self.document.getElementsByTagName('input')[1].value = usr_pwd
while(self.identify() == ''):
pass
self.document.getElementsByTagName(
'input')[2].value = self.identify()
self.document.getElementById('btnSave').click()
self.wait()
break
except:
self.__ie.refresh()
sleep(4)
self.wait()
flag = 't'
if flag == 't':
print(u'登陆成功！')
sleep(2)
self.__ie.navigate(self.fast_ur)
self.wait()
self.document.getElementById('FastRecommend').click()
print(u'开始快速推荐')
self.wait()
return 1
else:
print('Error: Can not log in ' + flag)
print('usr_name = ' + usr_name)
print('usr_pwd = ' + usr_pwd)
return 0
def identify(self):
print(u'获得验证码')
return self.document.getElementById('ValidateValue').value
# def reset_flag(self):
# flag = self.document.getElementById(
# 'DataNotFound').getElementsByTagName('p')
# if flag.length > 0:
# flag[0].innerHTML = 't'
def get_flag(self):
flag = self.document.getElementById(
'DataNotFound')
if flag:
flag = flag.getElementsByTagName('p')
if flag.length > 0:
return flag[0].innerHTML
return 't'
def visible(self):
self.__ie.Visible = 1 - self.__ie.Visible
def wait(self):
print(u'等待中')
while self.__ie.Busy or self.__ie.ReadyState != 4:
sleep(1)
def nwait(self):
while not self.__ie.Busy or self.__ie.ReadyState == 4:
sleep(1)
def quit(self):
print(u'退出')
self.main_window.destroy()
self.__ie.quit()
def auto(self):
result_info = self.result.readlines()
usr_info = self.usr.readlines()
while usr_info:
usr_name, usr_pwd = usr_info.pop().split(',')
if self.login(usr_name, usr_pwd) == 0:
continue
while result_info:
line = result_info.pop()
print(u'录入' + line[:-1].decode('gbk'))
info_name, info_id, info_phone, info_city = line.split(',')
info_name = info_name.decode('gbk')
info_city = info_city[:-1].decode('gbk')
if info_city in self.city_name:
# if True:
if self.input(info_name, info_id, info_phone):
result_info.append(line)
break
else:
break
print(u'录入完成')
self.result.close()
self.usr.close()
if(len(result_info) > 0):
print(u'账号已用完，未录入信息在result.txt中')
result = open(self.result_file, 'w')
for line in result_info:
result.write(line)
result.close()
def input(self, info_name, info_id, info_phone):
print(u'输入信息')
tmp_doc = self.document.getElementById(
'BusinessShowDiv').contentWindow.document
tmp_doc.getElementById('txtIDCardNO').value = info_id
tmp_doc.getElementById('txtMobile').value = info_phone
tmp_doc.getElementById('txtCustomerName').value = info_name
tmp_doc.getElementById('btnAdd').click()
sleep(2)
self.wait()
tmp = tmp_doc.getElementById(
'DataNotFound').getElementsByTagName('p')
while tmp.length == 0:
tmp = tmp_doc.getElementById(
'DataNotFound').getElementsByTagName('p')
if tmp.length > 0:
tmp = tmp[0].innerHTML
if u'该客户可提交贷款' in tmp:
print(u'可提交贷款')
tmp_button = tmp_doc.getElementsByTagName('button')
tmp_button[0].click()
sleep(1)
tmp_button = tmp_doc.getElementsByTagName('button')
tmp_button[0].click()
sleep(2)
self.wait()
tmp_doc.getElementById('btnContinuance').click()
self.wait()
return 0
if u'上限' in tmp:
print(u'推荐数量达到上限')
return 1
tmp_button = tmp_doc.getElementsByTagName('button')
# while tmp_button.length == 0:
# tmp_button = tmp_doc.getElementsByTagName('button')
tmp_button[0].click()
return 0
def get_nodes(self, parent_node, tag):
"""
>>> coldiv=GetNodes(body,"div")
"""
child_nodes = []
for childNode in parent_node.getElementsByTagName(tag):
child_nodes.append(childNode)
return child_nodes
def node_by_attr(self, nodes, nodeattr, nodeval):
"""
>>> div_id_editor=node_by_attr(coldiv,"id","editor_ifr")
"""
for node in nodes:
if str(node.getAttribute(nodeattr)) == nodeval:
return node
return None
def set_node(self, node, val):
node.innerHTML = val
def ppt2pdf(input, output):
global w
w = DispatchEx("PowerPoint.Application")
w.DisplayAlerts = False
doc = w.Presentations.Open(input, False, False, False)
doc.ExportAsFixedFormat(output, 2, 1, PrintRange=None)
def LifeCardDownloadingExcel(self):
'''
How it works:
-- Get a list of Testcases already in plan
-- Get a list of the generic Testcases, since some fields will be needed
-- For this second list make "title" indexed dictionary to correlate with
testcases in plan
-- Get the Catalog tree and generate a dictionariy of id -> (Category, SubCategory) labels
-- Generate a list of lists (range or row, cols data in Excel Terms)
-- Each row has the the 11 fields
-- Open Excel, the file and get the sheet
-- Calculate offsets and write the data to the range
-- Save, Close and Quit Excel
'''
wkbook = None
tc_row_offset = 2
tc_col_offset = 1
ticket_row_offset = 2
ticket_col_offset = 4
try:
self.LogAppend('Creating an RPC interface to the server')
rpc = RpcInterface(self.serverurl, self.serverusername, self.serverpassword)
# Get Test Cases In Plan - and Order Them
self.LogAppend('Compiling list of TestCases in Plan from server')
tcips = rpc.listTestCasesExt(self.lifecardcatalog[0], self.lifecardtestplan[0], True)
self.LogAppend('Got %d testcases, processing them' % len(tcips))
tcips = map(TestCaseInPlan, tcips) # build a list of TestCaseInPlan
tcips.sort(key=attrgetter('title')) # sort the list in place using title (our test_id)
# Get Test Cases (for additional needed fields)
self.LogAppend('Compiling list of TestCases from server')
tcases = rpc.listTestCasesExt(self.lifecardcatalog[0], '', True) # Get from server
it_tcases = map(TestCase, tcases) # Create the objects
tcases = dict(map(lambda x: (x.title, x), it_tcases)) # make a title indexed
tcasesb = dict(map(lambda x: (x.page_name, x), it_tcases)) # make a page_name indexed dict
# Get the Test Catalogs and build a dictionary of ids / we only have two levels
self.LogAppend('Start processing catalogs')
testcats = dict()
for catalog in map(TestCatalog, rpc.listSubCatalogsExt(self.lifecardcatalog[0])):
testcats[catalog.id] = (catalog.title, '')
for subcat in map(TestCatalog, rpc.listSubCatalogsExt(catalog.id)):
testcats[subcat.id] = (catalog.title, subcat.title)
self.LogAppend('Creating Excel Range Data')
lrange = list()
# Create a the range input for Excel (list of lists)
for tcip in tcips:
ltcase = list()
tcase = tcases[tcip.title]
ltcase.extend(testcats[tcase.get_catalog_id()]) # category - subcategory
ltcase.extend([tcase.title, tcase.cpeid, tcase.headline])
ltcase.extend([tcip.get_status(), tcip.comment, tcip.tracefile, tcip.testednetwork,
tcip.author, tcip.timestamp.split('T')[0]])
lrange.append(ltcase)
self.LogAppend('Compiling ticket list')
since = datetime.datetime(year=2014, month=1, day=1)
ticket_ids = rpc.getRecentChanges(since)
multicall = rpc.multicall()
self.LogAppend('Getting tickets')
for ticket_id in ticket_ids:
multicall.ticket.get(ticket_id)
tickets = map(Ticket, multicall())
tickets.sort(key=attrgetter('id'))
self.LogAppend('Getting tickets changelog')
multicall = rpc.multicall()
for ticket_id in ticket_ids:
multicall.ticket.changeLog(ticket_id)
# create the ticketchangelog objects from call result
tlogs = multicall()
tlogs = dict(zip(ticket_ids, tlogs))
for ticket_id, clogs in tlogs.iteritems():
tloglist = list()
for clog in clogs:
tloglist.append(TicketChangeLog(clog))
tlogs[ticket_id] = tloglist
tstatusdown = list()
tstatusdown.append('new') if self.lcdownnew else None
tstatusdown.append('open') if self.lcdownopen else None
tstatusdown.append('closed') if self.lcdownclosed else None
tstatusdown.append('fixed') if self.lcdownfixed else None
tstatusdown.append('rejected') if self.lcdownreject else None
tstatusdown.append('investigation') if self.lcdowninvest else None
self.LogAppend('Will download following ticket types: %s' % str(tstatusdown))
self.LogAppend('Generating Excel Data Range for tickets')
ltrange = list()
if self.lcdowncopywithvendorcomments:
ltrange2 = list()
for ticket in tickets:
if ticket.status not in tstatusdown:
self.LogAppend('Skipping ticket %d with status %s' % (ticket.id, ticket.status))
continue
lticket = list()
lticket.extend([ticket.id, ticket.version, ticket.reporter])
# lticket.append('%s\n%s' % (ticket.summary, ticket.description))
lticket.append('%s\n---------------------\n%s' % (ticket.summary, ticket.description))
if ticket.testcaseid in tcasesb:
lticket.append(tcasesb[ticket.testcaseid].title)
else:
lticket.append('Exploratory Testing')
lticket.append(ticket.status.capitalize())
lticket.append(ticket.priority)
# lticket.append('') # skip 'Test Comment Column'
lticket.append(ticket.created)
tlog = tlogs[ticket.id]
owncomment = ''
vencomment = ''
for comment in filter(lambda x: x.name == 'comment', tlog):
# format timestamp author and comment
if not comment.new:
continue
if comment.author == self.lifecardauthorup:
vencomment += '[%s]\n' % (comment.tstamp.strftime('%Y-%m-%dT%H:%M'),)
vencomment += comment.new
vencomment += '\n'
else:
owncomment += '[%s] %s\n' % (comment.tstamp.strftime('%Y-%m-%dT%H:%M'), comment.author)
owncomment += comment.new
owncomment += '\n'
lticket.append(owncomment)
# lticket.extend(['', '', ]) # skip 2 columnts
if not self.lcdownfiltervendorcomments:
lticket.append(vencomment)
else:
lticket.append('')
resoltxt = ''
for resolution in filter(lambda x: x.name == 'resolution', tlog):
if not resolution.new:
continue
resoltxt += '[%s]\n' % (resolution.tstamp.strftime('%Y-%m-%dT%H:%M'),)
resoltxt += resolution.new
resoltxt += '\n'
lticket.append(resoltxt)
ltrange.append(lticket)
if self.lcdowncopywithvendorcomments:
lticket2 = copy.deepcopy(lticket)
lticket2[-2] = vencomment
ltrange2.append(lticket2)
# raise Exception('Ticket List Compiled')
if not lrange and not ltrange:
self.LogAppend('Nothing to download to excel')
else:
if self.lcdownmakecopy:
curdate = datetime.date.today()
prepdate = curdate.strftime('%Y-%m-%d')
appcw = int(curdate.strftime('%W'))
curdate.replace(month=1, day=1)
if curdate.weekday != 0:
# Jan 1st is not monday, so all days until
# first monday are isoweek = 0 and we want
# calendar week 1
appcw += 1
appcw = 'cw%02d' % appcw
dirname, basename = os.path.split(self.lifecardexcel)
excelbase, excelext = os.path.splitext(basename)
basename = prepdate + '-' + excelbase + '-' + appcw + excelext
excelfile = os.path.join(dirname, basename)
self.LogAppend('Copying %s -> %s' % (self.lifecardexcel, excelfile))
shutil.copyfile(self.lifecardexcel, excelfile)
else:
excelfile = self.lifecardexcel
if self.lcdowncopywithvendorcomments:
vendirname, venbasename = os.path.split(excelfile)
venname, venext = os.path.splitext(venbasename)
venname += '-vendor'
venbasename = venname + venext
venexcelfile = os.path.join(vendirname, venbasename)
shutil.copyfile(excelfile, venexcelfile)
exinstances = [(excelfile, ltrange),]
if self.lcdowncopywithvendorcomments:
exinstances.append((venexcelfile, ltrange2))
wkbooks = list()
xls = list()
for excel_file, ticket_range in exinstances:
self.LogAppend('Opening Workbook %s' % excelfile)
pythoncom.CoInitialize()
# xl = Dispatch('Excel.Application')
# xl = win32com.client.gencache.EnsureDispatchEx("Excel.Application")
xl = DispatchEx('Excel.Application') # Opens different instance
xls.append(xl)
xl.Visible = 1
# xl.Interactive = True if self.lcdownkeepexcelopen else False
xl.Interactive = True
wkbook = xl.Workbooks.Open(excel_file)
wkbooks.append(wkbook)
if not lrange:
self.LogAppend('No test cases to write down to Excel')
else:
lempty = [[''] * len(lrange[0])] * len(lrange)
wksheet = wkbook.Sheets('Test Cases')
if False and wksheet.AutoFilterMode:
wksheet.ShowAllData()
topleft = wksheet.Cells(tc_row_offset, tc_col_offset)
botright = wksheet.Cells(tc_row_offset + len(lrange) - 1, tc_col_offset + len(lrange[0]) - 1)
self.LogAppend('Writing Data to Worksheet')
wksheet.Range(topleft, botright).Value = lempty
wksheet.Range(topleft, botright).Value = lrange
self.LogAppend('End downloading test-case-in-plan information')
if not ticket_range:
self.LogAppend('No tickets to write down to Excel')
else:
self.LogAppend('Writing Data to Worksheet')
# FIXME: if there are no tickets ... I would also need to clear the list
# The property .UsedRange should give access to cells that have already
# been used (I can delete the range)
lempty = [['',] * len(ticket_range[0])] * len(tickets)
wksheet = wkbook.Sheets('Bug Tracking')
if False and wksheet.AutoFilterMode:
wksheet.ShowAllData()
topleft = wksheet.Cells(ticket_row_offset, ticket_col_offset)
botright = wksheet.Cells(ticket_row_offset + len(ticket_range) - 1,
ticket_col_offset + len(ticket_range[0]) - 1)
botrightempty = wksheet.Cells(ticket_row_offset + len(tickets) - 1,
ticket_col_offset + len(ticket_range[0]) - 1)
wksheet.Range(topleft, botrightempty).Value = lempty
wksheet.Range(topleft, botright).Value = ticket_range
self.LogAppend('End downloading tickets information to %s' % excel_file)
try:
if not self.lcdownexcelnotsave:
self.LogAppend('Saving workbook')
wkbook.Save()
if not self.lcdownkeepexcelopen:
self.LogAppend('Closing workbook and Quitting Excel')
wkbook.Close(False)
wkbooks.remove(wkbook)
xl.Quit()
xls.remove(xl)
except Exception, e:
self.LogAppend('Error saving/closing/quitting Excel file: %s' % str(e))
self.LogAppend('End downloading to Lifecard')
except Exception, e:
self.LogAppend('Error during Download to LifeCard: %s' % str(e))
def vsd2pdf(input, output):
global w
w = DispatchEx("Visio.Application")
w.Visible = False
doc = w.Documents.Open(input)
doc.ExportAsFixedFormat(1, output, 1, 0)
