Excel Import in the Cloud – grab the code

Today I got the question is it possible to use import from the Excel file in the cloud version of Business Central. And since I never did that (in the cloud) I asked my colleague if I could still him this task. He agreed so I can share with you how to do so.

Find Code on GitHub

The whole working example you can find on my GitHub here: https://github.com/mynavblog/ExcelCloud

Using File Management and Excel Buffer

As in the old good days, I also am using the File Management Codeunit and Excel Buffer table. But this time I would need to use different functions – which are based on the InStream.

The first step is creating a process-only report which will have two fields on the request page. One for the File Name which will be imported and the second one with the Sheet Name which needs to be imported.

For this report, no Dataitem is needed.

In the trigger OnOpenPage, I added the code which will allow choosing the file. In the parameter, it will be required to specify the TempBlob. But not as it was in past, the record (which now is obsolete) but the Codeunit. Using it gives me the possibility in the simplest way to create InStream which later will be used.

Additionally, I added a code that checks if the file has content and if the file extension is what I need.

The effect of that is when I run the report, I can see the screen below.

Then I need to choose which Excel Sheet from the Book1.xlsx file I should import. I created the field on the request page – Choose Sheet Name.

On Assist Edit I can use on function from Excel Buffer table – SelectSheetsNameStream. This function is slightly different than the one which can be used on-prem installations because as a parameter it takes the InStream.

If there is only one Sheet in the book it will be chosen automatically but if there is more this is what the user will see.

The last step to import data is a function that will read and process the rows. I added it to the OnPreReport trigger in the Report. Again the InStream is needed here to open the Excel Book. Then with the same function as in the past, it is possible to read the rows and store them in the Excel Buffer table.

Processing of the file I moved to separate Codeunit – then you can use the report as a template for multiple imports.

The final result of my import to the very complicated table you can find below.