使用openpyxl实现
只支持xlsx文件,不支持xls
import openpyxl
def read_cell(io, sheet, cell='A2'):
"""
读取单元格
:param io: Excel文件路径
:param sheet: 读取哪一张表,str, int eg: 'Sheet1' or 0
:param cell: 读取哪一个单元格
:return: value --> str
"""
excel = openpyxl.load_workbook(io)
if isinstance(sheet, str):
ws = excel[sheet]
elif isinstance(sheet, int):
name = excel.sheetnames[sheet]
ws = excel[name]
else:
raise TypeError('sheet must be int or str, not %s' % type(sheet))
val = ws[cell].value
if val:
return val
return ''
使用xlrd实现
只支持xls(version > 2.x),既支持xls又支持xlsx(version < 2.x)
import xlrd
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
def read_cell(io, sheet, cell='A1'):
"""
读取单元格
:param io: Excel文件路径
:param sheet: 读取哪一张表,str, int eg: 'Sheet1' or 0
:param cell: 读取哪一个单元格
:return: value --> str
"""
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, end_rowx=int(pos_row))
if value:
return value[0]
return ''