Introduction to the ExcelDataProvider Module

The ExcelDataProvider module provides a data provider API for reading and writing Excel spreadsheets through the DataProvider API. It supports both modern Excel formats (.xlsx, Excel 2007+) and legacy formats (.xls, Excel 97-2003).

The following classes are provided by this module:

ExcelReadDataProvider - for reading Excel files
ExcelReadDataProviderFactory - factory for read providers
ExcelWriteDataProvider - for writing Excel files
ExcelWriteDataProviderFactory - factory for write providers
ExcelWorksheetReadDataProvider - for reading specific worksheets

Reading Excel Files

Basic Reading

%requires ExcelDataProvider
 
# Read from a file path
ExcelReadDataProvider dp("data.xlsx", {"header_names": True});
list<hash<auto>> records = map $1, dp.searchRecords();
 
# Read from a stream
FileInputStream fis("data.xlsx");
ExcelReadDataProvider dp2(fis, {"header_names": True});
 
# Read from binary data
binary data = File::readBinaryFile("data.xlsx");
ExcelReadDataProvider dp3({"data": data, "header_names": True});

Read Options

path: Path to the Excel file
stream: Input stream for Excel data
data: Binary Excel data
worksheet: Name of the worksheet to read (default: first sheet)
header_names: If True, use first row as header names
header_cells: Cell range for headers (e.g., "a1:e1" or just "1" for row 1)
data_cells: Cell range for data (e.g., "a2:e100")
headers: List of header names to use
tolwr: Convert header names to lowercase
timezone: Timezone for date values

Multi-Sheet Access

ExcelReadDataProvider dp("workbook.xlsx");
 
# List all worksheets
list<string> sheets = dp.getChildProviderNames();
 
# Access specific worksheet
AbstractDataProvider sheet = dp.getChildProvider("Sheet2");
list<hash<auto>> records = map $1, sheet.searchRecords();

Writing Excel Files

Basic Writing

%requires ExcelDataProvider
 
# Write to a file
ExcelWriteDataProvider dp("output.xlsx", {
    "worksheet": "Data",
    "headers": ("Name", "Age", "City"),
});
 
dp.createRecord({"Name": "Alice", "Age": 30, "City": "New York"});
dp.createRecord({"Name": "Bob", "Age": 25, "City": "Boston"});
 
dp.commit();  # Write the file

Write Options

path: Output file path
stream: Output stream
worksheet: Worksheet name (default: "Sheet1")
headers: List of column headers
format: Output format - "xlsx" (default) or "xls"
streaming: Use streaming mode for large files (reduces memory usage)

Output Formats

The write provider supports two formats:

xlsx: Modern Excel format (Excel 2007+, default)
xls: Legacy Excel format (Excel 97-2003)

# Write legacy .xls format

ExcelWriteDataProvider dp("legacy.xls", {"format": "xls"});

Supported Data Types

The module automatically handles the following Qore data types:

Strings
Integers and floats (as numeric cells)
Booleans
Dates (with timezone support)
Formulas (read: evaluates to result; write: as calculated value)

Error Handling

Common exceptions:

EXCEL-READ-OPTION-ERROR: Invalid read options or option conflicts
EXCEL-WRITE-OPTION-ERROR: Invalid write options or option conflicts
EXCEL-INVALID-LOCATION: Invalid cell range format

Release Notes

ExcelDataProvider v1.1

added ExcelWriteDataProvider for writing Excel files
added ExcelWriteDataProviderFactory
added support for XLS (Excel 97-2003) format in both read and write operations
added tolwr option to convert header names to lowercase
added header_names option that defaults to row 1 when no header_cells specified
added data option to read from binary data
added streaming option for memory-efficient large file writes
added mutual exclusion validation for conflicting options
fixed namespace in ExcelReadDataProviderFactory (was incorrectly CsvUtil)
fixed typos in option names and comments
fixed Java error message for unknown worksheet
fixed resource leak in getWorksheets() methods

ExcelDataProvider v1.0

initial release of the module

Table of Contents