python excel合并单元格处理的方法

workbook = xlrd.open_workbook(path)

if path.split('.xl')[1] == 's':

    workbook = xlrd.open_workbook(path, formatting_info=True)

# 获取sheet

sheet = workbook.sheet_by_index(0)

# 获取列数

r_num = sheet.nrows

# 获取行数

c_num = sheet.ncols

merge = sheet.merged_cells

print(merge)   # [(1, 5, 0, 1), (1, 5, 1, 2)], 对应上面两个合并的单元格

read_data =[]

for r in range(r_num):

    li = []

    for c in range(c_num):

        # 读取每个单元格里的数据，合并单元格只有单元格内的第一行第一列有数据，其余空间都为空

        cell_value = sheet.row_values(r)[c]

        # 判断空数据是否在合并单元格的坐标中，如果在就把数据填充进去

        if cell_value is None or cell_value == '':

            for (rlow, rhigh, clow, chigh) in merge:

                if rlow <= r < rhigh:

                    if clow <= c < chigh:

                        cell_value = sheet.cell_value(rlow, clow)

        li.append(cell_value)

    read_data.append(li)

f = xlwt.Workbook()

sheet1 = f.add_sheet('sheet1', cell_overwrite_ok=True)

headers = ['序号', "姓名", '列表', '1月', '2月', '3月', '4月', '5月']

name = ['1-AAA', '2-BBB', '3-CCC', '4-DDD']

status = ['累计应发数', '累计专项扣除-五险一金', '累计免税收入', '累计扣个税']

# 生成第一行header

for i in range(0,len(headers)):

    sheet1.write(0,i,headers[i])

# 生成序号、姓名单元格

i, j = 1, 0

while i < 10*len(name) and j < len(name):

    sheet1.write_merge(i,i+9,0,0,name[j].split('-')[0])

    sheet1.write_merge(i,i+9,1,1,name[j].split('-')[1])

    i += 10

    j += 1

#生成列表

i = 0

while i < 10*len(column0):

    for j in range(0,len(status)):

        sheet1.write(j+i+1,3,status[j])

    i += 10

# 保存

f.save(path)