Acquire / file · All operations

List a workbook's sheets

A variant of Read a spreadsheet

Return the names of the sheets in a workbook rather than their contents.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today, with the excel and zipfs extensions.

The DuckDB step

List the sheets of a workbook with no rows read

SQL
INSTALL excel; LOAD excel;
INSTALL zipfs FROM community; LOAD zipfs;
-- a workbook is a zip and lists its sheets in xl/workbook.xml, in the order they appear in the file
CREATE TEMP TABLE result AS
SELECT position, regexp_extract(s, 'name="([^"]*)"', 1) AS sheet_name
FROM (SELECT unnest(arr) AS s, generate_subscripts(arr, 1) AS position
      FROM (SELECT regexp_extract_all(content, '<sheet [^>]*/>') AS arr FROM read_text('zip://<your_scratch_dir>/sheets.xlsx.zip/xl/workbook.xml')));
SELECT * FROM result ORDER BY position;
SELECT CASE WHEN ((SELECT list(sheet_name ORDER BY position) FROM result) = ['Orders', 'Totals', 'Notes'] AND (SELECT count(*) = 1 AND max(region) = 'north' FROM read_xlsx('<your_scratch_dir>/sheets.xlsx', sheet = 'Totals'))) THEN 'as expected' ELSE error('the workbook did not list its sheets Orders, Totals and Notes, or a listed sheet could not be read by name') END AS verdict;

Replace <your_scratch_dir> with a directory of your own that you can write to before running the statement.

  • It reads these from <your_scratch_dir>, which you supply:
    • sheets.xlsx: An Excel workbook of three sheets: Orders (order_id, customer; one row 1, Alice Ng), Totals (region, total; one row north, 420) and Notes (note; one row none).
    • sheets.xlsx.zip: The same bytes as sheets.xlsx, under a .zip name, for an extension that opens archives by that name.
  • It needs the excel and zipfs extensions.
  • The statement checks its own result and raises an error when it is wrong: “the workbook did not list its sheets Orders, Totals and Notes, or a listed sheet could not be read by name”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

KNIME Analytics Platform (1)
RapidMiner Studio (2)

Background

KNIME's Read Excel Sheet Names node reads an Excel file and outputs the names of the sheets it contains.[1] RapidMiner's Read Excel Sheet Names operator returns the sheets of an Excel file as a table with two attributes, Sheet Number and Sheet Name.[2]

When to use it

DuckDB's read_xlsx function reads the first sheet of an .xlsx file unless its sheet option names another.[3] List a workbook's sheets first when it arrives with tabs whose names or order you do not know, then read each sheet by name. (Meridian's view)

KNIME's Excel Reader orders sheets as Excel displays them, which is not necessarily lexicographic order.[4] When KNIME's Excel Reader reads several files by sheet name, it fails if any of them does not contain a sheet with that name.[4] Across a folder of workbooks, list each one's sheets before reading, so a renamed tab is found and reported rather than stopping the run. (Meridian's view)

In Office Open XML (OOXML), standardised as ISO/IEC 29500, a sheet element has a state attribute whose default value is visible.[5][6] The OOXML sheet state values are visible, hidden and veryHidden.[7] RapidMiner's List Google Sheets operator lists each sheet of a Google Sheets spreadsheet with its title, type and visibility.[8] RapidMiner can read and update only Google Sheets sheets of type GRID.[8] Keep each sheet's visibility beside its name, because a hidden sheet may hold lookups or workings rather than the data you came for. (Meridian's view)

KNIME's Read Excel Sheet Names node is limited in performance by its underlying Apache POI library, and reading large files with it can take a long time.[1]

The method

A SpreadsheetML document consists of a workbook element containing sheets and sheet elements that reference the worksheets in the workbook.[9] Inside the .zip package of a .xlsx workbook, the workbook.xml file contains the sheet elements that reference the worksheets.[9] Each SpreadsheetML sheet element carries a sheet ID, unique within the workbook, and a relationship ID that identifies the part defining that sheet.[9] A SpreadsheetML sheet element's name attribute is required, and the name must be unique.[6]

In the Google Sheets API, spreadsheets.get returns a spreadsheet without the data in its grids by default.[10] A Google Sheets spreadsheet can contain multiple sheets, each with a unique numeric sheet ID and a sheet title.[11]

Compared with the alternatives

In pandas, read_excel with sheet_name set to None returns a dictionary containing a DataFrame for each sheet.[12] Reading every sheet at once only stands in for a list of names when every sheet holds a table; a list lets you choose before anything is parsed. (Meridian's view)

Sources

  1. [1]Read Excel Sheet Namesdocumentation
  2. [2]Read Excel Sheet Names - Rapidminer Documentationdocumentation
  3. [3]Excel Extension – DuckDBdocumentation
  4. [4]Excel Readerdocumentation
  5. [5]Office Open XML - Wikipediasecondary source
  6. [6]Sheet Class (DocumentFormat.OpenXml.Spreadsheet) | Microsoft Learndocumentation
  7. [7]SheetStateValues Enum (DocumentFormat.OpenXml.Spreadsheet) | Microsoft Learndocumentation
  8. [8]List Google Sheets - Rapidminer Documentationdocumentation
  9. [9]Structure of a SpreadsheetML document | Microsoft Learndocumentation
  10. [10]Method: spreadsheets.get | Google Sheets | Google for Developersdocumentation
  11. [11]Google Sheets API Overview | Google for Developersdocumentation
  12. [12]pandas.read_excel — pandas 3.1.1 documentationdocumentation