programar excel xpp

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!!😎

4 comments / Add your comment below

  1. Thanks for the post it was awesome and helpful.
    I needed to make some modifications in my case because of the validation columns limits of 256 characters.
    I added the following modification;
    private ExcelWorksheet setupWorkSheet;
    private boolean useSetUp;
    public boolean parmUseSetup(boolean _useSetup = useSetUp)
    useSetUp = _useSetup;
    return useSetUp;
    setupWorkSheet = package.Workbook.Worksheets.Add(“Setup”);
    and in the set list validation I modified it as follows;
    private void setListValuesValidation(ExcelWorksheet _excelWorksheet, int _colNum, container _conValues)
    DataValidation.ExcelDataValidationList validation;
    str colLetter = ExcelCellAddress::GetColumnLetter(_colNum);

    validation = _excelWorksheet.DataValidations.AddListValidation(strFmt(‘%1:%1’, colLetter));

    //load the data into setup fields.
    if(useSetUp && setupWorkSheet != null)
    this.writeExcelSetupContent(setupWorkSheet, _conValues, colLetter);
    str formula = strFmt(‘Setup!$%1$%2:$%1$%3’,colLetter, 1, conLen(_conValues));
    for (int i = 1; i
    validation.get_Formula().get_Values().Add(conPeek(_conValues, i));

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información. ACEPTAR

Aviso de cookies