I’ve seen a lot of headlines about how someone in some country has taught himself how to draw using Excel for years, and the results are amazing. I have great respect for their patience. But as a programmer, you have to challenge yourself. We can do this in ten minutes!
The basic idea
The basic idea is to read the color value of each pixel of the image and fill each cell in Excel with a color. So the main use is PIL, OpenPyXL these two libraries.
Use PIL
PIL is Python inside do image processing is very common when a library, function is also very powerful, here only need to use a small part of the function of PIL.
from PIL import Image
img = Image.open(img_path) # fetch image
width, height = img.size Get the image size
r, g, b = img.getpixel((w - 1, h - 1)) Get the pixel color value
Copy the codeImage.open() is a PIL function to open an Image, supporting multiple Image types
Img_path is the image path, which can be relative or absolute
Img. size is the size property that gets the image, including the width and height of the image
Img.getpixel () is a function that gets the color value of an image, passing in a tuple or list of pixel coordinates xy
Openpyxl use
Openpyxl is almost the most full-featured Library in Python for manipulating Excel files, and only a fraction of its functionality is needed here.
import openpyxl
from openpyxl.styles import fills
workbook = openpyxl.Workbook()
worksheet = workbook.active
cell.fill = fills.PatternFill(fill_type="solid", fgColor=hex_rgb)
workbook.save(out_file)
Copy the codeOpenpyxl.workbook () creates a new Excel file
Workbook. active Activates a worksheet
Cell. Fill = fills.PatternFill(fill_type=”solid”, fgColor= hex_RGB) fills a cell, fill_type=”solid” is the fill type, fgColor= hex_RGB is the fill color
Workbook.save () saves the file, passing in the name of the file to save
Write a code
The core usage of PIL and OpenPyXL described above is needed to write this drawing requirement. But when actually writing, there are some other questions, such as:
-
Getpixel () gets the color values in RGB decimal, but the fgColor ‘parameter in fills.PatternFill receives the color values in hexadecimal
This problem is actually decimal to hexadecimal, easy to solve
def int_to_16(num): num1 = hex(num).replace('0x'.' ') num2 = num1 if len(num1) > 1 else '0' + num1 If there is only one digit, add zeros in front of it return num2 Copy the code -
Excel cells are rectangles by default, so changing them to squares won’t distort the image
if h == 1: _w = cell.column _h = cell.col_idx Adjust column width worksheet.column_dimensions[_w].width = 1 # Adjust row height worksheet.row_dimensions[h].height = 6 Copy the code
Here we use a double for loop, the outer layer is width, the inner layer is height, is column by column fill color, so check if h == 1, avoid adjusting column width multiple times.
-
Excel supports a limited number of styles
This is a serious problem. If we directly operate the large hd image, the excel file output may prompt us that there is something wrong with the file and it needs to be repaired automatically when it is opened.
But when it’s done, all the colors are gone!
After querying the data, we found that the number of large rows supported by version 13 excel is 1048576, and the maximum number of columns is 16384. The number of cells we use is far below the limit.
After changing pictures, changing Excel version, modifying code and other inadequate tests, the cause of the problem was found.
Excel is made up of multiple XML files, and the filled colors are stored in a style.xml file. If the file is too large, it will cause an error when opened.
So to solve this problem, there are two solutions, the first is to reduce the image, the second is to reduce the image color. Reduce the picture with the function of reducing the picture color, reduce the picture color method can use grayscale, binarization and other methods.
Generally speaking, it is necessary to control the number of colors * the number of cells < the threshold (about 3300W).
MAX_WIDTH = 300
MAX_HEIGHT = 300
def resize(img):
w, h = img.size
if w > MAX_WIDTH:
h = MAX_WIDTH / w * h
w = MAX_WIDTH
if h > MAX_HEIGHT:
w = MAX_HEIGHT / h * w
h = MAX_HEIGHT
return img.resize((int(w), int(h)), Image.ANTIALIAS)
Copy the codeThe final result
God pays off, open the final output excel can already see the effect!
So anything that can be solved with Python will eventually be solved with Python.
All the code
# draw_excel.py
from PIL import Image
import openpyxl
from openpyxl.styles import fills
import os
MAX_WIDTH = 300
MAX_HEIGHT = 300
def resize(img):
w, h = img.size
if w > MAX_WIDTH:
h = MAX_WIDTH / w * h
w = MAX_WIDTH
if h > MAX_HEIGHT:
w = MAX_HEIGHT / h * w
h = MAX_HEIGHT
return img.resize((int(w), int(h)), Image.ANTIALIAS)
def int_to_16(num):
num1 = hex(num).replace('0x'.' ')
num2 = num1 if len(num1) > 1 else '0' + num1
return num2
def draw_jpg(img_path):
img_pic = resize(Image.open(img_path))
img_name = os.path.basename(img_path)
out_file = './result/' + img_name.split('. ') [0] + '.xlsx'
if os.path.exists(out_file):
os.remove(out_file)
workbook = openpyxl.Workbook()
worksheet = workbook.active
width, height = img_pic.size
for w in range(1, width + 1) :for h in range(1, height + 1) :if img_pic.mode == 'RGB':
r, g, b = img_pic.getpixel((w - 1, h - 1))
elif img_pic.mode == 'RGBA':
r, g, b, a = img_pic.getpixel((w - 1, h - 1))
hex_rgb = int_to_16(r) + int_to_16(g) + int_to_16(b)
cell = worksheet.cell(column=w, row=h)
if h == 1:
_w = cell.column
_h = cell.col_idx
Adjust column width
worksheet.column_dimensions[_w].width = 1
# Adjust row height
worksheet.row_dimensions[h].height = 6
cell.fill = fills.PatternFill(fill_type="solid", fgColor=hex_rgb)
print('write in:', w, ' | all:', width + 1)
print('saving... ')
workbook.save(out_file)
print('success! ')
if __name__ == '__main__':
draw_jpg('mona-lisa.jpg')
Copy the codeThe attached:
The number of colors * the number of cells < the threshold (around 256^4), you might wonder where this 256^4 came from.
This is, of course, a random test I took.
Since there are color number and cell number of these two variables, then naturally there should be more than two test methods. One observation color number, one observation cell number.
But I’ve only done one test here for the number of colors. (The biggest reason is that it takes too long to generate excel with tens of thousands of rows * columns… Lazy…).
count = 0
MAX_WIDTH = 255
for w in range(1, MAX_WIDTH + 1) :for h in range(1, MAX_WIDTH + 1):
cell = worksheet.cell(column=w, row=h)
if h == 1:
_w = cell.column
_h = cell.col_idx
Adjust column width
worksheet.column_dimensions[_w].width = 1
# Adjust row height
worksheet.row_dimensions[h].height = 6
if count < 255 ** 3:
back = int_to_16(num=count)
back = '0' * (6 - len(back)) + back
else:
back = ' '.join([int_to_16(random.randint(0.255)) for _ in range(3)])
cell.fill = fills.PatternFill(fill_type="solid", fgColor=back)
count += 1
Copy the codeCount is the variable that records the colors, ensuring that each color is not repeated, but currently computer RGB represents only 256^3 colors at most
Test excel thresholds by adjusting the value of MAX_WIDTH
The resulting test Excel is as follows:
The last
Due to limited energy lazy, ability is also limited, so there is no test of a single color, there may be other methods can not have this threshold limit.
All codes have been uploaded to Github. Welcome to friendly exchange and discussion [handshake]