Putty(Unix)运行Python脚本遇openpyxl缺失报错求替代方案

通过Putty在Unix环境运行Python脚本时触发ImportError: Missing optional dependency 'openpyxl'，但脚本在Jupyter中运行正常；因权限限制无法安装openpyxl库，需无需额外安装依赖即可读取xlsx文件的替代方案。

报错栈：

Model Spec ILD_Distributeur_xxx.xlsx ERROR: Traceback (most recent call last): 
File "/iri/local/stat-prd/infoscan/code_recensement/code_recensement_geo_retailers_prod_test.py", line 63, in <module> 
VGH_detailed = pd.read_excel("{}/{}".format(path,file), sheet_name="VGH_detailed", header=1) 
File "/usr/local/lib/python3.7/site-packages/pandas/util/_decorators.py", line 311, in wrapper 
return func(*args, **kwargs) 
File "/usr/local/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 364, in read_excel 
io = ExcelFile(io, storage_options=storage_options, engine=engine) 
File "/usr/local/lib/python3.7/site-packages/pandas/io/excel/_base.py", line 1233, in __init__ 
self._reader = self._engines[engine](self._io, storage_options=storage_options) 
File "/usr/local/lib/python3.7/site-packages/pandas/io/excel/_openpyxl.py", line 521, in __init__ 
import_optional_dependency("openpyxl") 
File "/usr/local/lib/python3.7/site-packages/pandas/compat/_optional.py", line 118, in import_optional_dependency 
raise ImportError(msg) from None 
ERROR: Traceback (most recent call last): 
ImportError: Missing optional dependency 'openpyxl'.  Use pip or conda to install openpyxl.

关键代码片段：

try:   
    import os
    from re import search, IGNORECASE
    import pandas as pd
    import datetime
    import math
    import numpy as np
    import traceback
    import sys
    
        
    path = "//server/path1/" 
    list_dossier = []
    time = []
    patho = "//server/path2/"
           
    for dossier in os.listdir(path):
        
        if search( "File_name", dossier,  IGNORECASE) and search( "xlsx", dossier, IGNORECASE) and not search( "copie", dossier, IGNORECASE) and not search( "dev", dossier, IGNORECASE) and not search( "provisoire", dossier, IGNORECASE) and not search( "temporaire", dossier, IGNORECASE) :
            if "~" in dossier or "$" in dossier:
                pass
            else:
                list_dossier.append(dossier)

    for i in list_dossier:        
        pathi = "{}/{}".format(path,i)
        modification_time = os.path.getmtime(pathi)
        time.append(modification_time)

    last_time = time
    last_time.sort()
    last_time = last_time[-1]


    result = list_dossier[time.index(last_time)]
    print(result)

    file = [file]
    file = str(file[-1])


    # nous importons les feuilles VGH_detailed et Geographies
    VGH_detailed = pd.read_excel("{}/{}".format(path,file), sheet_name="VGH_detailed",  header=1)
    Geographies = pd.read_excel("{}/{}".format(path,file), sheet_name="Geographies",  header=1)

    # on declare la table 
    table = pd.DataFrame(index=np.arange(len(VGH_detailed)), columns=["Model","Super Folder","Folder","Level","Geography Label","ILD Code","Legacy Code","Drill Down to Store Level"])

已尝试修改语法、检查文件、测试Unix代码等方法，均未解决问题。

方案1：使用xlrd引擎（需环境预装xlrd<2.0） #

pandas默认优先用openpyxl读取xlsx，若Unix环境已安装xlrd 1.2.0及以下版本（该版本支持xlsx格式），可直接指定engine='xlrd'绕过openpyxl：

修改关键代码中的pd.read_excel调用：

# 读取VGH_detailed工作表
VGH_detailed = pd.read_excel("{}/{}".format(path,file), sheet_name="VGH_detailed", header=1, engine='xlrd')
# 读取Geographies工作表
Geographies = pd.read_excel("{}/{}".format(path,file), sheet_name="Geographies", header=1, engine='xlrd')

