In this blog post, I will demonstrate a modelling pattern that I often use for importing data from Excel, and explain the choices made. The ExcelImporter component from the Mendix AppStore is one of the most used business components in Mendix. So, as a Mendix business engineer, this one should be in your toolbox! I will use a real-life case to demonstrate how I think you can use it best.

Set-up

The easiest way to add the ExcelImporter module to your project, is to click the ‘AppStore’ button from the Mendix Business Modeler.
The ExcelImporter is listed in the Most popular components. Click it, and then click the ‘Download’ button. Also, read the documentation of the appstore module. After downloading, add the ExcelImport as a new module to your project.

Now, if your project suddenly contains a lot of errors, you are missing the MxModelReflection module. Don’t worry about the errors: just download and add the Mx Model Reflection module from the AppStore, and these errors will be resolved.

The case

Consider the following user story:

As a stock manager, I want to register each delivery, in order to keep track on the stock

From our supplier, we receive an Excel file with delivered products for each delivery. This leads to the following domain model in the module ‘Stock’:

The DeliveryFile inherits from ExcelImporter.TemplateDocument, which is itself a specialization of System.FileDocument. This allow file uploads/downloads to the Mendix application.

Why is a specialization of TemplateDocument needed? Because we want to be able to set the role security specifically for this type of object: If you would set the security straight on the ExcelImporter module, you are modifying an AppStore module, which is almost never a good idea. And if you use multiple Excel imports in your application, you want this one to be allowed only for the Stock Manager role. If you would allow a Stock Manager to create TemplateDocument objects, that role would be able to do the other Excel imports as well, which is not want you want.

Why not let the Delivery entity inherit from TemplateDocument?

Although this would also work, from maintenance perspective it would be risky: suppose we want to clean-up all the imported Excel files at some point: then we don’t want the business objects (Delivery objects) to be deleted as well! It is neater to separate the business objects from the technical (file) objects.

Each line from the Excel file (DeliveredProduct) is part of a group (Delivery), and so after importing, we want the created DeliveredProducts to be linked to the Delivery object. We create an ExcelImporter Template titled ‘Delivery’, configured as pictured:

Note the following settings in this template:

• Name: The title of this template will be used in the import microflow, so it is important to spell it correctly.
• Mendix object: This mapping will create objects of type Stock.DeliveredProduct for each line in the Excel file.
• Reference to imported objects: For each object created, the Excel importer will set the reference ‘DeliveredProduct_Delivery’. This means that we need to pass a Delivery object to refer to whenever the import is initiated.
• Import action: We just want to create a new line for each object, not synchronize existing objects in our database. Otherwise, we’d have to specify a key attribute on which to find existing object.
• Columns: Here we just map the columns in our Excel file to the attributes in our domain model. We don’t need to specify key attributes, because the import action is ‘Create all’, so this is very straightforward.

We need the following pages:

• A page that contains a list of all the registered deliveries: Delivery_Overview
• A page where the user can upload a new delivery file: DeliveryFile_Upload
• A page that shows the details of a delivery that has been processed: Delivery_Details

Delivery_Overview contains a Data Grid of all Delivery objects from the database.
The button Details opens the page Delivery_Details.
The button ‘Import new’ opens the microflow DeliveryImportFile_New, which creates a new DeliveryFile object and passes that to the page DeliveryImport_Upload:

The page DeliveryImportFile_Upload contains a data view for the calling DeliveryImportFile object.
The data view contains a File Manager widget, set to upload-only, which allows a user to upload a file.
The button ‘Import file’ in the control bar triggers the microflow DeliveryImportFile_Import:

This microflow first calls another microflow, DeliveryImportFile_RunExcelImporter, which does the actual import.

This microflow contains the call to the Java action StartImportByTemplate from the ExcelImporter module. This Java action will do the actual import:

The parameters explained:

• TemplateObject: The ExcelImporter needs to know how to import the Excel file. This is defined by a template: it defines for example at which row to start and which columns map to which attributes in the domain model. Earlier in this microflow, we have retrieved the template named ‘Delivery’ from the database into the microflow variable $Template.
• ImportExcelDoc: The FileDocument object containing the Excel file. The file manager widget has taken care of this by uploading. The ‘File uploaded’-split earlier in this microflow validates that there is a file uploaded, by checking the boolean  $DeliveryImportFile/hasContents. Now we can know for sure that $DeliveryImportFile contains an uploaded file. However, we do not know if it is a valid Excel file. The ExcelImporter takes care of these checks, and possible raised exception in the Java action. So we need to catch these exceptions by adding an error handler, which will neatly show any exception during import to the user.
• ImportObjectParameter: A template can be configured to set a ‘Reference to import object’. To set this reference, it needs an object. Since our template creates DeliveredProduct objects and sets their reference DeliveredProduct_Delivery, the template needs an object of the entity Delivery. So before calling the import, a new Delivery object is created: $NewDelivery.

If the import is successful (i.e. a Delivery is returned). the rest of the form logic is done, and the page Delivery_Details will be shown for the new Delivery: