X++ code to export data in form of excel in D365FO

Hi,

In this post, we will view the code to export selected data in form of excel through X in D365FO. For demo purpose, I am trying to export Purchase agreements data which will have purchase agreement Id and its invoice amount (invoiced through Purchase orders connected to Purchase agreement).

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;

class DAX_SendInfoToExcel
{        
	/// 

    /// Runs the class with the specified arguments.
    /// 
    /// The specified arguments.
    public static void main(Args _args)
    {
        MemoryStream memoryStream = new MemoryStream();
        PurchAgreementHeader	purchAgreement;
        PurchTable				purchTable;
        VendInvoiceJour			vendInvoiceJour;
        VendInvoicePurchLink	vendInvoicePurchLink;

        using (var package = new ExcelPackage(memoryStream))
        {
            var row = 1;

            var worksheets = package.get_Workbook().get_Worksheets();
            var worksheet = worksheets.Add("Export");
            var cells = worksheet.get_Cells();

            OfficeOpenXml.ExcelRange cell = cells.get_Item(row, 1);
            System.String value = "Purchase Agreement";
            cell.set_Value(value);

            cell = null;
            value = "Invoice amount";
            cell = cells.get_Item(row, 2);
            cell.set_Value(value);

            while select purchAgreement
            {
                row   ;
				cell = null;

                cell = cells.get_Item(row, 1);
                cell.set_Value(purchAgreement.PurchNumberSequence);
                cell = null;

				select purchId from purchTable
                    where purchTable.matchingAgreement == purchAgreement.RecId;

				select sum(InvoiceAmount) from vendInvoiceJour
					group by vendInvoiceJour.PurchId
					exists join vendInvoicePurchLink
						where vendInvoiceJour.PurchId   == vendInvoicePurchLink.PurchId
                           && vendInvoiceJour.InvoiceId == vendInvoicePurchLink.InvoiceId
                           && vendInvoiceJour.InternalInvoiceId == vendInvoicePurchLink.InternalInvoiceId
                           && vendInvoiceJour.InvoiceDate == vendInvoicePurchLink.InvoiceDate
                           && vendInvoicePurchLink.origPurchId == purchTable.PurchId;
 	
                cell = cells.get_Item(row, 2);
                cell.set_Value(vendInvoiceJour.InvoiceAmount);
            }

            package.Save();
            file::SendFileToUser(memoryStream, 'PurchAgrmtInvoiceTotals');
        }
    }

}

On executing the above code, we receive below output:

Regards,

Chaitanya Golla