Let's walk through the possible causes and fixes for why wb.sheetnames returns an empty list even though you can see the "Data" sheet in Excel:

1. Your file might not be a valid XLSX format #

Openpyxl only supports XLSX/XLSM/XLTX/XLTMB files. If your file was originally an older XLS (Excel 97-2003) file and you just renamed the extension to .xlsx, openpyxl won’t parse it correctly.

• Verify the file type: Right-click the file → Properties → Check the "Type of file" field. If it says "Microsoft Excel 97-2003 Worksheet", that’s the root issue.
• Fix it: Open the file in Excel, go to File → Save As, and explicitly select "Excel Workbook (*.xlsx)" as the format (don’t just click "Save").
• Test with a compatible library: Use xlrd (which supports XLS) to confirm:

  import xlrd
  wb = xlrd.open_workbook("IS-2-1000.xlsx")
  print(wb.sheet_names())  # If this returns "Data", the format was the problem
  

2. Load the workbook in read-only mode #

Files with complex formatting or minor corruption often parse better in read-only mode, which skips non-essential file structure elements. Update your code to:

import openpyxl
wb = openpyxl.load_workbook("IS-2-1000.xlsx", data_only=True, read_only=True)
print(wb.sheetnames)

3. Check the file's internal structure #

XLSX files are ZIP archives under the hood. You can confirm if worksheet data actually exists:

• Rename the file to IS-2-1000.zip
• Open it with a ZIP tool (like WinRAR or 7-Zip)
• Navigate to the xl/worksheets/ folder. If you see files like sheet1.xml here, the worksheets exist; if the folder is empty, Excel might be showing a cached version of the file.

4. Check for very hidden worksheets #

While openpyxl detects regular hidden worksheets, very hidden worksheets (only unhidden via VBA) might be excluded by default. To confirm:

• Open the file in Excel
• Press Alt + F11 to open the VBA editor
• In the Project Explorer, expand your workbook → "Microsoft Excel Objects"
• If the "Data" sheet has a Visible property set to xlSheetVeryHidden (value 2), change it to xlSheetVisible (value -1) to make it detectable by openpyxl.

5. Fall back to pandas for testing #

If openpyxl still fails, try pandas—it uses different parsing engines and might detect the sheet:

import pandas as pd
df = pd.read_excel("IS-2-1000.xlsx", sheet_name="Data")
print(df.head())  # If this works, the issue is specific to openpyxl's parsing logic

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