Excel Buffer-Stream functions

Kratos3064

Hi, I recently got to know you cannot use SelectSheetsName / OpenBook functions from ExcelBuffer record for AL extensions. Instead BC has provided SelectSheetsName and OpenBookStream.

In the older versions, I can enter the name of the file and the sheets in the parameters. This will generate an Excel file.

In AL, the datatype of the first parameter of the functions is an InStream function. Ive not worked on Instream / Outstream functions. Ive tried to google, but I don't get how it works. Can someone help me out in explaining it and also help me in showing an example for these functions.

Thanks in advance.

ftornero

Hello @Kratos3064,

Here you have an example to read an Excel File.

codeunit 50104 LoadExcel
{
    trigger OnRun()

    begin
        UploadIntoStream('Load Excel File', '', 'Excel File (.xlsx)|*.xlsx', NombreFicheroLocal, FicheroInStream);
        Nombrehoja := ExcelBuf.SelectSheetsNameStream(FicheroInStream);
        ExcelBuf.LOCKTABLE;
        ExcelBuf.OpenBookStream(FicheroInStream, Nombrehoja);
        ExcelBuf.ReadSheet();
        if ExcelBuf.FindLast() then begin
            celdasleidas := ExcelBuf.Count;
            Message('Cells read %1', celdasleidas);
        end;
    end;

    var
        ExcelBuf: Record "Excel Buffer";
        FicheroInStream: InStream;
        NombreHoja: Text;
        NombreFicheroLocal: Text;
        celdasleidas: Integer;
}

ftornero

Hello @Kratos3064,

Here you have an example to read an Excel File.

codeunit 50104 LoadExcel
{
    trigger OnRun()

    begin
        UploadIntoStream('Load Excel File', '', 'Excel File (.xlsx)|*.xlsx', NombreFicheroLocal, FicheroInStream);
        Nombrehoja := ExcelBuf.SelectSheetsNameStream(FicheroInStream);
        ExcelBuf.LOCKTABLE;
        ExcelBuf.OpenBookStream(FicheroInStream, Nombrehoja);
        ExcelBuf.ReadSheet();
        if ExcelBuf.FindLast() then begin
            celdasleidas := ExcelBuf.Count;
            Message('Cells read %1', celdasleidas);
        end;
    end;

    var
        ExcelBuf: Record "Excel Buffer";
        FicheroInStream: InStream;
        NombreHoja: Text;
        NombreFicheroLocal: Text;
        celdasleidas: Integer;
}

Kratos3064

ftornero wrote: »

Hello @Kratos3064,

Here you have an example to read an Excel File.

codeunit 50104 LoadExcel
{
    trigger OnRun()

    begin
        UploadIntoStream('Load Excel File', '', 'Excel File (.xlsx)|*.xlsx', NombreFicheroLocal, FicheroInStream);
        Nombrehoja := ExcelBuf.SelectSheetsNameStream(FicheroInStream);
        ExcelBuf.LOCKTABLE;
        ExcelBuf.OpenBookStream(FicheroInStream, Nombrehoja);
        ExcelBuf.ReadSheet();
        if ExcelBuf.FindLast() then begin
            celdasleidas := ExcelBuf.Count;
            Message('Cells read %1', celdasleidas);
        end;
    end;

    var
        ExcelBuf: Record "Excel Buffer";
        FicheroInStream: InStream;
        NombreHoja: Text;
        NombreFicheroLocal: Text;
        celdasleidas: Integer;
}

Thank you @ftornero