I've seen a lot of headlines before , Say which country someone has insisted on self-study for many years excel Drawing a picture , The results are stunning . I admire their patience very much . But as a programmer , Naturally, I have to challenge myself . This demand , We can finish it in ten minutes ！
The basic idea to realize this requirement is to read the color value of each pixel of the picture , And then to excel Each cell in the is filled with color . So the main use is PIL、openpyxl These two libraries .
PIL yes Python It is a very common library for image processing , The function is also very powerful , All you need here is PIL A small part of the functions in .
from PIL import Image img = Image.open(img_path) # Read the picture width, height = img.size # Get image size r, g, b = img.getpixel((w - 1, h - 1)) # Get pixel color value
Image.open() yes PIL Function to open a picture inside , Support multiple picture types img_path Is the picture path , Can be a relative path , It could be an absolute path img.size Is to get pictures size attribute , Contains the width and height of the picture img.getpixel() Is a function to get the color value of the picture , You need to pass in a tuple or list, The value is the pixel coordinate xy
openpyxl Almost Python The most fully functional operation in excel The library of files , Only a small part of its functions are 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)
openpyxl.Workbook() Create a new one excel file workbook.active Activate a worksheet cell.fill = fills.PatternFill(fill_type="solid", fgColor=hex_rgb) Fill a cell ,fill_type="solid" Is the fill type ,fgColor=hex_rgb Is the fill color workbook.save() Save the file , You need to pass in the file name to save
The core that needs to be used to write this drawing requirement is the one described above PIL Follow openpyxl Several uses of . But when actually writing , There will be other problems , such as ：
1.getpixel() The color value obtained is rgb Decimal , but fills.PatternFill Inside fgColor` The color value received by the parameter is a hexadecimal value
This problem is actually the conversion of decimal to hexadecimal , It's easy to solve
def int_to_16(num):
num1 = hex(num).replace('0x', '')
num2 = num1 if len(num1) > 1 else '0' + num1 # When there is only one digit, fill in zero in front
return num22.excel The default cell is rectangular , Change to square so that the picture will not deform
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
There is a double for loop , The outer layer is `width`, The inner layer is `height`, Is a column of fill colors , So judge `if h == 1`, Avoid adjusting column width multiple times .
3.excel A limited number of styles are supported
This is a serious problem . If you directly operate the high-definition large picture , The last output excel When the file is opened , It may prompt us that there is a problem with the file , Need automatic repair .
But when it's repaired , You will find that all the colors filled have disappeared ！
Error message
At first, I thought it was because too many rows and columns were used .
After searching the information, we found that ,13 edition excel The number of lines supported is 1048576, The maximum number of columns is 16384, The number of cells we use is far from the limit .
After changing the picture 、 Replace excel edition , Modify the code and other insufficient tests , To find the cause of the problem .
It's because ,excel The original form of is composed of multiple xml file , The fill colors are stored in a style.xml In the document , When the file is too large, an error will be reported when opening .
So in order to solve this problem , There are two solutions , The first is to reduce the picture , The second is to reduce the color of the picture . When reducing the picture, it has the function of reducing the color of the picture , The method of reducing the color of the picture can be grayed out 、 Binarization and other methods .
On the whole , Is the need to control the number of colors * Number of cells < threshold （3300w about ).
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)
The sky is full of people , Open the last output excel You can already see the effect ！
So , Everything works Python Problem solved , In the end Python To solve .
# 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')attach =
The above said Number of colors * Number of cells < threshold （2564 about ) When , There may be doubts , This 2564 How did you get it .
This, of course, is what I got from the test .
Since there are two variables: the number of colors and the number of cells , So naturally, there should be more than two test methods . A number of observed colors , The number of observation cells .
But I only did one test of the number of colors here .( The biggest reason is to generate tens of thousands of lines * Tens of thousands of trains excel It's been a long time ... 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
count Is the variable that records the color , Make sure that each color does not repeat , But at present, computers RGB The color represented is at most 256^3 Kind of Through adjustment MAX_WIDTH To test excel The threshold of
Finally, the generated test excel as follows ：
... It's a little nice .！？？
Due to limited energy ,~ lazy ~, Limited ability ~ food ~, So there is no test for a single color , There may also be other methods that do not have the limit of this threshold .
