Upload Excel Data in D365FnO

 This is common requirement in X++ to create a class to upload data from Excel in a table. So below code you can use and customize as per your requirement.

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class ExcelUpload
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = “_args”>The specified arguments.</param>
    public static void main(Args _args)
    {
        System.IO.Stream stream;

        ExcelSpreadsheetName        sheeet;
        FileUploadBuild             fileUpload;
        DialogGroup                 dlgUploadGroup;
        FileUploadBuild             fileUploadBuild;
        FormBuildControl            formBuildControl;
        ProjTable                   projTable;
        Dialog                      dialog = new Dialog("Import the Projects data from Excel");

        dlgUploadGroup      = dialog.addGroup("Select excel file");
        formBuildControl    = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild     = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx');

        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(stream))
                {
                    int rowCount, i;
                    Package.Load(stream);
                    OfficeOpenXml.ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange     range = worksheet.Cells;
                    rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
                    try
                    {
                        for (i = 2; i<= rowCount; i++)
                        {
                            str projId      = range.get_Item(i, 1).value;
                            str projStatus  = range.get_Item(i, 2).value;
                            //str startDate   = range.get_Item(i, 3).value;

                            select firstonly projTable where projTable.ProjId == projId;
                            if(projTable)
                            {
                                ttsbegin;
                                projTable.selectForUpdate(true);
                                projTable.reread();
                                if(projTable.Status == ProjStatus::Created)
                                {
                                    projTable.status().runStartUp();
                                }
                                //projTable.StartDate = str2Date(startDate, 213);
                                if(range.get_Item(i, 3).value)
                                {
                                    projTable.StartDate = DateTimeUtil::date(range.get_Item(i, 3).value);
                                    projTable.doUpdate();
                                }
                                ttscommit;
                            }
                        }
                        info("Data inserted succesfully");
                    }
                    catch (Exception::Error)
                    {
                        throw error("error here");
                    }
                }
            }
        }
    }

}

How to use strSplit() Function in x++

While doing customization I came across a scenario where the datatype of a column was of string type and value was stored as "1111.93799999999999994". And the requirement was to get  exact two digits after the decimal point. No rounding up/down was required. In the below code example you can achieve this.

class stringSplitFunctionDemo
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        str str1='1111.93799999999999994';
       
        List strlist=new List(Types::String);
        ListIterator iterator;
        str _Value,beforeDec,afterDec,finalOutput;
        int x;
        ;

        strlist=strSplit(str1,'.');
        iterator = new ListIterator(strlist);
        
        while(iterator.more())
        {
            x= x+1;

            if(x==1)
            {
                beforeDec =iterator.value();            
            }
            if(x==2)
            {
                afterDec = iterator.value();
            }
            iterator.next();
        }

        finalOutput = beforeDec +'.'+ subStr(afterDec,1,2);
        Info(finalOutput);


    }

}