使用olefile方法从目录打开excel文件

Question

你好

我试图打开多个excel文件（xls）并将它们放在一个数据框中。我正在使用.glob()访问此处的文件：

all_files = glob.glob('D:\Anaconda Hub\ARK analysis\Ark analysis\data\year2021\\february\\**.xls')

示例输出是一个列表，因此：

['D:\\Anaconda Hub\\ARK analysis\\Ark analysis\\data\\year2021\\february\\ARK_Trade_02012021_0619PM_EST_601875e069e08.xls',
 'D:\\Anaconda Hub\\ARK analysis\\Ark analysis\\data\\year2021\\february\\ARK_Trade_02022021_0645PM_EST_6019df308ae5e.xls',
 'D:\\Anaconda Hub\\ARK analysis\\Ark analysis\\data\\year2021\\february\\ARK_Trade_02032021_0829PM_EST_601b2da2185c6.xls',
 'D:\\Anaconda Hub\\ARK analysis\\Ark analysis\\data\\year2021\\february\\ARK_Trade_02042021_0637PM_EST_601c72b88257f.xls',
 'D:\\Anaconda Hub\\ARK analysis\\Ark analysis\\data\\year2021\\february\\ARK_Trade_02052021_0646PM_EST_601dd4dc308c5.xls',
 'D:\\Anaconda Hub\\ARK analysis\\Ark analysis\\data\\year2021\\february\\ARK_Trade_02082021_0629PM_EST_6021c739595b0.xls'..]

我正在使用olefile方法。这是我的密码：

import os
import glob
import olefile as ol
import pandas as pd

 # using olefile to iterate to extract each excel file to be readible 
with open(all_files,'r') as file:
    if file.endswith('.xls'):
        ole = ol.OleFileIO(file)
        if ole.exists('Workbook'):
            d = ole.openstream('Workbook')
            df = pd.read_excel(d, engine='xlrd', header=3, skiprows=3)
            print(df.head())

但是，我得到了这个错误：

TypeError: expected str, bytes or os.PathLike object, not list

我不明白为什么我会犯这个错误。我正在迭代列表以选择一个字符串，并将其传递到其余步骤中。。。 如果您能正确执行此操作并将excel文件输出到单个数据框中，我们将不胜感激。 提前谢谢

Answer 1

我相信您使用的是传统格式/版本的Microsoft Excel

在这种情况下，错误消息TypeError: expected str, bytes or os.PathLike object, not list提供了大量信息。您的代码有一行：with open(all_files,'r') as file:，您已经将整个列表传递给了open()

请尝试以下代码：

import os
import glob
import olefile
import pandas as pd

all_files = glob.glob('excelfiles/*.xls')

for file in all_files:
    with olefile.OleFileIO(file) as ole:     # Since olefile v0.46
        if ole.exists('Workbook'):
            d = ole.openstream('Workbook')
            df = pd.read_excel(d, engine='xlrd', header=3, skiprows=3)
            print(df.head())

我从共享文件中获得的输出：

   ARKG  2021-02-01  Sell  ... PACIFIC BIOSCIENCES OF CALIFORNIA INC  210508  0.0645
0  ARKK  2021-02-01   Buy  ...                 FATE THERAPEUTICS INC  154509  0.0608
1  ARKK  2021-02-01   Buy  ...                            PACCAR INC  263029  0.1024
2  ARKK  2021-02-01   Buy  ...                          TERADYNE INC  295371  0.1465
3  ARKK  2021-02-01   Buy  ...                 BEAM THERAPEUTICS INC   58218  0.0241
4  ARKK  2021-02-01  Sell  ...         REGENERON PHARMACEUTICALS INC    5130  0.0111

[5 rows x 8 columns]
   ARKG  2021-02-03  Sell  ...  TWIST BIOSCIENCE CORP   97415  0.1615
0  ARKK  2021-02-03   Buy  ...  SPOTIFY TECHNOLOGY SA  385932  0.4980
1  ARKK  2021-02-03   Buy  ...             PACCAR INC  318474  0.1231
2  ARKK  2021-02-03   Buy  ...  FATE THERAPEUTICS INC   98059  0.0394
3  ARKK  2021-02-03   Buy  ...           TERADYNE INC  104809  0.0524
4  ARKK  2021-02-03  Sell  ...               ROKU INC   53551  0.0924

[5 rows x 8 columns]