D365 Export data on a File - Using Action Menu Item Button click

Exporting a table data

Here is the Action Menu Class 

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.Style;

using OfficeOpenXml.Table;

class SLD_CBLDyeingProcessCodeLineExport

{

    public static void main(Args _args)

    {

        CommaStreamIo           io                      = CommaStreamIo::constructForWrite();

        str                     fileName                = strFmt('%1.csv', "Chemical Lines Export"), fileContent;

        str                     runTime;

           

       CBLDyeingProcessCodeLine  cBLDyeingProcessCodeLine;

 

        // Write header

        io.writeExp(["Serial Number", "Process Id", "Process Type", "Parameter", "Item", "Item Name", "Density", "Gram / Liter", "Time bases", "Consumption", "Chemical rate Rs./Kg", "Cost/Meter","Value" ]);

 

        // Write line

        while select firstfast cBLDyeingProcessCodeLine

  

        {

            io.writeExp([cBLDyeingProcessCodeLine.LineNum,

                         cBLDyeingProcessCodeLine.SLD_ProcessId,

                         cBLDyeingProcessCodeLine.CBLProcTypeEDT,

                         cBLDyeingProcessCodeLine.ParameterName,

                         cBLDyeingProcessCodeLine.Item,

                         cBLDyeingProcessCodeLine.ItemName,

                         cBLDyeingProcessCodeLine.SLD_Density,

                         cBLDyeingProcessCodeLine.Value,

                         cBLDyeingProcessCodeLine.Fixed,

                         cBLDyeingProcessCodeLine.Consumption,

                         cBLDyeingProcessCodeLine.Rs,

                         cBLDyeingProcessCodeLine.Cost_Meter,

                         cBLDyeingProcessCodeLine.Water_LM

                        ]);

        }

        // Set stream

        System.IO.Stream stream = io.getStream();

        stream.Position =0;

        // Set stream reader

        System.IO.StreamReader sReader = new System.IO.StreamReader(stream);

        // Set file cotentn strin

        fileContent = sReader.ReadToEnd();

        // Save file

        file::SendStringAsFileToUser(fileContent, fileName);

    }

}

D365 Excel File Import - Dialouge

 To import excel file you need to create two classes and third you need to use them

Classes:

1) SLD_FileUploadDialogBuilder

/// <summary>
/// Creates a File Dialog and returnns the dialog instance
/// </summary>
public final class SLD_FileUploadDialogBuilder
{
    /// <summary>
    /// Constructs a dialog with file upload control
    /// </summary>
    /// <param name = "_dialogCaption"></param>
    /// <param name = "_dialogGrpFieldLabelTitle"></param>
    /// <param name = "_fileUloadControlId"></param>
    /// <param name = "_fileTypeAcceptd"></param>
    /// <returns></returns>
    public static Dialog construct(str _dialogCaption, str _dialogGrpFieldLabelTitle, str _fileUloadControlId, str _fileTypeAcceptd = ".xlsx")
    {
        Dialog dialog = new Dialog(_dialogCaption);
        DialogGroup dlgUploadGroup = dialog.addGroup(_dialogGrpFieldLabelTitle);
        FormBuildControl formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());

        FileUploadBuild fileUploadBuild =   formBuildControl.addControlEx(classStr(FileUpload), _fileUloadControlId);
        fileUploadBuild.style( FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted(_fileTypeAcceptd);

        return dialog;

    }

}

 

2) SLD_ImportExcelHelper

internal final class SLD_DyeingProcessCodeImportExcelHelper
{

    public static void openDialogToUploadFile()
   {    
         //Construct dialog
        str fileUploadCtrlId = "UploadCtrl";
        Dialog dialog = SLD_FileUploadDialogBuilder::construct("Import Chemical Lines from Excel", "@SYS54759", fileUploadCtrlId, ".xlsx");

        if (dialog.run() && dialog.closedOk())
        {
              //perform excel reading and other operation
            SLD_DyeingProcessCodeImportExcelHelper::startProcessExcelFile(dialog, fileUploadCtrlId);

            
            info("Import is completed.");
           
        }
        else
        {
            warning("No file / canceled");
        }
    }

 

    private static void startProcessExcelFile(Dialog _dialog, str _fileUploadCtrlId)
    {
        int fileControlId = _dialog.formRun().controlId(_fileUploadCtrlId);
        FileUpload fileUploadControl = _dialog.formRun().control(fileControlId);
        FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

        if (fileUploadResult != null && fileUploadResult.getUploadStatus())
        {
            using (System.IO.Stream fileStream = fileUploadResult.openResult())
            {
                using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(fileStream))
                {
                    using ( OfficeOpenXml.ExcelWorksheet excelWrkSheet =  excelPackage.get_Workbook().get_Worksheets().get_Item(1))
                    {
                        using (OfficeOpenXml.ExcelRange excelRange = excelWrkSheet.Cells)
                        {              
                           
                            int lastRow = excelWrkSheet.Dimension.End.Row;
                            for (int rowNum = 2; rowNum <= lastRow; rowNum++)
                            {
                                try
                                {
                                    str processId = excelRange.get_Item(rowNum, 1).Value; //master order
                                    str receiptLineNum = excelRange.get_Item(rowNum, 2).Value;
                                    str processType = excelRange.get_Item(rowNum, 3).Value;
                                    str parameterName = excelRange.get_Item(rowNum, 4).Value;
                                    str itemId = excelRange.get_Item(rowNum, 5).Value;
                                    str itemName = excelRange.get_Item(rowNum, 6).Value;

                                    str density = excelRange.get_Item(rowNum, 7).Value;
                                    str gramPerLtr = excelRange.get_Item(rowNum, 8).Value;
                                    str timeBased = excelRange.get_Item(rowNum, 9).Value;
                                    str consumptionKg = excelRange.get_Item(rowNum, 10).Value;
                                    str chemicalRateRsPerKg = excelRange.get_Item(rowNum, 11).Value;
                                    str costPerMeter = excelRange.get_Item(rowNum, 12).Value;
                                    str value = excelRange.get_Item(rowNum, 13).Value;
                                    str boxAndCane = excelRange.get_Item(rowNum, 14).Value;
                                    str rupees =  excelRange.get_Item(rowNum, 15).Value;

                                    if (SLD_DyeingProcessCodeImportExcelHelper::validateFileKeyFieldsInfo (processId, receiptLineNum))
                                    {
                                        processId = strRTrim(strLTrim(processId));
                                        receiptLineNum = strRTrim(strLTrim(receiptLineNum));
                                        processType = strRTrim(strLTrim(processType));
                                        parameterName = strRTrim(strLTrim(parameterName));
                                        itemId = strRTrim(strLTrim(itemId));

                                        // reset your db insert logic here
                                    
                                    }
                                }
                                catch
                                {
                                    continue;
                                }
                            }
                        }
                    }
                }
            }
        }
    } 

}

 

finally, a button click handler

    [Control("Button")]
    class btnImportChemicalLinesDialogue
    {
        /// <summary>
        ///      Opens the dialog to upload an excel file
        /// </summary>
        public void clicked()
        {                

            SLD_DyeingProcessCodeImportExcelHelper::openDialogToUploadFile(); // note you can also pass form run in parameters
        }

    }