Working with Excel

This page provides practical, step-by-step guidance for working with Excel sheets in Erik's ToolBox. You will learn how to upload and download files, work with multiple sheets, attach Excel data to saved programs, and follow common patterns for data import, export, and processing workflows.

Uploading an Excel File

Before your script can read data from an Excel file, you need to upload the file into the ToolBox.

Open the ToolBox interactive code editor in Business Central.
Navigate to the Excel page (Excel) from the ToolBox menu. Alternatively, you can call exopen() from a script to open the Excel page directly.
On the Excel page, select the Upload action from the action bar.
A file dialog opens. Browse to the Excel file on your local machine and select it.
If the workbook contains several sheets, you are asked which sheet to load.
The ToolBox loads the sheet and displays its contents in the spreadsheet viewer.

Once the file is uploaded, the data is available in memory and your scripts can access it using exget() and exset().

Upload Tips

Make sure your Excel file is saved and closed before uploading. File locks may prevent the upload if the file is open in another application.
If your file has multiple sheets, only one sheet is active at a time. You can switch between sheets by drilling down on the Sheet field.
Large files with many thousands of rows will take longer to upload. For very large datasets, consider splitting the data into smaller files.

Downloading Results

After your script has written data to Excel cells, you can download the result as an Excel file.

Run your script to populate the Excel cells. The script should call exopen() at the end to open the Excel viewer.
On the Excel page, review the data in the spreadsheet viewer to confirm it looks correct.
If you want a specific file name, type it into the File name field before downloading.
Select the Download action from the action bar.
The file is saved to your browser's default Downloads folder.

Download Tips

Always review the data in the viewer before downloading to catch formatting issues or missing data.
Use the File name field to give the downloaded file a meaningful name. If you do not set a file name, the ToolBox uses toolboxdata.xlsx.
The downloaded file is a standard Excel file that you can open in Microsoft Excel, Google Sheets, or any other spreadsheet application.

Working with Multiple Sheets

Excel workbooks can contain multiple sheets, and the ToolBox supports switching between them.

Viewing the Current Sheet Name

The Sheet field at the top of the Excel page shows which sheet is currently active. This is the sheet that your exget() and exset() calls are operating on.

Switching Sheets

Open the Excel page by calling exopen() or navigating to it from the ToolBox menu.
Drill down on the Sheet field (this is only possible after a workbook has been uploaded).
A list of available sheets in the workbook is displayed.
Select the sheet you want to work with.
The viewer updates to show the selected sheet's data.

After switching sheets, all subsequent exget(), exset(), and exclear() calls operate on the newly selected sheet.

Multi-Sheet Processing Pattern

If your script needs to process data from multiple sheets, you can process the first sheet, call exopen() to let the user switch sheets, then continue processing the new sheet after the viewer is closed.

var
    Row: Integer;
    SheetTotal: Decimal;
    CellValue: Text;
begin
    // Process the currently active sheet
    Row := 2;
    while exget(Row, 1) <> '' do begin
        CellValue := exget(Row, 2);
        if CellValue <> '' then
            Evaluate(SheetTotal, CellValue);
        Row += 1;
    end;
    Message('Current sheet total: %1', SheetTotal);

    // Open viewer so the user can switch sheets
    exopen();

    // Process the newly selected sheet
    Row := 2;
    SheetTotal := 0;
    while exget(Row, 1) <> '' do begin
        CellValue := exget(Row, 2);
        if CellValue <> '' then
            Evaluate(SheetTotal, CellValue);
        Row += 1;
    end;
    Message('Next sheet total: %1', SheetTotal);
end;

Attaching Excel Data to Programs

One of the most powerful features of the Excel integration is the ability to attach Excel sheet data to saved programs. Your program can include pre-loaded data that is available every time someone runs it.

How Attachment Works

When you save a program that has been working with Excel data, the ToolBox stores the Excel data alongside the program in the Program table:

Field	Type	Purpose
WithExcelSheet	Boolean	Set to `true` when the program has attached Excel data
ExcelSheet	Blob	Contains the binary Excel data

Saving a Program with Excel Data

Upload an Excel file or populate cells using your script.
Write and test your script in the ToolBox editor.
Save the program using the standard Save action.
The ToolBox automatically detects that Excel data is present and attaches it to the saved program.

Loading a Program with Excel Data

Open the program list and select a program that has attached Excel data.
The ToolBox loads the program code and the attached Excel data automatically.
The Excel data is available in memory. Your script can immediately call exget() to read the attached data.
You do not need to upload a file manually.

Use Cases for Attached Excel Data

Template programs: Create a program with a pre-defined Excel template that users fill in with new data each time.
Reference data: Attach a lookup table or mapping file that the script references during processing.
Sample data: Distribute a program with sample data so users can test it without preparing a file.
Report templates: Attach a formatted template that the script populates with live Business Central data.

Clearing Data

Clear All Cells from a Script

Use exclear() in your script to empty the current sheet before writing new data:

begin
    exclear();                 // Start with a blank sheet
    exset('A1', 'Report');
end;

Clear a Single Cell

There is no function that clears one cell. Write an empty text to it instead:

begin
    exset('A1', '');           // Clear cell A1
    exset(2, 3, '');           // Clear cell C2
end;

