How to import Excel File with Excel Importer| Mendix 10
Prerequisites Marketplace modules
Before starting the tutorial, ensure that you have the Mx Model Reflection and Community Commons modules installed in your project. If you don’t have it, you can install it using the following links.
- Mx Model Reflection
https://marketplace.mendix.com/link/component/69
- Community Commons
https://marketplace.mendix.com/link/component/170
Step by Step
1- Search for “Excel Importer” on the marketplace in Studio Pro and click download
2- Select the option “Add as a new module” and click “Import”
3- Go to the App Explorer, right click and choose “Add Module”. Give it the Name “ExcelImport”.
4- Inside the module “ExcelImport”, go to “Security” and create a User Role with the name “User”
5- Open “Security” under the project settings.
Go to User Role and choose “Administrator”.
Edit the module roles and make sure the role has permissions to the following modules:
- CommunityCommons
- ExcelImporter
- MxModelReflection
- ExcelImport
6- In the current project we have on the main module only one entity called “Request” and also a page with a data grid.
7- Inside the domain model of the ExcelImport, create a non-persistent entity called “RequestNP”.
Give read and write permissions to the “User” module role.
8- Create another persistent Entity with the name “File”. This entity should be a generalization of the “TemplateDocument” entity from the module “ExcelImporter”.
Give read and write permissions to the “User” module role.
9- Associate both entities with an association of 1 to many (one file to many RequestNP)
10- Create the following folder structure for the “File” entity.
11- Add a new page inside the “Pages” folder. Name the page “File_Import” and select the “Blank Page Template”.
12- Drag and drop a data view to the page. Insert a “file manager” widget inside the data view.
13- Create a new button on the homepage. As “On click” event, choose “Create object”. For entity select “File” and for the page “File_Import”
14- Inside the dataview, add a new button with the caption “Upload”. Create a microflow named “ACT_File_Import” and choose “call a microflow” as an event
15- Confirm if the microflow has a “File object” as a parameter.
16- Add a “Retrieve” action with the entity “Template”. As a “Range”, choose “First”. For the “XPath constraint write “[Title = ‘Requests’]” and change the Object name to “Template”.
Add a decision with the following condition, “$Template != empty”. To the false, show a message saying “No template selected for this file”.
17- Search for “StartImportByTemplate” java action on the App Explorer.
Template object: $Template
Import excel doc: $File
Import object parameter: $File
18- Right-click on the java action and click on “Set error handling”. Select as type “Custom without rollback”
19- On the Error handler path, add a “Show message” activity with the text “The following error occurred when importing document: {1} — {2}”.
Parameter 1: $File/Name
Parameter 2: $latestError/Message
20- Create a new list of type “Request” from the main module. Change the name to “RequestList_toCommit”
21- Add a new “Retrieve” action and select as a Source “By association”. Change the list name to “RequestNPList”
22- Insert a “Loop” action after the retrieve. For the “Loop type” choose “For each”. For “Iterate over” select “RequestNPList”.
23- Put inside the loop a “Retrieve” action. As a Source, select From database and choose “Request”. Choose “First” and add to the XPath constraint “[Name = $IteratorRequestNP/Name]”.
Drag and drop a list operation and select the “Find” Operation.
List: RequestList_toCommit
Member: Name
Equals: $IteratorRequestNP/Name
24- Add a decision with the expression: “Request = empty and $FindRequest = empty”
25- To the happy way, add a “Create Request” object and change the values to:
Name: $IteratorRequestNP/Name
Status: $IteratorRequestNP/Status
After that action, change the “RequestList_toCommit” by adding the object “$NewRequest”
26- Outside the loop, create a decision with the expression: “$RequestList_toCommit != empty”
27- If true, add a commit Object action and commit the list “RequestList_toCommit”
28- Select all the actions, from the Retrieve action with the name “RequestNPList” until the end of the loop, right-click and select “extract to a microflow”. Give it the name “SUB_File_Import”
29- Select everything inside the ACT microflow and extract to a microflow with the prefix BP (Business Process). The final name should be “BP_File_Import”
30- Inside the BP microflow, add a new variable action to the start of the microflow. Select “Boolean” as a Data type and “true” as a valid. Change the variable name to “AllValid”.
To the false way of the decisions, change the variable value to “false”. Check the images below.
31- Return to the ACT microflow and add a decision after the BP. Inside the decision write “AllValid”. After that “Show a message” saying “The import is finished” and close the page.
32- Go to the project “Navigation” and add the “ExcelImporter” overview page
33- Go to the pages and microflows created and change the roles allowed.
34- Run the project
35- Go to the Model Reflection page and select the following modules:
- ExcelImport
- ExcelImporter
- YOURMAINMODULE
Click the button “Click to Refresh”
36- Inside the ExcelImport overview page, create a new template like the image below.
37- At the end of the page, create new columns. To select the first column of the Excel file, you should select as a “column number” the number 0. Create a column for each attribute.
38- Return to the homepage and click on the “File Import” button.
39- Upload your Excel file and click “Upload”
40- If all went well, you will see the values from the Excel file in your application
This is the end of the tutorial.
I’m creating a Mendix community on Discord to make life easier for devs. You can ask questions, help other people and have access to all the tutorials.
If you want to be part of this community, you can do so through this invite.
Discord: https://discord.gg/YHre8dXz3q
Read more
From the Writer
If you enjoyed this article you can find more on our Medium page. For great videos, you can visit our Youtube page.
Are you interested in getting more involved with our community? Join us in our Discord Community Channel.
From the Publisher -
Inspired by this article to bring your ideas to life with Mendix? Sign up for a free account! You’ll get instant access to the Mendix Academy, where you can start building your skills.
For more articles like this one, visit our Medium page. And you can find a wealth of instructional videos on our community YouTube page.
Speaking of our community, join us in our Slack community channel. We’d love to hear your ideas and insights!
