Exporting data to Excel through X++ code

By -

             Sometimes we need to export data from Microsoft Dynamics 365 to Excel using x++ code.
Through this post can see how to export data from form to excel

class SMJ_ExportData
{

    public static void main(Args args)
    {
        PurchParmLine  PurchParmLine,PurchParmLineSelect;
        PurchParmLine = args.record();
        DocuFileSaveResult saveResult = DocuFileSave::promptForSaveLocation("@ApplicationPlatform:Testingexcelexport", "xlsx", null, "Testing excel export");
        if (saveResult && saveResult.parmAction() != DocuFileSaveAction::Cancel)
        {
            saveResult.parmOpenParameters('web=1');
            saveResult.parmOpenInNewWindow(false);
            System.IO.Stream workbookStream = new System.IO.MemoryStream();
            System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
      
            using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
            {
                var currentRow=1;
                var worksheets = package.get_Workbook().get_Worksheets();
                var worksheet = worksheets.Add("First sheet");
                var cells = worksheet.get_Cells();

                var cell = cells.get_Item(currentRow,1);
                System.String value="SNo.";
                cell.set_Value(value);

                cell=null;
                value="Purchae order";
                cell=cells.get_Item(currentRow,2);
                cell.set_Value(value);

                cell=null;
                value="Line number";
                cell=cells.get_Item(currentRow,3);
                cell.set_Value(value);

                cell=null;
                value="Item number";
                cell=cells.get_Item(currentRow,4);
                cell.set_Value(value);

                cell=null;
                value="Procurement category";
                cell=cells.get_Item(currentRow,5);
                cell.set_Value(value);

                cell=null;
                value="Text";
                cell=cells.get_Item(currentRow,6);
                cell.set_Value(value);

                cell=null;
                value="Site";
                cell=cells.get_Item(currentRow,7);
                cell.set_Value(value);

                cell=null;
                value="Warehouse";
                cell=cells.get_Item(currentRow,8);
                cell.set_Value(value);

                cell=null;
                value="CW update";
                cell=cells.get_Item(currentRow,9);
                cell.set_Value(value);

                cell=null;
                value="Quantity";
                cell=cells.get_Item(currentRow,10);
                cell.set_Value(value);

               
                cell=null;
                value="Unit price";
                cell=cells.get_Item(currentRow,11);
                cell.set_Value(value);

                cell=null;
                value="Vendor batch date";
                cell=cells.get_Item(currentRow,12);
                cell.set_Value(value);

                cell=null;
                value="Line net amount";
                cell=cells.get_Item(currentRow,13);
                cell.set_Value(value);

                cell=null;
                value="Vendor expiry date";
                cell=cells.get_Item(currentRow,14);
                cell.set_Value(value);

                cell=null;
                value="Quality order status";
                cell=cells.get_Item(currentRow,15);
                cell.set_Value(value);

                while select PurchParmLineSelect 
                         where  PurchParmLineSelect.OrigPurchId == PurchParmLine.OrigPurchId 
                            && PurchParmLine.SMJ_SNo != ""
                {

                    currentRow ++;
                    cell=null;

                    cell=cells.get_Item(currentRow,1);
                    cell.set_Value(PurchParmLineSelect.SMJ_SNo);
                    cell=null;

                    cell=cells.get_Item(currentRow,2);
                    cell.set_Value(PurchParmLineSelect.OrigPurchId);
                    cell=null;

                    cell=cells.get_Item(currentRow,3);
                    cell.set_Value(PurchParmLineSelect.PurchaseLineLineNumber);
                    cell=null;

                    cell=cells.get_Item(currentRow,4);
                    cell.set_Value(PurchParmLineSelect.ItemId);
                    cell=null;

                    cell=cells.get_Item(currentRow,5);
                    cell.set_Value(PurchParmLineSelect.ProcurementCategory);
                    cell=null;

                    cell=cells.get_Item(currentRow,6);
                    cell.set_Value(PurchParmLineSelect.name());
                    cell=null;

                    cell=cells.get_Item(currentRow,7);
                    cell.set_Value(InventDim::find(PurchParmLineSelect.InventDimId).InventSiteId);
                    cell=null;

                    cell=cells.get_Item(currentRow,8);
                    cell.set_Value(InventDim::find(PurchParmLineSelect.InventDimId).InventLocationId);
                    cell=null;

                    cell=cells.get_Item(currentRow,9);
                    cell.set_Value(PurchParmLineSelect.PdsCWReceiveNow);
                    cell=null;

                    cell=cells.get_Item(currentRow,10);
                    cell.set_Value(PurchParmLineSelect.ReceiveNow);
                    cell=null;

                    cell=cells.get_Item(currentRow,11);
                    cell.set_Value(PurchParmLineSelect.PurchPrice);
                    cell=null;

                    cell=cells.get_Item(currentRow,12);
                    cell.set_Value(PurchParmLineSelect.PdsVendBatchDate);
                    cell=null;

                    cell=cells.get_Item(currentRow,13);
                    cell.set_Value(PurchParmLineSelect.LineAmount);
                    cell=null;

                    cell=cells.get_Item(currentRow,14);
                    cell.set_Value(PurchParmLineSelect.PdsVendExpiryDate);
                    cell=null;

                    cell=cells.get_Item(currentRow,15);
                    cell.set_Value(PurchParmLineSelect.qualityOrderStatusDisplay());
                    cell=null;

                  
                }
                
                package.Save();
            }
            memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
            DocuFileSave::processSaveResult(memoryStream, saveResult);
        }

    }

}

