python读取包含层级关系的excel

1.场景描述

使用python读取包含层级关系excel，数据示例如下图所示。

2.代码

import xlrd
def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook(r'2018年全国省市区列表.xlsx')
    print('所有sheet名称:', workbook.sheet_names())
    sheet2 = workbook.sheet_by_index(0)  # sheet索引从0开始

    rows_num = sheet2.nrows # 获取所有行数
    cols_num = sheet2.ncols # 获取所有列数
    province_city_list = []
    for r in range(1,rows_num):#行索引的取值范围
        entity_dict = {}
        for c in range(cols_num):
            cell_value = sheet2.row_values(r)[c]
            # print('第%d行第%d列的值：[%s]' % (r, c, sheet2.row_values(r)[c]))
            if (cell_value is None or cell_value == ''):
                cell_value = (get_merged_cells_value(sheet2, r, c))
            # 设置各属性值
            if c==0:
                if (cell_value is None or cell_value == ''):
                    entity_dict["province"] = ''
                else:
                    entity_dict["province"] = cell_value
            elif c==1:
                if (cell_value is None or cell_value == ''):
                    entity_dict["city"] = ''
                else:
                    entity_dict["city"] = cell_value
            elif c==2:
                if (cell_value is None or cell_value == ''):
                    entity_dict["county"] = ''
                else:
                    entity_dict["county"] = cell_value
            elif c==3:
                if (cell_value is None or cell_value == ''):
                    entity_dict["station"] = ''
                else:
                    entity_dict["station"] = cell_value
        print(entity_dict)
        province_city_list.append(entity_dict)
    return province_city_list
def get_merged_cells(sheet):
    """
    获取所有的合并单元格，格式如下：
    [(4, 5, 2, 4), (5, 6, 2, 4), (1, 4, 3, 4)]
    (4, 5, 2, 4) 的含义为：行 从下标4开始，到下标5（不包含）  列 从下标2开始，到下标4（不包含），为合并单元格
    :param sheet:
    :return:
    """
    return sheet.merged_cells
def get_merged_cells_value(sheet, row_index, col_index):
    """
    先判断给定的单元格，是否属于合并单元格；
    如果是合并单元格，就返回合并单元格的内容
    :return:
    """
    merged = get_merged_cells(sheet)
    for (rlow, rhigh, clow, chigh) in merged:
        if (row_index >= rlow and row_index < rhigh):
            if (col_index >= clow and col_index < chigh):
                cell_value = sheet.cell_value(rlow, clow)
                # print('该单元格[%d,%d]属于合并单元格，值为[%s]' % (row_index, col_index, cell_value))
                return cell_value
                break
    return None
if __name__ == "__main__":
    province_city_list = read_excel()
    for line in province_city_list:
        print(line)

打印输出：

{'province': '北京市', 'city': '北京市', 'county': '东城区', 'station': '景山街道'}
{'province': '北京市', 'city': '北京市', 'county': '西城区', 'station': '金融街街道'}
{'province': '北京市', 'city': '北京市', 'county': '朝阳区', 'station': '朝外街道'}

...

转载于：https://www.cnblogs.com/cupleo/p/13815125.html