from openpyxl.styles import Border,Side,Alignment,Fontfrom 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-K1sheet1.merge_cells('A1:K1')sheet1['A1'] = '记录表'# 设置font 字体font_title = Font(u'微软雅黑',size=18)# 引用font字体sheet1['A1'].font = font_title# 引用居中样式sheet1['A1'].alignment = align# 设置行高 第一行 40sheet1.row_dimensions[1].height = 40sheet1.merge_cells('A2:B2')sheet1['A2'] = '123:'sheet1['A2'].alignment = alignsheet1.merge_cells('C2:K2')# 设置第二行行高25sheet1.row_dimensions[2].height = 25sheet1['A3'] = '1:'sheet1['A3'].alignment = alignsheet1.merge_cells('B3:E3')sheet1['F3'] = '2:'sheet1['F3'].alignment = alignsheet1.merge_cells('G3:K3')sheet1.row_dimensions[3].height = 25sheet1['A4'] = '3:'sheet1['A4'].alignment = alignsheet1.merge_cells('B4:E4')sheet1['F4'] = '4:'sheet1['F4'].alignment = alignsheet1.merge_cells('G4:K4')sheet1.row_dimensions[4].height = 25# 设置列宽 A20 F10 E10sheet1.column_dimensions['A'].width = 20sheet1.column_dimensions['F'].width = 10sheet1.column_dimensions['E'].width = 10title_list = [1,2,3,4,5,6,7,8,9,10,11]sheet1.row_dimensions[5].height = 25for row in sheet1.iter_rows(min_row=5,max_row=5,max_col=11):i = 0for cell in row:cell.value = title_list[i]cell.alignment = aligncell.border = borderi += 1list = [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 = 0for cell in row:cell.value = list[i]cell.alignment = aligncell.border = borderi += 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)
