In this article, we'll walk through the process of importing Excel files into D365 F&O for bank charges. After the upload is complete, the contents of the Excel file are read, a journal is created with journal entries, and the journal is posted. The entire process will be divided into two parts. Part One, is this article which focuses on setting up the import process. Part Two, which will be a separate article, which will cover creating journal entries and posting the journal for payment.

Below is the excel file we will be importing.

Part One: Setting Up the Import Process

Create a class and Import Namespaces

Import necessary namespaces to handle file I/O operations and work with Excel files, including reading, writing, and styling Excel documents. These namespaces provide the essential classes and methods for file handling and Excel operations. For instance, System.IO is used for input and output operations with files. OfficeOpenXml and the rest of the namespaces defined are used to read, write, and style Excel documents.

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
using OfficeOpenXml.Style;

Extend RunBaseBatch and Define Variables

Create a class that extends RunBaseBatch. The RunBaseBatch class provides the basic functionality required for batch processing. This class contains all the variables and methods required to perform the import operation. It initializes various variables that will be used throughout the import process.


class BulkImportBankCharges extends RunBaseBatch
{
    Filename filename;
    System.IO.Stream stream;
    FileUploadTemporaryStorageResult fileUploadResult;
    OfficeOpenXml.ExcelRange range;
    int line, lineimported, lineerror, lineskipped;
    Counter loop;
    FileUploadBuild dialogFileUpload;
    FileUpload fileUpload;
    DialogRunbase dialog;
    boolean poUpdate;
    DialogField dialogFieldJournalType, dialogBankAccount, dialogJournalName,
    dialogPayMode;
    JournalType JournalType;
    Period Period;
    LedgerJournalACType ledgerJournalACType, ledgerJournalACTypeOffset;
    MainAccountNum mainAccountNum, mainAccountNumOffset;
    DimensionValue dimensionValue1, dimensionValue2;
    AmountCur amountCurDebit, amountCurCredit;
    ExchRate exchRate, exchRateSecond;
    PaymMode PaymMode;
    Bank Bank;
    Paymode Paymode;
    TransDate transDate;
    JournalName JournalName;
    str eslipNo;
    str payeeName, documentNum, journalType, journalName;

    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        fileName
    #endmacro
}

Implement the Create Method

In this step, we implement the create method responsible for processing each row of data from the uploaded Excel file. The method iterates through the rows, extracting relevant information. For each row, it initiates the creation of journal, journal lines and posting of the journal using the BankChargesJournalCreation helper class. The contents of the helper class will be covered in detail in part two of the article. Lastly the transactions is inserted into receipt table.


void create(int row)
{
    ReceiptTable rTable;
    NumberSeq receptnumberSeq;
    LedgerJournalTrans ledgerJournalTrans;
    ReceiptNo num;
    NumberSeq numberSeq;
    container con;
    str accountType, chargeDesc, refNo;
    int rowCount, columncount, j;
    AccountNum AccountNum;

    transDate = str2Date(range.get_Item(row, 1).Value, 123);
    accountType = range.get_Item(row, 2).Value;
    mainAccountNum = range.get_Item(row, 3).Value;
    dimensionValue1 = range.get_Item(row, 4).Value;
    dimensionValue2 = range.get_Item(row, 5).Value;
    chargeDesc = range.get_Item(row, 6).Value;
    amountCurDebit = range.get_Item(row, 7).Value;
    amountCurCredit = range.get_Item(row, 8).Value;
    payeeName = range.get_Item(row, 9).Value;
    exchRate = 1;
    refNo = range.get_Item(row, 10).Value;

    if (!transDate || !mainAccountNum || !payeeName || !amountCurDebit || !documentNum || !Period)
    {
        Box::warning("Incomplete Data in the Excel");
    }

    // Ledger dimension accounts
    LedgerDimensionAccount custAccount;
    LedgerDimensionAccount bankAcc;

    custAccount = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(AccountNum, LedgerJournalACType::Cust);
    // Offset A/c
    bankAcc = LedgerDynamicAccountHelper::getDynamicAccountFromAccountNumber(Bank, LedgerJournalACType::bank);

    // Create a GL journal - Customer & Ledger
    JournalId jNum = BankChargesJournalCreation::createJournal(journalName, 'Bulk Bank Charges');
    BankChargesJournalCreation::createLines(jNum, PaymMode, mainAccountNum, dimensionValue1, dimensionValue2, "Bulk Bank Charges", LedgerJournalACType::Ledger, 0, LedgerJournalACType::Bank, bankAcc, amountCurDebit, Ledger::accountingCurrency(CompanyInfo::current()), journalName);

    // Post Journal
    BankChargesJournalCreation::postLedgerJournal(jNum);

    // Insert into Receipt Table
    ttsbegin;
    receptnumberSeq = NumberSeq::newGetNum(ReceiptParameters::numRefReceiptNos());
    num = receptnumberSeq.num();
    receptnumberSeq.used();

    rTable.ReceiptNo = num;
    rTable.ReceiptDate = transDate;
    rTable.ReceiptType = ReceiptType::None;
    rTable.AccountNum = mainAccountNum;
    rTable.AmountReceived = amountCurDebit;
    rTable.PaymMode = PaymMode;
    rTable.BankAccountNum = mainAccountNumOffset;
    rTable.DocumentNo = documentNum;
    rTable.DocumentDate = transDate;
    rTable.DateEntered = today();
    rTable.Period = Period;
    rTable.ReceivedFrom = payeeName;
    rTable.EnteredBy = curUserId();
    rTable.DatePosted = today();
    rTable.Posted = NoYes::Yes;
    rTable.PostedBy = curUserId();
    rTable.Description = 'Bank Charges';
    rTable.insert();
    ttscommit;
}

