python openpyxl模块合并单元格，设置行高，列宽，边框，居中，字体样式

kyiiiii男友

已于 2022-12-05 08:54:00 修改

阅读量1w

点赞数 2

文章标签： python excel

于 2022-02-23 15:11:35 首次发布

本文链接：https://blog.csdn.net/Kyiiiii/article/details/123090587

版权

from openpyxl.styles import Border,Side,Alignment,Font
from openpyxl import load_workbook,Workbook
# 居中样式
align = Alignment(horizontal='center',vertical='center',wrap_text=True)
# 边框样式
border = Border(left=Side(border_style='thin'),
    right=Side(border_style='thin'),
    top=Side(border_style='thin'),
    bottom=Side(border_style='thin'))

wb = Workbook()

# temp_names = wb.sheetnames
# sheet1 = wb.get_sheet_by_name(temp_names[3])

sheet1 = wb.active
# 合并单元格A1-K1
sheet1.merge_cells('A1:K1')
sheet1['A1'] = '记录表'
# 设置font 字体
font_title = Font(u'微软雅黑',size=18)
# 引用font字体
sheet1['A1'].font = font_title
# 引用居中样式
sheet1['A1'].alignment = align
# 设置行高 第一行 40
sheet1.row_dimensions[1].height = 40

sheet1.merge_cells('A2:B2')
sheet1['A2'] = '123:'
sheet1['A2'].alignment = align
sheet1.merge_cells('C2:K2')
# 设置第二行行高25
sheet1.row_dimensions[2].height = 25

sheet1['A3'] = '1:'
sheet1['A3'].alignment = align
sheet1.merge_cells('B3:E3')
sheet1['F3'] = '2:'
sheet1['F3'].alignment = align
sheet1.merge_cells('G3:K3')
sheet1.row_dimensions[3].height = 25

sheet1['A4'] = '3:'
sheet1['A4'].alignment = align
sheet1.merge_cells('B4:E4')

sheet1['F4'] = '4:'
sheet1['F4'].alignment = align
sheet1.merge_cells('G4:K4')
sheet1.row_dimensions[4].height = 25

# 设置列宽 A20 F10 E10
sheet1.column_dimensions['A'].width = 20
sheet1.column_dimensions['F'].width = 10
sheet1.column_dimensions['E'].width = 10

title_list = [1,2,3,4,5,6,7,8,9,10,11]
sheet1.row_dimensions[5].height = 25

for row in sheet1.iter_rows(min_row=5,max_row=5,max_col=11):
    i = 0
    for cell in row:
        cell.value = title_list[i]
        cell.alignment = align
        cell.border = border
        i += 1

list = [1,2,3,4,5,6,7,8,9,10,11]
sheet1.insert_rows(6)
for row in sheet1.iter_rows(min_row=6, max_row=6, max_col=11):
    i = 0
    for cell in row:
        cell.value = list[i]
        cell.alignment = align
        cell.border = border
        i += 1
# 保存
wb.save(r'C:\Users\Desktop\Temp\test_openpyxl.xlsx')

minrow = sheet1.min_row  # 最小行
maxrow = sheet1.max_row  # 最大行
mincol = sheet1.min_column  # 最小列
maxcol = sheet1.max_column  # 最大列


totallist = []
for row in sheet1.iter_rows(min_row=minrow, max_row=maxrow, max_col=maxcol):
    sublist = []
    for cell in row:
        sublist.append(cell.value)
    totallist.append(sublist)

print(totallist)