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 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 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 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 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 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 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 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 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 convert_excel_htm3(t_path, local_file_path):
from win32com.client import DispatchEx
# w = win32com.client.DispatchEx('Word.Application') # 或者使用下面的方法，使用启动独立的进程：
xl = DispatchEx('Excel.Application') # 加载excel文件
# 后台运行，不显示，不警告
xl.Visible = 0
xl.DisplayAlerts = 0
wb = xl.Workbooks.Open(local_file_path) # 打开文件
wb.WebOptions.Encoding = 65001 # 65001编码是utf-8
wb.SaveAs(t_path, FileFormat=44) # 保存在本地 新文件夹下, constants.xlHtml==44
xl.Workbooks.Close()
xl.Quit()
del xl
def conversion(self):
'''转换封面EXCEL为PDF'''
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
for name_list_index, name_list in enumerate(self.doc_code):
print('当前文件转换进度', name_list_index + 1, "/", len(self.doc_code))
exportfile = name_list
filenames = exportfile.split('.')[0] + '.xlsx'
filename = filenames.replace("input", "tmp")
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
print('封面转为PDF文件：', exportfile)
xlApp.Quit()
print('封面转为PDF文件完成')
print("=><=" * 25)
def excel_catch_screen(filename, sheetname, screen_area, picture_name, flag):
pythoncom.CoInitialize() # excel多线程相关
try:
excel = DispatchEx("Excel.Application") # 启动excel
excel.Visible = True # 可视化
excel.DisplayAlerts = False # 是否显示警告
wb = excel.Workbooks.Open(filename) # 打开excel
ws = wb.Sheets(sheetname) # 选择sheet
# 循环处理每个截图区域
for i in range(0, len(screen_area)):
ws.Range(screen_area[i]).CopyPicture() # 复制图片区域
ws.Paste() # 粘贴
excel.Selection.ShapeRange.Name = picture_name[
i] # 将刚刚选择的Shape重命名，避免与已有图片混淆
ws.Shapes(picture_name[i]).Copy() # 选择图片
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img_name = picture_name[i] + ".PNG" # 生成图片的文件名
img.save(img_name) # 保存图片
# 记录已处理的截图区域
x = screen_area[i]
# 将已处理的截图区域从列表移除
area_list.remove(x)
# 记录已处理的截图名称
y = picture_name[i]
# 将已处理的截图名称从列表移除
picture_list.remove(y)
# 打印日志
print('移除区域：' + x + ' 移除图片：' + y)
print('剩余计数：' + str(len(screen_area)) + ' ' +
str(len(picture_name)))
flag = 'N' # 如果程序执行到这里，说明所有截图都正常处理完成，将flag置为N
except Exception as e:
flag = 'Y' # 只要有任一截图异常，退出当前程序，将flag置为Y，等待再次调用此函数
print('error is:', e) # 打印异常日志
finally:
wb.Close(SaveChanges=0) # 关闭工作薄，不保存
excel.Quit() # 退出excel
time.sleep(5)
pythoncom.CoUninitialize()
return flag # 返回flag
def xls2pdf(input, output):
'''
xls 和 xlsx 文件转换
'''
exportfile = output
try:
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(input, False)
books.ExportAsFixedFormat(0, exportfile)
books.Close(False)
return True
except Exception as e:
print(e)
return False
finally:
xlApp.Quit()
def xls(self, filename):
'''
xls 和 xlsx 文件转换
'''
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
books = xlApp.Workbooks.Open(filename, False)
# 循环保存每一个sheet
for i in range(1, self.sheetnum + 1):
sheetName = books.Sheets(i).Name
xlSheet = books.Worksheets(sheetName)
name = sheetName + '.pdf'
exportfile = os.path.join(self._export_folder, name)
xlSheet.ExportAsFixedFormat(0, exportfile)
print('保存 PDF 文件：', exportfile)
books.Close(False)
xlApp.Quit()
def exportImg(filename, sheet_name, screen_area):
from win32com.client import DispatchEx
import pythoncom
from PIL import ImageGrab
pic_name = filename[:-4] + 'png'
pythoncom.CoInitialize()
excel = DispatchEx("Excel.Application")
excel.Visible = True
excel.DisplayAlerts = False
workbook = excel.Workbooks.Open(filename)
worksheet = workbook.Sheets(sheet_name)
worksheet.Range(screen_area).CopyPicture()
worksheet.Paste()
excel.Selection.ShapeRange.Name = pic_name
worksheet.Shapes(pic_name).Copy()
img = ImageGrab.grabclipboard()
img.save(pic_name)
workbook.Close(SaveChanges=0)
excel.Quit()
pythoncom.CoUninitialize()
def getPdfOutlines(pdfpath, listpath, 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 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 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 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 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 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 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 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 main():
FILE_PATH = sys.argv[1]
assert os.path.exists(FILE_PATH)
# Use Excel 2016 library; change/remove if not using 2016
gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 9)
# Create instance of Excel
xl = DispatchEx('Excel.Application')
# Set application properties
xl.Visible = 0
xl.DisplayAlerts = False
# Create workbook object
workbook = xl.Workbooks.Open(FILE_PATH)
# Loop to see if workbook is ready to proceed
workbook_ready = False
tries = 0
while not workbook_ready:
try:
workbook.Activate()
except Exception:
sleep(1)
tries += 1
if tries == 10:
break
else:
workbook_ready = True
"""For silent refresh operations, use the FastCombine property in
conjunction with the Application.DisplayAlerts property, set to False"""
workbook.Queries.FastCombine = True
# Common connection types; add more if needed
connection_types = {
'1_ODBC': 2,
'2_OLEDB': 1,
'3_MODEL': 7,
'4_WORKSHEET': 8
}
# Refresh connections in a specific order
for connection_type in sorted(connection_types.keys()):
refresh_connection(excel=xl,
workbook=workbook,
connection_type=connection_types[connection_type])
# Disable events while caches refresh
xl.EnableEvents = False
refresh_pivot_caches(excel=xl, workbook=workbook)
xl.EnableEvents = True
# Calculate any necessary calculations in the workbook
xl.Calculate()
# Close workbook object
workbook.Close(True)
# Close Excel instance
xl.Quit()
# Remove Excel instance
del xl
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
excel.RUN('FindNewMPSlines_Macro')
excel.RUN('RemoveCompanion')
wbP.SaveAs(Save_file_address)
excel.Quit()
del excel # ensure Excel process ends
print 'MPS NewLine has been composed and saved in folder : ' + Save_file_address
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
excel.RUN('FindNewMPSlines_Macro')
excel.RUN('RemoveCompanion')
wbP.SaveAs(Save_file_address)
excel.Quit()
del excel # ensure Excel process ends
print 'MPS NewLine has been composed and saved in folder : ' + Save_file_address
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
__author__ = "Sigai"
import os
from win32com.client import DispatchEx, constants
from win32com.client.gencache import EnsureDispatch
full_path = os.path.abspath(os.path.curdir)
EnsureDispatch('Word.Application') # makepy 导入Word类库，否则constants无法使用
msword = DispatchEx('Word.Application')
msword.Visible = False # 是否可见
msword.DisplayAlerts = 0
doc = msword.Documents.Open(FileName=os.path.join(full_path,
r'word.docx')) # 打开已有文件
#newdoc = msword.Documents.Add() # 添加新文件
doc.SaveAs(FileName=os.path.join(full_path, r'new.txt'), FileFormat=4) #另存为
doc.Close()
msword.Quit()
def gen_pdf_from_xlsx(df_data, df_conf):
df_temp = pd.DataFrame()
list_globalid = list(set(df_data['ID'].to_list()))
print(list_globalid)
file_out = get_path() + 'failed_overview.xlsx'
xlApp = DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = 0
fields_not_found = []
for gid in list_globalid:
df_temp = df_data[df_data['ID'] == gid]
o_detail = pd.Series(df_temp.iloc[0])
try:
filename = get_path() + 'rs/xlsx_p' + str(gid) + '.xlsx'
# 创建一个excel
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet()
title_format = workbook.add_format({
'bold': True,
'text_wrap': False,
'align': 'center',
'valign': 'top',
'font_size': 30,
})
title_format.set_bottom(1)
block_format = workbook.add_format({
'bold': True,
'text_wrap': False,
'align': 'left',
'valign': 'top',
'font_size': 16
})
block_format.set_bottom(1)
label_format = workbook.add_format({
'bold': True,
'text_wrap': True,
'align': 'left',
'valign': 'top',
'font_size': 12
})
label_format.set_bottom(3)
value_format = workbook.add_format({
'text_wrap': False,
'align': 'right',
'valign': 'top',
'font_size': 11,
'num_format': '#,##0.00'
})
value_format.set_bottom(3)
last_group = 0
for n_idx, n_row in df_conf.iterrows():
try:
l_format = block_format
row = int(n_row['StartLine'])
column = int(n_row['StartColumn']) - 1
if n_row['Group'] == 'Payslip':
worksheet.merge_range(row,
column,
row,
column + 5,
data=n_row['Labels'],
cell_format=title_format)
elif n_row['Group'] == 'BasicInfo':
value = ""
if str(n_row['Fields']).strip() != "":
try:
value = o_detail[n_row['Fields']]
l_format = label_format
except Exception as e:
print('No field found', n_row['Fields'],
'error', e)
fields_not_found.append(n_row['Fields'])
worksheet.write(row, column, n_row['Labels'], l_format)
if n_row['Type'] != 'AMT':
worksheet.write(row, column + 1, value, l_format)
else:
worksheet.write_number(row, column + 1, value,
value_format)
else:
if last_group != n_row['Group']:
base_row = int(n_row['StartLine'])
# else:
value = ""
if str(n_row['Fields']).strip() != "":
try:
value = o_detail[n_row['Fields']]
l_format = label_format
except Exception as e:
print('No field found', n_row['Fields'],
'error', e)
fields_not_found.append(n_row['Fields'])
if n_row['Type'] != 'AMT':
worksheet.write(base_row, column + 1, value,
l_format)
worksheet.write(base_row, column, n_row['Labels'],
l_format)
base_row = base_row + 1
else:
if isinstance(value, float):
worksheet.write_number(base_row, column + 1,
value, value_format)
worksheet.write(base_row, column,
n_row['Labels'], l_format)
base_row = base_row + 1
except Exception as e:
print('write excel file failed with fields:',
n_row['Group'], n_row['Labels'])
print('error log', e)
last_group = n_row['Group']
# print(x,y)
worksheet.set_column("A:A", 6)
worksheet.set_column("B:B", 18)
worksheet.set_column("C:C", 12)
worksheet.set_column("D:D", 5)
worksheet.set_column("E:E", 18)
worksheet.set_column("F:F", 12)
worksheet.set_column("G:G", 6)
worksheet.set_paper(
9) #https://xlsxwriter.readthedocs.io/page_setup.html
worksheet.set_margins(left=0.7, right=0.7, top=0.3, bottom=0.5)
# (x,y) = df_temp.shape
# worksheet.print_area(0,0,x-1,y-1)
worksheet.fit_to_pages(1, 1) #
# worksheet.set_landscape() #worksheet.set_portrait()
worksheet.set_default_row(hide_unused_rows=True) #隐藏无效值
workbook.close()
except Exception as e:
print('write excel file failed:', file_out)
print('error log', e)
try:
file_pdf = get_path() + 'rs/pdf_p' + str(gid) + '.pdf'
if os.access(file_pdf, os.F_OK):
os.remove(file_pdf)
books = xlApp.Workbooks.Open(filename, False)
books.ExportAsFixedFormat(0, file_pdf) #0 is pdf,1 is xps
books.Close(False)
print('Save PDF Files：', file_pdf)
except Exception as e:
print('Get PDF file failed:', file_pdf)
print('error log', e)
xlApp.Quit()
print('Not found items:', set(fields_not_found))
