方案概述
利用 VBA 的接口对 Excel 表格进行截图,然后读取剪切板中的图片数据,将其保存在图片文件中。
环境配置
- 操作系统: MacOs 12.4
- Office Excel: 必须安装这个软件
- Python: 3.9
具体实现
excel.py
# -*- coding: utf-8 -*-
import os
from PIL import ImageGrab
import xlwings as xw
# get_screenshot
def excel_catch_screen(excel_file, pic_file, excel_sheet_name='Sheet1', excel_range=''):
# 使用xlwings的app启动
app = xw.App(visible=True, add_book=False)
# 打开文件
wb = app.books.open(excel_file)
# 选定sheet
sheet = wb.sheets(excel_sheet_name)
if excel_range:
all = sheet.range(excel_range) # 获取指定范围的 range
else:
all = sheet.used_range # 获取有内容的 range
# 复制图片区域
all.api.copy_picture()
# 获取剪贴板的图片数据
img = ImageGrab.grabclipboard()
dir = os.path.join(os.path.abspath('.'), 'data')
if os.path.exists(dir) is False:
os.mkdir(dir)
pic_file_path = os.path.join(dir, pic_file)
img.save(pic_file_path) # 保存图片
wb.close() # 不保存,直接关闭
app.quit()
index.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from datetime import date
from optparse import OptionParser
import os
import shutil
import sys
from excel import excel_catch_screen
from wechat import wechat_send_pic_msg
if __name__ == '__main__':
parser = OptionParser(usage='''%prog excel_filename image_filename [options]\nExamples:
%prog test.xlsx test.png
%prog test.xlsx test.png -p Sheet2 -r 'A1:C10'
%prog test.xlsx test.png -r 'B5:C8' ''')
parser.add_option('-p', '--page', help='pick a page (sheet) by page name. When not specified (and RANGE either not specified or doesn\'t imply a page), first page will be selected')
parser.add_option('-r', '--range', metavar='RANGE', help='pick a range, in Excel notation. When not specified all used cells on a page will be selected')
opts, args = parser.parse_args()
print(opts)
print(args)
if len(args) != 2:
parser.print_help(sys.stderr)
parser.exit()
# excel_catch_screen(args[0], args[1], opts.page, opts.range)
dir = os.path.join(os.path.abspath('.'), 'data')
if os.path.exists(dir) is False:
os.mkdir(dir)
pic_file_path = os.path.join(dir, 'user_data1.png')
wechat_send_pic_msg(pic_file_path)
使用方式
# 举个🌰
python3 ./index.py text.xlsx test.png -p Sheet1 -r "A1:C10"