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 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(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 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 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 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 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()
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 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 open_ie(url, size=(900, 680)):
"""打开IE浏览器"""
width, height = size
ie = DispatchEx('InternetExplorer.Application')
# 设置窗口style
ie.Visible = 1
ie.ToolBar = 0
ie.AddressBar = 0
ie.MenuBar = 0
ie.StatusBar = 0
ie.Resizable = 0
ie.Width = width
ie.Height = height
# 设置打开的 URL
ie.Navigate(url)
win32gui.SetForegroundWindow(ie.HWND)
#win32gui.SetWindowPos(ie.HWND, win32con.HWND_TOPMOST,
win32gui.SetWindowPos(
ie.HWND, 0, 0, 0, width, height, win32con.SWP_NOACTIVATE
| win32con.SWP_NOOWNERZORDER | win32con.SWP_SHOWWINDOW)
return ie
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 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 __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 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 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 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 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 __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 refresh_excel(self, excel_path: str) -> bool:
print("----Refreshing Excel")
try:
excel = DispatchEx("Excel.Application")
excel.Visible = 1
excel.Workbooks.Open(Filename=excel_path, ReadOnly=0)
excel.Application.Run("'" + excel_path + "'" + REFRESH_MACRO)
print("> Refreshed")
excel.Workbooks(1).Close(SaveChanges=1)
excel.Application.Quit()
return True
except:
print("?? Refresh 2 Error")
print(traceback.format_exc())
return False
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 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 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 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 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 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 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)
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 doBeiKaoBiaoPrint(self,
printList=[('test', 'test', 'test', 'test', 'test',
'test', 'test', 'test', 'test')]):
DIQU, DANWEI = DD()
msword = DispatchEx(r'Word.Application')
msword.Visible = CESHI
#msword.DisplayAlerts = CESHI
doc = msword.Documents.Add()
pre_section = doc.Sections(1)
new_seciton = doc.Range(pre_section.Range.End - 1,
pre_section.Range.End - 1).Sections.Add()
new_range = new_seciton.Range
new_range.Text = u" 卷内备考\n"
new_range.Font.Size = 15
new_range.ParagraphFormat.Alignment = 0
new_table = new_range.Tables.Add(
doc.Range(new_range.End - 1, new_range.End - 1), 1, 1)
new_table.Rows(1).Borders(constants.wdBorderBottom).LineStyle = 1
new_table.Rows(1).Borders(constants.wdBorderTop).LineStyle = 1
new_table.Rows(1).Borders(constants.wdBorderLeft).LineStyle = 1
new_table.Rows(1).Borders(constants.wdBorderRight).LineStyle = 1
new_table.Rows(1).Height = 640
new_table.Cell(1, 1).Range.Font.Size = 12
new_table.Cell(1,1).Range.Text = u"档号:" + printList[0][0]+printList[0][1]+printList[0][2]+printList[0][3] +u"\n\n\n" +u"互见号:" + printList[0][4] + u"\n\n\n" + \
u"说明:" + u"\n\n\n" +u" 卷内共有\n"+u" 文字材料 " + unicode(printList[0][5]) + u" 件，" + \
u"共 "+ unicode(int(printList[0][6])) + u" 页\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n" + \
u" 立卷人:" + printList[0][7] + u"\n\n" + \
u" 立卷日期:" + printList[0][8] + u"\n\n" + \
u" 检查人:\n\n\n"+ \
u" 年 月 日"
#删除第一页空白
pre_section = doc.Sections(1)
doc.Range(pre_section.Range.Start, pre_section.Range.End - 1).Delete(1)
path = os.getcwd() + "\\打印\\卷内备考表" + datetime.datetime.now().strftime(
'%Y%m%d%H%M%S') + ".doc"
saveAndOpen(msword, doc, path)
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_codes):
try:
print('当前文件转换进度', name_list_index + 1, "/",
len(self.doc_codes))
exportfile = name_list
filename = exportfile.split('.')[0] + '.xlsx'
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
print('封面转为PDF文件：', exportfile)
except Exception as err:
messagebox.showerror("Warning!", err)
with open(os.path.join(os.getcwd(), "error.txt"), "a") as f:
traceback.print_exc(file=f)
print(err)
continue
xlApp.Quit()
print('封面转为PDF文件完成')
print("=><=" * 25)
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
#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='浙江省'
GROUP BY Region
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
'''
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
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 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()
def vsd2pdf(input, output):
global w
w = DispatchEx("Visio.Application")
w.Visible = False
doc = w.Documents.Open(input)
doc.ExportAsFixedFormat(1, output, 1, 0)
