How to create a photo Excel spreadsheet in python

Do you love spreadsheets? Do you also love coding?

Well, if yes, then here’s a fun thing you can do with python and excel spreadsheets!

In this tutorial, we will create an excel spreadsheet that will contain colored cells and will display an image when you zoom out !
Now I know that there are websites online that allow you to do that, but most of them are paid, so I thought of creating a tutorial where you can do it yourself in just three easy steps! So without any delay, let’s get started!

Prerequisites

I am assuming that you have Python3, pip and Virtual Environment installed already. If you don’t, then you can visit these sites to install Python and Virtual Environment.

Now working in Virtual Environment is entirely optional. if you have python and don’t wanna work in Virtual Environment, it’s perfectly fine; you can just skip to step 2.

Step 1: Creating a Virtual Environment

We will create a virtual environment for our project and name it image_spreadsheet.
In your terminal type the following.

# Creating Virtual Environment
virtualenv --system-site-packages -p python3 image_spreadsheet
# Switching to the folder
cd image_spreadsheet
# Before we activate the environment, we see which python is being used
which python
# Now we activate the environment
source ./bin/activate
# This time you should see the python in your Virtual Environment
which python
Your terminal should look something like this

Step 2: Installing Dependencies

We will use OpenCV and XlsxWriter to create our files

# Installing OpenCV - This will also install numpy if you don't have it
pip install opencv-python
# Installing XlsxWriter for creating Excel files
pip install XlsxWriter

Step 3: Writing Code!

Okay, we are almost done and this is the fun part!

Create a file named “run.py” in your image_spreadsheet folder and open it in any text editor (I use VS Code which you can download here)

Also get the image you want to insert into your excel file and name it “photo” with whatever extension it has .jpg .png etc. Place it in the same folder as well.

Now open run.py and start writing the code. First, we will import the libraries and set up required functions

# Necessary imports
import cv2
import xlsxwriter

# This function is supposed to convert RGB values to hex
# On more info how we created this visit: http://psychocodes.in/без-рубрики/rgb-to-hex-conversion-and-hex-to-rgb-conversion-in-python/

def rgb2hex(r,g,b):
    return "#{:02x}{:02x}{:02x}".format(r,g,b)

Then we will process the image to suit our desired size. For this exercise we are using the image below. Dont forget to place it in the same folder as your python file and enter the appropriate name in line 24

We will use this image for our project
# Now we will read the image
# make sure the image is in the same directory as your python file
# ENTER THE NAME OF YOUR FILE HERE
image = cv2.imread("photo.jpg")
print("The image size is: " + str(image.shape))

# Usually images are of larger size wich will slow down the program
# We will resize the image to your preferred size
inp = input("Enter 's' to input size or anything else to use default size: ")

# creating default size
default_size = (75,150)

if inp == 's':
    h = input('Enter Height: ')
    w = input('Enter Width: ')
    default_size = (int(w),int(h))

scaled_image = cv2.resize(image, default_size)
print("The new size is: " + str(scaled_image.shape))

Finally, we will create a workbook and fill in values. You have the option of leaving the cells blank or entering some text in them which you can choose in line 48

# Now creating workbook
workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()

# Looping through rows and columns
for row in range(scaled_image.shape[0]):
    # we will print also whats being processed
    print("Processing row "+str(row))
    for col in range(scaled_image.shape[1]):

        cell_format = workbook.add_format()
        b = scaled_image[row,col,0]
        g = scaled_image[row,col,1]
        r = scaled_image[row,col,2]
        # Getting hex code
        color = rgb2hex(r,g,b)
        cell_format.set_bg_color(color)
        # you can have the cells say whatever you want
        worksheet.write(row, col, 'hello', cell_format)

# closing the spreadsheet
workbook.close()

The last step would be to run your file so go to your terminal and run your python file

python run.py

Viola!

We are done! So now you can create as many spreadsheets as you like with all sorts of pictures you want!

You can also directly get the code on my GitHub page below:
https://github.com/kharbandaraghu/image2excel

Thanks for staying with me! Hope to see you again in my page : )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s