使用Python读取/解析Excel (xls)文件
使用Python读取/解析Excel (xls)文件
提问于 2010-05-31 18:28:31
用Python读取Excel (XLS)文件(而不是CSV文件)的最佳方式是什么？
有没有Python默认支持的内置包来完成这项任务？
回答 11
Stack Overflow用户
发布于 2017-05-23 12:04:41
使用pandas：
import pandas as pd
xls = pd.ExcelFile(r"yourfilename.xls") #use r before absolute file path
sheetX = xls.parse(2) #2 is the sheet number+1 thus if the file has only 1 sheet write 0 in paranthesis
var1 = sheetX['ColumnName']
print(var1[1]) #1 is the row number...Stack Overflow用户
发布于 2017-04-06 22:15:45
您可以选择其中的任何一个http://www.python-excel.org/
我推荐使用python xlrd库。
使用以下命令安装它
pip install xlrd导入使用
import xlrd要打开工作簿，请执行以下操作
workbook = xlrd.open_workbook('your_file_name.xlsx')按名称打开工作表
worksheet = workbook.sheet_by_name('Name of the Sheet')按索引打开工作表
worksheet = workbook.sheet_by_index(0)读取单元格值
worksheet.cell(0, 0).value Stack Overflow用户
发布于 2018-10-28 19:53:40
对于xlsx，我喜欢之前发布的解决方案https://web.archive.org/web/20180216070531/https://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python。我只使用标准库中的模块。
def xlsx(fname):
import zipfile
from xml.etree.ElementTree import iterparse
z = zipfile.ZipFile(fname)
strings = [el.text for e, el in iterparse(z.open('xl/sharedStrings.xml')) if el.tag.endswith('}t')]
rows = []
row = {}
value = ''
for e, el in iterparse(z.open('xl/worksheets/sheet1.xml')):
if el.tag.endswith('}v'): # Example: <v>84</v>
value = el.text
if el.tag.endswith('}c'): # Example: <c r="A3" t="s"><v>84</v></c>
if el.attrib.get('t') == 's':
value = strings[int(value)]
letter = el.attrib['r'] # Example: AZ22
while letter[-1].isdigit():
letter = letter[:-1]
row[letter] = value
value = ''
if el.tag.endswith('}row'):
rows.append(row)
row = {}
return rows添加的改进是通过工作表名称获取内容，使用re获取列，并检查是否使用了sharedstring。
def xlsx(fname,sheet):
import zipfile
from xml.etree.ElementTree import iterparse
import re
z = zipfile.ZipFile(fname)
if 'xl/sharedStrings.xml' in z.namelist():
# Get shared strings
strings = [element.text for event, element
in iterparse(z.open('xl/sharedStrings.xml'))
if element.tag.endswith('}t')]
sheetdict = { element.attrib['name']:element.attrib['sheetId'] for event,element in iterparse(z.open('xl/workbook.xml'))
if element.tag.endswith('}sheet') }
rows = []
row = {}
value = ''
if sheet in sheets:
sheetfile = 'xl/worksheets/sheet'+sheets[sheet]+'.xml'
#print(sheet,sheetfile)
for event, element in iterparse(z.open(sheetfile)):
# get value or index to shared strings
if element.tag.endswith('}v') or element.tag.endswith('}t'):
value = element.text
# If value is a shared string, use value as an index
if element.tag.endswith('}c'):
if element.attrib.get('t') == 's':
value = strings[int(value)]
# split the row/col information so that the row leter(s) can be separate
letter = re.sub('\d','',element.attrib['r'])
row[letter] = value
value = ''
if element.tag.endswith('}row'):
rows.append(row)
row = {}
return rows页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/2942889
复制相关文章
相似问题