A Comprehensive Guide to Creating High Performance – Excel Generation

Reading an excel file is not the same as reading PDFs or Word Documents. To generate excel documents in Mendix, we always use the built-in Export Excel or Excel Exporter. Let’s look at the same excel generation using java code We’ll look at Excel generation with the help of a simple java action and the Apache POI Java library.

In this case, we’re generating an excel document with 20,000 student records in a matter of seconds.

Are you curious to know more, let’s get into details:

Simply follow the 10 steps as shown below to generate.

Example: – Student Excel Generation Using a Simple Domain Model

You can create a simple Mendix application using the above domain model, and ensure that the following POI jars are added to the project’s user lib folder, as shown below:

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

1. List of Student Objects
2. File Document object

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

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 Begin writing code between the begin user code and end user code sections.

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 and give a name to it.  

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

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

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 code snippet below to create headers for Excel by creating rows and cells.

// Row Creation

XSSFRow row;

int rowid = 0;

row = spreadsheet.createRow(rowid++);

// Cell Creation

Cell header1 = row.createCell(0);

header1.setCellValue(“Student Id”);

header1.setCellStyle(style);

Cell header2 = row.createCell(1);

header2.setCellValue(“Name”);

header2.setCellStyle(style);

Cell header3 = row.createCell(2);

header3.setCellValue(“Age”);

header3.setCellStyle(style);

Step 8: The code snippet below assists in creating each row for each student record in the database..

for (Student student : StudentList) {

// Row creation for each student record

row = spreadsheet.createRow(rowid++);

int cellid = 0;

// Cell creation for each student attribute

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

cell.setCellValue(student.getStudentId());

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

cell1.setCellValue(student.getName());

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

cell2.setCellValue(student.getAge());

}

Step 9: Write the created workbook with the help of the below 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: We finally made it to the end of the code. To finish the custom java action, add the return statement below.

return__StudentExcel;

Create a microflow that calls this java action. Then, run the application and trigger the microflow to see the generated file with 20000 records in 3 seconds.

Please click the button below to see the faster Excel generation.

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

The excel file was created in record time! It will look like this.