openpyxl处理.xlsx合并单元格数据
一、之前学过xlrd处理.xls格式的excel数据,想着excel使用.xlsx格式比较多,所以用openpyxl做一个处理.xlsx格式的excel数据(仅供参考)
二、思路:(我这里只展示一个表单的情况)
1.读取excel文件
2.获取表单里面单元格数据(包含普通单元格、合并单元格)
3.将单元格数据用字典形式存在列表中
三、实践
1.读取excel文件:
1.1、首先下载第三方库openpyxl,进入doc使用命令pip install openpyxl
1.2、代码中引用openpyxl
import openpyxl
1.3、读取excel文件,定义文件对象
data = openpyxl.load_workbook("压测手机号.xlsx")
1.4、上面定义文件对象后就相当于进入文件了,进了文件后我们要知道文件里的表单,所以定义文件的表单对象
sheet = data['Sheet1'] # Sheet1为表单名
1.5、定义了表单对象后,就可以进行下一步获取表单中单元格内容了
2.获取表单里面单元格数据
2.1、获取指定单元格数据(row表示行,column表示列):
a = sheet.cell(row=3, column=1).value
print(a)
2.2、单元格有合并单元格的情况,所以需要能够获取合并单元格的数据
# 获取合并单元格需要用到下面几个
print(sheet.max_row) # TODO 获取最大行数
print(sheet.min_row) # TODO 获取最小行数
print(sheet.max_column) # TODO 获取最大列数
print(sheet.min_column) # TODO 获取最小列数
openpyxl中使用merged_cells可以查询出所有的合并单元格行和列,得到的是下面的A2:A3 A5:A6 B5:B6 C5:C6
merge_lists = sheet.merged_cells
A2:A3 A5:A6 B5:B6 C5:C6
然后处理合并单元格,得到合并单元格的行列坐标,我这里做了个方法,整体代码如下
def merge():
# TODO 查询该sheet表单所有合并单元格
merge_lists = sheet.merged_cells
print(merge_lists)
merge_all_list = [] # 接收最终内容并返回
# TODO 遍历合并单元格
for merge_list in merge_lists:
# TODO 获取单个合并单元格的起始行(row_min)终止行(row_max)和起始列(col_min)终止列(col_max)
row_min, row_max, col_min, col_max = merge_list.min_row, merge_list.max_row, merge_list.min_col, merge_list.max_col
# 这里判断如果合并单元格起始、终止的行和列都不相等,说明合并单元格既合并了行又合并了列,两个for循环依次取出行列位置分别存在x,y中
if (row_min != row_max and col_min != col_max):
row_col = [(x, y) for x in range(row_min, row_max+1) for y in range(col_min, col_max+1)]
merge_all_list.append(row_col) # 取出的值存在列表中
# 这里判断如果合并单元格起始、终止行相等,起始、终止列不相等,说明合并单元格只合并了列,所以行不动,只循环取出列的值,存在y中,行可以随意取row_min/row_max
elif (row_min==row_max and col_min != col_max):
row_col = [(row_min, y) for y in range(col_min, col_max + 1)]
merge_all_list.append(row_col) # 取出的值存在列表中
# 这里判断如果合并单元格起始、终止行不相等,起始、终止列相等,说明合并单元格只合并了行,所以列不动,只循环取出行的值,存在x中,列可以随意取col_min/col_max
elif (row_min != row_max and col_min == col_max):
row_col = [(x, col_min) for x in range(row_min, row_max + 1)]
merge_all_list.append(row_col) # 取出的值存在列表中
return merge_all_list # 最终返回列表
# TODO 得到的是个这样的列表值:[[(2, 1), (3, 1)], [(5, 1), (6, 1)], [(5, 2), (6, 2)], [(5, 3), (6, 3)]],列表中每个列表表示合并单元格的跨度
使用上面方法得到合并单元格的行列后,就可以使用行列坐标取出合并单元格数据了,这样普通单元格、合并单元格数据都可以得到了,也做了个方法,代码如下
def merge_values(*rr): # 这里方法传入一个元组入参,这个*叫啥入参来着,忘记了,可以去查查基础知识
mm_list = merge() # 这里调用上面的获取合并单元格的方法
# 循环取出合并单元格方法得到的值(这个值还是列表),检查传入的参数是不是在这些值里面
for ii in range(0, len(mm_list)):
if rr in mm_list[ii]: # 在,那就取值里面的第一个值(即合并单元格左上角的第一个行列坐标);
value11 = sheet.cell(row=mm_list[ii][0][0], column=mm_list[ii][0][1]).value
return value11
else: # 不在,说明不是合并单元格,使用普通单元格方法获取即可
value2 = sheet.cell(*rr).value
# 这个判断可要可不要,主要用来提示单元格为空,后期可以用log打印
# if value2 is None:
# return "None:单元格无数据"
# else:
return value2
将取到的表单数据用列表包字典的形式展示,并且都用第一行的标题做为key,这样后期可以方便取值
def list_excel_data():
list_val = []
for x in range(sheet.min_row, sheet.max_row+1):
if x != 1:
dict_val = {}
for y in range(sheet.min_column, sheet.max_column+1):
kk = (1, y)
i = (x, y)
dict_val[merge_values(*kk)] = merge_values(*i)
list_val.append(dict_val)
return list_val
最终展示效果:
[{'phone': '17601348350', 'name': '张三', 'sex': 22}, {'phone': '17601348350', 'name': '李四', 'sex': 33}, {'phone': '17601348352', 'name': '王五', 'sex': 40}, {'phone': '17601348358', 'name': '赵六', 'sex': '9'}, {'phone': '17601348358', 'name': '赵六', 'sex': '9'}, {'phone': '17601348360', 'name': '钱七', 'sex': 18}]
Process finished with exit code 0
全部代码如下:(没有注释了)
import openpyxl
data = openpyxl.load_workbook("压测手机号.xlsx")
sheet = data['Sheet1']
# a = sheet.cell(row=3, column=1).value # TODO 获取指定行、列的值
# print(sheet.title) # TODO 获取sheet名称
# print(sheet.max_row) # TODO 获取最大行数
# print(sheet.min_row) # TODO 获取最小行数
# print(sheet.max_column) # TODO 获取最大列数
# print(sheet.min_column) # TODO 获取最小列数
def merge():
# TODO 查询该sheet表单所有合并单元格
merge_lists = sheet.merged_cells
# print(merge_lists)
merge_all_list = []
# TODO 遍历合并单元格
for merge_list in merge_lists:
# TODO 获取单个合并单元格的起始行(row)和起始列(col)
row_min, row_max, col_min, col_max = merge_list.min_row, merge_list.max_row, merge_list.min_col, merge_list.max_col
if (row_min != row_max and col_min != col_max):
row_col = [(x, y) for x in range(row_min, row_max+1) for y in range(col_min, col_max+1)]
merge_all_list.append(row_col)
elif (row_min==row_max and col_min != col_max):
row_col = [(row_min, y) for y in range(col_min, col_max + 1)]
merge_all_list.append(row_col)
elif (row_min != row_max and col_min == col_max):
row_col = [(x, col_min) for x in range(row_min, row_max + 1)]
merge_all_list.append(row_col)
return merge_all_list
# TODO 得到的是个这样的列表值:[[(2, 1), (3, 1)], [(10, 1), (10, 2), (10, 3), (11, 1), (11, 2), (11, 3)]]
def merge_values(*rr):
mm_list = merge()
for ii in range(0, len(mm_list)):
if rr in mm_list[ii]:
value11 = sheet.cell(row=mm_list[ii][0][0], column=mm_list[ii][0][1]).value
return value11
else:
value2 = sheet.cell(*rr).value
# if value2 is None:
# return "None:单元格无数据"
# else:
return value2
def list_excel_data():
list_val = []
for x in range(sheet.min_row, sheet.max_row+1):
if x != 1:
dict_val = {}
for y in range(sheet.min_column, sheet.max_column+1):
kk = (1, y)
i = (x, y)
dict_val[merge_values(*kk)] = merge_values(*i)
list_val.append(dict_val)
return list_val
bb = list_excel_data()
print(bb)
# print(bb[0].get('name'))
data.close()