使用 Pandas 读取超过 65536 行的 Excel 文件

场景

今天需要合并天猫订单数据，由于前期6.18活动有很多数据需要处理，将几个月份合并一起，结果报错。

问题分析

Excel 文件的格式曾经发生过一次变化，在 Excel 2007 以前，使用扩展名为 .xls 格式的文件，这种文件格式是一种特定的二进制格式，最多支持 65,536 行，256 列表格。从 Excel 2007 版开始，默认采用了基于 XML 的新的文件格式 .xlsx ，支持的表格行数达到了 1,048,576，列数达到了 16,384。需要注意的是，将 .xlsx 格式的文件转换为 .xls 格式的文件时，65536 行和 256 列之后的数据都会被丢弃。

Pandas 读取 Excel 文件的引擎是 xlrd ， xlrd 虽然同时支持 .xlsx 和 .xls 两种文件格式，但是在源码文件 xlrd/sheet.py 中限制了读取的 Excel 文件行数必须小于 65536，列数必须小于 256。

if self.biff_version >= 80:
    self.utter_max_rows = 65536
else:
    self.utter_max_rows = 16384
self.utter_max_cols = 256

这就导致，即使是 .xlsx 格式的文件， xlrd 依然不支持读取 65536 行以上的 Excel 文件（源码中还有一个行数限制是 16384，这是因为 Excel 95 时代， xls 文件所支持的最大行数是 16384）。

解决办法

openpyxl 是一个专门用来操作 .xlsx 格式文件的 Python 库，和 xlrd 相比它对于最大行列数的支持和 .xlsx 文件所定义的最大行列数一致。
首先安装 openpyxl ：

pip install openpyxl

Pandas 的 read_excel 方法中，有 engine 字段，可以指定所使用的处理 Excel 文件的引擎，填入 openpyxl ，再读取文件就可以了。

import os
import pandas as pd

# 将文件读取出来放一个列表里面

pwd = '1'  # 获取文件目录

# 新建列表，存放文件名
file_list = []

# 新建列表存放每个文件数据(依次读取多个相同结构的Excel文件并创建DataFrame)
dfs = []

for root,dirs,files in os.walk(pwd):  # 第一个为起始路径，第二个为起始路径下的文件夹，第三个是起始路径下的文件。
    for file in files:
        file_path = os.path.join(root, file)
        file_list.append(file_path)  # 使用os.path.join(dirpath, name)得到全路径
        df = pd.read_excel(file_path)  # 导入xlsx文件，将excel转换成DataFrame
        dfs.append(df)
        


# 将多个DataFrame合并为一个
df = pd.concat(dfs)

# 数据输出，写入excel文件，不包含索引数据
# 数据写入 Excel，需要首先安装一个 engine，由 engine 负责将数据写入 Excel，pandas 使用 openpyx 或 xlsxwriter 作为写入引擎。
df.to_excel('test\\1.xlsx', index=False,engine='openpyxl') # 导出 Excel，一般不需要索引，将 index 参数设为 False

www.qmpython.com