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': '朝外街道'}
...