Excel Integration Overview

Erik's ToolBox includes built-in Excel integration that lets you read and write spreadsheet data directly from AL scripts within Business Central. You can upload Excel files, interact with cell data using simple AL functions, attach Excel sheets to saved programs, and download results -- all without leaving the Business Central web client.

This page provides a high-level introduction to the Excel integration feature, how it works, and what you can accomplish with it.

What Is Excel Integration?

Excel integration in Erik's ToolBox bridges the gap between Business Central data and Excel spreadsheets. Instead of exporting data to Excel externally, editing it, and then importing it back, you can handle the entire workflow within a single ToolBox script.

The integration is built around two core components:

Component	Description
Excel Page	A Business Central page that provides a spreadsheet viewer and management interface within the ToolBox. You use this page to upload, view, and download Excel files.
Excel Codeunit (ToolBox Excel)	The backend codeunit that manages Excel data in memory, handles file I/O operations, and exposes the cell-level functions that your AL scripts call.

Together, these components give you a complete Excel processing environment embedded inside Business Central.

How It Works

The Excel integration follows a straightforward workflow:

Load data -- You upload an Excel file from your local machine into the ToolBox, or your script generates data from scratch by writing to cells.
Process data -- Your AL script reads cell values using exget(), writes values using exset(), and empties the sheet using exclear(). You can combine these operations with any standard AL logic, including record operations, loops, and conditionals.
View results -- You call exopen() to open the Excel viewer page and inspect the spreadsheet data visually.
Export results -- You download the processed Excel file to your local machine using the Download action on the Excel page.

All Excel data is held in memory during script execution and can be persisted by attaching it to a saved program.

Core AL Functions

The ToolBox runtime provides four built-in functions for working with Excel cell data:

Function	Purpose
`exget('A1')` / `exget(row, col)`	Read the value of a specific cell
`exset('A1', value)` / `exset(row, col, value)`	Write a value to a specific cell
`exclear()`	Clear all cells of the current sheet
`exopen()` / `exopen(caption)`	Open the Excel viewer page to display the current sheet

Cells are addressed either by a cell reference such as 'C2' or by 1-based row and column numbers (row 1, column 1 is cell A1). The row/column form of exget() always returns text, so use Evaluate() to convert numbers and dates; the cell-reference form returns a typed value when assigned to a Decimal, Integer, Date, or Time variable. exset() stores numeric, date, time, and boolean values with their native cell type.

For detailed documentation of each function, including parameters, return values, and examples, see the Excel Functions Reference.

The Excel Page

The Excel page shows the current sheet in a spreadsheet viewer and provides these controls:

Control	Description
Upload action	Opens a file dialog so you can select an Excel file from your local machine and load it into the ToolBox
Download action	Saves the current Excel data as a file to your local Downloads folder
Clear action	Removes all cell data from the current sheet after a confirmation, giving you a blank slate
Scroll Left / Scroll Right actions	Move the visible column range of the viewer one column at a time
Sheet field	Shows the active sheet. When a workbook with several sheets has been uploaded, drill down on the field to pick a different sheet
File name field	The name used when you download the data (editable)

These controls give you full control over the Excel file lifecycle -- from loading data to configuring the output and saving results.

Use Cases

Excel integration supports a wide range of practical scenarios in Business Central:

Importing Data from Excel

You can upload an Excel file containing data from an external source and use a ToolBox script to read each row and create or update Business Central records. This is useful for:

Importing customer or vendor master data from external systems
Loading price lists maintained in spreadsheets
Processing data migration files during implementation projects
Reading configuration data provided by clients in Excel format

Exporting Data to Excel

You can use a script to query Business Central data, write it to Excel cells, and then download the result. This is useful for:

Creating custom reports that are not available in the standard report library
Exporting data extracts for auditors or external stakeholders
Generating data files for integration with systems that consume Excel input
Building summary dashboards with calculated values

Processing and Transforming Data

You can upload an Excel file, transform the data using AL logic, and then either import the transformed data into Business Central or download the modified file. This is useful for:

Cleaning and validating data before import
Applying business rules to spreadsheet data
Reformatting data from one layout to another
Performing calculations across Excel rows and columns

Creating Reports

You can build structured Excel reports entirely from script logic by writing headers, data rows, and summary calculations to cells. This gives you complete control over the report layout without needing to design a formal Business Central report object.

Excel Data Storage

Excel data in ToolBox is stored as a binary large object (blob) in the Program table. When you save a program that uses Excel functions, the associated Excel data is preserved alongside the program code.

The Program table includes two fields that manage this:

Field	Type	Description
WithExcelSheet	Boolean	Indicates whether the program has an associated Excel sheet
ExcelSheet	Blob	Contains the binary Excel data attached to the program

When you load a saved program that has WithExcelSheet set to true, the ToolBox automatically loads the associated Excel data into memory, making it immediately available to your script through exget() and the other Excel functions.

This persistence mechanism means you can create programs that come pre-loaded with reference data, templates, or input files. Users who run these programs will have the Excel data available without needing to upload a file each time.

Multi-Sheet Support

Excel workbooks often contain multiple sheets, and the ToolBox supports working with them:

When you upload a workbook with multiple sheets, you are asked which sheet to load.
The Sheet field on the Excel page shows which sheet is currently active.
Drill down on the Sheet field to switch to a different sheet in the uploaded workbook.
All exget(), exset(), and exclear() operations apply to the currently active sheet.

This lets you process workbooks that organize data across multiple sheets, such as a workbook with separate sheets for different months, regions, or data categories.

Data Type Handling

exget(row, col) always returns a Text value, even if the cell contains a number or date. Use Evaluate() to convert the returned text to the appropriate data type for calculations or comparisons.
exget('A1') returns a typed value when assigned to a non-text variable: numeric cells become Decimal (or a rounded Integer), date cells Date, and time cells Time.
exset() stores Boolean, Integer, Decimal, Date, Time, DateTime, Duration, and Option values with their native cell type; other values are stored as text. Pass Format(Value, ...) when you want a particular text layout.

See the Excel Functions Reference for details.

Performance Considerations

When working with large Excel files, keep the following in mind:

Cell-by-cell operations -- Each call to exget() or exset() processes a single cell. For large datasets, this means many individual function calls. Structure your code to minimize redundant reads.
Memory -- Excel data is held in memory during script execution. Very large spreadsheets with thousands of rows and many columns will consume more server memory.
Bulk patterns -- When writing data, prepare your values in variables first and then write them to cells in a tight loop. This is more efficient than interleaving reads and writes.

For most practical use cases -- importing a few hundred to a few thousand rows -- performance is not a concern. The Excel functions execute quickly and the overhead per cell is minimal.

Getting Started with Excel

To start using Excel integration in your ToolBox scripts:

Open the ToolBox interactive code editor in Business Central.
Write a script that uses exset() to populate some cells, then calls exopen() to view the result.
Experiment with exget() by uploading an Excel file and reading its contents in a script.
Explore the Excel page actions Upload, Download, and Clear to manage your files.

For step-by-step instructions, see the Working with Excel guide.

For a complete reference of all Excel functions, see the Excel Functions Reference.