python openpyxl
参考：https://blog.csdn.net/JunChen681/article/details/126360091
颜色
Color(index=0) # 根据索引进行填充 # Color(rgb='00000000') # 根据rgb值进行填充 # index COLOR_INDEX = ( '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4 '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9 '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14 '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19 '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24 '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29 '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34 '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39 '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44 '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49 '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54 '00969696', '00003366', '00339966', '00003300', '00333300', #55-59 '00993300', '00993366', '00333399', '00333333', #60-63 ) BLACK = COLOR_INDEX[0] WHITE = COLOR_INDEX[1] RED = COLOR_INDEX[2] DARKRED = COLOR_INDEX[8] BLUE = COLOR_INDEX[4] DARKBLUE = COLOR_INDEX[12] GREEN = COLOR_INDEX[3] DARKGREEN = COLOR_INDEX[9] YELLOW = COLOR_INDEX[5] DARKYELLOW = COLOR_INDEX[19]
字体
sheet0.cell(row, col).font = Font(name='宋体', size=12, color=Color(index=row), b=False, i=False) # size sz 字体大小 # b bold 是否粗体 # i italic 是否斜体 # name family 字体样式
边框
Side(style='thin',color=Color(index=0)) # style可选项 style = ('dashDot','dashDotDot', 'dashed','dotted', 'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick', 'thin') # 'medium' 中粗 # 'thin' 细 # 'thick' 粗 # 'dashed' 虚线 # 'dotted' 点线
填充
PatternFill(patternType='solid',fgColor=Color(), bgColor=Color()) # fgColor 前景色 # bgColor 后景色 # 参数可选项 patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', 'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', 'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', 'lightHorizontal', 'darkTrellis', 'darkVertical'} ws.cell(3,3).fill = PatternFill()
对齐
Alignment(horizontal='fill',vertical='center') # 参数可选项 horizontal = {'fill', 'distributed', 'centerContinuous', 'right', 'justify', 'center', 'left', 'general'} vertical = {'distributed', 'justify', 'center', 'bottom', 'top'} ws.cell(3,3).alignment= Alignment()
import openpyxl from openpyxl.styles import Font, Border, Side, Alignment, Color,PatternFill,colors from openpyxl.worksheet.hyperlink import Hyperlink def readExel(filename = "D:/test.xlsx"): inwb = openpyxl.load_workbook(filename) # 读文件 sheetnames = inwb.get_sheet_names() # 获取读文件中所有的sheet，通过名字的方式 ws = inwb.get_sheet_by_name(sheetnames[0]) # 获取第一个sheet内容 # 获取sheet的最大行数和列数 rows = ws.max_row cols = ws.max_column for r in range(1, rows): for c in range(1, cols): print(ws.cell(r, c).value) def deleteExcelData(filename = "D:/test.xlsx"): workbook = openpyxl.load_workbook(filename) # 读文件 ws = workbook.active # 获取当前活跃的worksheet对象（sheet表） #删除行 ws.delete_rows(2) workbook.save(filename) def writeExcel(saveExcel = "D:/test.xlsx"): workbook = openpyxl.Workbook() # 打开一个将写的文件 sheet0 = workbook.create_sheet(index=0) # 在将写的文件创建sheet # fgColor 前景色 # bgColor 后景色 # 参数可选项 # patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', # 'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', # 'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', # 'lightHorizontal', 'darkTrellis', 'darkVertical'} # sheet1 = outwb.create_sheet(index=1) for row in range(1, 64): for col in range(1, 4): # sheet0.cell(row, col).value = row * 2 # 写文件i # b:是否粗字体，i:是否斜字体 sheet0.cell(row, col).font = Font(name='宋体', size=12, color=Color(index=row), bold=False, italic=False) # 左右，上下对齐设置 sheet0.cell(row, col).alignment= Alignment(horizontal='center',vertical='center') #设置背景色 sheet0.cell(row, col).fill=PatternFill("solid", fgColor=colors.YELLOW) #方式1 #设置超链接 # sheet0.cell(row, col).hyperlink ='https://www.baidu.com/' #文件的相对路径 # sheet0.cell(row, col).hyperlink = './tt.txt' #方式2 sheet0.cell(row, col).value = '=HYPERLINK("{}", "{}")'.format('./tt.txt', str(row * 2 )) # sheet0.cell(row, col).value = '=HYPERLINK("{}", "{}")'.format('https://www.baidu.com/', str(row * 2 )) sheet0.cell(row, col).style = 'Hyperlink' print(row) workbook.save(saveExcel) # 一定要记得保存 workbook.close() #设置单元格的高度和宽度 def setExceCellWidHeight(saveExcel = "D:/test.xlsx"): workbook = openpyxl.Workbook() # 打开一个将写的文件 sheet0 = workbook.create_sheet(index=0) # 在将写的文件创建sheet #设置第一行高度为30 row = sheet0.row_dimensions[1] row.height = 30 #设置第一列宽带为50 # 不能传1，2，3只能用 A，B，C # col = sheet0.column_dimensions[1] col = sheet0.column_dimensions['A'] col.width = 50 # sheet1 = outwb.create_sheet(index=1) for row in range(1, 10): for col in range(1, 4): sheet0.cell(row, col).value = row * 2 # 写文件 # sheet1.cell(row, col).value = row * 2 workbook.save(saveExcel) # 一定要记得保存 workbook.close() if __name__=="__main__": writeExcel() # readExel() # deleteExcelData() # setExceCellWidHeight()
def builtin_styles(path): workbook = openpyxl.Workbook() sheet = workbook.active sheet["A1"].value = "Title" sheet["A1"].style = "Title" sheet["A2"].value = "Headline 1" sheet["A2"].style = "Headline 1" sheet["A3"].value = "Headline 2" sheet["A3"].style = "Headline 2" sheet["A4"].value = "Headline 3" sheet["A4"].style = "Headline 3" sheet["A5"].value = "Headline 4" sheet["A5"].style = "Headline 4" sheet["A6"].value = "Hyperlink" sheet["A6"].style = "Hyperlink" sheet["A7"].value = "Followed Hyperlink" sheet["A7"].style = "Followed Hyperlink" sheet["A8"].value = "Linked Cell" sheet["A8"].style = "Linked Cell" workbook.save(path)
def merge_style(path): workbook = openpyxl.Workbook() sheet = workbook.active sheet.merge_cells("A2:G4") top_left_cell = sheet["A2"] light_purple = "00CC99FF" green = "00008000" thin = Side(border_style="thin", color=light_purple) double = Side(border_style="double", color=green) top_left_cell.value = "Hello world" top_left_cell.border = Border(top=double, left=thin, right=thin,bottom=double) top_left_cell.fill = GradientFill(stop=("000000", "FFFFFF")) top_left_cell.font = Font(b=True, color="FF0000", size=16) top_left_cell.alignment = Alignment(horizontal="center",vertical="center") workbook.save(path)
编辑增加颜色值
