Mac OS Big Sur 11.3.1 Excel 16.47无法打开XlsxWriter生成的XLSX文件咨询

问题原因 #

你遇到的是Mac版Excel和Windows版Excel的文件校验规则差异导致的兼容性问题，核心诱因有以下几个：

• 工作表命名规则差异：Mac版Excel对工作表名的校验更严格，不允许名称超过31个字符，也不允许包含\ / ? * [ ] :这类特殊字符，而Windows版Excel对这类问题的容错性更高，你直接用CSV文件名作为工作表名，很容易触发该限制。
• 空工作表校验：如果存在空CSV文件，写入空工作表后，Mac版Excel会直接判定文件损坏无法打开，Windows版则可以正常加载。
• XlsxWriter版本适配问题：你使用的XlsxWriter 1.4.2版本在部分老版本Mac Excel中存在默认导出参数不兼容的情况。

解决方案 #

你可以按照以下步骤修改代码解决问题：

1. 预处理工作表名称，裁剪长度并过滤非法字符
2. 跳过空的CSV文件，避免写入空工作表
3. 可选更换导出引擎为openpyxl，该引擎对Mac版Excel的兼容性更好
4. 采用上下文管理器写法自动处理文件流关闭，避免文件结构异常

修改后的参考代码如下：

import os
import pandas as pd
import glob
import re

path = './'
all_files = glob.glob(os.path.join(path, "*.csv"))

# 匹配非法字符的正则
illegal_char_re = re.compile(r'[\\/:*?"<>|\[\]]')

# 使用上下文管理器管理ExcelWriter，自动处理资源关闭
with pd.ExcelWriter('out.xlsx', engine='xlsxwriter') as writer:
    for f in all_files:
        # 跳过空CSV文件
        if os.path.getsize(f) == 0:
            continue
        df = pd.read_csv(f)
        # 处理工作表名
        sheet_name = os.path.splitext(os.path.basename(f))[0]
        # 替换非法字符为下划线
        sheet_name = illegal_char_re.sub('_', sheet_name)
        # 裁剪到Excel允许的最大31个字符长度
        sheet_name = sheet_name[:31]
        df.to_excel(writer, sheet_name=sheet_name, index=False)

如果修改后仍有问题，可以安装openpyxl后更换引擎：

• 先执行安装命令：pip install openpyxl==3.0.10
• 将ExcelWriter的engine参数改为engine='openpyxl'即可。

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