使用openpyxl操作Excel文件时出现「At least one sheet must be visible」错误的解决办法

Got it, let's tackle this error you're facing—both when opening Excel files manually and using openpyxl to save them. Here's what's going on and how to fix it:

Why This Error Occurs #

Excel (and libraries like openpyxl that follow Excel's file standards) requires at least one visible worksheet in every workbook. The error pops up when all sheets in the file are set to either hidden or very hidden—Excel can't load or save a workbook with no accessible sheets.

Fix 1: Manually Repair the Excel File #

If you can't even open the file normally, use VBA to unhide a sheet:

• Open Excel, then press Alt + F11 to launch the VBA Editor.
• In the left-hand "Project Explorer" pane, find your workbook (it might be listed as VBAProject (sample.xlsx)).
• Expand the workbook node, right-click on any hidden sheet, and select Properties.
• In the "Properties" window, find the Visible field:
  • Change its value from 2 - xlSheetVeryHidden or 1 - xlSheetHidden to -1 - xlSheetVisible.
• Close the VBA Editor, then save your workbook—you should now be able to open it normally.

Fix 2: Fix It Programmatically with openpyxl #

Your current code fails because the source workbook has no visible sheets. Add a check to ensure at least one sheet is visible before saving:

import openpyxl
from openpyxl.worksheet.properties import SheetVisibility

# Load the workbook
workbook = openpyxl.load_workbook(filename='sample.xlsx', read_only=False)

# Check if any sheets are visible
has_visible_sheet = any(sheet.sheet_state == 'visible' for sheet in workbook.worksheets)

# If no visible sheets, make the first one visible
if not has_visible_sheet:
    # Option 1: Use the SheetVisibility enum for clarity
    workbook.worksheets[0].sheet_state = SheetVisibility.visible
    # Option 2: Directly assign the string value (works too)
    # workbook.worksheets[0].sheet_state = 'visible'

# Now save the workbook without errors
workbook.save('test.xlsx')

How This Works: #

• In openpyxl, each worksheet has a sheet_state property that can be 'visible', 'hidden', or 'veryHidden'.
• The code checks if any sheet is visible—if not, it sets the first sheet's state to visible, satisfying Excel's requirement for saving.

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