程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式師世界 >> 編程語言 >  >> 更多編程語言 >> Python

Python3-excel document operation (3): use openpyxl library to process excel table: get picture information in excel table

編輯:Python

1.簡介:

和word文檔一樣,ExcelA file can also be thought of as a special compressed file,可以用unzip命令進行解壓.

同理,可以使用openpyxl來獲取excel中的圖片信息.

2. 使用unzip命令來解壓Excel,to get the picture:

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.

3. 使用openpyxl來獲取excel中的圖片信息:

# -*- 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

4. openpyxl.drawing.image源碼:

# 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......


  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式師世界 All Rights Reserved