Openpyxl is a Python library for manipulating excel documents of the format (xlsx/xlsm/xltx/xltm) created in different variants of Microsoft Excel. The Genesis of the library happened due to lack of a native library to read/write natively from Python the Office Open XML format.
Openpyxl provides python with data set processing capabilities and allows creating different types of database files. One of the stark features offered by the library is allowing the user to define an image inside a cell of the sheet (worksheet). This opens the room for incorporating visual data inside our worksheet, allowing for more comprehensive and explicit results.
To install the openpyxl library execute the following command in the command-line:
pip install openpyxl
For the purpose of importing images inside our worksheet, we would be using a method found inside the openpyxl library under the name of openpyxl.drawing.image.Image. The method is a wrapper over PIL.Image method found in PIL (pillow) library. Due to which it is necessary for the PIL (pillow) library to be installed in order to use this method.
Test Image (test.png):
Below is the implementation –
Python3
import openpyxl # It is not required for one to create a workbook on # filesystem, therefore creating a virtual workbook wrkb = openpyxl.Workbook() # Number of sheets in the workbook (1 sheet in our case) ws = wrkb.worksheets[0] # Adding a row of data to the worksheet (used to # distinguish previous excel data from the image) ws.append([10, 2010, "Geeks", 4, "life"]) # A wrapper over PIL.Image, used to provide image # inclusion properties to openpyxl library img = openpyxl.drawing.image.Image('test.png') # The Coordinates where the image would be pasted # (an image could span several rows and columns # depending on it's size) img.anchor = 'A2' # Adding the image to the worksheet # (with attributes like position) ws.add_image(img) # Saving the workbook created under the name of out.xlsx wb.save('out.xlsx') |
Output (out.xlsx):-
Explanation
The above code first creates a workbook and saves in the variable wrkb (abbreviation for workbook). wrkb.worksheet[0] specifies the lists of sheets in the book. Since we only want one sheet, we specified 0 as an argument. ws.append() is used to add data to our worksheet. In our case we are adding a row of data 10, 2010, "Geeks", 4, "life" to our worksheet. openpyxl.drawing.image.Image('test.png') specifies the path of the image that would be added inside the worksheet (test.png in our case). img.anchor = 'A2' is used to specify the coordinates at which the image is to be pasted/added.
By default the image would be added from cell A1 (anchor A1) or the first cell of our workbook. This position could be changed by specifying a cell coordinates in img.anchor attribute. ws.add_image() adds the image inside the worksheet. This is a method used to finalize the image changes that we want. All other attributes like anchor, would be supplied before this function. wrkb.save() is used to save our worksheet. A path (relative/absolute) is required as an argument, along with any extension included in the path (if not explicitly provided).
