Excel Import Extended Column Mapping and Import Value Type Handling

UPDATED

I'm updating the original entry I did for the Excel Import Extension. I've re-written it in a much better and concise format.

Please note you'll have to have completed the original Import Excel Sample from serenity to fully implement this extension

---

There are 2 types of files you'll use, general-reusable files and module-specific files. These make up a total of 5 files.

General files: Built to be able to be used by any module regardless.

• myImportHelper.cs
• myImportFields.cs
• ExcelImportForm.cs

Module-specific files: Built to specifically manage your modules' need.

• myModuleImportDialog.ts
• myModuleExcelImportEndpoint.cs

You can find all these files in: https://github.com/sayuga/SerenityTutorials/tree/master/SerenityExcelImport

---

You'll want to add the 3 general files in a folder you can easily reference for all your modules. Aside from updating the project root namespace, you shouldn't be needing to edit any other than the myImportFields. More about that below.

Next, add the module-specific files to your module and edit the root namespace as well as the any sections labelled as myModule like the class name in myModuleExcelImportEndpoint.cs.

[ConnectionKey(typeof(MyRow)), ServiceAuthorize]
public class myModuleExcelImportController : ServiceEndpoint
{
}

---

In the myModuleImportDialog.ts, you'll want to update the class name as well as making sure the correct service is called. Additionally, if you use lookups, you'll want to reload those.

 @Serenity.Decorators.registerClass()
    export class myModuleExcelImportDialog extends Serenity.PropertyDialog<any, any> {

        private form: ExcelImportForm;
               //...

                        myModuleExcelImportService.ExcelImport({
                            FileName: this.form.FileName.value.Filename
                        }, response => {
                            //if you have lookups you will need to reload each of them to refresh any new
                            //values using Q.reloadLookup. Example: Q.reloadLookup('Default.AddressLog')
                        //...
                    },
                },
           ];
        }
    }
}

Now lets edit the myModuleExcelImportEndpoint.cs file. Update the namespace and class accordingly. After doing that, you'll be doing the following, adding a list of exceptionHeaders to skip and adding a reference to the handler to be used when importing to a field.

Exception Header is a list of the titles of system fields you don't want to capture in the error messages. Items such as ID fields for joint items or other such fields you don't want the user to be told are missing from the importation file.

To add Exception headers use exceptionHeaders.Add(myFields.someField.Title). You'll need to change someField with the Field from the table you wish to add to the exception list and you'll need to add a Add() call for each title.

---

To add the handlers you'll have to add a specific codeset.

entType = jImpHelp.entryType.String; //<--Update Me according to type of field to merge with
fieldTitle = myFields.someField.Title;//<--Update Me
obj = myImpHelp.myExcelVal(row, myImpHelpExt.GetEntry(headerMap, fieldTitle).Value, worksheet);
if (obj != null)
{
    importedValues.Add(obj);
    sysHeader.Add(fieldTitle);
    a = jImpHelp.myImportEntry(importedValues, myErrors, sysHeader, row, entType, myConnection);
    if (a != null)
    {
        currentRow.someField= a; //<--Update Me
    }
    sysHeader.Clear();
    importedValues.Clear();
}

You'll notice 3 locations needing to be updated.

• entType: References a type from an enum created to properly call the neccesary handler according to the field being imported.
• fieldTitle: Title of the field that will be checked for in the imported file and then used to access the field value in the imported file.
• currentRow.someField: The field for the value to merge into.

You'll want to create this set for each field you wish to import and update the 3 fields accordingly.

You'll notice that there are two null checks. This is to ensure that the field only merges a value as long as the column exists in the imported file and if the value imported is not null.

---

Lastly, you'll want to edit your myImportFields.cs file. Here you create the handlers and update the enum used to reference the correct handler.

To update the enum, just add the field you wish to create to the list like with any other enum:

public enum entryType
{
    String = 1,
    Decimal = 2,
    //..
    newEnumVal = 10
}

You can then update the myImportEntry by adding the case to the switch:

