Friday, September 21, 2018

Export Excel in Dynamics 365 for finance and operation

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

class VendInvoiceXLExportMatch
{
    public static void main(Args _args)
    {
        VendInvoiceInfoTable        localVendInvoiceInfoTable;
        
        VendInvoiceXLExportMatch ExportMatch = new VendInvoiceXLExportMatch();
        if(!localVendInvoiceInfoTable.Num)
            throw error("Invoice number must be filled in");

        localVendInvoiceInfoTable = _args.record();
        
        ExportMatch.Export(localVendInvoiceInfoTable);
    }

    public void Export(VendInvoiceInfoTable        _localVendInvoiceInfoTable)
    {
        VendInvoiceInfoLine vendInvoiceInfoLine;  
        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 Invoice Data Matching");
            var cells = CustTableWorksheet.get_Cells();
            OfficeOpenXml.ExcelRange cell = cells.get_Item(currentRow, 1);
            System.String value = "PurchId";
            cell.set_Value(value);
            cell = null;
            value = "ItemId";
            cell = cells.get_Item(currentRow, 2);
            cell.set_Value(value);
            value = "Qty";
            cell = cells.get_Item(currentRow, 3);
            cell.set_Value(value);
            value = "PurchPrice";
            cell = cells.get_Item(currentRow, 4);
            cell.set_Value(value);
            value = "LineAmount";
            cell = cells.get_Item(currentRow, 5);
            cell.set_Value(value);

            //Get Data in to Excel
            while select OrigPurchId,ItemId,ReceiveNow,PurchPrice,LineAmount from vendInvoiceInfoLine
                where vendInvoiceInfoLine.ParmId == _localVendInvoiceInfoTable.ParmId
                    && vendInvoiceInfoLine.TableRefId == _localVendInvoiceInfoTable.TableRefId
            {
                currentRow ++;
                cell = null;
                cell = cells.get_Item(currentRow, 1); 
                cell.set_Value(vendInvoiceInfoLine.OrigPurchId);
                cell = null;              
                cell = cells.get_Item(currentRow, 2); 
                cell.set_Value(vendInvoiceInfoLine.ItemId);
                cell = null;
                cell = cells.get_Item(currentRow, 3);
                cell.set_Value(vendInvoiceInfoLine.ReceiveNow);
                cell = null;
                cell = cells.get_Item(currentRow, 4);
                cell.set_Value(vendInvoiceInfoLine.PurchPrice);
                cell = null;
                cell = cells.get_Item(currentRow, 5);
                cell.set_Value(vendInvoiceInfoLine.LineAmount);
            }
            package.Save(); 
            file::SendFileToUser(memoryStream, strFmt('Invoice:%1',_localVendInvoiceInfoTable.Num));
        }
    }

}

No comments:

Post a Comment

Convert Call stack to readable format in D365FO X++

//Input --container _xppCallStack = xSession::xppCallStack();  Public static str POL_formatXppCallStack(container _xppCallStack, int _skipFr...