Clear All Cells from the Page

Use the Clear action on the Excel page to clear all cells on the current sheet at once. This gives you a completely blank sheet, which is useful when you want to start fresh before writing new data.

Setting the File Name

By default, the ToolBox names the downloaded file toolboxdata.xlsx. You can set a custom file name in the File name field on the Excel page before you download.

Setting a descriptive file name is especially useful when generating reports or exports. A name like "Customer_Balance_Report_2024.xlsx" is much more useful than a default name.

Scrolling the Viewer

The Scroll Left and Scroll Right actions on the Excel page move the visible column range one column at a time, so you can review wide sheets in the viewer.

Common Patterns

Pattern 1: Import Excel Data into Business Central

Upload an Excel file and use a script to read each row and create Business Central records.

var
    Item: Record Item;
    Row: Integer;
    ItemNo: Text;
    UnitPriceText: Text;
    ImportCount: Integer;
begin
    Row := 2; // Skip header row
    while exget(Row, 1) <> '' do begin
        ItemNo := exget(Row, 1);
        if not Item.Get(ItemNo) then begin
            Item.Init();
            Item."No." := CopyStr(ItemNo, 1, 20);
            Item.Description := CopyStr(exget(Row, 2), 1, 100);
            UnitPriceText := exget(Row, 3);
            if UnitPriceText <> '' then
                Evaluate(Item."Unit Price", UnitPriceText);
            Item.Insert(true);
            ImportCount += 1;
        end;
        Row += 1;
    end;
    Message('Import complete. %1 items created.', ImportCount);
end;

Pattern 2: Export Business Central Data to Excel

Query a table and write the results to Excel for download.

var
    Vendor: Record Vendor;
    Row: Integer;
begin
    exset(1, 1, 'Vendor No.');
    exset(1, 2, 'Name');
    exset(1, 3, 'City');
    exset(1, 4, 'Balance (LCY)');

    Row := 2;
    if Vendor.FindSet() then
        repeat
            exset(Row, 1, Vendor."No.");
            exset(Row, 2, Vendor.Name);
            exset(Row, 3, Vendor.City);
            exset(Row, 4, Format(Vendor."Balance (LCY)"));
            Row += 1;
        until Vendor.Next() = 0;

    Message('Exported %1 vendors.', Row - 2);
    exopen();
end;

Pattern 3: Data Validation Before Import

Validate uploaded Excel data and flag issues before creating records.

var
    CustomerNo: Code[20];
    Customer: Record Customer;
    Row: Integer;
    Errors: Integer;
begin
    exset(1, 5, 'Validation');
    Row := 2;
    while exget(Row, 1) <> '' do begin
        CustomerNo := CopyStr(exget(Row, 1), 1, 20);
        if CustomerNo = '' then begin
            exset(Row, 5, 'FAIL: Missing customer number');
            Errors += 1;
        end else if not Customer.Get(CustomerNo) then begin
            exset(Row, 5, 'FAIL: Customer not found');
            Errors += 1;
        end else
            exset(Row, 5, 'PASS');
        Row += 1;
    end;

    if Errors > 0 then
        Message('Validation found %1 errors. Review the Validation column.', Errors)
    else
        Message('All %1 rows passed validation.', Row - 2);
    exopen();
end;

Pattern 4: Report Generation

Build structured reports entirely in Excel using script logic.

var
    Customer: Record Customer;
    TotalBalance: Decimal;
    Row: Integer;
begin
    exset(1, 1, 'Customer Balance Report');
    exset(2, 1, 'Date: ' + Format(Today()));
    exset(4, 1, 'No.');
    exset(4, 2, 'Name');
    exset(4, 3, 'Balance (LCY)');

    Row := 5;
    if Customer.FindSet() then
        repeat
            Customer.CalcFields("Balance (LCY)");
            exset(Row, 1, Customer."No.");
            exset(Row, 2, Customer.Name);
            exset(Row, 3, Format(Customer."Balance (LCY)"));
            TotalBalance += Customer."Balance (LCY)";
            Row += 1;
        until Customer.Next() = 0;

    Row += 1;
    exset(Row, 1, 'Total');
    exset(Row, 3, Format(TotalBalance));
    exopen();
end;

Pattern 5: Data Migration with Verification

Use Excel as an intermediate verification step between source and target tables:

Run a script to export source data to Excel.
Review the data in the Excel viewer and make any manual corrections.
Run a second script to import the verified Excel data into the target table.

This approach gives you a visual checkpoint between read and write steps, which is valuable for data migrations where accuracy is critical.

Tips and Best Practices

Always include a header row. Write descriptive column headers in row 1 to make downloaded files self-documenting.
Set the file name before downloading. Use the File name field so recipients know what the file contains.
Validate before importing. Run a validation pass over uploaded data before creating or modifying Business Central records.
Use the Excel viewer for debugging. Call exopen() at strategic points in your script to inspect the current state of the data.
Attach reference data to programs. If your program needs a lookup table that does not change often, attach it as an Excel sheet.
Handle empty cells gracefully. Always check for empty strings when reading cells with exget(). Attempting to evaluate an empty string will cause errors.
Read typed values directly. Amount := exget('C2'); gives you a Decimal straight from a numeric cell; the row/column form always returns text.