注意：xlrd 2.0及以上版本仅支持xls格式，若环境中xlrd为高版本，此方案不可用。

方案2：Jupyter中转xlsx为CSV，Unix读取CSV #

利用Jupyter可正常运行脚本的特性，先在Jupyter中将目标xlsx的指定工作表导出为CSV，再在Unix环境用pd.read_csv读取（无需额外依赖）：

步骤1：Jupyter中执行导出代码 #

import pandas as pd

# 读取目标xlsx的指定工作表
VGH_detailed = pd.read_excel("//server/path1/目标文件名.xlsx", sheet_name="VGH_detailed", header=1)
Geographies = pd.read_excel("//server/path1/目标文件名.xlsx", sheet_name="Geographies", header=1)

# 导出为CSV保存到同服务器路径
VGH_detailed.to_csv("//server/path1/VGH_detailed.csv", index=False, encoding='utf-8')
Geographies.to_csv("//server/path1/Geographies.csv", index=False, encoding='utf-8')

步骤2：修改Unix脚本读取逻辑 #

替换原pd.read_excel为pd.read_csv：

# 读取CSV文件
VGH_detailed = pd.read_csv("{}/VGH_detailed.csv".format(path))
Geographies = pd.read_csv("{}/Geographies.csv".format(path))

若需自动处理最新文件，可在Jupyter中设置定时导出任务，或在Unix脚本中添加CSV存在性检查，缺失时提示手动导出。

方案3：Python标准库手动解析xlsx（无第三方依赖） #

xlsx本质是ZIP压缩包，内部用XML存储表格数据，可通过标准库zipfile和xml.etree.ElementTree解析：

以下是读取指定工作表的工具函数，可直接集成到脚本中：

import zipfile
import xml.etree.ElementTree as ET
import pandas as pd

def read_xlsx_without_openpyxl(file_path, sheet_name, header_row=1):
    # 定义xlsx内部XML命名空间
    ns = {'main': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
    
    # 打开xlsx压缩包
    with zipfile.ZipFile(file_path, 'r') as zf:
        # 查找目标工作表对应的资源ID
        with zf.open('xl/workbook.xml') as f:
            tree = ET.parse(f)
            root = tree.getroot()
            sheet_rId = None
            for sheet in root.findall('.//main:sheet', ns):
                if sheet.attrib['name'] == sheet_name:
                    sheet_rId = sheet.attrib['r:id']
                    break
            if not sheet_rId:
                raise ValueError(f"工作表 {sheet_name} 不存在")
        
        # 获取工作表XML文件路径
        with zf.open('xl/_rels/workbook.xml.rels') as f:
            tree = ET.parse(f)
            root = tree.getroot()
            sheet_path = None
            for rel in root.findall('.//Relationship'):
                if rel.attrib['Id'] == sheet_rId:
                    sheet_path = rel.attrib['Target']
                    break
            if not sheet_path:
                raise ValueError(f"无法找到工作表 {sheet_name} 的文件路径")
        
        # 提取单元格数据
        with zf.open(f'xl/{sheet_path}') as f:
            tree = ET.parse(f)
            root = tree.getroot()
            data = []
            for row in root.findall('.//main:row', ns):
                row_data = []
                for cell in row.findall('.//main:c', ns):
                    cell_value = cell.find('.//main:v', ns)
                    row_data.append(cell_value.text if cell_value is not None else '')
                data.append(row_data)
        
        # 转换为DataFrame并跳过指定表头行
        df = pd.DataFrame(data[header_row:], columns=data[header_row-1])
        return df

修改脚本中的读取代码：

# 使用自定义函数读取xlsx
file_path = "{}/{}".format(path,file)
VGH_detailed = read_xlsx_without_openpyxl(file_path, sheet_name="VGH_detailed", header_row=1)
Geographies = read_xlsx_without_openpyxl(file_path, sheet_name="Geographies", header_row=1)

注意：该函数仅处理基础单元格数据，若xlsx包含合并单元格、公式计算值等复杂格式，需额外适配逻辑。

内容的提问来源于stack exchange，提问作者Soukayna Ben Moh