The need to export excel files is something common in the day to day of a business. In this blog post, I will teach you how to export excel files using Excel Exporter module.
Prerequisites Marketplace modules
- Mx Model Reflection Before starting the tutorial, make sure that you have the Mx Model Reflection module installed in your project. If you don't have it, you can install it using the following link. https://marketplace.mendix.com/link/component/69
Step by Step
1 - Download the module Excel Exporter from the marketplace.
2 - Insert page "Excel_Document_Overview" into navigation (and MxObjects_Overview from Model Reflection if you don't have it yet)
3 - Create a new project module called ExcelExport
4 - Double click on the module security and create a new module role called "Admin"
5 - Go to application security, select admin and click in Edit roles. Give admin access to this role
6 - In ExcelExport module, create ExcelDocument Entity as a Generalization of FileDocument
7 - Select Access Rules tab and give permissions to the user role "Admin".
8 - Create another entity called ProjectExport with the same attributes of the entity that you want to export the data.
9 - Select Access Rules tab and give permissions to the user roles.
10 - Add an association between ExcelExport and ProjectExport. This association should be 1 to * (one to many)
11 - Double click on the association and select "Delete 'ProjectExport' object(s) as well" under "On Delete of 'ExcelDocument' object"
12 - Create a folder to each entity as such:
13 - Create a microflow called ACT_ProjectExport_ExportList inside the "ProjectExport/Microflows/ACT" folder
14 - Inside the microflow, add a retrieve action. Select as a source "From Database" and choose the entity "MxTemplate". As a constraint write "[Name = 'Projects']" (The "Projects" can be changed for what makes more sense in your own project)
15 - Add a decision to check if $MxTemplate is not empty ($MxTemplate != empty)
16 - Add a Create Object action. Select as Entity "ExcelDocument" from ExcelExport Module. Click in New and select "DeleteAfterDownload" attribute. Set the value as "true".
17 - Create a new list. As entity use "ProjectExport" from ExcelExport Module and change the list name to ProjectExportList.
18 - Add a new Retrieve action to the microflow with the entity from where you want to export the data. As an example we will select "Project".
19 - Now it is necessary to iterate throughout the list. To do so, add a Loop action and select the list we just retrieved.
20 - Inside the loop, add a new create object action. Select as a Entity "ProjectExport" from ExcelExport Module. Fill all the entity attributes with the data from the iterator.
21 - After the attributes values, fill the association with the ExcelDocument object.
22 - Add a change list action. Select "ProjectExportList" and use the object we just created as a value.
23 - Outside the loop, drag and drop a commit object action. Select the list called "ProjectExportList".
24 - Go to microflow properties and give access to the module role "admin".
25 - Save and go to the App Module -> Marketplace Modules -> XLSReport -> UseMe Folder and Select "GenerateReport" microflow.
26 - Right click on the microflow and click Duplicate. Right Click on the duplicated microflow and move to ExcelExport Module -> ExcelDocument/Microflows/SUB folder and rename it to SUB_ExcelDocument_GenerateReport
27 - Inside the microflow, delete the create CustomExcel action. Regarding the parameters, add a new with a data type object and with ExcelDocument as a entity.
28 - Double click on the java action "GenerateExcel". Change the inputs values to the following objects:
Template object: $Template
Output Document: $ExcelDocument
Input Object: $ExcelDocument
29 - Go to microflow properties and give access to the module role "admin".
30 - Go back to the ACT_ProjectExport_ExportList microflow and add at the end the SUB_ExcelDocument_GenerateReport. Double click on the SUB and fill the parameter values.
31 - In your overview page, add a new microflow button and select ACT_ProjectExport_ExportList microflow.
Change the caption to "Export" and the icon to the export icon.
32 - After running the project, click on the Model Reflection page located on the menu.
33 - Select "ExcelExport" and "XLSReport" Modules. Then, “Click to Refresh” in order to synchronise all entities and microflows.
34 - On the menu, click on Excel Exporter page and Select New button.
35 - Create a new template by filling the fields with the following values and click "Save and Next":
Document Type: Excel 2007 and higher
Filename: Projects
Input object: ExcelExport.ExcelDocument
36 - Select a date format for the "Date time export format" field
37 - In Worksheets, click in new and fill all the fields with the data that you see on the image below.
38 - Click on the "New" button inside the "Column data" tab. A popup will appear, in which we can fill in the column number, name and select the attribute we want to see exported. It is important that the first column has the number 0 instead of 1.
39 - Create all the columns you need for your export.
40 - Go back to the overview page and click on the "Export" button.
After the download is finished, you can open the file and all your projects will be available.
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
From the publisher
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.
Top comments (0)