知识问答

使用Python读取/解析Excel（xls）文件

内容 隐藏
问题：使用Python读取/解析Excel（xls）文件
回答 0
回答 1
回答 2
回答 3
回答 4
回答 5
回答 6
回答 7
回答 8
回答 9
回答 10

问题：使用Python读取/解析Excel（xls）文件

用Python读取Excel（XLS）文件（而不是CSV文件）的最佳方法是什么。

有默认情况下Python支持的内置程序包吗？

点击查看英文原文

What is the best way to read Excel (XLS) files with Python (not CSV files).

Is there a built-in package which is supported by default in Python to do this task?

回答 0

我强烈建议使用xlrd读取.xls文件。

voyager提到了COM自动化的使用。几年前自己做过此事，请注意做这是真正的PITA。需要警告的数量巨大，并且缺少文档并且令人讨厌。我遇到了许多奇怪的错误和陷阱，其中一些花费了许多小时才能弄清楚。

更新：对于较新的.xlsx文件，推荐用于读写的库似乎是openpyxl（感谢IkarPohorský）。

点击查看英文原文

I highly recommend xlrd for reading .xls files.

voyager mentioned the use of COM automation. Having done this myself a few years ago, be warned that doing this is a real PITA. The number of caveats is huge and the documentation is lacking and annoying. I ran into many weird bugs and gotchas, some of which took many hours to figure out.

UPDATE: For newer .xlsx files, the recommended library for reading and writing appears to be openpyxl (thanks, Ikar Pohorský).

回答 1

使用熊猫：

import pandas as pd

xls = pd.ExcelFile("yourfilename.xls")

sheetX = xls.parse(2) #2 is the sheet number

var1 = sheetX['ColumnName']

print(var1[1]) #1 is the row number...
点击查看英文原文

Using 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...

回答 2

您可以选择其中任意一个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
点击查看英文原文

You can choose any one of them http://www.python-excel.org/
I would recommended python xlrd library.

install it using 

pip install xlrd

import using 

import xlrd

to open a workbook 

workbook = xlrd.open_workbook('your_file_name.xlsx')

open sheet by name

worksheet = workbook.sheet_by_name('Name of the Sheet')

open sheet by index

worksheet = workbook.sheet_by_index(0)

read cell value 

worksheet.cell(0, 0).value

回答 3

我认为熊猫是最好的选择。已经有一个答案在这里使用与熊猫ExcelFile的功能，但它并没有为我正常工作。从这里我发现read_excel可以正常工作的函数：

import pandas as pd
dfs = pd.read_excel("your_file_name.xlsx", sheet_name="your_sheet_name")
print(dfs.head(10))

PS您需要为xlrd安装read_excel正常工作

更新21-03-2020：正如您可能在此处看到的那样，xlrd引擎存在问题，它将不推荐使用。该openpyxl是最好的替代品。因此，作为描述在这里，规范的语法应为：

dfs = pd.read_excel("your_file_name.xlsx", sheet_name="your_sheet_name", engine="openpyxl")
点击查看英文原文

I think Pandas is the best way to go. There is already one answer here with Pandas using ExcelFile function, but it did not work properly for me. From here I found the read_excel function which works just fine:

import pandas as pd
dfs = pd.read_excel("your_file_name.xlsx", sheet_name="your_sheet_name")
print(dfs.head(10))

P.S. You need to have the xlrd installed for read_excel function to work

Update 21-03-2020: As you may see here, there are issues with the xlrd engine and it is going to be deprecated. The openpyxl is the best replacement. So as described here, the canonical syntax should be:

dfs = pd.read_excel("your_file_name.xlsx", sheet_name="your_sheet_name", engine="openpyxl")

回答 4

对于xlsx，我喜欢先前发布的解决方案https://web.archive.org/web/20180216070531//programming/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获取列以及检查是否使用了共享字符串。

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
点击查看英文原文

For xlsx I like the solution posted earlier as https://web.archive.org/web/20180216070531/https://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python. I uses modules from the standard library only.

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

Improvements added are fetching content by sheet name, using re to get the column and checking if sharedstrings are used.

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

