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.
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.
HI. I have tried this one, but the excel look messy, is there any way to adjust cell width? Merge cell? Thank you.
I’m afraid it is not supported.
Hope this will be useful for import excel file in business central.
This is titled “How to use the Excel Buffer in Business Central cloud”, but then half way down you note: “Obviously, that function isn’t available for the cloud, because you don’t have direct access to server files” – so I think for the help of future visitors perhaps rename the blog title to Business Central On Prem.
On the other hand, do you have an example of using the new STREAM functions to actually create an excel file from BC Cloud?
That note is only for the overload function that accepts a filename. But apart from the side note everything in this blog post is applicable for the the cloud version. Please grab the code and try it out in your online sandbox.
“Temp Blob” did not exist as a codeunit for me, but TempBlob does exist as a record with all the functions in BC16 – was this a recent change?
No, the codeunit “Temp Blob” is still there and should be used instead of the table.
Thanks – I found it – I was looking for the al source.
Another question – if I need to create multiple sheets (cloud version), can I do this with a new book, or do I have to save it and then use the update stream method to add more sheets?
Never mind – I found SelectOrAddSheet works in the cloud
Hi Arend-Jan, I used your solution, and it works fine except for one thing. When I reopen Business Central after I have created the excel sheet I get an error and I cannot login to Business Central anymore. The error is “Something went wrong. An error has occured, Date and Time xxx , Azure AD tenant xxx, Environment xxx, Operation xxx. When I delete my browser history (Chrome and Edge) I can open Business Central again. Do you perhaps know what the cause is of this? It looks like it is still downloading the excel sheet.
I’m sorry, but I have no idea what is causing that.
Hi Arend-Jan, big thank you for this examples. The only issue i have is when i populate my template with values the initial formatting disappears from populated cells. Maybe you know how the cell could be populated with values leaving the initial formatting of this cell?
Some note: im working with saas(not onpremise version)
How can I display a progress bar when importing via Excel Buffer?