python excel 颜色填充 excel样式

开发一个项目的时候遇到一个问题,项目特别急,也没时间查资料,于是提了个问题, 在这里插入图片描述
其实问题的描述还是不够清楚,是创建一个excel,设置m行n列的单元格为实线,每一列单元格分割s个填充颜色, 不够的像下一个单元格取(前提是所有单元格列都已经都已经填充了m//s次)
感谢广大菜鸟给出的回答,由于我之前给的问题后面改了,但是基础功能还是有了
另外他也出了一个博客,也是关于excel填充的python绘制表格不同颜色的excel


from openpyxl import Workbook
from openpyxl.styles import PatternFill, Side, Border
# 仿照excel格式
# excel文件路径
file_path = 'C:/Users/Lenovo/Desktop/工作簿2.xlsx'
colors = ['000000', '44546A']
fills = [PatternFill("solid", fgColor=color) for color in colors]
workbook = Workbook()
sheet = workbook.create_sheet("Sheet1", 0)0
rows, cols = 30, 9
colorIndex = 1
block_height, block_width = 5, 1
for i in range(int(rows / block_height)):for j in range(int(cols / block_width)):colorIndex = (colorIndex + 1) % 2for p in range(block_height):row = block_height * i + pfor q in range(block_width):col = j * block_width + qcell = sheet.cell(column=col + 1, row=row + 1)cell.fill = fills[colorIndex]cell.border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))
workbook.save(file_path)

于是我根据他选中单元格,的关键函数整理重新复写了一个函数
关键函数:

cell = sheet.cell(column=col + 1, row=row + 1)
cell.fill = fills[colorIndex]
cell.border = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))

整理函数如下


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# 创建指定格式excelfrom random import randintfrom openpyxl import Workbookfrom 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 = colorselse:# 未传入颜色,设置100种随机颜色colors = [''.join([hexcolor[randint(0, 15)] for i in range(6)]) for i in range(100)]# 初始化颜色下标colorIndex = 0colorIndexm = 0# 设置余量numbers = 0# 生效颜色fills = [PatternFill("solid", fgColor=color) for color in colors]# 设置存储文件路径file_path = 'test.xlsx'# 初始化Workbookworkbook = 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'))# 设置颜色下标-满足条件切换颜色if row % spec == 0: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'))# 计数+1numbers += 1# 设置颜色下标-满足条件切换颜色if numbers % spec == 0:colorIndexm = (colorIndexm + 1) % len(colors)# 存储excelworkbook.save(file_path)# excel创建完成# 读取excel内容转换为图片from PIL import ImageGrabimport xlwings as xw# 使用xlwings的app启动app = xw.App(visible=True, add_book=False)# 打开文件wb = app.books.open(file_path)# 选定sheetsheet = wb.sheets("Sheet1")# 获取有内容的区域all = sheet.used_range# 复制图片区域all.api.CopyPicture()# 粘贴sheet.api.Paste()# 设置文件名img_name = 'data' + ".png"# 当前图片pic = sheet.pictures[0]# 复制图片pic.api.Copy()# 获取剪贴板的图片数据img = ImageGrab.grabclipboard()# 保存图片img.save(img_name)# 删除sheet上的图片pic.delete()# 不保存，直接关闭wb.close()# 退出xlwings的app启动app.quit()#存入数据库......# 图片转换存入数据库完成imagepath = img_namereturn imagepath