D365 : Upload Via Excell Code

/// <summary>

///            Modification : Added NoYes dialog box and export template feature for Changing PO

/// </summary>

/// <remarks>

///         Class for updating Department in PO.

/// </remarks>

using System.IO;

using OfficeOpenXml;

class LT_Supp_UploadDepPO

{

    public static void main(Args args)

    {

        LT_Supp_UploadDepPO updateCoverRes = new LT_Supp_UploadDepPO();

        DialogButton diagBut;

        ;

        diagBut = Box::yesNoCancel('Do you wish to upload ->"Yes" or Export template ->"No" or Abort->"Cancel"', DialogButton::Yes, 'Click');

        

        if(diagBut == DialogButton::Yes)

        {

            updateCoverRes.run();

            info("Processing completed");

        }

        else if(diagBut == DialogButton::No)

        {

            updateCoverRes.exportTemplate();

        }

    }

    /// <summary>

    /// Export template for this job

    /// </summary>

    public void exportTemplate()

    {

        int i = 1;

        MemoryStream memoryStream = new MemoryStream();

        using (var package = new ExcelPackage(memoryStream))

        {

            var currentRow = 1;

            var worksheets = package.get_Workbook().get_Worksheets();

            var CustTableWorksheet = worksheets.Add("Export");

            var cells = CustTableWorksheet.get_Cells();

            ;

            OfficeOpenXml.ExcelRange cell = cells.get_Item(currentRow, i);

            System.String value = "Purchase order number";

            cell.set_Value(value);

            i++;

            cell = null;

            value = "From Department";

            cell = cells.get_Item(currentRow, i);

            cell.set_Value(value);

            i++;

            cell = null;

            value = "To Department";

            cell = cells.get_Item(currentRow, i);

            cell.set_Value(value);

            i++;

            package.Save();

            file::SendFileToUser(memoryStream, 'Update_Purchase_Order_Department.xlsx');

        }

    }

    /// <summary>

    /// Get the values from file and Upload Purchase Order Department Through Excell

    /// </summary>

    public void run()

    {

        FileUploadTemporaryStorageResult fileUploadResult;

        OfficeOpenXml.ExcelRange range;

        System.IO.Stream stream;

        ExcelWorksheet worksheet;

        ExcelSpreadsheetName sheeet;

        FormBuildControl formBuildControl;

        FileUploadBuild fileUpload, fileUploadBuild;

        DialogGroup dlgUploadGroup;

        FileUpload fileUploadControl;

        Dialog dialog = new Dialog('Upload Purchase Order Department Through Excell');

        int rowCount, row, i = 1;

        PayrollLeaveRequestId_GCC leaveRequestId;

        PurchTable                  purchTable,purchtableloc;

        str legalCompany;

        PurchIdBase                         PurchId;

        OMOperatingUnitNumber               toDept,fromdept;

        ;

        

        dlgUploadGroup = dialog.addGroup('@SYS54759');

        formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());

        fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted('.xlsx');

    

        if (dialog.run() && dialog.closedOk())

        {

            fileUploadControl   = dialog.formRun().control(dialog.formRun().controlId('Upload'));

            fileUploadResult = fileUploadControl.getFileUploadResult();

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

            {

                stream = fileUploadResult.openResult();

                using (ExcelPackage package = new ExcelPackage(stream))

                {

                    package.Load(stream);

                    worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);

                    range       = worksheet.Cells;

                    rowCount    = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;

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

                    {

                        PurchId = range.get_Item(row, 1).value;

                        toDept = range.get_Item(row, 2).value;

                        fromdept    = range.get_Item(row, 3).value;

                        purchtableloc = PurchTable::find(PurchId);

                        

                       /* select purchtableloc

                            where(purchtableloc.PurchStatus == PurchStatus::None

                            ||  purchtableloc.PurchStatus == PurchStatus::Received);*/

                                    

      

                        if(purchtableloc)

                        {

                            //i = 3;

                            ttsbegin;

                            purchtableloc.selectForUpdate(true);

                            purchtableloc.LTDepartment = fromdept;

                            purchtableloc.modifiedField(fieldNum(purchTable, LTDepartment));

                            purchtableloc.doupdate();

                            info(strFmt("PO No -- %1, Before Departement -- %2 ,After Department ---%3",purchtableloc.PurchId,toDept,purchtableloc.LTDepartment));

                            ttscommit;

                        }

                    }

                }

            }

            else

            {

                error('Error: Uploading failed');

            }

        }

    }

}