下面函数,想要将图片放入指定的excel表格
如何放置
def createtabletoimg(rows: int = 18, cols: int = 9, spec: int = 1, colors: list = []):
"""
传入excel 以spec分割列,填充颜色,不够列数不够厚等所有列均填充完成后再由上到下填充spec个单元格颜色,读取填充好的excel 将其转换为图片
:param rows: excel行数
:param cols: excel列数
:param spec: 每列多少单元格同一颜色
:param colors: 填充颜色
:return: 写入数据库的image路由地址
"""
# 返回路径
imagepath = None
# 返回坐标
# 创建指定格式excel
import math
from random import randint
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side
# 十六进制颜色取值表
hexcolor = [chr(i) for i in range(48, 58)] + [chr(i) for i in range(65, 71)]
# 设置excel表格填充颜色
if len(colors) > 1:
# 传入颜色
colors = colors
else:
# 未传入颜色,设置100种随机颜色
colors = [''.join([hexcolor[randint(0, 15)] for i in range(6)]) for i in range(100)]
# 初始化颜色下标
colorIndex = 0
colorIndexm = 0
# 设置余量
numbers = 0
# image
images = 0
# 生效颜色
fills = [PatternFill("solid", fgColor=color) for color in colors]
# 设置存储文件路径
file_path = 'test.xlsx'
# 初始化Workbook
workbook = Workbook()
# 创建新工作表
sheet = workbook.create_sheet("Sheet1", 0)
# 循环列
# excel 行列从1开始算
for col in range(1, cols + 1):
t = rows - rows % spec
# 循环行
# excel 行列从1开始算
for row in range(1, 1 + t):
# 选中行列对象
cell = sheet.cell(column=col, row=row)
# 填充对象颜色
cell.fill = fills[colorIndex]
# 设置对象边框
cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin'))
images += 1
print(images, math.ceil(spec / 2))
# 设置颜色下标-满足条件切换颜色
if row % spec == 0:
colorIndex = (colorIndex + 1) % len(colors)
if images == math.ceil(spec / 2):
# 导入图片模块
from openpyxl.drawing.image import Image
# 读取图片
img = Image('power.png')
# 添加图片
cell.add_image(img)
images = 0
else:
colorIndex = (colorIndex + 1) % len(colors)
# 剩余部分填充
if rows % spec > 0:
for row in range(t + 1, rows + 1):
# 选中行列对象
cell = sheet.cell(column=col, row=row)
# 填充对象颜色
cell.fill = fills[colorIndexm]
# 设置对象边框
cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
bottom=Side(style='thin'))
# 计数+1
numbers += 1
images += 1
print(images, math.ceil(spec / 2))
# 设置颜色下标-满足条件切换颜色
if numbers % spec == 0:
colorIndexm = (colorIndexm + 1) % len(colors)
if images == math.ceil(spec / 2):
# 导入图片模块
from openpyxl.drawing.image import Image
# 读取图片
img = Image('power.png')
# 添加图片
cell.add_image(img)
images = 0
# 存储excel
workbook.save(file_path)
图片下载地址
https://gitee.com/xxhaadyq/image/raw/master/picture/power.png
