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)
python openpyxl模块 合并单元格,设置行高,列宽,边框,居中,字体样式
于 2022-02-23 15:11:35 首次发布