DEV Community

Jeremy K.
Jeremy K.

Posted on

Extracting Excel Worksheet Names with Python

#python #programming

When working with Excel files in data engineering or office automation, it is often useful to inspect the workbook structure before reading data. Knowing the worksheet names helps you locate the right data, process multiple sheets in batch, or validate file contents. This guide shows how to retrieve worksheet names using Free Spire.XLS for Python.

Install Free Spire.XLS for Python 

pip install Spire.Xls.Free
Enter fullscreen mode Exit fullscreen mode

The library supports both .xls (Excel 97-2003) and .xlsx (Excel 2007+) formats. Microsoft Office is not required.

Get All Worksheet Names

The core API is straightforward:

  • Workbook.Worksheets returns the worksheet collection.
  • Each Worksheet object exposes its name through the .Name property. 
from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("test.xlsx")

names = [sheet.Name for sheet in workbook.Worksheets]
print(names)  # e.g., ['Report', 'Target Comparison', 'Actual Completion', 'Summary', 'Remarks']

workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Worksheets includes all sheets in the workbook, including hidden ones.

Get Only Hidden Worksheet Names

In some cases, you may need to handle hidden worksheets separately, such as when auditing a workbook or recovering accidentally hidden sheets. Use Worksheet.Visibility and compare it with WorksheetVisibility.Hidden.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("test.xlsx")

hidden_names = [
    sheet.Name
    for sheet in workbook.Worksheets
    if sheet.Visibility == WorksheetVisibility.Hidden
]

print(hidden_names)  # e.g., ['Summary', 'Remarks']

workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Free Version Limitations

Free Spire.XLS for Python is sufficient for basic operations such as reading worksheet names. However, its free version has limits:

  • For .xls files, each workbook supports up to 5 worksheets, and each worksheet supports up to 200 rows.
  • For .xlsx files, this limitation does not apply.

For large workbooks with many worksheets, prefer the .xlsx format.

Resource Cleanup

After using a Workbook object, call workbook.Dispose() to release memory. This is especially important when processing multiple files.

For safer cleanup, use try/finally:

workbook = Workbook()
try:
    workbook.LoadFromFile("test.xlsx")
    names = [sheet.Name for sheet in workbook.Worksheets]
    print(names)
finally:
    workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Comparison with Other Options

Option How to get sheet names Install footprint Requires Office?
Free Spire.XLS [sheet.Name for sheet in workbook.Worksheets] Medium No
openpyxl wb.sheetnames Lightweight No
pandas pd.ExcelFile(path).sheet_names Heavier (includes numpy dependency) No

When to use which

  • pandas: Best if your project already uses pandas for data analysis.
  • openpyxl: A good lightweight choice when you only need to read sheet names.
  • Free Spire.XLS: Useful when you also need read/write and formatting operations. If you later need to rename, copy, or format worksheets, you can continue using the same API and avoid switching dependencies.

Key APIs

API Purpose
Workbook.Worksheets Access all worksheets in the workbook
Worksheet.Name Get a worksheet name
Worksheet.Visibility Check whether a worksheet is hidden
WorksheetVisibility.Hidden Enum value for hidden worksheets

Summary

Extracting Excel worksheet names is a lightweight but frequent task. Free Spire.XLS for Python covers two common needs with minimal code:

  • Get all sheet names using Worksheet.Name
  • Filter hidden sheet names using Worksheet.Visibility

In real projects, choose the tool that best fits your existing Excel workflow to avoid unnecessary dependencies.

Top comments (0)