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.
- List of Student Objects
- 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.
