Last week, I saw a question about importing data from Excel file to Business Central using AL Language. A few years ago, the only solution was to use DotNet integration. However, with the Business Central, there is a much quicker and better approach. 
All functions for working with Excel files are available in a table called “Excel Buffer” (table 370 “Excel Buffer”). This table contains many useful functions to work with the Excel file, even functions for working with formulas or column style.
For importing data to Business Central, there are three crucial functions.
SelectSheetsNameStream(…)
The first of them is called SelectSheetsNameStream(). This function opens the Excel file and finds the sheet name. If the file contains only one sheet, the sheet is selected automatically. If there are more sheets defined, the user is asked to select the one to import.
OpenBookStream(…), ReadSheet()
The remaining functions are ReadSheet() and OpenBookStream().
The ReadSheet function reads the file’s content (that must be initialised using OpenBookStream at first) and stores loaded values into the Excel Buffer table.
How to load data from Excel
Once we know all these functions, we can create a procedure to load an Excel file quickly. Firstly, we need to upload the file (we can do it using the BLOBImportWithFilter function from the Codeunit 419 “File Management”). Then we need to choose the sheet we want to upload (SelectSheetsNameStream(…)) and prepare the Excel buffer table (OpenBookStream(…), ReadSheet(…)).
After that, we can just go through records in the Excel buffer table and process the table as usual. Besides row number, column number or the cell value, we have available information about cell style (bold, italic, underlined) and cell data type (date, number, text, …).
local procedure ImportExcelFile()
var
TempExcelBuffer: Record "Excel Buffer" temporary;
FileManagement: Codeunit "File Management";
TempBlob: Codeunit "Temp Blob";
SheetName, ErrorMessage : Text;
FileInStream: InStream;
ImportFileLbl: Label 'Import file';
begin
// Select file and import the file to tempBlob
FileManagement.BLOBImportWithFilter(TempBlob, ImportFileLbl, '', FileManagement.GetToFilterText('', '.xlsx'), 'xlsx');
// Select sheet from the excel file
TempBlob.CreateInStream(FileInStream);
SheetName := TempExcelBuffer.SelectSheetsNameStream(FileInStream);
// Open selected sheet
TempBlob.CreateInStream(FileInStream);
ErrorMessage := TempExcelBuffer.OpenBookStream(FileInStream, SheetName);
if ErrorMessage <> '' then
Error(ErrorMessage);
TempExcelBuffer.ReadSheet();
if Rec.FindSet() then
repeat
Message('%1, %2: %3', TempExcelBuffer."Row No.", TempExcelBuffer."Column No.", TempExcelBuffer."Cell Value as Text");
until TempExcelBuffer.Next() < 1;
end;
