Python中LookupError: unknown encoding: us-ascii错误的解决方法

问题 #

我有一个无数据的简单.xlsx文件，想要向单元格写入值并保存工作簿。在Python 3.11.4中运行以下openpyxl代码：

import openpyxl
path = "C:\\Users\\abc\\Downloads\\Integration_Recon\\Output\\file.xlsx"
target_workbook = openpyxl.load_workbook(path)
target_sheet = target_workbook['Sheet1']
target_sheet.cell(row=2, column=2, value = "111111")
target_workbook.save(path)

时出现如下错误：

Traceback (most recent call last):
  File "c:\Users\abc\Downloads\Integration_Recon\Code\tst2.py", line 6, in <module>
    target_workbook.save(path)
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\workbook\workbook.py", line 386, in save
    save_workbook(self, filename)
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\writer\excel.py", line 294, in save_workbook
    writer.save()
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\writer\excel.py", line 77, in write_data
    self._write_worksheets()
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\writer\excel.py", line 215, in _write_worksheets
    self.write_worksheet(ws)
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\writer\excel.py", line 200, in write_worksheet
    writer.write()
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\worksheet\_writer.py", line 361, in write
    self.close()
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\worksheet\_writer.py", line 369, in close
    self.xf.close()
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\openpyxl\worksheet\_writer.py", line 289, in get_stream
    with xf.element("worksheet", xmlns=SHEET_MAIN_NS):
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\contextlib.py", line 144, in __exit__
    next(self.gen)
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\et_xmlfile\xmlfile.py", line 50, in element
    self._write_element(el)
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\site-packages\et_xmlfile\xmlfile.py", line 77, in _write_element
    xml = tostring(element)
          ^^^^^^^^^^^^^^^^^
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\xml\etree\ElementTree.py", line 1098, in tostring
    ElementTree(element).write(stream, encoding,
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\xml\etree\ElementTree.py", line 731, in write
    with _get_writer(file_or_filename, encoding) as (write, declared_encoding):
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "C:\Users\abc\AppData\Local\Programs\Python\Python311\Lib\xml\etree\ElementTree.py", line 794, in _get_writer
    file = io.TextIOWrapper(file,
           ^^^^^^^^^^^^^^^^^^^^^^
LookupError: unknown encoding: us-ascii

因已有大量基于openpyxl的代码，不想改用pandas等其他模块，请问该错误产生的原因是什么？如何修复？

错误原因 #

该错误源于openpyxl依赖的et_xmlfile库在生成XML内容时，错误使用了非标准编码名称us-ascii；而Python 3.11+对编码名称的校验更严格，仅识别标准的ascii标识符，导致编码解析失败。

修复方案 #

方法1：升级et_xmlfile到最新版本 #

et_xmlfile的新版本已修复该编码名称错误，执行以下命令更新：

pip install --upgrade et_xmlfile

方法2：临时映射编码（无法升级库时使用） #

在代码开头添加编码映射逻辑，将us-ascii手动关联到标准的ascii编码：

import codecs
# 注册编码映射
codecs.register(lambda name: codecs.lookup('ascii') if name.lower() == 'us-ascii' else None)

import openpyxl
path = "C:\\Users\\abc\\Downloads\\Integration_Recon\\Output\\file.xlsx"
target_workbook = openpyxl.load_workbook(path)
target_sheet = target_workbook['Sheet1']
target_sheet.cell(row=2, column=2, value = "111111")
target_workbook.save(path)

方法3：更换空白Excel文件 #

部分旧空白Excel文件可能存在格式异常，触发编码问题。可以用Excel手动创建新的空白.xlsx文件替换原文件后，再运行代码。

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