Microsoft Business Central File Handling Part 3

The Microsoft Directions Asia Conference offered many sessions on a variety of topics. The big news was the unveiling of the April ’19 Release of Dynamics 365 Business Central and the announcement of the end of Windows Client beginning with the October 2019 release.  

The ArcherPoint team also gained some practical knowledge, such as handling files in Microsoft Dynamics Business Central online.

This blog series discusses each of those tasks. Part 1 explained how to export a file to Excel and download it, while Part 2 discussed uploading a file and storing it in a table. This blog explains how to update an existing Excel file.

Updating an Existing Excel File in Microsoft Dynamics Business Central Online

In the example in the first blog in this series, we generated an Excel file from scratch. But what if you want to export an Excel file that contains a certain layout and design? The best way to do that is to prepare an Excel file with the desired layout and use that as a template.

The overall process is not so different from exporting a new Excel file. The differences are that we now need to first initialize the Excel Buffer table with the file that we want to use. Secondly, we are not just adding rows but instead setting the value of specific cells.

In this example, we are going to use the Order Template.xlsx file and fill it with the data of a sales order. To keep it simple, we assume the file is in the first record of the Excel Template table.

Prerequisites:

1. Dynamics 365 Business Central Sandbox Environment
2. Dynamics 365 Business Central should run from Azure Cloud
3. Visual Studio Code

This task includes the following steps:

1. Create a codeunit and run it from a sales order list page
2. Initialize the Excel Buffer table
3. Fill the cells with data
4. Close the Excel file and download it to the user

Create a New Codeunit and Set the Table No. to Table Sales Header

1. Create the function Export2Excel and call it from the OnRun trigger. Pass in the SalesHeader record that needs to be exported.
2. Create a page extension for the Sales Order List page with an action to run the codeunit.

This is the basic structure. We can now move on to create the code that exports the data.

Initialize the Excel Buffer Table

Before we can set any data, we need to initialize the Excel Buffer table with the Excel file that we will use as a template:

1. Create a function InitExcel.
2. Create a variable for the Excel Template table.
3. Create an InStream variable.
4. Read the content of the first record in the Excel Template table and create the InStream variable.
5. Update the Excel Buffer table from the InStream variable. Pass in the name of the Excel worksheet that will hold the data.
6. Call the InitExcel function from the Export2Excel function.

Figure 2 – Initializing the Excel Buffer table in Microsoft Dynamics Business Central Online

Populate the Cells with Data

The Excel template file is a prepared file; only the cells must be populated with data from the sales order.

1. Create a function FillHeaderData to set all cell values for the Sales Order Header.
   
   Figure 3 – Creating a function FillHeaderData to set all cell values for the Sales Order Header in Microsoft Dynamics Business Central Online
2. Create a function FillLineData to create the rows in the Excel template.
   
   Figure 4 – Creating a function FillLineData to create the rows in the Excel template in Microsoft Dynamics Business Central Online
3. Create a function FillExcelBuffer.
4. Create a local variable for the Excel Buffer table.
5. Call the function FillHeaderData and FillLineData with the local variable.
6. Call the function WriteAllToCurrentSheet on the Excel Buffer table to store the data from the local variable to the Excel Buffer.
7. Add a line to the function Export2Excel  to call the FillExcelBuffer function.
   
   Figure 5 – Adding a line to the function Export2Excel to call the FillExcelBuffer function in Microsoft Dynamics Business Central Online

Close the Excel file and Download It to the User

1. Add two lines to the Export2Excel function to close the Excel file and download it the user:

TempExcelBuf ,CloseBook();

TempExcelBuf ,OpenExcel();

2. Publish the app and test it from the Sales Order List page.
3. The codeunit should now look like Figure 6.
   
   Figure 6 – Codeunit after publishing the app and testing it from the Sales Order List page in Microsoft Dynamics Business Central Online
   
   Figure 7 – Publishing the app and testing it from the Sales Order List page in Microsoft Dynamics Business Central Online

Stay tuned for the last blog in this series.

If you have any Dynamics NAV or Business Central questions for any version, contact ArcherPoint.

Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics Business Central or NAV.

If you are interested in NAV/Business Central development, check out our collection of NAV/BC Development Blogs.

Trending Posts

Stay Informed