使用openpyxl取消冻结行列致Excel损坏，求无损坏解决方案

我尝试用openpyxl取消Excel文件中前2行和第一列的冻结窗格，但用Excel打开文件时会收到提示，称Excel发现文件内容存在问题，询问是否恢复。恢复后窗格确实已取消冻结，但每次打开都会弹出损坏提示。

当前使用的取消冻结代码：

import openpyxl
from openpyxl.worksheet.views import Selection, Pane

filename = '...\\ExcelFile.xlsx'
wb = openpyxl.load_workbook(filename)
ws = wb.active

# 当前取消冻结的代码，打开Excel时会弹出错误
ws.views.sheetView[0].topLeftCell = 'A1'  # 滚动到左上角
ws.views.sheetView[0].pane = Pane()  # 清空窗格设置
ws.views.sheetView[0].selection[0] = Selection()
wb.save(filename)

测试用Excel文件数据：

补充：冻结窗格前后的sheetview信息

冻结窗格前

<openpyxl.worksheet.views.SheetView object>
Parameters:
windowProtection=None, showFormulas=None, showGridLines=None, showRowColHeaders=None, showZeros=None, rightToLeft=None, tabSelected=True, showRuler=None, showOutlineSymbols=None, defaultGridColor=None, showWhiteSpace=None, view=None, topLeftCell=None, colorId=None, zoomScale=None, zoomScaleNormal=None, zoomScaleSheetLayoutView=None, zoomScalePageLayoutView=None, zoomToFit=None, workbookViewId=0, pane=None, selection=[<openpyxl.worksheet.views.Selection object>
Parameters:
pane=None, activeCell='A1', activeCellId=None, sqref='A1']

已应用冻结窗格

<openpyxl.worksheet.views.SheetView object>
Parameters:
windowProtection=None, showFormulas=None, showGridLines=None, showRowColHeaders=None, showZeros=None, rightToLeft=None, tabSelected=True, showRuler=None, showOutlineSymbols=None, defaultGridColor=None, showWhiteSpace=None, view=None, topLeftCell=None, colorId=None, zoomScale=None, zoomScaleNormal=None, zoomScaleSheetLayoutView=None, zoomScalePageLayoutView=None, zoomToFit=None, workbookViewId=0, pane=<openpyxl.worksheet.views.Pane object>
Parameters:
xSplit=1.0, ySplit=2.0, topLeftCell='B3', activePane='bottomRight', state='frozen', selection=[<openpyxl.worksheet.views.Selection object>
Parameters:
pane='topRight', activeCell=None, activeCellId=None, sqref=None, <openpyxl.worksheet.views.Selection object>
Parameters:
pane='bottomLeft', activeCell=None, activeCellId=None, sqref=None, <openpyxl.worksheet.views.Selection object>
Parameters:
pane='bottomRight', activeCell='A1', activeCellId=None, sqref='A1']

问题根源在于你直接替换pane和selection的方式不符合Excel的XML规范：冻结窗格时会生成多个对应不同窗格的Selection对象，你之前只替换了第一个，剩余的空Selection会导致Excel判定文件损坏。

正确的处理方式如下：

import openpyxl

filename = '...\\ExcelFile.xlsx'
wb = openpyxl.load_workbook(filename)
ws = wb.active

sheet_view = ws.views.sheetView[0]
# 回到左上角单元格
sheet_view.topLeftCell = 'A1'
# 直接将pane设为None，彻底取消冻结状态
sheet_view.pane = None
# 清空所有多余的selection对象
sheet_view.selection.clear()
# 添加一个符合初始状态的Selection
sheet_view.selection.append(openpyxl.worksheet.views.Selection(
    activeCell='A1',
    sqref='A1'
))

wb.save(filename)

说明 #

通过将pane设为None，可以直接恢复到无冻结的状态；清空原有的selection列表并添加一个正常的初始选择（对应未冻结时的单选择状态），就能完全匹配冻结前的sheetview配置，避免Excel抛出文件损坏提示。

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