Perform the Import Operation

Define the import method to perform the import operation. This method orchestrates the import process by opening the Excel file, iterating through the rows, and calling the create method for each row to create journal entries.

void import()
{
    setPrefix("Import Bank Charges Lines");
    poUpdate = false;

    if (this.openFile())
    {
        using (ExcelPackage Package = new ExcelPackage(stream))
        {
            int rowCount, i, endRow, startRow;

            Package.Load(stream);

            ExcelWorksheet worksheet = Package.Workbook.Worksheets[1];
            range = worksheet.Cells;

            endRow = worksheet.Dimension.End.Row;
            startRow = worksheet.Dimension.Start.Row;
            rowCount = endRow - startRow + 1;

            try
            {
                for (i = 2; i <= rowCount; i++)
                {
                    real AmountCur = range[i, 7].Value;
                    // If charges is zero, stop processing
                    if (AmountCur == 0)
                    {
                        break;
                    }
                    // Try-catch block
                    try
                    {
                        this.create(i);
                        lineimported++;
                    }
                    catch
                    {
                        lineerror++;
                    }
                }
            }
            catch
            {
                throw error("Error - Import template");
            }
        }
    }

    if (lineerror > 0)
    {
        Box::warning(strfmt("%1 record(s) failed during import!", lineerror));
    }
    else
    {
        Box::info(strfmt("%1 Record(s) imported successfully", lineimported));
    }
}

Open the File

Define the openFile method to handle the file opening process. This method checks if the file upload was successful and opens the file stream for reading.

boolean openFile()
{
    boolean ret = false;

    if (fileUploadResult != null && fileUploadResult.getUploadStatus())
    {
        stream = fileUploadResult.openResult();
        ret = true;
    }
    else
    {
        throw error(strfmt("@SYS62207", filename));
    }

    return ret;
}

Create Dialog

Define the dialog method to create a dialog for file upload and other inputs. This method creates a dialog interface for the user to upload the Excel file and input additional details like Journal Type, Journal Name, Bank Account, and Payment Mode. The dialog also includes a button for template creation.

public Object dialog()
{
    dialog = super();

    DialogGroup dialogGroup = dialog.addGroup('Upload file');
    DialogGroup mainGroup;
    FormBuildControl dialogGroupControl;
    FormBuildButtonControl CreateTemp;
    FormBuildControl formBuildControl = dialog.formBuildDesign().control(dialogGroup.name());

    dialogFileUpload = formBuildControl.addControlEx(classstr(FileUpload), 'FileUpload');
    dialogFileUpload.style(FileUploadStyle::MinimalWithFilename);
    dialogFileUpload.fileNameLabel("@SYS308842");
    dialogFileUpload.fileTypesAccepted(".xlsx");

    // Additional dialog groups and fields
    dialog.addGroup('Journal Details').columns(2);
    dialogFieldJournalType = dialog.addFieldValue(enumStr(JournalType), JournalType, "Journal Type", "");
    dialogJournalName = dialog.addField(extendedTypeStr(JournalName), JournalName);

    dialog.addGroup('Payment').columns(2);
    dialogBankAccount = dialog.addField(extendedTypeStr(Bank), Bank);
    dialogPayMode = dialog.addField(extendedTypeStr(Paymode), Paymode);

    mainGroup = dialog.addGroup("Group");
    dialogGroupControl = dialog.formBuildDesign().control(mainGroup.formBuildGroup().id());
    CreateTemp = dialogGroupControl.addControl(FormControlType::Button, 'CreateTemp');
    CreateTemp.text("@SYS91539");

    return dialog;
}

Handle Dialog Post Run

Define dialogPostRun method to handle the dialog post-run events and button actions. These methods manage the dialog's behavior during and after the file upload process. It ensures that the Ok button is enabled only after the file upload is completed successfully.

