单元格拆分
def get_index(capital):
"""
大写字母(Excel列头)转数字
:param capital: 'A' --> 0, 'AA' --> 26
:return: int
"""
number = 0
capital = capital.upper()
for char in capital:
number = number * 26 + ord(char) - ord('A') + 1
return number - 1
需注意xlrd的版本,只支持xls(version > 2.x),既支持xls又支持xlsx(version < 2.x)
读取整列(xlrd)
import xlrd
def read_col(io, sheet, cell='A1'):
"""
读取列
:param io: Excel文件路径
:param sheet: 读取哪一张表,str, int eg: 'Sheet1' or 0
:param cell: 从哪一个单元格开始读取
:return: value --> list
"""
wb = xlrd.open_workbook(io)
if isinstance(sheet, str):
ws = wb.sheet_by_name(sheet)
elif isinstance(sheet, int):
ws = wb.sheet_by_index(sheet)
else:
raise TypeError('sheet must be int or str, not %s' % type(sheet))
pos_col, pos_row = re.findall('[A-Z]+|[0-9]+', cell)
col_index = get_index(pos_col)
value = ws.col_values(col_index, start_rowx=int(pos_row) - 1)
if value:
return value
return ''
读取整列(xlwings)
import xlwings as xw
def read_col(io, sheet=0, cell='A1') -> list:
"""
读取Excel表格的某一列
:param io: 文件
:param sheet: 工作表
:param cell: 单元格,如:A1
:return: list[str]
"""
assert isinstance(sheet, (str, int)), "sheet 必须为str或int类型"
app = xw.App(visible=False) # 后台运行
workbook = xw.Book(io) # 打开Excel文件
# workbook = app.books.open(io) # WPS use
# 读取Excel表格的某一列
if isinstance(sheet, str):
data = workbook.sheets(sheet).range(cell).expand().value
else:
data = workbook.sheets[sheet].range(cell).expand().value
# 关闭Excel文件
workbook.close()
app.quit()
return data or []
读取整行
def read_row(io, sheet, cell='A1'):
"""
读取行
:param io: Excel文件路径
:param sheet: 读取哪一张表,str, int eg: 'Sheet1' or 0
:param cell: 从哪一个单元格开始读取
:return: value --> list
"""
wb = xlrd.open_workbook(io)
if isinstance(sheet, str):
ws = wb.sheet_by_name(sheet)
elif isinstance(sheet, int):
ws = wb.sheet_by_index(sheet)
else:
raise TypeError('sheet must be int or str, not %s' % type(sheet))
pos_col, pos_row = re.findall('[A-Z]+|[0-9]+', cell)
col_index = get_index(pos_col)
value = ws.row_values(col_index, start_colx=int(pos_row) - 1)
if value:
return value
return ''