python win32com 操作excel插入字段 word excel ppt 压缩包等文档

代码作用

实现python 在excel指定位置写入字段，在指定位置插入word/excel/图片等文件

import sys, os
import win32com.client

baseDirPath = sys.path[0]   
print(baseDirPath)

info_file_path = baseDirPath + '\\files\\info.xlsx'
template_file_path = baseDirPath + '\\files\\template.xlsx'   
ICON_EXCEL = baseDirPath + '\\files\\WORDICON.EXE'   #EXCEL图标所在地址
ICON_WORD = baseDirPath + '\\files\\XLICONS.EXE'     #WORD图标所在地址

output_path = baseDirPath + '\\files\\output'
if not os.path.exists(output_path):
  os.makedirs(output_path)
  
rarType = ['rar', 'zip', '7z']
picType = ['jpg', 'png', 'bmp', 'jpeg']
excelType = ['xls', 'xlsx']
docType = ['doc','docx']

if __name__ == '__main__':

  #使用WPS，关闭WPS程序
  try:
    wps = win32com.client.gencache.EnsureDispatch('kwps.application')
  except:
    wps = win32com.client.gencache.EnsureDispatch('wps.application')
    
  try:
    wps.Quit
  except:
    pass
  
  excelApp = win32com.client.gencache.EnsureDispatch('Excel.Application')
  rel_wb = excelApp.Workbooks.Open(info_file_path)   #打开info.xlsx
  rel_ws = rel_wb.Worksheets(1)                      #excel第‘1’个sheet
  total_rows = rel_ws.UsedRange.Rows.Count
  total_cols = rel_ws.UsedRange.Columns.Count
  
  
  #打开模板excel
  temp_wb = excelApp.Workbooks.Open(template_file_path)   #打开模板文件
  temp_ws = temp_wb.Worksheets(1)
  print('temp_wb Excel open')
  ######插入字段  将info.xlsx中的字段写入到template.xlsx的B列
  temp_ws.Range('B2').Value = rel_ws.Cells(1, 'C').Value
  temp_ws.Range('B3').Value = rel_ws.Cells(1, 'D').Value
  temp_ws.Range('B4').Value = rel_ws.Cells(1, 'E').Value
  temp_ws.Range('B5').Value = rel_ws.Cells(1, 'F').Value
  
  ######插入附件
  #info.xlsx中'A1'信息为附件所在文件夹的名称
  insertFilePath = baseDirPath + '\\files\\AddFiles\\' + str(rel_ws.Cells(1, 'A').Value)
  #遍历所有待插入附件
  for root, dirs, files in os.walk(insertFilePath):
    for file in files:
      file_path = os.path.join(root, file)
      fType = file.split('.')[-1].lower()    #文件类型
      if '~$' in file:              #doc/excel 打开时的缓存文档
        continue
      if fType.lower() == 'pdf':    #暂无法导入pdf文档
        continue
      
      if fType in picType:          #图片类型 放在C2
        col = 'C'
        row = 2
        shape = temp_ws.Pictures().Insert(Filename = file_path)
        shape.Top = temp_ws.Cells(row, col).Top + 10
        shape.Left = temp_ws.Cells(row, col).Left + 10
        shape.Width = 50
      elif fType in rarType:        #压缩包类型 放在C5
        col = 'C'
        row = 5
        shape = temp_ws.Shapes.AddOLEObject(Filename = file_path, Link = False)
        shape.Top = temp_ws.Cells(row, col).Top + 10
        shape.Left = temp_ws.Cells(row, col).Left + 10
      elif fType in excelType:      #excel类型 放在C4
        icon_file_path = ICON_EXCEL
        add_file_name = file.split('.')[0]
        col = 'C'
        row = 4
        shape = temp_ws.Shapes.AddOLEObject(Filename = file_path, Link = False,
                  DisplayAsIcon = True, IconFileName = icon_file_path, IconIndex = 0, 
                  IconLabel = add_file_name)
        shape.Top = temp_ws.Cells(row, col).Top + 10
        shape.Left = temp_ws.Cells(row, col).Left + 10
      elif fType in docType:        #word类型 放在C3
        icon_file_path = ICON_WORD
        add_file_name = file.split('.')[0]
        col = 'C'
        row = 3
        shape = temp_ws.Shapes.AddOLEObject(Filename = file_path, Link = False,
                  DisplayAsIcon = True, IconFileName = icon_file_path, IconIndex = 0, 
                  IconLabel = add_file_name)
        shape.Top = temp_ws.Cells(row, col).Top + 10
        shape.Left = temp_ws.Cells(row, col).Left + 10
      
  excel_out_path = output_path + '\\outputExcel.xlsx'      
  temp_wb.SaveAs(excel_out_path)
  temp_wb.Close()
  excelApp.Application.Quit()
  print('finish--------------')

案例附件：
https://download.csdn.net/download/mjc1321/89064542