XXL publications

Tips and tricks
1

When generating large files, two issues have to be considered carefully:

  1. the app memory must be cared for: if the whole file is, at some point in the algorithm, loaded in memory, we might saturate the app’s allocated resources
  2. the default publication UX is not sufficient: at the moment, publications are synchronous, and we cannot let users wait several minutes for the file to be generated

The v5.0 of Simplicité included optimizations targeting those issues for default Excel/CSV exports, but not for business publications (the ones you develop).

This tip&trick uses the same solutions, respectively:

  1. The app’s memory (heap) serves as a buffer between the database and the file (DB → heap → File), the algorithm must load the data by chunks to preserve that memory.
    • when reading the data from the database, use pagination to avoid SQL requests that will return 100k lines
    • when writing the data, use Streams (for Excel) or append to a file on disk (for CSV)
  2. Do an asynchronous call to a service in charge of building and sending the file, and display a non-blocking indicator to the user that the file is being built.

This example is built on the Demo module and is configured as follows:

  1. a front-end action that re-uses Simplicité’s toast system, that asynchronously calls the specified URL /ui/ext/DemoPublication and displays the non-blocking toast to the user
$ui.view.widget.toastLoading("Export en cours...", null, "/ui/ext/DemoPublication");

Cursor_and_Export_de_données___300k_lignes__CSV_ou_Excel_-Private___SIOrg-_Simplicité_Software_Community_Forum

  1. an external object that hijacks the display method to serve the excel file as a Stream (not as a byte array). The code to generate the file uses pagination.
package com.simplicite.extobjects.DemoXxlExport;

import java.io.IOException;
import java.util.concurrent.atomic.AtomicInteger;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Sheet;

import com.simplicite.util.AppLog;
import com.simplicite.util.ExternalObject;
import com.simplicite.util.ObjectDB;
import com.simplicite.util.exceptions.SearchInterruptedException;
import com.simplicite.util.tools.ExcelTool;
import com.simplicite.util.tools.ExcelTool.ExcelRow;
import com.simplicite.util.tools.HTTPTool;
import com.simplicite.util.tools.Parameters;
import com.simplicite.webapp.tools.ServletParameters;

/**
 * External object DemoPublication
 */
public class DemoPublication extends ExternalObject {
	private final String FILENAME = "filename-set-in-extobject.xls";
	private final int PAGINATION_SIZE = 1000; // number of record per "page" (DB -> HEAP)
	private final int FLUSH_FREQUENCY = 100;  // number of rows in memory (HEAP -> FILE) 
	private final int SLEEP_SECONDS = 30;	  // simulate long generation
	
	@Override
	public Object display(Parameters params) {
		try{
			outputStreamResponse(params);
			return "";
		}
		catch(Exception e){
			AppLog.error(null, e, getGrant());
			return e.getMessage();
		}
	}
	
	private void outputStreamResponse(Parameters params) throws SearchInterruptedException, IOException, InterruptedException{		
		HttpServletResponse response = ((ServletParameters)params).getResponse();
		response.setContentType(HTTPTool.MIME_TYPE_XLS);
		response.setHeader("Content-Disposition", "attachment; filename=\""+FILENAME+"\"");			
		getExcelWithPagination().generate(response.getOutputStream());
	}
	
	private ExcelTool getExcelWithPagination() throws IOException, SearchInterruptedException, InterruptedException{
		ExcelTool xls = new ExcelTool(null, FLUSH_FREQUENCY);
		Sheet sheet = xls.addSheet("Sheet 1");
		AtomicInteger rowIndex = new AtomicInteger(0);
		
		// prefer using a dedicated instance if the process takes time because tmp instance may be used elsewhere
		ObjectDB supplier = getGrant().getObject("MyXXLExportOfDemoSupplier", "DemoSupplier");;
		
		// use search **with** pagination
		supplier.search(true, PAGINATION_SIZE, 
			(rows)->{ // calllback called for each page of PAGINATION_SIZE rows
				for(String[] row : rows){
					supplier.setValues(row);
					final ExcelRow r = new ExcelRow(rowIndex.getAndIncrement());

					int columnIndex = 1;
					r.add(xls.newCell(columnIndex++, supplier.getFieldValue("demoSupCode")));
					r.add(xls.newCell(columnIndex++, supplier.getFieldValue("demoSupName")));

					xls.addRow(sheet, r);
				}	
			}
		);
		
		supplier.destroy();
		
		Thread.sleep(SLEEP_SECONDS*1000);
		
		return xls;
	}
}

Find below the working Add-on to the Demo: DemoXxlExport-1.0.xml (7.1 KB)

2 Likes