Excel Reader Reference

A class to read and extract data from Excel files (.xlsx, .xlsm, .xlsb).

This class provides methods to load workbooks, extract data from sheets, tables, and named ranges, with robust error handling.

__init__(file_path, file_mode='read')

Initialize the ExcelReader with the path to the Excel file.

Warning: file_mode must be set to 'write' to use the write_table method.

Parameters:

Name	Type	Description	Default
file_path	Path | str	Path to the Excel file, specified as a Path object or string.	required
file_mode	str	Mode of operation, either 'read' (default) or 'write'.	'read'

Raises:

Type	Description
ImportError	If the file does not exist, is not a valid Excel file.
RuntimeError	If file_mode is not 'read' or 'write'.

extract_data(source_name, source_type)

Extract data from an Excel file based on the source type and name.

Expected the specified source type to be either: - An entire worksheet with the header in the first row (sheet) - A named range (range) - An Excel table (table)

Parameters:

Name	Type	Description	Default
source_name	str	Name of the sheet, table, or range to extract.	required
source_type	str	Type of source ('sheet', 'table', or 'range').	required

Returns:

Type	Description
list[dict]	Data extracted as a list of dictionaries.

Raises:

Type	Description
ImportError	If the source type is invalid or if there are issues extracting data.

extract_from_range(range_name)

Extract a named range from an Excel file and return it as a list of dictionaries.

Parameters:

Name	Type	Description	Default
range_name	str	Name of the range to extract.	required

Returns:

Type	Description
list[dict]	A list containing the range data as dictionaries.

Raises:

Type	Description
ImportError	If the range cannot be loaded or if there are issues with the file.

extract_from_sheet(sheet_name)

Extract a sheet from an Excel file and return it as a list of dictionaries.

Parameters:

Name	Type	Description	Default
sheet_name	str	Name of the sheet to extract.	required

Returns:

Type	Description
list[dict]	A list containing the sheet data as dictionaries.

Raises:

Type	Description
ImportError	If the sheet cannot be loaded or if there are issues with the file.

extract_from_table(table_name)

Extract a table from an Excel file and return it as a list of dictionaries.

Parameters:

Name	Type	Description	Default
table_name	str	Name of the table to extract.	required

Returns:

Type	Description
list[dict]	A list containing the table data as dictionaries.

Raises:

Type	Description
ImportError	If the table cannot be loaded or if there are issues with the file.

load_excel_workbook(*, data_only=True, read_only=False)

Load an Excel workbook with robust error handling.

Parameters:

Name	Type	Description	Default
data_only	bool	Whether to return cell values (not formulas).	True
read_only	bool	Use openpyxl's read-only mode for large files.	False

Returns:

Name	Type	Description
workbook	Workbook	Workbook object.

Raises:

Type	Description
ImportError	If the file cannot be loaded due to various reasons.

write_table(sheet_name, table_name, columns, rows, *, freeze_header=True)

Write a list of rows to an Excel file as a formatted table.

If the file already exists, it will be overwritten. The method creates a new workbook and a new sheet with the specified name and writes the data as an Excel Table (ListObject) with the given table name.

See the test_write_table() test case for an example of how to use this method.

Parameters:

Name	Type	Description	Default
sheet_name	str	Name of the worksheet.	required
table_name	str	Name of the Excel Table (ListObject) created on the sheet.	required
columns	list[str]	List of column header strings.	required
rows	list[list[Any]]	List of row data (each row is a list aligned with columns).	required
freeze_header	bool	Whether to freeze the first row.	True

Raises:

Type	Description
RuntimeError	If the ExcelReader instance is not in 'write' mode.