Wednesday, November 28, 2018

File Uploads in Dynamics 365 For Operations

I was recently reviewing some file import methods and I came across a new (for me) method of uploading files from a client.  It turns out this is quite simple.

This calls a file finding dialog:
FileUploadTemporaryStorageResult uploadResult = Dynamics.AX.Application.File::GetFileFromUser();

This returns a System.IO.Stream:
System.IO.Stream stream = uploadResult.openResult();

These snippets were part of class that imported a 2 column spreadsheet in Excel.  Please note that X++ now supports using statements. Here is the full code for reference:

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

public class PayrollCodeImport
{
    public static void main(Args _args)
    {
        System.String value = null;
        OfficeOpenXml.ExcelRange cell = null;
        PayrollCode payrollCode;
        //upload
        FileUploadTemporaryStorageResult uploadResult = Dynamics.AX.Application.File::GetFileFromUser();
        PayrollCodeId payrollCodeId;
        Description  description;

        if(!uploadResult)
        {
            return;
        }
        //construct a new spreadsheet document with the upload stream
        OfficeOpenXml.ExcelPackage package = new ExcelPackage(uploadResult.openResult());
        //OXMLSpreadsheetDocument_RU spreadsheet = OXMLSpreadsheetDocument_RU::constructFromStream(uploadResult.openResult());
        //get the worksheets from the workbook
        OfficeOpenXml.ExcelWorksheets worksheets = package.get_Workbook().get_Worksheets();
        //get the worksheet by name
        OfficeOpenXml.ExcelWorksheet payrollcodeWorksheet = worksheets.get_Item("Import") as OfficeOpenXml.ExcelWorksheet;
        //skip the header line
        for (int i = payrollcodeWorksheet.Dimension.Start.Row + 1; i <= payrollcodeWorksheet.Dimension.End.Row; i++)
        {
            value = null;
            cell = payrollcodeWorksheet.Cells.get_Item(i, 1);
            value = cell.Value;
            payrollCodeId = value;

            value = null;
            cell = payrollcodeWorksheet.Cells.get_Item(i, 2);
            value = cell.Value;
            description = value;

            if(payrollCodeId && description)
            {
                payrollCode.Clear();

                select forupdate payrollCode where payrollCode.PayrollCodeId == payrollCodeId;
                ttsbegin;
                if(!payrollCode.RecId)
                {
                    payrollCode.PayrollCodeId = payrollCodeId;
                    payrollCode.Description = Description;
                    payrollCode.insert();
                }

                ttscommit;
            }
        }
    }

}