I'm a technical developer that has been doing Microsoft Dynamics 365 development since 2018

Comments

  1. CaptJohnDecember 19, 2021 at 3:23 PM

    I tried running this code unfortunately, nothing happened when I clicked the download button. I am new to dynamics 365 x++ .

    ReplyDelete

Post a Comment

Popular posts from this blog

SSRS Report using Controller , Contract and RDP classes in D365

By -
Image
STEP 1 : Create new TEMP table STEP 2 : Create query. STEP 3 : Create a Contract class [DataContractAttribute] class SMJ_PerdiemContract {     PerdiemStatus    perdiemstatus;     [DataMemberAttribute(identifierStr(PerdiemStatus)),         SysOperationLabelAttribute ("Perdiem status"),         SysOperationHelpTextAttribute("Perdiem status"),         SysOperationDisplayOrderAttribute("1")]         public PerdiemStatus parmPerdiemStatus(PerdiemStatus _PerdiemStatus = perdiemstatus)     {         perdiemstatus = _PerdiemStatus;         return perdiemstatus;     } } STEP 4 : Create a DP class: [SRSReportQueryAttribute(queryStr(SMJ_PerdiemReport)), // query  SRSReportParameterAttribute(classStr(SMJ_PerdiemContract))]// contract class     public class SMJ_PerdiemDP extends SRSReportDataProviderBase {     SMJ_PerdiemTableTempDB  perdiemTableTempDB;     PerdiemStatus           perdiemStatus;        PerdiemRequestID        PerdiemRequ
Read more

COC for Form level method

By -
We can extend the logic of public and protected methods without having to use event handlers. COC, allows to use the method local and global variables. COC must be end with _Extension. Form level method : System search there are any extend methods , If Extend method is exist,run COC method first , next() method Invoke Standard form methods . Override Method: [ ExtensionOf ( formstr ( CustTable ))]     final class CustTableForm_Extension     {         public void init()         {             // here add the business logics             next init();             // here add the business logics         }     } Get buffers : FormRun formRun = this as   FormRun ;     FormDataSource CustTable_DS =formRun.datasource(ForomDatasourceStr(CustTable,CustTable));     CustTable custTable = CustTable_DS.Cursor();     FormControl custAccount = formRun.design().ControlName( FormControlStr ( FormName ,ControlName));        Example:
Read more

How to pass the parameter from one form to another in Dynamic365

By -
1. Create two forms with Name FormA & FormB   2. Below code is override in clicked method() of button. void  clicked() {      // Args class is usually used in Axapta for passing parameters between forms      Args             args;      FormRun          formRun;     ;     args =  new   args ();      // Our values which we want to pass to FormB      // If we want pass just simple string we can use 'parm' method of 'Args' class like     args.parm(  LeaveRequestID.text() );      // If we want pass whole record we can use 'parm' method of 'Args' class        args.record(TableName);      // Run FormB     args.name(  formstr ( FormB ) );     formRun = classFactory.formRunClass( Args );     formRun.init();     formrun.run();     formrun.wait();      super (); } 3. Now override init() method in FormB. public   void  init() {      str             anyStringValueFromCaller;      TableName       tablename;
Read more

Multi Select Lookup in SSRS Report in D365

By -
Multi Select Lookup in SSRS Report in D365 Description:-  Here we will create multi select lookup in ssrs report so we can select multiple data from lookup. Here I have given sample demonstration for how to generate multi select lookup in ssrs report. First, you must create contract class for that and create method for parameters. Contract Class: - [DataContractAttribute, SysOperationContractProcessingAttribute( classstr (SampleUIBuilder))] class SampleContract implements SysOperationValidatable {     List   ListItemId; [DataMemberAttribute( "Item Id" ),AifCollectionTypeAttribute( "Item Id" , Types ::String),        SysOperationLabelAttribute( literalStr ( "Item Id" ))]        public List parmItemId( List _ListItemId = ListItemId) {               ListItemId = _ListItemId;               return ListItemId; } } //For validation parameters,     public boolean validate()     {         boolean              is
Read more