回答 5

你可以使用任何的库列在这里（如Pyxlreader是基于JExcelApi的，或xlwt），加上COM自动化使用Excel本身的文件的阅读，但是对于您将引入厅认定为软件的依赖，这可能并不总是一个选择。

点击查看英文原文

You can use any of the libraries listed here (like Pyxlreader that is based on JExcelApi, or xlwt), plus COM automation to use Excel itself for the reading of the files, but for that you are introducing Office as a dependency of your software, which might not be always an option.

回答 6

如果您需要旧的XLS格式。下面的代码为ansii’cp1251’。

import xlrd

file=u'C:/Landau/task/6200.xlsx'

try:
    book = xlrd.open_workbook(file,encoding_override="cp1251")  
except:
    book = xlrd.open_workbook(file)
print("The number of worksheets is {0}".format(book.nsheets))
print("Worksheet name(s): {0}".format(book.sheet_names()))
sh = book.sheet_by_index(0)
print("{0} {1} {2}".format(sh.name, sh.nrows, sh.ncols))
print("Cell D30 is {0}".format(sh.cell_value(rowx=29, colx=3)))
for rx in range(sh.nrows):
   print(sh.row(rx))
点击查看英文原文

If you need old XLS format. Below code for ansii ‘cp1251’.

import xlrd

file=u'C:/Landau/task/6200.xlsx'

try:
    book = xlrd.open_workbook(file,encoding_override="cp1251")  
except:
    book = xlrd.open_workbook(file)
print("The number of worksheets is {0}".format(book.nsheets))
print("Worksheet name(s): {0}".format(book.sheet_names()))
sh = book.sheet_by_index(0)
print("{0} {1} {2}".format(sh.name, sh.nrows, sh.ncols))
print("Cell D30 is {0}".format(sh.cell_value(rowx=29, colx=3)))
for rx in range(sh.nrows):
   print(sh.row(rx))

回答 7

Python Excelerator也可以处理此任务。 http://ghantoos.org/2007/10/25/python-pyexcelerator-small-howto/

它在Debian和Ubuntu中也可用：

 sudo apt-get install python-excelerator
点击查看英文原文

Python Excelerator handles this task as well. http://ghantoos.org/2007/10/25/python-pyexcelerator-small-howto/

It’s also available in Debian and Ubuntu:

 sudo apt-get install python-excelerator

回答 8

您可能还考虑运行（非python）程序xls2csv。将其输入xls文件，然后应返回一个csv。

点击查看英文原文

You might also consider running the (non-python) program xls2csv. Feed it an xls file, and you should get back a csv.

回答 9

对于较早的Excel文件，有一个OleFileIO_PL模块可以读取所使用的OLE结构化存储格式。

点击查看英文原文

For older Excel files there is the OleFileIO_PL module that can read the OLE structured storage format used.

回答 10

    with open(csv_filename) as file:
        data = file.read()

    with open(xl_file_name, 'w') as file:
        file.write(data)

您可以使用内置包将CSV转换为excel以上格式。CSV可以使用内置的dictreader和dictwriter程序包处理，其工作方式与python词典的工作方式相同。这很容易，我目前不知道任何内置的excel软件包，但是我遇到过openpyxl。这也非常简单明了。您可以在下面看到代码段，希望对您有所帮助

    import openpyxl
    book = openpyxl.load_workbook(filename)
    sheet = book.active 
    result =sheet['AP2']
    print(result.value)
点击查看英文原文 
    with open(csv_filename) as file:
        data = file.read()

    with open(xl_file_name, 'w') as file:
        file.write(data)

You can turn CSV to excel like above with inbuilt packages. CSV can be handled with an inbuilt package of dictreader and dictwriter which will work the same way as python dictionary works. which makes it a ton easy I am currently unaware of any inbuilt packages for excel but I had come across openpyxl. It was also pretty straight forward and simple You can see the code snippet below hope this helps

    import openpyxl
    book = openpyxl.load_workbook(filename)
    sheet = book.active 
    result =sheet['AP2']
    print(result.value)