Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how Python reads the picture function in excel". The editor shows you the operation process through an actual case. The operation method is simple and fast, and it is practical. I hope this article "how Python reads the picture function in excel" can help you solve the problem.

1. Read excel files

Let's first look at how to read excel files. There are many ways to read excel files. Select the openpyxl module here, and the installation statement is as follows:

Pip install openpyxl

We also need to use some other modules, as follows:

Pip install pyzbarpip install pillowpip install numpy

Now we can get started.

In Excel, there are workbooks, tables, units, and so on. Here to put it briefly, the workbook is an excel file, the table is our lower left-hand corner of excel switch sheet1, sheet2, the unit is a grid. Let's read an excel file:

From openpyxl import load_workbook# load excelwb = load_workbook ("111.xlsx") # switch to the first table ws = wb [wb.sheetnames [0]] # get A3 unit cell = ws ['A3'] # output the value of A3 unit print (cell.value) 2. Read the picture in excel

There are many ways to read pictures in excel, and this article will share two ways.

(1) use zipfile module

Excel itself is a compressed file. After we change the suffix of excel to zip, we will see some picture files in the xl/media directory after manual decompression. These pictures are the pictures inserted by excel. Therefore, we can read the excel images by decompressing them. The code is as follows:

Import osfrom zipfile import ZipFile# decompress directory unzip_path = ". / unzip" if not os.path.exists (unzip_path): os.mkdir (unzip_path) with ZipFile ("111.xlsx") as f: for file in f.namelist (): # decompress the image part of the file if file.startswith ("xl/media"): f.extract (file, path=unzip_path) (2) read using openpyxl

The above operation can get the image in excel, but there is a drawback. That is, we don't know which picture comes from which unit, and in some cases it is very comparative to know which unit the picture comes from. Let's solve this problem:

From openpyxl import load_workbookwb = load_workbook ("111.xlsx") ws = wb [wb.sheetnames [0]] # traversal all for image in ws._images: print (image) in the table

We first read a table and then call _ images to get all the pictures in the table. But we can't operate this picture yet. we'll see the next section on the specific operation of the picture. Let's first see how to know which unit the picture comes from. We can output the anchor._from of the picture:

From openpyxl import load_workbookwb = load_workbook ("111.xlsx") ws = wb [wb.sheetnames [0]] for image in ws._images: # location information of the output picture print (image.anchor._from)

The specific input contents are as follows:

Parameters:col=0, colOff=1, row=0, rowOff=1

Where col represents the line number and row represents the column number. According to this information, we can know the unit of the picture. Col=0,row=0, for example, should represent unit A1. If col=1,row=1, it should represent B2 unit.

Third, deal with the read pictures

There are many operations for image processing, which depends on the specific needs. Here I share the operation of converting images in excel into pillow images and ndarray objects. After the conversion, we can use numpy and pillow to perform various operations on the image.

Import numpy as npfrom PIL import Imagefrom openpyxl import load_workbookwb = load_workbook ("111.xlsx") ws = wb [wb.sheetnames [0]] for image in ws._images: # convert a picture into a picture object in Pillow img = Image.open (image.ref) .convert ("RGB") # convert the picture object in Pillow into the ndarray array img = np.array (img)

If the picture in our excel is a QR code, we can do the following:

Import numpy as npfrom PIL import Imagefrom pyzbar import pyzbarfrom openpyxl import load_workbookwb = load_workbook ("111.xlsx") ws = wb [wb.sheetnames [0]] for image in ws._images: # convert into an easy-to-operate picture object img = Image.open (image.ref) .convert ("RGB") img = np.array (img) # parse the QR code data = pyzbar.decode (img) if data: Text = data [0] .data.decode ('utf-8') print (text) else: print ("content not recognized")

In addition to the above methods, you can also do the following:

1. Change the suffix name of the excel file to be read to zip and become a compressed file.

2. Extract the file again.

3. In the unzipped folder, there are pictures in excel.

4. If you read the pictures in excel in this way, you will read the pictures in the folder. Like ordinary files, you can do all kinds of processing.

Specific implementation code

'' File Name: readexcelimg Author: tim Date: 19:52 on 2018-7-26 Description: read the pictures in excel, print the picture path, first convert excel into zip package, decompress the zip package, there is a folder under the package to store pictures Read this picture''import os import zipfile # to determine whether it is a file and to determine whether a file exists def isfile_exist (file_path): if not os.path.isfile (file_path): print ("It's not a file or no such file exist!% s"% file_path) return False else: return True # modify the file type name in the specified directory Change the excel suffix name to .zip def change_file_name (file_path New_type='.zip'): if not isfile_exist (file_path): return''extend = os.path.splitext (file_path) [1] # get the file extension if extend! =' .xlsx 'and extend! =' .xls': print ("It's not an excel file!%"% file_path) return False file_name = os.path.basename (file_path) # get the file name New_name = str (file_name.split ('.') [0]) + new_type # new file name Name it: xxx.zip dir_path = os.path.dirname (file_path) # get the directory where the file is located new_path = os.path.join (dir_path, new_name) # New file path if os.path.exists (new_path): os.remove (new_path) os.rename (file_path, new_path) # Save the new file, the old file will be replaced by return new_path # and the new file path will be returned Zip package # extract the file def unzip_file (zipfile_path): if not isfile_exist (zipfile_path): return False if os.path.splitext (zipfile_path) [1]! = '.zip': print ("It's not a zipfile!% s"% zipfile_path) return False file_zip = zipfile.ZipFile (zipfile_path) 'r') file_name = os.path.basename (zipfile_path) # get the file name zipdir = os.path.join (os.path.dirname (zipfile_path), str (file_name.split ('.) [0])) # get the directory where the file resides for files in file_zip.namelist (): file_zip.extract (files, os.path.join (zipfile_path) Zipdir)) # extract to the specified file directory file_zip.close () return True # read the extracted folder Print image path def read_img (zipfile_path): if not isfile_exist (zipfile_path): return False dir_path = os.path.dirname (zipfile_path) # get the directory where the file resides file_name = os.path.basename (zipfile_path) # get the file name pic_dir = 'xl' + os.sep +' media' # excel, and then decompress it The picture is in the media directory pic_path = os.path.join (dir_path, str (file_name.split ('.') [0]), pic_dir) file_list = os.listdir (pic_path) for file in file_list: filepath = os.path.join (pic_path) File) print (filepath) # combine functions def compenent (excel_file_path): zip_file_path = change_file_name (excel_file_path) if zip_file_path! ='': if unzip_file (zip_file_path): read_img (zip_file_path) # main if _ _ name__ = ='_ main__': compenent ('/ Users/Desktop/test/people.xlsx') off This is the end of the introduction to "how Python reads pictures in excel". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.