Lambda读取S3中Excel文件报UnsupportedOperation错误的原因与解决

我在创建AWS Lambda函数时，需要从S3存储桶读取现有的Excel(.xlsx)文件，并用Pandas转换成DataFrame。同存储桶里的CSV文件能正常完成相同流程，但处理Excel文件时收到UnsupportedOperation错误。

代码示例 #

import pandas as pd
import re
import boto3
import logging

logging.getLogger().setLevel(logging.INFO)
logger = logging.getLogger()

s3_client = boto3.client('s3')
acc_tech_s3_object = s3_client.get_object(Bucket='trend-exemptions-inputs', Key='aws-acc-tech-service.xlsx')

acc_tech_s3_object_body = acc_tech_s3_object['Body']

logger.info("acc_tech: %s", str(acc_tech_s3_object_body))
logger.info("acc_tech type: %s", type(acc_tech_s3_object_body))

df_account = pd.read_excel(acc_tech_s3_object_body, sheet_name="aws-acc-tech-service")
...

错误信息 #

Test Event Name
Test

Response
{
  "errorMessage": "seek",
  "errorType": "UnsupportedOperation",
  "requestId": "",
  "stackTrace": [
    "  File \"/var/lang/lib/python3.9/importlib/__init__.py\", line 127, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n",
    "  File \"<frozen importlib._bootstrap>\", line 1030, in _gcd_import\n",
    "  File \"<frozen importlib._bootstrap>\", line 1007, in _find_and_load\n",
    "  File \"<frozen importlib._bootstrap>\", line 986, in _find_and_load_unlocked\n",
    "  File \"<frozen importlib._bootstrap>\", line 680, in _load_unlocked\n",
    "  File \"<frozen importlib._bootstrap_external>\", line 850, in exec_module\n",
    "  File \"<frozen importlib._bootstrap>\", line 228, in _call_with_frames_removed\n",
    "  File \"/var/task/lambda_function.py\", line 32, in <module>\n    df_account = pd.read_excel(acc_tech_s3_object_body, sheet_name=\"aws-acc-tech-service\")\n",
    "  File \"/opt/python/pandas/io/excel/_base.py\", line 478, in read_excel\n    io = ExcelFile(io, storage_options=storage_options, engine=engine)\n",
    "  File \"/opt/python/pandas/io/excel/_base.py\", line 1496, in __init__\n    ext = inspect_excel_format(\n",
    "  File \"/opt/python/pandas/io/excel/_base.py\", line 1375, in inspect_excel_format\n    stream.seek(0)\n"
  ]
}

日志信息 #

[INFO]  2023-06-09T14:12:35.512Z        acc_tech: <botocore.response.StreamingBody object at 0x7f88e65ba1f0>
[INFO]  2023-06-09T14:12:35.512Z        acc_tech type: <class 'botocore.response.StreamingBody'>

我知道这是Pandas的seek操作导致的问题，但为什么只有Excel文件会出现这个错误？有没有可行的解决办法？

为什么只有Excel文件出错？ #

Pandas处理CSV和Excel的逻辑存在差异：

• pd.read_csv可直接处理流式对象（比如S3的StreamingBody），因为它无需回溯读取，从头到尾流式解析即可完成。
• 而pd.read_excel在正式解析前，需要先探测Excel文件格式，这个过程需要多次调用seek()回到文件开头或在不同位置读取文件头信息。但S3返回的StreamingBody是单向流式对象，仅支持一次性顺序读取，不支持seek()这类回溯操作，因此触发了UnsupportedOperation错误。

解决办法 #

有三种常见的可行方案：

方案1：将StreamingBody转换为支持seek的BytesIO对象 #

把S3返回的流式内容读取到内存中的BytesIO对象里，BytesIO支持随机访问（包括seek()），能满足Pandas读取Excel的要求：

import pandas as pd
import re
import boto3
import logging
from io import BytesIO

logging.getLogger().setLevel(logging.INFO)
logger = logging.getLogger()

s3_client = boto3.client('s3')
acc_tech_s3_object = s3_client.get_object(Bucket='trend-exemptions-inputs', Key='aws-acc-tech-service.xlsx')

# 将流式内容读取到BytesIO中
acc_tech_s3_object_body = BytesIO(acc_tech_s3_object['Body'].read())

df_account = pd.read_excel(acc_tech_s3_object_body, sheet_name="aws-acc-tech-service")

方案2：使用S3资源的download_fileobj写入BytesIO #

通过boto3的S3资源接口，直接将文件内容写入BytesIO对象：

import pandas as pd
import re
import boto3
import logging
from io import BytesIO

logging.getLogger().setLevel(logging.INFO)
logger = logging.getLogger()

s3 = boto3.resource('s3')
bucket = s3.Bucket('trend-exemptions-inputs')

file_obj = BytesIO()
bucket.download_fileobj('aws-acc-tech-service.xlsx', file_obj)
# 回到文件开头，准备读取
file_obj.seek(0)

df_account = pd.read_excel(file_obj, sheet_name="aws-acc-tech-service")

方案3：下载到Lambda临时目录（适合大文件） #

如果Excel文件体积较大，读取到内存可能触发Lambda的内存限制，可先将文件下载到Lambda的临时存储目录（/tmp），再读取本地文件：

import pandas as pd
import re
import boto3
import logging

logging.getLogger().setLevel(logging.INFO)
logger = logging.getLogger()

s3_client = boto3.client('s3')
# 下载到临时目录
temp_file_path = '/tmp/aws-acc-tech-service.xlsx'
s3_client.download_file('trend-exemptions-inputs', 'aws-acc-tech-service.xlsx', temp_file_path)
# 读取本地文件
df_account = pd.read_excel(temp_file_path, sheet_name="aws-acc-tech-service")

内容的提问来源于stack exchange，提问作者vile_goat