public static object myImportEntry(List<object> myVal, List<string> myErrors, List<string> expectedHeader, int row, entryType myType, IDbConnection myConnection)
{
    object myField = null;
    object val = myVal[0];
    try
    {
        switch (myType)
        {
            case entryType.String:
                myField = Convert.ToString(val);
                break;
            case entryType.Decimal:
                myField = Convert.ToDecimal(val);
                break;
            case entryType.newEnumVal :
                myField =""; //handle incoming value. Example: oneValJoin(myConnection, myErrors, row, expectedHeader[0], val);
                break;                `
            default:
                myField = null;
                break;
        }
        return myField;
    }
    catch (Exception e)
    {
        foreach (string exh in expectedHeader)
        {
            myErrors.Add(exh + " on row " + row + " had a Exception : " + e.Message);
        }

        return myField;
    }
}

You'll notice that the myVals and the expectedHeaders are lists. This is so that you can use it for one or many values depending on the needs of your handler.

---

All that is left is to add the button to the myModuleGrid.ts:

getButtons() {
    var buttons = super.getButtons();
    var MyRow = CustomerRow.Fields;

    buttons.push(Common.ExcelExportHelper.createToolButton({
        grid: this,
        onViewSubmit: () => this.onViewSubmit(),
        service: myModuleService.baseUrl + '/ListExcel',
        separator: true,
        hint: "",
        title: "Master List"
    }));
           
    // add our import button
    buttons.push({
        title: 'Import Excel',
        cssClass: 'export-xlsx-button',
        onClick: () => {
            // open import dialog, let it handle rest
            var dialog = new myModuleExcelImportDialog();
            dialog.element.on('dialogclose', () => {
                this.refresh();
                dialog = null;
            });
            dialog.dialogOpen();
        }
    });

    return buttons;
}

Examples of Custom Handlers

Joint Field with a Single Required Field:

public static Int32 oneValJoin(IDbConnection myConnection, List<String> myErrors, int row, string expectedHeader, dynamic val)
{
    string wsField = Convert.ToString(val);
    var g = AddressLogRow.Fields;
    var currRow = myConnection.TryFirst<AddressLogRow>(q => q.Select(g.AddressLogId).Where(g.Floor == wsField));

    if (currRow == null)
    {
        try
        {
            var newRow = myConnection.InsertAndGetID(new AddressLogRow { Floor = wsField, Room  = "(Imported Field)" }); //create new entry
            myErrors.Add("Warning: On Row " + row + ": Entity with name " + wsField + " was not found so it was added.");
            return Convert.ToInt32(newRow);
        }
        catch (IOException e)
        {
            myErrors.Add("IOException for Field " + expectedHeader + " on row " + row + " : " + e.Message);
        }
    }
    return currRow.AddressLogId.Value;
}

Joint Field with Multiple Required Fields:

public static Int32 multiValJoin(IDbConnection myConnection, List<String> myErrors, int row, List<string>expectedHeader, List<dynamic> val)
{
    string[] wsField = new string[] { Convert.ToString(val[0]), Convert.ToString(val[1]) };
    var g = AddressLogRow.Fields;
    var currRow = myConnection.TryFirst<AddressLogRow>(q => q.Select(g.CustomerId).Where(g.Floor == wsField[0] & g.Room== wsField[1])); //checks if field exists

    if (currRow == null)
    {
        try
        {
            var newRow = myConnection.InsertAndGetID(new AddressLogRow { Floor = wsField[0], Room = wsField[1] }); //create new entry                                `
            myErrors.Add("Warning: On Row " + row + ": Entity with name " + wsField[0] + " and sub-name "+ wsField[1]+ "was not found so it was added.");
            return Convert.ToInt32(newRow);
        }
        catch (IOException e)
        {
            myErrors.Add("IOException for Fields " + expectedHeader[0]+" and "+ expectedHeader[1] + "  on row " + row + " : " + e.Message);                    
        }
    }
    return currRow.CustomerId.Value;
}