sumList = []
for i in range(2, sheet.max_row):
value = sheet.cell(row=i, column=1).value
if value:
sumList.append(value)
else:
break
如果你要合并并居中的内容不在A列只要更改“column=”中的内容即可。
2. 进行合并单元格操作
preRow = 0
finRow = 0
flag = sumList[0]
for i in range(len(sumList)):
if sumList[i] != flag:
flag = sumList[i]
finRow = i - 1
if finRow >= preRow:
sheet.merge_cells("A{}:A{}".format(preRow+2, finRow+2))
preRow = finRow + 1
if i == len(sumList) - 1:
finRow = i
sheet.merge_cells("A{}:A{}".format(preRow+2, finRow+2))
wsArea = sheet["A{}:A{}".format(2, 2+len(sumList))]
for i in wsArea:
for j in i:
j.alignment = Alignment(horizontal='center', vertical='center')
本文全部代码
import os
import openpyxl
from openpyxl.styles import Alignment
# 获得当前py文件的路径
path = os.getcwd()
# 打开py文件目录下的名为test.xlsx文件的第一张sheet页
wb = openpyxl.load_workbook(path + "/test.xlsx")
sheet = wb["Sheet1"]
# 先把A列的所有内容放入一个List中
sumList = []
for i in range(2, sheet.max_row):
value = sheet.cell(row=i, column=1).value
if value:
sumList.append(value)
else:
break
# 开始合并单元格
preRow = 0
finRow = 0
flag = sumList[0]
for i in range(len(sumList)):
if sumList[i] != flag:
flag = sumList[i]
finRow = i - 1
if finRow >= preRow:
sheet.merge_cells("A{}:A{}".format(preRow+2, finRow+2))
preRow = finRow + 1
if i == len(sumList) - 1:
finRow = i
sheet.merge_cells("A{}:A{}".format(preRow+2, finRow+2))
# 居中单元格
wsArea = sheet["A{}:A{}".format(2, 2+len(sumList))]
for i in wsArea:
for j in i:
j.alignment = Alignment(horizontal='center', vertical='center')
# 保存表
wb.save(path + '/test1.xlsx')
----------------------->
