如何将Python爬虫获取的Reddit评论导出至Excel？解决打开报错问题

问题描述 #

通过爬虫获取Reddit评论后导出到Excel文件test1.xlsx，但Excel报错：

Excel cannot open the file 'test1.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension mates the format of the file.

现有代码：

url = "https://www.reddit.com/r/bayarea/comments/17v80j5/do_you_see_yourself_using_the_ca_high_speed_rail/"

submission = reddit.submission(url=url)

posts = []

for top_level_comment in submission.comments:
    if isinstance(top_level_comment, MoreComments):
        continue
    posts.append(top_level_comment.body)

posts = pd.DataFrame(posts,columns=["body"])

indexNames = posts[(posts.body == '[removed]') | (posts.body == '[deleted]')].index

posts.drop(indexNames, inplace=True)
print(posts)


writer = pd.ExcelWriter('test1.xlsx', engine = 'openpyxl')
posts.to_excel(writer, sheet_name='data', index = False)

解决方法 #

不需要更换库，问题出在未正确关闭ExcelWriter对象，导致文件未完成写入就被截断，出现损坏。

方案1：手动关闭写入器 #

在posts.to_excel(...)后添加关闭语句：

writer = pd.ExcelWriter('test1.xlsx', engine = 'openpyxl')
posts.to_excel(writer, sheet_name='data', index = False)
writer.close()  # 新增这行，完成文件写入

方案2：使用上下文管理器（推荐） #

用with语句自动管理资源，无需手动关闭：

with pd.ExcelWriter('test1.xlsx', engine='openpyxl') as writer:
    posts.to_excel(writer, sheet_name='data', index=False)

备选方案：简化写法 #

如果不需要自定义ExcelWriter的额外配置，可直接调用DataFrame的to_excel方法，pandas会自动处理资源：

posts.to_excel('test1.xlsx', sheet_name='data', index=False, engine='openpyxl')

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