前言:
因为涉及业务数据量比较大,想到了自动化处理,所以自己写了一些表格的操作方法批量处理数据。
例如:
获取合并单元格的内容,常见在各个字段菜单下对应的值分类和表头的合并的值;
除此之外,需要对数据遍历对单行或单列的非空值长度获取;
以及,对列名和列号之间的转换更加方便单元格的遍历,比如:B列->第2列;
最后,优化了获取单元格值的方法,可以通过字符串“A1”、数组“(1, 1)”输入获取普通单元格的值或合并单元格的值。
直接上代码
# _*_ coding:utf-8 _*_
from openpyxl.reader.excel import ExcelReader
class ReadXlsxFile(ExcelReader):
"""读xlsx文件"""
def __init__(self, filename, sheet_name=None, read_only=False, data_only=False, keep_links=True):
"""
初始化文件
:param sheet_name: sheet 表名,如:sheet1
:param filename: xlsx文件路径
"""
ExcelReader.__init__(self, fn=filename, read_only=read_only, data_only=data_only, keep_links=keep_links)
self.read()
self.sheet_name = sheet_name
if sheet_name is not None:
self.ws_ = self.wb[self.sheet_name]
def __del__(self):
self.wb.close()
def ws(self, sheet_name=None):
if sheet_name is not None:
self.sheet_name = sheet_name
self.ws_ = self.wb[self.sheet_name]
return self.wb[self.sheet_name]
def get_max_col_len(self):
"""自定义获取表中的最大列的长度,既最大行数"""
col_max_lens = []
for col in self.ws().columns:
cols_value = [f'{cell.value}' for cell in col]
for v in cols_value[::-1]:
if v != 'None' and v.strip() != '':
cols_value.reverse()
col_max_lens.append(len(cols_value) - cols_value.index(v))
break
return max(col_max_lens)
def get_max_row_len(self):
"""自定义获取表中的最大行的长度,既最大列数"""
row_max_lens = []
for row in self.ws().rows:
rows_value = [f'{cell.value}' for cell in row]
for v in rows_value[::-1]:
if v != 'None' and v.strip() != '':
rows_value.reverse()
row_max_lens.append(len(rows_value) - rows_value.index(v))
break
return max(row_max_lens)
def get_col_len(self, column:int, null_value_allow=10):
"""
获取单独一列有值的长度,例如:A列长度是5
:param column: 列名,例如:A、B、C、AA
:param null_value_allow: 获取最大行数的计算逻辑:从“column”列第一个单元格开始读取,如果遇到连续“null_value_allow=10”个空值(包括空格)则减去10
备注:如果中间可能出现空值数大于10时,需做相应调整
:return: int,数字
"""
contNull = 0
row = 0
while True:
row += 1
cell_value = self.ws_.cell(row, column).value
if cell_value is None or f"{cell_value}".strip() == "":
contNull += 1
else:
contNull = 0
if contNull >= null_value_allow:
return row - null_value_allow
def get_row_len(self, row:int, null_value_allow=10):
"""
获取单独一行有值的内容长度
:param row: 行号,例如:1
:param null_value_allow: 获取最大行数的计算逻辑:从“row”行第一个单元格开始读取,如果遇到连续“null_value_allow=10”个空值(包括空格)则减去10
备注:如果中间可能出现空值数大于10时,需做相应调整
:return: int,数字
"""
contNull = 0
column = 0
while True:
column += 1
cell_value = self.ws_.cell(row, column).value
if cell_value is None or f"{cell_value}".strip() == "":
contNull += 1
else:
contNull = 0
if contNull >= null_value_allow:
return column - null_value_allow
def get_colnum(self, letter:str):
"""
通过列名,获取列号,例如:A列=1列,AA列=27列(26进制转10进制) 26 ** (长度 - 1) * 序号
:param letter: 列名,例如:A,BA
:return: int 类型
"""
letterCol = 0
for le in range(1, len(letter)+1):
letterCol += 26 ** (len(letter)-le) * ("ABCDEFGHIJKLMNOPQRSTUVWXYZ".index(letter[le-1].upper()) + 1)
return letterCol
def get_colname(self, num:int):
"""
通过列序号获取列字母,例如:(10进制转26进制)
1列,返回 A,
27列,返回 AA
:param num: int, 数字
:return: str, 字符串
"""
l = []
while True:
num, rem = divmod(num, 26)
l.append('ABCDEFGHIJKLMNOPQRSTUVWXYZ'[rem - 1])
if num == 0:
return ''.join(l[::-1])
def get_cell_value(self, *cell_loc):
"""
读取单元格内容,包括合并的单元格的值
:param cell_loc: 单元格
例如:
字符串格式:"A14"
元组格式:(14, 1)
:return:
"""
if len(cell_loc) == 1:
cell_loc = cell_loc[0]
elif len(cell_loc) == 2:
# 把元组格式单元格转换为字符串单元格
cell_loc = self.ws_.cell(*cell_loc).coordinate
else:
raise ValueError("单元格,格式输入错误,请输入例如:get_cell_value('A2') 或者 get_cell_value(2, 1)")
# 判断单元格是否是合并单元格
if type(self.ws_[cell_loc]).__name__ != "MergedCell":
return self.ws_[cell_loc].value
else:
# 获取所有合并单元格列表
merged_cell_ranges_list = self.ws_.merged_cells.ranges
# 遍历所有合并单元格
for merged_cell in merged_cell_ranges_list:
if cell_loc in merged_cell:
contf = self.ws_[f"{merged_cell}".strip().split(":")[0]].value
return contf
else:
return self.ws_[cell_loc].value
if __name__ == '__main__':
rxf = ReadXlsxFile(sheet_name="Sheet1", file_path=r"C:\Users\Administrator\Desktop\学生信息统计表.xlsx")
print("获取所有的sheet页", rxf.wb.sheetnames)
# 查看其他sheet页内容
rxf.ws('sheet2')
print(rxf.get_cell_value("B3"))
# 查看最大行数、列数,可以指定sheet页,如:rxf.ws('sheet3').max_row
print(rxf.ws().max_row)
print(rxf.ws().max_column)
print("读取单元格值:名读取、序号读取、普通单元格读取、合并单元格读取")
print("*"*100+"\n")
rxf.ws('sheet1')
print(rxf.get_cell_value("B3"))
print(rxf.get_cell_value("B4"))
print(rxf.get_cell_value("B8"))
print(rxf.get_cell_value("B12"))
print("\n"+"*"*100+"\n")
print(rxf.get_cell_value(3, 2))
print(rxf.get_cell_value(13, 2))
print(rxf.get_cell_value(17, 2))
print(rxf.get_cell_value(21, 2))
print("\n"+"*"*100+"\n")
# 读取“D”列有值长度
print("读取“D”列有值长度: ", rxf.get_col_len(rxf.get_colnum("D")))
# 读取“14”行有值长度
print("读取“14”行有值长度: ", rxf.get_row_len(14))
# 把列名转换成列号,例如:AA列->第27列
print("把列名转换成列号,例如:AA列->第27列: ", rxf.get_colnum("AA"))
# 把列号转换成列名,例如:第28列->AB列
print("把列号转换成列名,例如:第28列->AB列: ", rxf.get_colname(28))
演示案例:如下是一张表格,现对以下表格的合并单元格进行读取
运行结果:
EXCEL表格: