Generate and Import Excel files with X++ in #MSDyn365FO

We all know that the management and integration that #MSDyn365FO brings as standard with Microsoft 365 products is very good, especially with Excel files. Even so, there are occasions when we have to develop our own code to generate files or to import them for different reasons, such as importing files with a specific format that does not conform to standard integration, or generating files for which we must do a series of previous calculations.

For this reason, I have decided to share with you a class that I have developed. This class allows you to manage these files in a really simple way by adding a few lines of code to your project.

To develop this class, I have relied on the EPPlus library. This library is already included as a reference within the Application Suite model, so we can start using it in our code without the need for additional steps, and it will allow us to manage Excel workbooks in a very agile and intuitive way, allowing us to completely forget the damn Interops.

Using this class will allow you, on the one hand, to generate Excel files, simply passing the values you want to add in the header (first row), and a list of the values you want to add in the rest of the rows. You can also define a list of values to generate a dropdown with these values within the file, and thus generate templates that can be completed by users, using these lists of values, and imported again.

You can download this class and a sample usage class from my MSDyn365FO tools repository on GitHub.

Let’s see an example of Exporting an Excel file in X++ using the mentioned class.

1. The first step will be to create an instance of this class and indicate the name that we will give to the file, and the name that we will give to the generated worksheet:

2. Next, we create the header of the whorksheet, for that we have to initialize the parameter hasHeader to true and pass a container with the values that the header will have:

3. Now, we indicate what are the values to be exported. To do this, we pass a list of containers, which will contain the values in the same order as indicated in the file header:

4. As we have previously said, we have the option of adding a list of values that will generate a dropdown in the values associated with the column that we indicate. We will do this by passing a map, in which the key will be the name of the column (header), and the value will be a container with the values that must appear:

5. To finish, all we have to do is execute the method exportFile().

As a result, you will be able to download an Excel file like this:

Just as easy would be to import an Excel file using X ++. The only thing we will need is to indicate if the file we receive has a header or not, which will allow us to know if we should start importing in the first or second row, then we will pass a memoryStream that will contain the file itself, and we will execute the method importFile().

This method will return a map in which the key will be of type int and will contain the line number, and the value of the map will be a container with the values of that line.

With these simple steps we can manage any type of Excel file through X ++ code in a very simple way, allowing us to focus on what is important, what we have to do with that data, and not so much on file management. per se.

I hope you find this useful and, of course, every single comment will be welcome. Happy summer and happy holidays!!😎