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() # 关闭多线程
##New and Old file month to determine which tab to use Old_file_month = time.strftime('%B', time.strptime(Oldfile_date, '%Y.%m.%d')) Newfile_month = time.strftime('%B', time.strptime(Newfile_date, '%Y.%m.%d')) NewFile_address = Location_front + Newfile_date + '.xlsx' OldFile_address = Location_front + Oldfile_date + '.xlsx' print 'Oldfile date is: ' + Oldfile_date print 'Newfile date is: ' + Newfile_date print 'NewFile Month is: ' + Newfile_month print 'OldFile Month is: ' + Old_file_month excel = DispatchEx('Excel.Application') wbG = excel.Workbooks.Open(OldFile_address) wbP = excel.Workbooks.Open(Write_file_address) excel.visible = 0 # note altered sheet name; also .Select is not required wbG.Worksheets(Old_file_month).Copy(Before=wbP.Worksheets('CombinedData')) wbP.Worksheets(Old_file_month).Name = 'OLDDATA' wbX = excel.Workbooks.Open(NewFile_address) wbX.Worksheets(Newfile_month).Copy(Before=wbP.Worksheets('CombinedData')) wbP.Worksheets(Newfile_month).Name = 'NEWDATA' ## no running out display alets excel.DisplayAlerts = 0 excel.RUN('FindNewMPSlines_Macro') excel.RUN('RemoveCompanion') wbP.SaveAs(Save_file_address)
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')
from PIL import ImageGrab, Image from win32com.client import Dispatch, DispatchEx import pythoncom, os, itertools, time file = 'C:\\Users\\Administrator\\Desktop\\2020年7月份一号项目生产数据每小时汇报.xlsx' file_name = os.path.abspath(file) # 把相对路径转成绝对路径 pythoncom.CoInitialize() # 开启多线程 # 创建Excel对象 excel = DispatchEx('excel.application') excel.visible = False # 不显示Excel excel.DisplayAlerts = 0 # 关闭系统警告(保存时不会弹出窗口) # excel.ScreenUpdating = 1 # 关闭屏幕刷新 workbook = excel.workbooks.Open(file_name) # 打开Excel文件 sheet = workbook.worksheets['氯化系统(7号炉)'] img_name = '1氯化系统(7号炉)' # screen_area = sheet.used_range # 有内容的区域 # creen_area.CopyPicture() # 复制图片区域 # 设置复制的区域 sheet.Range('A1:Q85').CopyPicture() sheet.Paste() # 粘贴 excel.Selection.ShapeRange.Name = img_name # 将刚刚选择的Shape重命名,避免与已有图片混淆 sheet.Shapes(img_name).Copy() # 选择图片 img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据 # time.sleep(2) print(img) # 可以弄个报错 img.save("C:\\Users\\Administrator\\Desktop\\Report\\" + img_name + ".PNG") workbook.Close(False) # 关闭Excel文件,不保存 excel.Quit() # 退出Excel pythoncom.CoUninitialize() # 关闭多线程