High Performance – Excel Generation (Dynamic Entity & Columns)

In my previous blog post, I went over how to create Excel sheets with predefined columns. For reference, if you missed it, you can find it right here: A Comprehensive Guide to Creating High Performance – Excel Generation

We’ll delve into this subject in more detail and offer a thorough tutorial on how to create high-performance Excel generation in this blog post. For data analysis, reporting, and visualisation, Excel is a crucial tool. However, creating Excel files can be time- and resource-consuming, especially when working with large datasets and altering Mendix’s column layout.

As a result, we will examine Excel generation with dynamic entities and columns in more detail in this post using a straightforward Java action and the Apache POI Java library. We’ll look at how to generate Excel files on the fly based on shifting requirements and Mendix data structures.

You will have a clear understanding of how to create Excel sheets with dynamic entities and columns in Mendix by the end of this post, giving you the ability to handle various data structures with ease. Prepare to increase your understanding of and proficiency with Excel generation!

To generate Excel sheets with dynamic entities and columns, follow the 10 steps outlined below. These steps will guide you through the process and help ensure that your Excel generation is optimized for high performance and efficiency.

Let us start with a simple domain model for Student Excel Generation

Create the simple Mendix application with the above domain model and make sure the following POI jars have been added to your user lib folder of the project as shown below.

commons-codec-l.15.jar	poi-5.2.3.jar
commons-collections4-4.4.jar	poi-examples-5.2.3.jar
commons-compress-1.21.jar	poi-excelant-5.2.3.jar
commons-io-2.11.0.jar	poi-javadoc-5.2.3.jar
commons-logging-1.2.jar	poi-ooxml-5.2.3.jar
commons-math3-3.6.1.jar	poi-ooxml-full-5.2.3.jar
curvesapi-1.07.jar	poi-ooxml-lite-5.2.3.jar
jakarta.activation-2.0.1.jar	poi-scratchpad-5.2.3.jar
jakarta.xml.bind-api-3.0.1.jar SparseBitSet- 1.2.jar	SparseBitSet- 1.2.jar
log4j-api-2.18.0.jar	xmlbeans-5.1.1.jar
slf4j-api-1.7.36.jar

Find the jars in https://mvnrepository.com/

Step 1: Create a new java action for generating Excel with two parameters.

1. List of Dynamic Entity Objects (Type Parameter)
2. File Document object

The output of the java action is an excel-file-document.

a. First create a type of parameter called DynamicEntity under the Type parameters tab of java action.

b. Now create two parameters, one by selecting the List as type and the DynamicEntity type parameter as Entity, and the other one is File Document Object.

Step 2: Click deploy for the eclipse to edit the Java action and remove the line which has the sentence “Java action was not implemented”.

Step 3: Now start writing the code between the begin user code and the end user code section.

Step 4: Create a workbook that helps to create the Excel file in .xlsx.

XSSFWorkbook workbook = new XSSFWorkbook ();

Step 5: Now, create a blank Excel sheet with the sheet name.

XSSFSheet sheet = workbook.createSheet(“Student Data”);

Step 6: Add the below code snippet to apply some styles to the header of the excel sheet.

CellStyle style = workbook.createCellStyle();

Font headerFont = workbook.createFont();

headerFont.setColor(IndexedColors.WHITE.index);

style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style.setFont(headerFont);

Step 7: Add the below code snippet to create headers for Excel by creating row and respective cells for each attribute in an entity. The below snippet generates a header row with dynamic columns.

// Row Creation

XSSFRow row;

int rowid = 0;

row = spreadsheet.createRow(rowid++);

// Cell Creation

IMendixObject TopStudent = DynamicEntity.stream().findFirst().get();

for (int i = 0; i < TopStudent.getMembers(getContext()).size(); i++)

{

Set<?> columns = TopStudent.getMembers(getContext()).entrySet();

Object[] columnsarray = columns.toArray();

String[] column = columnsarray[i].toString().split(“=”);

String header = column[0];

Cell headercell = row.createCell(i);

headercell.setCellValue(header);

headercell.setCellStyle(style);

}           

Step 8: Below snippet helps to create each row for each record of the dynamic entity containing each column.

for (IMendixObject student: DynamicEntity) {

// Row creation for each student record

row = spreadsheet.createRow(rowid++);

int cellid = 0;

// Cell creation for each student attribute

for (int i = 0; i < student.getMembers(getContext()).size(); i++)

{

Set<?> columns = student.getMembers(getContext()).entrySet();

Object[] columnsarray = columns.toArray();

String[] column = columnsarray[i].toString().split(“=”);

String header = column[0];

if (!(student.getValue(getContext(), header)==null))

{

Cell cell = row.createCell(cellid++);

cell.setCellValue(student.getValue(getContext(), header).toString());

}

}

}

Step 9: Write the created workbook with the help of the following snippet:

ByteArrayOutputStream bytearraystream = new ByteArrayOutputStream();

workbook.write(bytearraystream);

// Convert to ByteArray

byte[] barray = bytearraystream.toByteArray();

InputStream is = new ByteArrayInputStream(barray);

workbook.close();

// Store the input stream to file document object.

Core.storeFileDocumentContent(getContext(), __StudentExcel, is);

StudentExcel.setHasContents(true);

Step 10: Finally, we reached the end of the code. Add the below return statementto complete the custom java action.

return __StudentExcel;

Now create a microflow and call the Java action by passing any entity. Then run the application and trigger the microflow to see the generated file in 5 seconds, which contains all records of the specified entity with all the attribute values.

Please click below to experience faster Excel generation.

https://excelgeneration-sandbox.mxapps.io/index.html?profile=Responsive

The excel file has been generated super-fast! It will look like the one below.

 

Conclusion

In conclusion, generating Excel sheets with dynamic entities and columns can be a challenging task, but with the help of a simple Java action and the Apache POI Java library, developers can create optimized Excel files that meet the needs of their users. By following the ten steps outlined in this guide, developers can create efficient and effective Excel generation solutions within their Mendix applications, even when working with large datasets and complex data structures.