python 提取 mask Python 提取图表数据

转载

mob64ca1409d8ea 2023-10-09 21:25:03

文章标签 python 提取 mask python excel win32 imageGrab 文章分类 Python 后端开发

调用python脚本实现Excel截图,导出图片

关键字: 多线程(threading),python,excel,截图,图片导出,COMObject,ImageGrab,WIN32

前提:

因公司接到一个需求,需要把客户提供的excel文档里面的图表导出来以图片形式保存以及展示

实现思路:

python 导出excel原生图表 (柱形图,折线图,饼图)
根据公司优化要求,导致在研究开发的时候有两个版本,都把它列举出来的,方便一次性参考,
以及本人在解决问题遇到一些问题以及需要参考到网址,也一并列举出来,因为本人发现不能
仅仅做一个cv程序员,虽然一般开发上,70%问题都有大量的人解决过并愿意分享出来,但剩下30%
未被开发的呢?

思路一:使用ImageGrab截图(多线程)

# Windows 10
  # Python 3.6

  # python -m pip install pywin32
  # python -m pip install pillow

  from PIL import ImageGrab
  import win32com.client as win32
  import os
  import sys
  import threading
  import win32process
  import pythoncom
  import win32api
  import win32con

  # 关闭excel方法,防止自动关闭不完成
  def shutdown(excel):
      hwnd  =  excel.hwnd
      t,p = win32process.GetWindowThreadProcessId(hwnd)
      try:
          handle = win32api.OpenProcess(win32process.PROCESS_THRMINATE,0,p)
          if handle:
              win32api.TerminateProcess(handle,0)
              win32api.CloseHandle(handle)
      except Exception as e:
          print("close_excel_probelm:%s" % e)

  # 导出图片方法
  def exprotChart(fileName,ImageFileName,lock,excel_id):# fileName 文件名字 ImageFileName 图片名字
      print('-----python start------')
      try:
          pythoncom.CoInitialize()#win32多线程必须初始化这个,进程之间才能通信
          excel = win32.Dispatch(pythoncom.CoGetInterfaceAndReleaseStream(excel_id,pythoncom.IID_IDispatch))
          WorkbookDirectory=r'C:\Users\mi\Desktop\excelChart\ExcelChart'#本人使用目录
          WorkbookFilename=fileName
          filePath=os.path.join(WorkbookDirectory,ImageFileName)
          #加载excel
          lock.auquire()
          wb = excel.Workbooks.Open(os.path.join(WorkbookDirectory,WorkbookFilename))
          ws = wb.WorkSheets('Sheet1')#可以直接输入数字表示第几个sheet,或者输入名字
          ws.Shape(1),Copy() #复制对应的图表到剪贴板
          img = ImageGrab.grabclipboard() #从剪贴板中获取图片数据
          img.save(filePath)#把图片保存在指定路径
          wb.Close(True)
          excel.Quit()
          print("python_thread : %s ---end---")
      except Exception as e:
          print("python_thread : %s  encounter probelms!!!")
          print(e)

      finally:
          lock.release()
          pythoncom.CoUninitialize()

      return filePath

  if  __name__ =='__main__':
      #初始化
        lock  =  threading.Lock()
        pythoncom.CoInitialize()
        start = time.time()
        list =['P2.xlsx','P3.xlsx','P4.xlsx','P1.xlsx']
        threads =[]
        Excel = win32.DispatchEx('Excel.Application')

  for index in range(len(list)):
        pngName = 'test'+str(index)+'.png'
        excel_id  = pythoncom.CoMarshalInterThreadInterfaceInStream(pythoncom.IID_IDispatch,Excel)
        t = threading.Thread(target=exprotChart,args=(list[index],pngName,lock,excel_id))
        # print("method=="+t.getName())
        threads.append(t)

  for th in threads:
      th.setDaemon(True)
      th.start()
  #     time.sleep(1)
  #     print(threading.currentThread().getName())

  for th in threads:
      th.join(12)

  end =  time.time()
  print(end-start)
  shutdown(Excel)

思路二:使用COMObject 原生方法导出图片(多线程)

# 导出图片方法
  # Windows 10
  # Python 3.6

  # python -m pip install pywin32
  # python -m pip install pillow

  from PIL import ImageGrab
  import win32com.client as win32
  import os
  import sys
  import threading
  import win32process
  import pythoncom
  import win32api
  import win32con

  # 关闭excel方法,防止自动关闭不完成
  def shutdown(excel):
      hwnd  =  excel.hwnd
      t,p = win32process.GetWindowThreadProcessId(hwnd)
      try:
          handle = win32api.OpenProcess(win32process.PROCESS_THRMINATE,0,p)
          if handle:
              win32api.TerminateProcess(handle,0)
              win32api.CloseHandle(handle)
      except Exception as e:
          print("close_excel_probelm:%s" % e)

  # 导出图片方法
  def exprotChart(fileName,ImageFileName,lock,excel_id):# fileName 文件名字 ImageFileName 图片名字
      print('-----python start------')
      try:
          pythoncom.CoInitialize()#win32多线程必须初始化这个,进程之间才能通信
          excel = win32.Dispatch(pythoncom.CoGetInterfaceAndReleaseStream(excel_id,pythoncom.IID_IDispatch))
          WorkbookDirectory=r'C:\Users\mi\Desktop\excelChart\ExcelChart'#本人使用目录
          WorkbookFilename=fileName
          filePath=os.path.join(WorkbookDirectory,ImageFileName)
          #加载excel
          lock.auquire()
          wb = excel.Workbooks.Open(os.path.join(WorkbookDirectory,WorkbookFilename))
          ws = wb.WorkSheets('Sheet1')#可以直接输入数字表示第几个sheet,或者输入名字
          chartObject =ws.ChartObjects(1)#获取对应的图表对象
          chartObject.Chart.Export(filePath)
          wb.Close(True)
          excel.Quit()
          print("python_thread : %s ---end---")
      except Exception as e:
          print("python_thread : %s  encounter probelms!!!")
          print(e)

      finally:
          lock.release()
          pythoncom.CoUninitialize()

      return filePath

  if  __name__ =='__main__':
      #初始化
        lock  =  threading.Lock()
        pythoncom.CoInitialize()
        start = time.time()
        list =['P2.xlsx','P3.xlsx','P4.xlsx','P1.xlsx']
        threads =[]
        Excel = win32.DispatchEx('Excel.Application')

  for index in range(len(list)):
        pngName = 'test'+str(index)+'.png'
        excel_id  = pythoncom.CoMarshalInterThreadInterfaceInStream(pythoncom.IID_IDispatch,Excel)
        t = threading.Thread(target=exprotChart,args=(list[index],pngName,lock,excel_id))
        # print("method=="+t.getName())
        threads.append(t)

  for th in threads:
      th.setDaemon(True)
      th.start()
  #     time.sleep(1)
  #     print(threading.currentThread().getName())

  for th in threads:
      th.join(12)

  end =  time.time()
  print(end-start)
  shutdown(Excel)