Python3-excel文档操作（三）：利用openpyxl库处理excel表格：获取excel表格中的图片信息

和word文档一样，Excel文件也可以认为是一个特殊的压缩文件，可以用unzip命令进行解压。

同理，可以使用openpyxl来获取excel中的图片信息。

cp file_20220729115746.xlsx file_excel_zip.zip. #把原excel文件名改为zip后缀

unzip file_excel_zip.zip -d file_zip #解压zip文件，结果如下：

Archive:  file_excel_zip.zip
  inflating: file_zip/docProps/app.xml  
  inflating: file_zip/docProps/core.xml  
  inflating: file_zip/xl/theme/theme1.xml  
  inflating: file_zip/xl/worksheets/sheet1.xml  
  inflating: file_zip/xl/drawings/drawing1.xml  
  inflating: file_zip/xl/drawings/_rels/drawing1.xml.rels  
  inflating: file_zip/xl/worksheets/_rels/sheet1.xml.rels  
  inflating: file_zip/xl/worksheets/sheet2.xml  
  inflating: file_zip/xl/media/image1.jpeg  
  inflating: file_zip/xl/styles.xml  
  inflating: file_zip/_rels/.rels    
  inflating: file_zip/xl/workbook.xml  
  inflating: file_zip/xl/_rels/workbook.xml.rels  
  inflating: file_zip/[Content_Types].xml

cd file_zip/xl/media

% ls

image1.jpeg

可见，在file_zip/xl/media目录先，存放着图片文件。

# -*- coding: utf-8 -*-
import os
import sys
import time

import openpyxl
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

def openxls_read_img(fname):
    '''
    获取图片信息
    '''
    wb=load_workbook(fname,data_only=True);
    sheet=wb['mysheet1'] #获取sheet
    for image in sheet._images:
        # 输出图片的位置信息
        print(image.anchor._from)
        print("image",image.path)   
        #print("image data",image._data())

if __name__ == '__main__':
    #1. case1
     # openxls_create();

     #2.case2
    fname = '人员列表.xlsx';
    #openxls_read(fname)

    #3.case3
    #img_path = "念奴娇_赤壁怀古_image1.jpg";
    #openxls_insert_img(fname,img_path)

    #4. case4
    openxls_read_img("file_20220729115746.xlsx")

运行结果：

% python3 openxls_creat_read.py

Parameters:

col=3, colOff=0, row=2, rowOff=0

image /xl/media/image1.jpeg

说明：

/xl/media/image1.jpeg 其实就是解压后的图片文件目录

# Copyright (c) 2010-2022 openpyxl

from io import BytesIO

try:
    from PIL import Image as PILImage
except ImportError:
    PILImage = False


def _import_image(img):
    if not PILImage:
        raise ImportError('You must install Pillow to fetch image objects')

    if not isinstance(img, PILImage.Image):
        img = PILImage.open(img)

    return img


[docs]class Image(object):
    """Image in a spreadsheet"""

    _id = 1
    _path = "/xl/media/image{0}.{1}"
    anchor = "A1"

    def __init__(self, img):

        self.ref = img
        mark_to_close = isinstance(img, str)
        image = _import_image(img)
        self.width, self.height = image.size

        try:
            self.format = image.format.lower()
        except AttributeError:
            self.format = "png"
        if mark_to_close:
            # PIL instances created for metadata should be closed.
            image.close()


    def _data(self):
        """
        Return image data, convert to supported types if necessary
        """
        img = _import_image(self.ref)
        # don't convert these file formats
        if self.format in ['gif', 'jpeg', 'png']:
            img.fp.seek(0)
            fp = img.fp
        else:
            fp = BytesIO()
            img.save(fp, format="png")
            fp.seek(0)

        data = fp.read()
        fp.close()
        return data


    @property
    def path(self):
        return self._path.format(self._id, self.format)

说明：

1) Image类中的path属性：其实就是解压后的图片文件目录；

2) Image类中的_data()方法：用于获取图片数据。例如，

print("image data",image._data())：

的执行结果是：

image data b'xffxd8xffxe0x00x10JFIFx00x01x01x00x00Hx00Hx00x00xffxe1x00pExifx00x00MMx00*x00x00x00x08x00x04x01x06x00x03x00x00x00x01x00x02x00x00x01x12x00x03x00x00x00x01x00x01x00x00x01(x00x03x00x00x00x01x00x02x00x00x87ix00x04x00x00x00x01x00x00x00>x00x00x00x00x00x03xa0x01x00x03x00x00x00x01x00x01x00x00xa0x02x00x04x00x00x00x01x00x00x02Sxa0x03x00x04x00x00x00x01x00x00x01x83x00x00x00x00xffxedx008Photoshop 3.0x008BIM......