如何从Python安全生成符合Excel规范的xlsx文件？

我知道Microsoft Excel打开某些电子表格时存在限制，偶尔会弹出这类错误/警告：

我们在'filename.xlsx'中发现部分内容存在问题。是否要尝试尽可能恢复内容？如果信任此工作簿的来源，请点击“是”。
检测到'filename.xlsx'文件存在错误
Excel已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。

虽然Google Sheets、LibreOffice和Apple Numbers的兼容性更稳定，但我的终端用户只习惯用Excel，而且他们需要完全信任我生成的表格数据。

这些带多工作表的表格是用类似下面的Python代码生成的：

with pd.ExcelWriter(f"{filename}.xlsx") as writer:
  for sheet in sheets:
    data = pd.DataFrame(...)
    data.to_excel(writer, sheet_name=f"{sheet}")
  writer.save()

之前我解决了工作表名称过长的问题（微软限制最多31个字符），错误就消失了，但最近错误又出现了，代码和数据内容都没明显变化。我查看了Excel的XML目录，只发现一些细微格式差异（比如默认字体、列宽）和XML架构差异，修复后的工作簿内容基本一致，但我没法只让用户“不用担心，数据大概率没问题”。

现在想知道：从Python生成Excel文件的最安全方式是什么？理想情况下，希望表格写入工具默认遵循Microsoft Excel的约束，在生成文件时就抛出潜在错误或警告。有没有比Pandas更可靠的Excel写入工具？或者有没有办法让Pandas生成的表格避免这类错误，让用户放心使用？

解决方案 #

优化Pandas的Excel写入配置 #

Pandas默认依赖openpyxl（xlsx格式）或xlwt（xls格式）作为引擎，调整参数可减少兼容性问题：

• 指定engine='openpyxl'，并对齐Excel默认格式：

  from openpyxl.styles import Font
  
  with pd.ExcelWriter(f"{filename}.xlsx", engine='openpyxl', mode='w') as writer:
      # 设置默认字体匹配Excel标准（Calibri 11号）
      workbook = writer.book
      workbook.default_style.font = Font(name='Calibri', size=11)
      
      for sheet in sheets:
          data = pd.DataFrame(...)
          # 关闭索引写入，手动设置合理列宽避免格式异常
          data.to_excel(writer, sheet_name=f"{sheet}", index=False)
          worksheet = writer.sheets[f"{sheet}"]
          
          # 自动调整列宽到合适长度
          for col in worksheet.columns:
              max_len = max(len(str(cell.value)) for cell in col)
              worksheet.column_dimensions[col[0].column_letter].width = max_len + 2
  

• 强制指定数据类型，避免Pandas自动推断生成Excel不兼容的格式：

  data = pd.DataFrame(...).astype({
      "日期列": "datetime64[ns]",
      "数值列": "float64",
      "文本列": "string"
  })
  

使用严格遵循Excel规范的写入库 #

如果Pandas的优化仍无法解决问题，可直接使用底层库，这类工具会在写入阶段校验Excel约束：

• openpyxl：直接操作Excel的OOXML结构，可提前校验所有合规性要求：

  from openpyxl import Workbook
  
  wb = Workbook()
  for sheet_name in sheets:
      # 提前校验工作表名称长度，不符合直接抛出错误
      if len(sheet_name) > 31:
          raise ValueError(f"工作表名称 '{sheet_name}' 超过31字符限制")
      ws = wb.create_sheet(title=sheet_name)
      
      data = pd.DataFrame(...)
      # 校验单元格内容长度（Excel单单元格文本最大32767字符）
      for r_idx, row in enumerate(data.itertuples(index=False), start=1):
          for c_idx, value in enumerate(row, start=1):
              if isinstance(value, str) and len(value) > 32767:
                  raise ValueError(f"单元格({r_idx}, {c_idx})内容过长，超出Excel限制")
              ws.cell(row=r_idx, column=c_idx, value=value)
  wb.save(f"{filename}.xlsx")
  

• xlsxwriter：专为生成兼容Excel的xlsx文件设计，内置约束校验：

  import xlsxwriter
  
  workbook = xlsxwriter.Workbook(f"{filename}.xlsx")
  for sheet_name in sheets:
      # 工作表名称不符合规范时直接报错
      worksheet = workbook.add_worksheet(sheet_name)
      data = pd.DataFrame(...)
      
      # 按Excel标准写入表头和数据
      worksheet.write_row(0, 0, data.columns)
      for r_idx, row in enumerate(data.values, start=1):
          worksheet.write_row(r_idx, 0, row)
  workbook.close()
  

生成后主动校验文件 #

用openpyxl加载生成的文件，做最终合规性检查：

from openpyxl import load_workbook

wb = load_workbook(f"{filename}.xlsx")
# 检查所有工作表名称长度
for sheet in wb.sheetnames:
    if len(sheet) > 31:
        raise ValueError(f"工作表名称 '{sheet}' 不符合Excel限制")
# 检查所有单元格内容长度
for ws in wb.worksheets:
    for row in ws.iter_rows(values_only=True):
        for cell in row:
            if isinstance(cell, str) and len(cell) > 32767:
                raise ValueError("存在内容过长的单元格，超出Excel限制")
wb.save(f"{filename}.xlsx")

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