How to use the Excel Buffer in Business Central cloud

A week ago, my friend Daniel asked a question on Twitter how to use the Excel Buffer to export data with Business Central online. Many functions on the Excel Buffer are only available for on-prem. When you change the target in app.json to ‘Cloud’ then those functions become unavailable.

Luckily I had a code example available so I decided to put that on GitHub. But I thought it would make sense to write a blog post to explain the code.

It contains two examples: the first example exports the Customer list to a new Excel file and the second exports a Sales Order to a prepared and uploaded Excel file. Both options offer to download or to email the created Excel file.

The example to export the Customer List can be found on the Customers page under Actions. The export Sales Order is on the Sales Order list under Actions.

Basic flow

The basic flow of creating a new Excel file with the Excel Buffer table can be found in Codeunit Export Customer 2 Excel, function CreateAndFillExcelBuffer.

local procedure CreateAndFillExcelBuffer(var TempExcelBuf: Record "Excel Buffer" temporary)
begin
    TempExcelBuf.CreateNewBook(SheetNameTxt);
    FillExcelBuffer(TempExcelBuf);
    TempExcelBuf.WriteSheet(HeaderTxt, CompanyName(), UserId());
    TempExcelBuf.CloseBook();
end;

All of this happens in memory. The CreateNewBook function creates a temporary server file and initializes the .Net components to write to that file. There is an overload function for CreateNewBook that accepts a filename parameter. Obviously, that function isn’t available for the cloud, because you don’t have direct access to server files. After this, you fill the Excel Buffer with data in the usual way.

local procedure FillExcelRow(
    var TempExcelBuf: Record "Excel Buffer" temporary;
    Customer: Record Customer)
begin
    with Customer do begin
        TempExcelBuf.NewRow();
        TempExcelBuf.AddColumn("No.", false, '', false, false, false, '', TempExcelBuf."Cell Type"::Text);
        TempExcelBuf.AddColumn(Name, false, '', false, false, false, '', TempExcelBuf."Cell Type"::Text);
        TempExcelBuf.AddColumn(Address, false, '', false, false, false, '', TempExcelBuf."Cell Type"::Text);
        TempExcelBuf.AddColumn("Post Code", false, '', false, false, false, '', TempExcelBuf."Cell Type"::Text);
        TempExcelBuf.AddColumn(City, false, '', false, false, false, '', TempExcelBuf."Cell Type"::Text);
        TempExcelBuf.AddColumn("Country/Region Code", false, '', false, false, false, '', TempExcelBuf."Cell Type"::Text);
    end;
end;

The WriteSheet function uses the data of the Excel Buffer to write to the temporary server file. Finally, the Close function clears the .Net components that are used to write to the file. Now you end up with a temporary server file. The next step is to get the file, either as a download to the client or into a stream so you can store it in a blob or attach it to an email.

Getting the file

To download the file to the client, you can only use the function OpenExcel. Other functions are blocked for the cloud. Below are the three functions in the Excel Buffer table, and I believe these functions could be reduced to just two functions. The function OpenExcel could be removed and the OnPrem limitation on function DownloadAndOpenExcel could be removed. As far as I can see, it would result in one function to be used for both cloud and on-premises with exactly the same behaviour. And no code duplication between OpenExcel and OpenExcelWithName.

    procedure OpenExcel()
    begin
        if OpenUsingDocumentService('') then
            exit;
        FileManagement.DownloadHandler(FileNameServer, '', '', Text034, GetFriendlyFilename);
    end;
    [Scope('OnPrem')]
    procedure DownloadAndOpenExcel()
    begin
        OpenExcelWithName(GetFriendlyFilename);
    end;
    [Scope('OnPrem')]
    procedure OpenExcelWithName(FileName: Text)
    begin
        if FileName = '' then
            Error(Text001);
        if OpenUsingDocumentService(FileName) then
            exit;
        FileManagement.DownloadHandler(FileNameServer, '', '', Text034, FileName);
    end;

Anyway… the only thing that I wanted to add is the FriendlyFileName. So I have created a function DownloadAndOpenExcel that does that and then calls the function OpenExcel. As a result, the web client will download the file to the local file system.

local procedure DownloadAndOpenExcel(var TempExcelBuf: Record "Excel Buffer" temporary)
begin
    TempExcelBuf.SetFriendlyFilename(BookNameTxt);
    TempExcelBuf.OpenExcel();
end;

Attach the Excel to an email

The other possibility is to get the file into a stream variable and attach it to an email. I have added a function EmailExcelFile to the Excel Buffer table with a tableextension. In the Codeunit EmailExcelFileImpl you can find the implementation of this function. The code below retrieves the Excel file into a stream and adds it as an attachment to the email.

local procedure AddAttachment(
    var SMTPMail: Codeunit "SMTP Mail";
    var TempExcelBuf: Record "Excel Buffer" temporary;
    BookName: Text)
var
    TempBlob: Codeunit "Temp Blob";
    InStr: InStream;
begin
    ExportExcelFileToBlob(TempExcelBuf, TempBlob);
    TempBlob.CreateInStream(InStr);
    SMTPMail.AddAttachmentStream(InStr, BookName);
end;
local procedure ExportExcelFileToBlob(
    var TempExcelBuf: Record "Excel Buffer" temporary;
    var TempBlob: Codeunit "Temp Blob")
var
    OutStr: OutStream;
begin
    TempBlob.CreateOutStream(OutStr);
    TempExcelBuf.SaveToStream(OutStr, true);
end;

As you can see, I’m using the new Temp Blob storage module of version 15. If you have the data in the Temp Blob Codeunit, then you could also store the file into a blob field in a table or send it to a web service.

Updating an existing Excel file

The other example is to export data to an already existing Excel file. It is basically updating that file without removing any of the existing data. An example Excel file is added to the repository on GitHub. You can import that file with the Excel Template page. The Excel Template table doesn’t store the Excel file in a blob field in the table, but instead, it uses the persistent blob feature of the Blob Storage module in v15.

The code for the Excel Buffer to load this file instead of creating a new file is in the function InitExcelBuffer in Codeunit Export Sales Order 2 Excel. It uses the function UpdateBookStream of the Excel Buffer. This function saves the stream to a temporary file on the server and then opens it.

local procedure InitExcelBuffer(var TempExcelBuf: Record "Excel Buffer" temporary): Boolean
var
    ExcelTemplate: Record "Excel Template";
    TempBlob: Codeunit "Temp Blob";
    InStr: InStream;
begin
    ExcelTemplate.FindFirst();
    if not ExcelTemplate.GetTemplateFileAsTempBlob(TempBlob) then
        exit;
    
    TempBlob.CreateInStream(InStr);
    TempExcelBuf.UpdateBookStream(InStr, SheetNameTxt, true);
    exit(true);
end;

Instead of adding new rows to the Excel Buffer, we have now to enter data directly into cells. This is an existing feature and nothing different from the past. Take a look at function FillHeaderData and FillLineData in the Codeunit for an example. Finally you need to call the function WriteAllToCurrentSheet to write the data into the Excel file. The rest of the code is similar to the previous example.