python openpyxl设置excel格式

python openpyxl设置excel格式

from openpyxl import Workbook # 新建时导入这个
from openpyxl import load_workbook # 读取时导入这个

from openpyxl.styles import Font, Alignment #设置单元格格式
from openpyxl.utils import get_column_letter, column_index_from_string #根据数字返回字母，根据字母反回列数字
#导入字体、边框、颜色以及对齐方式相关库

from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment #font字体，border边框，side边,patternfil填充，alignment对齐

wb=Workbook() #新建表

wbsheet=wb[weeklist] 打开表单
wbsheet = wb.create_sheet(weeklist,0) #创建表单

批量设置置中，字体
for i in range(7):
wbsheet[get_column_letter(i7+1)+“1”].alignment = Alignment(horizontal=‘center’, vertical=‘center’)
wbsheet[get_column_letter(i7+1)+“1”].font = Font(name=‘微软雅黑’, size=20, bold=True, italic=False, color=colors.BLACK) #bold加粗，italic斜体

设置填充效果
fill = PatternFill(‘solid’, fgColor=‘FFA500’)
wbsheet[‘A1’].fill=fill

设置某列宽度
wbsheet.column_dimensions[get_column_letter(num1*7+5)].width = 11.0

设置某行高度
wbsheet.row_dimensions[‘1’].height = 10

设置某单元格自动换行
wbsheet[‘G1’].alignment = Alignment(wrapText=True)

设置单元格合并
wbsheet.merge_cells(‘A1:F1’)

openpyxl自己测试还不能取范围设置，还请指教

这是批量选中单元格，然后设置边框的实例

    cell_range=wbsheet[get_column_letter(num1*7+1)+"1:"+get_column_letter(num1*7+6)+str(row-1)]
    border = Border(left=Side(border_style='hair',color='000000'),
                    right=Side(border_style='hair',color='000000'),
                    top=Side(border_style='hair',color='000000'),
                    bottom=Side(border_style='hair',color='000000'))
    for cellnum in cell_range:  #返回一个二维元组，一个元组是一行
        for cellnum1 in cellnum:
            cellnum1.border=border