X++ code to import records from excel in D365 F&O

 Before Writing Logic we have to add required namespaces and reference packages , because export excel related x++ classes required below namespaces.

  1. Using System.IO;
  2. Using OfficeOpenXml;
  3. Using OfficeOpenXml.ExcelPackage;
  4. Using OfficeOpenXml.ExcelRange;
In addition to the basic reference packages , we also require to add additional reference packages directory

To do this update the model parameters
  1. Directory
  2. DirectoryUpgrade
Code:

// Part1: Create a dialouge object which will work as a  import form and we can select the excel file by clicking the import  button , once the file is fully uploaded it will keep the data in temporary storage.

Using System.IO;
Using OfficeOpenXml;
Using OfficeOpenXml.ExcelPackage;
Using OfficeOpenXml.ExcelRange;

class BASImportCSVData
{

    public static void main(Args _args)
    {
        System.IO.Stream stream;

        ExcelSpreadsheetName sheet;

        FileUploadBuild fileUpload,fileUploadBuild;

        DialogGroup dialogUploadGroup;

        FormBuildControl formBuildControl;

        BASEmployeeDetails employeeDetails;

        Dialog dialog=new Dialog("Import Excel Form");

        dialogUploadGroup=dialog.addGroup("@SYS54759");

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

        fileUploadBuild=formBuildControl.addControlEx(classStr(fileUpload),"UploadExcel");

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted(".xlsx");

        //if(dialog.run() && dialog.closedOk())
        //{
        //   FileUpload fileUploadControl=dialog.formRun().control(dialog.formRun().controlId("Upload"));

            FileUploadTemporaryStorageResult fileUploadResult=file::GetFileFromUser(classStr(FileUploadTemporaryStorageStrategy));

            //fileUploadResult=fileUploadControl.getFileUploadResult();

            /*------------------part 1 end---------------------*/

            /*------------------part 2---------------------*/

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

            {
                stream=fileUploadResult.openResult();

                using(ExcelPackage  package= new ExcelPackage(stream))
                {
                    int rowCount, iterator;

                    package.Load(stream);

                    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(iterator=2;iterator<=rowCount;iterator++)
                    {
                        select * from employeeDetails where employeeDetails.EmpId==                                   range.get_Item(iterator,1).Value;

                        if(employeeDetails.EmpId == range.get_Item(iterator,1).Value)
                    {
                        update_recordset employeeDetails 
                            setting EmpId = range.get_Item(iterator,1).Value ,
                                EmpName = range.get_Item(iterator,2).Value
                            where employeeDetails.EmpId == range.get_Item(iterator,1).Value;
                              
                    }

                    else{


                    
                            employeeDetails.EmpId = range.get_Item(iterator,1).Value;

                            employeeDetails.EmpName = range.get_Item(iterator,2).Value;

                            employeeDetails.insert();
                    }
                     
                    }
                    }

                        

                    }

                
            
            
            /*------------------part 2 end---------------------*/

        
        else
        {

            Error("error occured.");
        }

    }

}

Comments

Post a Comment

Popular posts from this blog

How to Create a wizard in x++ d365

Image
 step1: Create a form and apply form pattern as wizard step2 : create a class and extend the class with SysWizard. code :            class BAS_WIzardScreen extends SysWizard {     #MACROLIB.AviFiles     FormName formname()     {         return formstr(BAS_WIzardForm);  // name of the wizard form     }     public void run()     {         info(strFmt("Thankyou"));     }     public void setupNavigation()     {     }     public boolean validate()     {         return true;     }     public static str description()     {         return "BASOFA WIZARD";  // title of the wizard     }     public static void main(Args _args)     {         BAS_WIzardScreen wizard = new BAS_WIz...
Read more

how to post trade agreement journals automatically using x++ code

  In Microsoft Dynamics AX, you can post trade agreement journals automatically using X++ code. Here are the steps to do so: Create a new job in X++. Write a code to fetch data from the trade agreement table. Create a journal header and journal lines based on the fetched data. Call the post() method of the journal header to post the journal. Repeat steps 2 to 4 for each trade agreement. Here's a sample code in X++: static void postTradeAgreementJournals(Args _args) { TradeAgreementTable tradeAgreementTable; GeneralJournalHeader generalJournalHeader; GeneralJournalLine generalJournalLine; // Fetch data from the Trade Agreement table select tradeAgreementTable where tradeAgreementTable.JournalPosting == NoYes::No; // Loop through each trade agreement while (tradeAgreementTable) { // Create a journal header generalJournalHeader = new GeneralJournalHeader(); generalJournalHeader.initValue(); generalJournalHeader.tra...
Read more

X++ code to CREATE AND POST A TRADE AGREEMENT IN MICROSOFT DYNAMICS 365 in D365 F & O

Reuirement : I have to create a table called BASTradeAgreementTable with fields like following Item number Product Name ToDate FromDate InventSiteId InventLocationId Amount From Unit Step2: After Creating Table I have to create a form and add the created table into the form datasource and add all the fields of that datasource to form grid. I have to create a button called Post whenever user clicks on that button i have to create and post Trade Agreement Journals with the data provided in the form grid. [Control("Button")]     class Post     {         /// <summary>         ///         /// </summary>         public void clicked()         {             int             recordsCount;              BASTradeAgreementTable    tradeAgreement;     ...
Read more