openpyl 设置单元格格式
1、设置超链接
# 设置超链接 sheet.cell(2,2).hyperlink = "www.baidu.com"
2.cell的其它属性
# cell 的其它属性 sheet.cell(row = 1,column = 2).column_letter sheet.cell(row = 1,column = 2).coordinate sheet.cell(row = 1,column = 2).col_idx sheet.cell(row = 1,column = 2).encoding sheet.cell(row = 1,column = 2).offset sheet.cell(row = 1,column = 2).is_date sheet.cell(row = 1,column = 2).data_type sheet.cell(row = 1,column = 2).has_style sheet.cell(row = 1,column = 2).style sheet.cell(row = 1,column = 2).style_id sheet.cell(row = 1,column = 2).font sheet.cell(row = 1,column = 2).alignment sheet.cell(row = 1,column = 2).border sheet.cell(row = 1,column = 2).fill sheet.cell(row = 1,column = 2).number_format sheet.cell(row = 1,column = 2).hyperlink
3、设置单元格的宽度和高度
# 调整列宽 sheet.column_dimensions['A'].width = 20 # 调整行高 sheet.row_dimensions[1].height = 49
# 自适应的单元格。 def adjust_column_dimension(sheet, min_row, min_col, max_col): column_widths = [] for i, col in enumerate(sheet.iter_cols(min_col=min_col, max_col=max_col, min_row=min_row)): for cell in col: value = cell.value if value is not None: if isinstance(value, str) is False: value = str(value) try: column_widths[i] = max(column_widths[i], len(value)) except IndexError: column_widths.append(len(value)) for i, width in enumerate(column_widths): col_name = get_column_letter(min_col + i) value = column_widths[i] + 2 sheet.column_dimensions[col_name].width = value adjust_column_dimension(sheet, 1,1, sheet.max_column)
4、合并单元格
# 合并单元格 sheet.merge_cells("A1:B1") sheet.merge_cells(start_column = 3, end_column = 5, start_row = 3, end_row = 5) print(sheet.merged_cells) print(sheet.merges_cell_ranges) # 合并单元格所组成的列表
5、设置字体样式
# Font 字体设置 from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side wb = load_workbook("工资表.xlsx") ws = wb['Sheet1'] font = Font(name='微软雅黑', # 字体名称 size = 11, # 字体大小 color = "FF000000", # 字体颜色 bold = True, # 加粗 italic = True, # 斜体 vertAlign = 'baseline', # 垂直对齐方式 underline = 'double', # 下划线 strike = False) # 删除线 ws['A2'].font = font wb.save("new_test.xlsx") wb.close()
6、Border和Side 边框设置
# 设置Border 和 Side from openpyxl import load_workbook from openpyxl.styles import PatternFill, Font, Alignment, Border, Side wb = load_workbook('工资表.xlsx') ws = wb['Sheet1'] side_type = Side(border_style = 'mediumDashDot',color = 'FF000000') # border_style 的取值范围：‘dashDot’,'dashDotDot','dashed','dotted','double','hair','medium','mediumDashDot','mediumDashDotDot','mediumDashed','slantDashDot','thick','thin' border = Border(left = side_type, right = side_type, top = side_type, bottom = side_type, diagonal_direction = 30, outline = side_type, vertical = side_type, horizontal = side_type) ws['A3'].border = border wb.save("new_test.xlsx") wb.close()
7、PatternFill 填充单元格设置
# PatternFill 单元格填充样式设置 from openpyxl import* from openpyxl.styles import PatternFill, Font, Alignment, Border, Side wb = load_workbook("工资表.xlsx") ws = wb['Sheet1'] fill = PatternFill(fill_type = 'darkDown',start_color='A6DA70D6',end_color='000000') ws['A4'].fill = fill wb.save("new_test.xlsx") wb.close() """ start_color 前景色，十六进制aRGB,a表示透明度，纯色填充，可以使用fill_type = solid,使用前景色。 十六进制 颜色说明 https://www.sioe.cn/yingyong/yanse-rgb-16/ 透明度设置 100%：FF 95%：F2 90%: E6 85%: D9 80%: CC 75%: BF 70%: B3 65%: A6 60%: 99 55%: 8C 50%: 80 45%: 73 40%: 66 35%: 59 30%: 4D 25%: 40 20%: 33 15%: 26 10%: 1A 5%: 0D 0%: 00 fill_type 填充类型 当fill_type为None时，以下参数不生效。 FILL_NONE = 'None' FILL_SOLID = 'solid' FILL_PATTERN_DARKDOWN = 'darkdown' FILL_PATTERN_DARKGRAY = 'darkGray' FILL_PATTERN_DARKGRID = 'darkGrid' FILL_PATTERN_DARKHORIZONTAL = 'darkHorizontal' FILL_PATTERN_DARKTRELLIS = 'darkTrellis' FILL_PATTERN_DARKUP = 'darkUp' FILL_PATTERN_DARKVERTICAL = 'darkVertical' FILL_PATTERN_GRAY0625 = 'gray0625' FILL_PATTERN_GRAY125 = 'gray125' FILL_PATTERN_LIGHTDOWN = 'lightDown' FILL_PATTERN_LIGHTGRAY = 'lightGray' FILL_PATTERN_LIGHTGRID = 'lightGrid' FILL_PATTERN_LIGHTHORIZONTAL = 'lightHorizontal' FILL_PATTERN_LIGHTTRELLIS = 'lightTrellis' FILL_PATTERN_LIGHTUP = 'lightUp' FILL_PATTERN_LIGHTVERTICAL = 'lightVertical' FILL_PATTERN_MEDIUMGRAY = 'mediumGray' """
8、Alignment 对齐设置
# Alignment 对齐设置 from openpyxl import* from openpyxl.styles import PatternFill, Font, Alignment, Border, Side wb = load_workbook("工资表.xlsx") ws = wb['Sheet1'] align = Alignment(horizontal='center', # 水平对齐，left,center,right vertical='center', # 垂直对齐，center，top,bottom text_rotation=0, # 文本旋转度 wrap_text=True, # 自动换行 shrink_to_fit=True, indent=0 # 缩进 ) ws['A6'].alignment = align wb.save("new_test.xlsx") wb.close()
9、数字格式设置
# 数字格式设置 """单元格的数字格式也是常见的，大家可以在openpyxl中的numbers.py文件中找到数字的格式，这里贴一些：""" BUILTIN_FORMATS = { 0: 'General', 1: '0', 2: '0.00', 3: '#,##0', 4: '#,##0.00', 5: '"$"#,##0_);("$"#,##0)', 6: '"$"#,##0_);[Red]("$"#,##0)', 7: '"$"#,##0.00_);("$"#,##0.00)', 8: '"$"#,##0.00_);[Red]("$"#,##0.00)', 9: '0%', 10: '0.00%' } from openpyxl import* from openpyxl.styles import PatternFill, Font, Alignment, Border, Side wb = load_workbook("工资表.xlsx") ws = wb['Sheet1'] ws['A9'].number_format = 'd-mmm-yy' wb.save("new_test.xlsx") wb.close()
10、复制单元格属性
from openpyxl.styles import Font fsheet1 = Font(name='Arial', size=10) # 复制时指定字体为“微软雅黑”，其他属性来自fsheet1 fsheet2 = fsheet1.copy(name="微软雅黑")
