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))