excel作图
思路：
1、提取一张图片的所有像素点的颜色
2、按象素位置填充对应的单元格背景色
问题：
1、excel无法多线程写入？（时间没有太明显 的优化，多线程写入偶尔会有格子颜色产生错乱，但不是必现，没有去解决，了解多线程即可 ）
2、已解决：无法转换太大张的图片（excel每个sheet中的格式有数量限制，每个颜色代表一种格式 ）
解决办法：格式数量最大 65535，通过判断pixel中包含的数据是否多于 65535 ，多则缩小图片一半，再判断一次
效果：
因为sheet只能承载 65535个【不同】的象素点，所以会有缩放，造成一定马赛克效果。
# -*- utf-8 -*-
'''
导入一张图片
提取象素
在excel中画出所有象素点（作图）
'''
from concurrent.futures.thread import ThreadPoolExecutor
__author__ = 'linkai'
import os
from tkinter import messagebox
from tkinter import filedialog
from PIL import Image
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill
import time
from openpyxl.utils import column_index_from_string
import logging
# import threading
# logging.basicConfig(level = logging.INFO,format = '%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
logger.setLevel(level=logging.INFO)
handler = logging.FileHandler("log.txt", mode="w")
handler.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)
console = logging.StreamHandler()
console.setLevel(logging.WARNING)
console.setFormatter(formatter)
logger.addHandler(handler)
logger.addHandler(console)
class get_picture_info(object):
def __init__(self):
# 获取windows桌面路径
self.__desktop_path = os.path.join(os.path.expanduser('~'), "Desktop")
logger.info(self.__desktop_path)
# 获取图片路径
def get_picture(self):
messagebox.showinfo(title="软件用法", message="1、选中一张图片；\n"
"2、系统在图片路径下自动生成excel象素图")
picture_path = filedialog.askopenfilename(title="选择一张图片", initialdir=self.__desktop_path)
return picture_path if os.path.exists(picture_path) else self.get_picture()
# 检测图片象素点格式数量，是否小于 65535
def check_pixes(self, width, height, pix, max_color_counts):
# 颜色累加至一个list
tmp_list = []
for x in range(0, width):
for y in range(0, height):
tmp_list.append(pix[x, y])
# list取唯一值，然后生成新的list，再计算list长度（颜色去重计数量）
if len(list(set(tmp_list))) < max_color_counts:
return (True,len(list(set(tmp_list))))
else:
return (False,len(list(set(tmp_list))))
# 解析图片,返回图片元素
def get_pixes_from_picture(self, picture_path):
im = Image.open(picture_path)
im_w, im_h = im.size
agree_to_scale = False
# 展示当前图片大小，并咨询是否缩放图片.
if im_w > im_h:
if im_w > 400:
im_w_new = 400
im_h_new = int(im_h / (im_w / im_w_new))
agree_to_scale = messagebox.askyesno(title="图片超大提醒",
message="该图片大小为：" + str(im_w) + " x " + str(im_h) + "\n" +
"建议缩放至：" + str(im_w_new) + " x " + str(im_h_new) + "\n" +
"否则生成的excel可能报错，是否缩放？")
else:
if im_h > 400:
im_h_new = 400
im_w_new = int(im_w / (im_h / im_h_new))
agree_to_scale = messagebox.askyesno(title="图片超大提醒",
message="该图片大小为：" + str(im_w) + " x " + str(im_h) + "\n" +
"建议缩放至：" + str(im_w_new) + " x " + str(im_h_new) + "\n" +
"否则生成的excel可能报错，是否缩放？")
# 调整图片大小
if agree_to_scale:
im_w, im_h = im_w_new, im_h_new
im = im.resize((im_w, im_h), Image.ANTIALIAS)
# 载入图片象素
pix = im.load()
# 检查象素类型是否小于 65535 ，如非，则减小一半的图片面积
MAX_COLOR_TYPE = 65535
while True:
check, pix_len = self.check_pixes(im_w, im_h, pix, MAX_COLOR_TYPE)
if check:
break
messagebox.showinfo(title="图片超大提醒", message="图片象素点格式多于65535，excel无法展示，尝试缩小")
im_w, im_h = int(im_w / (pix_len/MAX_COLOR_TYPE))-1, int(im_h / (pix_len/MAX_COLOR_TYPE))-1
im = im.resize((im_w, im_h), Image.ANTIALIAS)
pix = im.load()
return {
"image_width": im_w,
"image_height": im_h,
"pixes": pix
}
class set_excel_RGBS(object):
def __init__(self, width, height, pix, filepath):
self.__width = width
self.__height = height
self.__pix = pix
self.__filename_without_suffix, self.__suffix = os.path.splitext(filepath)
self.__excel_filepath = self.__filename_without_suffix + r".xlsx"
self.__wb = openpyxl.Workbook()
self.__ws = self.__wb.active
# 设置长宽
def set_cells_size(self):
# excel 行列单位比 1个列单位长/1个行单位长 ~= 6.33
times = 6.33
size = 1
for i in range(1, self.__width + 1):
self.__ws.column_dimensions[get_column_letter(i)].width = size
for i in range(1, self.__height + 1):
self.__ws.row_dimensions[i].height = size * times
logger.info((self.__ws.column_dimensions[get_column_letter(1)].width, self.__ws.row_dimensions[1].height))
# logging.info(self.__excel_filepath)
# self.__ws.cell(1,1,"haha")
def threading_messagebox(self, title, message):
logger.warning("threading start")
# messagebox.showinfo(title=title,message=message)
logger.warning("title=" + title + " ; message=" + message)
# 将pix转换为16进制rgb颜色
def convert_pix_to_RGB(self, pix):
pix_r, pix_g, pix_b = pix
# logging.info( str(hex(pix_r))[2:]+str(hex(pix_g))[2:]+str(hex(pix_b))[2:])
# logging.info(type(hex(pix_r*65536+pix_g*256+pix_b)))
return str(hex(pix_r * 65536 + pix_g * 256 + pix_b))[2:].zfill(6)
# 画图~！
def paint_in_sheet(self):
# 尽量不要用进程来调用
# t=threading.Thread(target=self.threading_messagebox,args=("等待提醒","excel画图需要比较长时间，请耐心等待\n"+
# "一张1027*768的图片大概需要3~5分钟\n"+
# "作完图后，系统会自动打开excel，请耐心等待",))
#
# t.setDaemon(True)
# t.start()
# t.join()
messagebox.showinfo(title="等待提醒", message="点击确认后开始excel作图\n"
"excel画图需要比较长时间，请耐心等待\n"
"一张1027*768的图片大概需要3~5分钟\n"
"作完图后，系统会自动打开excel，请耐心等待\n")
start_time = time.time()
'''
# WARNING:root:耗费时间 = 7.912883996963501
for i in range(1,self.__width+1):
#TODO 使用thread ＋threadpool 改成多线程，加快制图速度
for j in range(1,self.__height+1):
tmp_fgColor=self.convert_pix_to_RGB(self.__pix[i-1,j-1])
logger.info("paintting cell [" + get_column_letter(i) + str(j) + "] to color " +tmp_fgColor)
#excel填充背景
self.__ws[get_column_letter(i)+str(j)].fill = PatternFill("solid",fgColor=tmp_fgColor)
'''
# 多线程模式
# 这个多线程没有加快时间，而且产生了较多噪点，可能是excel只支持单线程修改？
MAX_THREADS = 10
with ThreadPoolExecutor(MAX_THREADS) as executor1:
executor1.map(self.drow_row, range(1, self.__width + 1))
end_time = time.time()
logger.warning("耗费时间 = " + str(end_time - start_time))
# 为多线程调用 设置的画行（单行上的所有列格）方法
def drow_row(self, row):
for j in range(1, self.__height + 1):
tmp_fgColor = self.convert_pix_to_RGB(self.__pix[row - 1, j - 1])
logger.info("paintting cell [" + get_column_letter(row) + str(j) + "] to color " + tmp_fgColor)
# logging.info(type(tmp_fgColor))
# logging.info("hexvalue="+tmp_fgColor)
# excel填充背景
self.__ws[get_column_letter(row) + str(j)].fill = PatternFill("solid", fgColor=tmp_fgColor)
# 保存文件
def save_to_excel(self):
self.__wb.save(self.__excel_filepath)
os.startfile(self.__excel_filepath)
if __name__ == "__main__":
myPaint = get_picture_info()
picture_path = myPaint.get_picture()
pic_value = myPaint.get_pixes_from_picture(picture_path)
result_excel = set_excel_RGBS(width=pic_value["image_width"], height=pic_value["image_height"],
pix=pic_value["pixes"], filepath=picture_path)
result_excel.set_cells_size()
result_excel.paint_in_sheet()
result_excel.save_to_excel()
浙公网安备 33010602011771号