Excel Functions Reference
This page provides a complete reference for the built-in Excel functions available in Erik's ToolBox AL scripts. These functions let you read and write cell data in the ToolBox Excel sheet directly from your code, clear the sheet, and open the Excel viewer to inspect results.
All Excel functions are part of the ToolBox runtime and do not require any additional imports or library references. You can call them directly from your script or any procedure.
Function Summary
| Function | Syntax | Description |
|---|---|---|
exget |
exget('A1') or exget(row, col) |
Read the value of a cell |
exset |
exset('A1', value) or exset(row, col, value) |
Write a value to a cell |
exclear |
exclear() |
Clear all cells of the current sheet |
exopen |
exopen() or exopen(caption) |
Open the Excel viewer page |
Cells can be addressed in two ways:
- Cell reference -- a text such as
'A1','C12', or'AB3': column letters followed by the row number. - Row and column numbers -- two integers, both 1-based. Row 1, column 1 is cell A1; column 27 is AA.
exget
Reads the value of a cell from the currently loaded Excel sheet.
Syntax
Value := exget(Cell); // cell reference, e.g. 'B2'
Value := exget(Row, Col); // 1-based row and column numbersParameters
| Parameter | Type | Description |
|---|---|---|
| Cell | Text | A cell reference such as 'A1'. |
| Row | Integer | The row number of the cell to read. 1-based (row 1 is the first row). |
| Col | Integer | The column number of the cell to read. 1-based (column 1 is column A). |
Return Value
The two forms return differently typed values:
| Form | Returns |
|---|---|
exget(Row, Col) |
Always Text. Numbers and dates come back as their text representation. |
exget(Cell) assigned to a Text or Code variable |
Text |
exget(Cell) assigned to any other type |
A typed value based on the cell type: a Number cell becomes Decimal (or Integer, rounded, when the target is an Integer), a Date cell becomes Date, a Time cell becomes Time, and a Text cell becomes Text. |
An empty or missing cell returns an empty string ('') in both forms.
Remarks
- With the row/column form, use
Evaluate()to convert the returned text to a number or date. - With the cell-reference form, assign directly to a
Decimal,Integer,Date, orTimevariable when the cell holds that kind of data. If the cell type does not match the variable type, a type error is raised. - If you specify a cell outside the populated range,
exget()returns an empty string. exget()reads from the currently active sheet. Switch sheets by drilling down on the Sheet field on the Excel page before reading.
Example
var
AmountText: Text;
Amount: Decimal;
begin
// Row/column form: always text, convert yourself
AmountText := exget(2, 3); // Read cell C2
if AmountText <> '' then begin
Evaluate(Amount, AmountText);
Message('Amount is: %1', Amount);
end else
Message('Cell C2 is empty');
// Cell-reference form: typed read from a numeric cell
Amount := exget('C2');
Message('Amount (typed): %1', Amount);
end;exset
Writes a value to a cell in the currently loaded Excel sheet.
Syntax
exset(Cell, Value); // cell reference, e.g. 'B2'
exset(Row, Col, Value); // 1-based row and column numbersParameters
| Parameter | Type | Description |
|---|---|---|
| Cell | Text | A cell reference such as 'A1'. |
| Row | Integer | The row number of the cell to write to. 1-based. |
| Col | Integer | The column number of the cell to write to. 1-based. |
| Value | Any | The value to write. Boolean, Integer, Decimal, Date, Time, DateTime, Duration, and Option values are stored with their native cell type; every other type is stored as text. |
Remarks
- If the cell already contains a value,
exset()overwrites it. - Typed values keep their type in the sheet, so a
Decimalbecomes a numeric cell and aDatea date cell. PassFormat(Value, ...)when you want a specific text layout instead. - You do not need to upload an Excel file before calling
exset(). The first call creates an in-memory sheet namedSheet1with the file nametoolboxdata.xlsx.
Example
begin
exset('A1', 'Name'); // Text header
exset('B1', 'Amount'); // Text header
exset(2, 1, 'Northwind'); // Text value (row/column form)
exset(2, 2, 1500.75); // Decimal value
exset('C2', Today()); // Date value
exopen();
end;exclear
Clears all cells of the current sheet, leaving it empty.
Syntax
exclear();Remarks
exclear()takes no parameters and removes every cell. There is no function to clear a single cell; write an empty text to it instead:exset('A1', '').- After calling
exclear(),exget()returns an empty string for every cell. - Clearing an already empty sheet has no effect and does not produce an error.
Example
begin
exset(1, 1, 'Temporary value');
Message('Before clear: %1', exget(1, 1));
exclear();
Message('After clear: [%1]', exget(1, 1)); // Shows []
end;exopen
Opens the Excel viewer page to display the currently loaded sheet data in a spreadsheet-style interface within Business Central.
Syntax
exopen();
exopen(Caption);Parameters
| Parameter | Type | Description |
|---|---|---|
| Caption | Text | Optional. A caption for the Excel viewer page. |
Remarks
exopen()displays the Excel page as a modal page. Script execution pauses until you close the Excel viewer.- You typically call
exopen()at the end of a script after populating cells, so you can review the results before downloading. - If no data has been written to any cells, the viewer opens with an empty sheet.
- From the Excel viewer, you can use the Download action to save the data as an Excel file, the Upload action to load a workbook, and the Clear action to empty the sheet.
exopen()requires a user session. It cannot be used in Job Queue runs.
Example
begin
exset(1, 1, 'Product');
exset(1, 2, 'Price');
exset(2, 1, 'Widget');
exset(2, 2, 29.99);
exopen('Price list'); // Opens the viewer to show the data
end;Complete Examples
Example 1: Read and Process an Uploaded Excel File
This script reads customer data from an uploaded Excel file and displays each row. It assumes the file has been uploaded via the Excel page before running the script.
var
CustomerName: Text;
Row: Integer;
begin
Row := 2; // Skip header row
while exget(Row, 1) <> '' do begin
CustomerName := exget(Row, 1);
Message('Row %1: %2', Row, CustomerName);
Row += 1;
end;
end;Example 2: Export Business Central Data to Excel
This script queries the Customer table and writes the data to Excel cells, then opens the viewer so you can download the file.
var
Customer: Record Customer;
Row: Integer;
begin
// Write header row
exset('A1', 'No.');
exset('B1', 'Name');
exset('C1', 'Balance');
// Write data rows
Row := 2;
if Customer.FindSet() then
repeat
Customer.CalcFields("Balance (LCY)");
exset(Row, 1, Customer."No.");
exset(Row, 2, Customer.Name);
exset(Row, 3, Customer."Balance (LCY)"); // stored as a numeric cell
Row += 1;
until Customer.Next() = 0;
Message('Exported %1 customers', Row - 2);
exopen();
end;Example 3: Data Validation Script
This script reads an uploaded Excel file, validates each row, and writes results to a validation column.
var
CustomerNo: Text;
Amount: Text;
AmountDec: Decimal;
Row: Integer;
ErrorCount: Integer;
begin
exset(1, 4, 'Validation Result');
Row := 2;
while exget(Row, 1) <> '' do begin
CustomerNo := exget(Row, 1);
Amount := exget(Row, 3);
if CustomerNo = '' then begin
exset(Row, 4, 'ERROR: Customer No. is required');
ErrorCount += 1;
end else
if not Evaluate(AmountDec, Amount) then begin
exset(Row, 4, 'ERROR: Invalid amount');
ErrorCount += 1;
end else
exset(Row, 4, 'OK');
Row += 1;
end;
Message('Validation complete. %1 issues found.', ErrorCount);
exopen();
end;Tips for Working with Cell References
- Row and column numbers are 1-based. The first row is 1, the first column is 1. There is no row 0 or column 0.
- Column mapping. Column 1 = A, Column 2 = B, Column 3 = C, and so on. Column 26 = Z, Column 27 = AA.
- Use cell references for fixed cells such as headers and totals (
exset('A1', 'Total')), and row/column numbers when looping. - Use named variables for column numbers. Instead of magic numbers like
exget(Row, 3), assign descriptive variable names to improve readability:
var
ColName: Integer;
ColCity: Integer;
Row: Integer;
begin
ColName := 1;
ColCity := 2;
Row := 2;
while exget(Row, ColName) <> '' do begin
Message('%1 in %2', exget(Row, ColName), exget(Row, ColCity));
Row += 1;
end;
end;- Detecting the end of data. A common pattern is to loop while
exget(Row, 1) <> '', incrementing the row number each iteration. This works when your first column always has a value for populated rows.
Data Type Handling
- Reading with row/column numbers:
exget(Row, Col)always returnsText. To use the value as a number, date, or boolean, callEvaluate()to convert it. - Reading with a cell reference:
exget('B2')returns a typed value when the target variable is notText/Code: numeric cells giveDecimalorInteger, date cells giveDate, time cells giveTime. - Writing:
exset()stores Boolean, Integer, Decimal, Date, Time, DateTime, Duration, and Option values with their native cell type and everything else as text. CallFormat()first when you need a specific text layout.
Common Conversion Patterns
var
DecValue: Decimal;
DateValue: Date;
CellText: Text;
begin
// Reading a decimal from Excel (row/column form)
CellText := exget(2, 1);
if CellText <> '' then
Evaluate(DecValue, CellText);
// Reading a decimal from Excel (typed cell-reference form)
DecValue := exget('A2');
// Writing with explicit formatting
exset(3, 1, Format(DecValue, 0, '<Precision,2:2><Standard Format,0>'));
exset(3, 2, Format(DateValue, 0, '<Day,2>-<Month,2>-<Year4>'));
end;Performance Tips
- Minimize redundant reads. If you need the same cell value multiple times, read it once into a variable rather than calling
exget()repeatedly. - Batch your writes. Structure your code as a tight loop that writes all values sequentially.
- Use early exit. When scanning rows, break out of the loop as soon as you find what you need.
- Pre-calculate values. Perform calculations in variables before writing to cells.