和word文檔一樣,ExcelA file can also be thought of as a special compressed file,可以用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目錄先,Image files are stored.
# -*- 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:
# The location information of the output image
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
<openpyxl.drawing.spreadsheet_drawing.AnchorMarker object>
Parameters:
col=3, colOff=0, row=2, rowOff=0
image /xl/media/image1.jpeg
說明:
/xl/media/image1.jpeg In fact, it is the decompressed image file directory
# 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屬性:In fact, it is the decompressed image file directory;
2) Image類中的_data()方法:Used to get image data.例如,
print("image data",image._data()):
的執行結果是:
image data b'\xff\xd8\xff\xe0\x00\x10JFIF\x00\x01\x01\x00\x00H\x00H\x00\x00\xff\xe1\x00pExif\x00\x00MM\x00*\x00\x00\x00\x08\x00\x04\x01\x06\x00\x03\x00\x00\x00\x01\x00\x02\x00\x00\x01\x12\x00\x03\x00\x00\x00\x01\x00\x01\x00\x00\x01(\x00\x03\x00\x00\x00\x01\x00\x02\x00\x00\x87i\x00\x04\x00\x00\x00\x01\x00\x00\x00>\x00\x00\x00\x00\x00\x03\xa0\x01\x00\x03\x00\x00\x00\x01\x00\x01\x00\x00\xa0\x02\x00\x04\x00\x00\x00\x01\x00\x00\x02S\xa0\x03\x00\x04\x00\x00\x00\x01\x00\x00\x01\x83\x00\x00\x00\x00\xff\xed\x008Photoshop 3.0\x008BIM......