/// <summary>
/// Disables the dialog Ok button until the file upload is complete.
/// </summary>
/// <param name="_dialog">The <c>Runbase</c> dialog object.</param>
public void dialogPostRun(DialogRunbase _dialog)
{
    fileUpload = dialog.formRun().control(dialog.formRun().controlId('FileUpload'));
    fileUpload.notifyUploadCompleted += eventhandler(this.uploadCompleted);
    this.setDialogOkButtonEnabled(_dialog, false);
}
/// <summary>
/// After the file has been uploaded, the Ok button is enabled.
/// </summary>
public void uploadCompleted()
{
    filename = fileUpload.fileName();
    this.setDialogOkButtonEnabled(dialog, true);
    fileUploadResult = fileUpload.getFileUploadResult();
    fileUpload.notifyUploadCompleted -= eventhandler(this.uploadCompleted);
}
/// <summary>
/// Enables or disables the dialog Ok button.
/// </summary>
/// <param name="_dialog">The <c>Runbase</c> dialog object.</param>
/// <param name="_isEnabled">Indicates to enable or disable the Ok button.</param>
protected void setDialogOkButtonEnabled(DialogRunbase dialog, boolean isEnabled)
{
    FormControl okButtonControl = dialog.formRun().control(dialog.formRun().controlId('OkButton'));
    if (okButtonControl)
    {
        okButtonControl.enabled(isEnabled);
    }
}

Get Values from Dialog

Define the getFromDialog method to retrieve values entered in the dialog fields. This method retrieves the values entered by the user in the dialog fields for Journal Type, Bank Account, Journal Name, and Payment Mode.

public boolean getFromDialog()
{
    boolean ret;
    ret = super();

    JournalType = dialogFieldJournalType.value();
    mainAccountNumOffset = dialogBankAccount.value();
    journalName = dialogJournalName.value();
    Bank = dialogBankAccount.value();
    PaymMode = dialogPayMode.value();

    return ret;
}

Pack and Unpack Methods

These pack and unpack methods serialize (pack) and deserialize (unpack) the object's data into a container format, ensuring it can be stored and retrieved consistently.

public container pack()
{
    return [#CurrentVersion, #CurrentList];
}

public boolean unpack(container packedClass)
{
    Version version = runbase::getVersion(packedClass);

    switch (version)
    {
        case #CurrentVersion:
            [version, #CurrentList] = packedClass;
            break;

        default:
            return false;
    }

    return true;
}

Create the Template

Define the CreateTemp_clicked method to generate an Excel template with predefined column headers.

public void CreateTemp_clicked()
{
    System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
    MultiSelectionHelper multiSelHelper = MultiSelectionHelper::construct();
    MemoryStream memoryStream = new MemoryStream();

    using (var package = new ExcelPackage(memoryStream))
    {
        var currentRow = 1;
        var worksheets = package.Workbook.Worksheets;
        var bankFileWorksheet = worksheets.Add("Export");
        var cells = bankFileWorksheet.Cells;

        OfficeOpenXml.ExcelRange cell = cells[currentRow, 1];
        cell.Value = "Trans Date";

        cell = cells[currentRow, 2];
        cell.Value = "Account Type";

        cell = cells[currentRow, 3];
        cell.Value = "Main Account";

        cell = cells[currentRow, 4];
        cell.Value = "Cost Center";

        cell = cells[currentRow, 5];
        cell.Value = "Source of Fund";

        cell = cells[currentRow, 6];
        cell.Value = "Description";

        cell = cells[currentRow, 7];
        cell.Value = "Debit";

        cell = cells[currentRow, 8];
        cell.Value = "Credit";

        cell = cells[currentRow, 9];
        cell.Value = "Payee Name";

        cell = cells[currentRow, 10];
        cell.Value = "Ref No";

        currentRow++;

        package.SaveAs(memoryStream);
        file::SendFileToUser(memoryStream, "Bank Charges.xlsx");
    }
}

Run the Class

The run method initiates the import process when the class is executed. It prompts the user with the dialog for file upload and other inputs, runs the import operation, and displays the result.

public void run()
{
    this.import();
    super();
}

static void main(Args _args)
{
    BulkImportBankCharges import;
    FormRun formRun;
    Args args;

    import = new BulkImportBankCharges();

    if (import.prompt())
    {
        import.run();
    }
}

public ClassDescription caption()
{
    return "Import Bank Charges";
}

Output

Conclusion

In conclusion, We've set up the import process, where we've imported an excel file and read from the excel. Stay tuned for part two where we will delve into creating journal entries and posting the journal. This is first of many articles to come.

Excel Import and Journal Processing in D365 F&O - Part One

Table of contents