Monday, January 25, 2021

How to import WBS (Work breakdown structure) data from MS Excel in Dynamics 365 FO


In most of the scenarios where developers get a requirement to import data from Microsoft Excel into the WBS Masters and Transaction tables they get stuck as WBS Paradigm includes lot of tables and classes to execute a proper WBS Header and Line structure.

Now with Data Entities in picture the task has become much more simpler to achieve.

Let's take a look at the code mentioned below in a class and see how its done : - 

Note : - The Below code has used the initialization of some custom fields

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

using OfficeOpenXml.Style;

using OfficeOpenXml.Table;

class WBSExcelUpload


    public static void main(Args    _args)


        MemoryStream                                  memoryStream    = new MemoryStream();

        WBSExcelUpload                              importWBS       = WBSExcelUpload::construct();

        ProjProjectWBSDraftEntity               projectwbsentity;

        ProjWBSActivityEstimatesEntity      activityEstimateEntity;

        ProjPlanVersion                               projPlanVersion,projPlanVersionProcess,projPlanVersioncount;

        FormRun                                            formRun;

        projPlanVersion =   _args.record();

        formRun =   _args.caller();

        if (Box::okCancel("Do you want to Upload Excel WBS Data?"

                                        , DialogButton::Cancel) == DialogButton::Ok)


            select count(RecId) from projPlanVersioncount

                where projPlanVersioncount.HierarchyId == projPlanVersion.HierarchyId;

            if (projPlanVersioncount.RecId != 1)





                update_recordset  projPlanVersionProcess

                setting ProcessedEstimation = NoYes::No

                where projPlanVersionProcess.HierarchyId   ==    projPlanVersion.HierarchyId;






    public static WBSExcelUpload construct()


        return new WBSExcelUpload();


    public void import()


        System.IO.Stream                stream;

        ExcelSpreadsheetName            sheet;

        FileUploadBuild                 fileUpload;

        DialogGroup                     dlgUploadGroup;

        FileUploadBuild                 fileUploadBuild;

        FormBuildControl                formBuildControl;

        ProjProjectWBSDraftEntity       wbsEntity;

        Dialog                          dialog = new Dialog('Import WBS Data from excel');

        dlgUploadGroup                  = dialog.addGroup("WBS Data Upload Group");

        formBuildControl                = dialog.formBuildDesign().control(;

        fileUploadBuild       = formBuildControl.addControlEx(classStr(FileUpload), 'UploadExcelWBS');


        if( && dialog.closedOk())


            FileUpload                          fileUploadControl   = dialog.formRun().control(dialog.formRun().controlId('UploadExcelWBS'));

            FileUploadTemporaryStorageResult    fileUploadResult    = fileUploadControl.getFileUploadResult();

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())


                stream = fileUploadResult.openResult();

                using (ExcelPackage Package = new ExcelPackage(stream))


                    int rowCount, i;


                    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;



                    for (i = 2; i<= rowCount; i++)


                        wbsEntity.ProjectId             =  range.get_Item(i, 1).Value;

                        wbsEntity.WBSId                 =  range.get_Item(i, 2).Value;

                        wbsEntity.Reference         =  range.get_Item(i, 3).Value;

                        wbsEntity.TaskID            =  range.get_Item(i, 4).Value;

                        wbsEntity.Task                  =  range.get_Item(i, 5).Value;

                        wbsEntity.Note                  =  range.get_Item(i, 6).Value;

                        wbsEntity.Category              =  ProjParameters::find().EmplCategory;

                        wbsEntity.Quantity          =  range.get_Item(i, 7).Value;

                        wbsEntity.ExpensePercent    =  range.get_Item(i, 8).Value;

                        wbsEntity.MarkupPercent     =  range.get_Item(i, 9).Value;

                        wbsEntity.StartDate             =  str2DateDMY(range.get_Item(i, 10).Value);

                        wbsEntity.EndDate               =  str2DateDMY(range.get_Item(i, 11).Value);





                    info(strFmt('%1 records processed', rowCount-1));






Please type in the comments section of this post if you have any queries.

Happy Coding !!!

No comments:

Post a Comment

How to reverse Free Text Invoice Voucher entries without Dialog

 Hey Folks ,  This blog post is in continuation of the previous post for Reversing Free Text Invoice Voucher entries with Dialog. Only diffe...