Draw Nozomi Sasaki in Excel with python

【environment】

windows8.1 　Excel 2013 　python2.7 　opencv3

【Overview】

Get the color information from Nozomi Sasaki's photo and fill it in an Excel cell.

[Folder structure]

|---sasaki_excel 　　　　　　|---sasaki_excel.py | --- sasaki_nozomi.jpg (Image of Nozomi Sasaki) | --- sasaki_nozomi.xlsx (Excel for drawing)

I used this image. 　

【program】

sasaki_excel.py

# -*- coding:utf-8 -*-

import cv2
from openpyxl import load_workbook
from openpyxl.styles import PatternFill

#Image loading
image = cv2.imread("sasaki_nozomi.jpg ")

#Excel file reading
wb = load_workbook(filename='sasaki_nozomi.xlsx')
#Take out the active sheet
ws = wb.active

#Create a list of alphabets using ASCII code
# col_name = ['A','B',...,'Z']
col_name = [chr(i) for i in range(65,65+26)]

#Excel column name list creation
i = 0
for j in xrange(26, len(image[0])):
    #Alphabet concatenation
    col_name.append(col_name[i] + col_name[j % 26])
    if (j + 1) % 26 == 0:
        i += 1
    if len(col_name) == len(image[0]):
        break

    

for gyo in xrange(len(image)):
    for retu in xrange(len(image[gyo])):
        for rgb in xrange(len(image[gyo][retu])):
            
            #Convert red, green, and blue values to hexadecimal
            #In hexadecimal'0x**'The last two characters are extracted because it is converted to
            red = hex(image[gyo][retu][0])[2:4]
            #For example'0xc'Since some hexadecimal numbers are converted to, concatenate 0
            if len(red) == 1:
                red = '0' + red
            green = hex(image[gyo][retu][1])[2:4]
            if len(green) == 1:
                green = '0' + green
            blue = hex(image[gyo][retu][2])[2:4]
            if len(blue) == 1:
                blue = '0' + blue
            
            #Change column and row width
            ws.column_dimensions[col_name[retu]].width = 0.3
            ws.row_dimensions[retu].height = 1.5
            
            #Get the cell name to fill
            cell_name = col_name[retu] + str(gyo + 1)
            #Set cell name
            cell = ws[cell_name]
            #Concatenate hexadecimal numbers
            color = str(blue) + str(green) + str(red)
            #fill
            cell.fill = PatternFill(patternType='solid', fgColor=color)

#Excel file save
wb.save('sasaki_nozomi.xlsx')

【result】

I never pasted the image (laughs) ~~ The cell width was adjusted manually. ~~ [2016.5.29 postscript] Incorporated cell width adjustment into the program rather than manually.

As an application of this program, you can search for mistakes by comparing the cell colors of two images.

[Reference site]

[Openpyxl playing with Excel files from Python](http://pepper.is.sci.toho-u.ac.jp/index.php?%A5%CE%A1%BC%A5%C8%2FPython%A4%AB % A4% E9Excel% A5% D5% A5% A1% A5% A4% A5% EB% A4% F2% A4% A4% A4% B8% A4% EBopenpyxl) ASCII code table

[Other language version]

Click here for Node.js version Click here for Ruby version