Recently I've added new dependency to project for generating .xls file.
pom.xml
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.0.0</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.0</version>
</dependency>
Code
public StreamingOutput createStreamedExcelReport(Map<String, Object> params, String templateName, String[] columnsToHide) throws Exception {
try(InputStream is = ReportGenerator.class.getResourceAsStream(templateName)) {
assert is != null;
final Transformer transformer = PoiTransformer.createTransformer(is);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
List<Area> xlsAreaList = areaBuilder.build();
Area xlsArea = xlsAreaList.get(0);
Context context = new PoiContext();
for(Map.Entry<String, Object> entry : params.entrySet()) {
context.putVar(entry.getKey(), entry.getValue());
}
xlsArea.applyAt(new CellRef("Sheet1!A1"), context);
xlsArea.processFormulas();
return new StreamingOutput() {
@Override
public void write(OutputStream out) throws IOException {
((PoiTransformer) transformer).getWorkbook().write(out);
}
};
}
}
Unfortunately it turns on probably jOOQ debug logging which is really bad because it logs all db queries. Application is making big query for .xls, so it shouldn't log that (~1,5GB output in 3min).
Example of logs
08:08:23.094 [http-bio-8080-exec-7] DEBUG org.jooq.tools.LoggerListener - Executing query : select "XXXXXXXX"."XXX", "XXXXXXX"."XXXXXXX" from "XXXXX" where "XXXXX"."XXX" = ?
08:08:23.095 [http-bio-8080-exec-7] DEBUG org.jooq.tools.LoggerListener - -> with bind values : select "XXXXXXXX"."XXX", "XXXXXXX"."XXXXXXX" from "XXXXX" where "XXXXX"."XXX" = 'XXXX.XXXXXXXXXXXXXXXXXXX'
08:08:23.098 [http-bio-8080-exec-7] DEBUG org.jooq.tools.StopWatch - Query executed : Total: 4.17ms
08:08:23.099 [http-bio-8080-exec-7] DEBUG org.jooq.tools.LoggerListener - Fetched result : +----------------------------------------+-----------+
08:08:23.100 [http-bio-8080-exec-7] DEBUG org.jooq.tools.LoggerListener - : |KEY |VALUE |
08:08:23.100 [http-bio-8080-exec-7] DEBUG org.jooq.tools.LoggerListener - : +----------------------------------------+-----------+
08:08:23.100 [http-bio-8080-exec-7] DEBUG org.jooq.tools.LoggerListener - : |xxxx.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX|0 0 3 * * ?|
08:08:23.100 [http-bio-8080-exec-7] DEBUG org.jooq.tools.LoggerListener - : +----------------------------------------+-----------+
08:08:23.100 [http-bio-8080-exec-7] DEBUG org.jooq.tools.StopWatch - Finishing : Total: 6.756ms, +2.586ms