SXSSF Transformer with JXLS 2

Question

I'd like to use the SXSSF transformer with JXLS. I tried to write my template in such a way that I wouldn't get the "Attempting to write a row in the range that is already written to disk" exception. The template captures known columns (e.g., "HEADER 0") and unknown columns (those that end in "_dynamic"). The number of dynamic columns can vary from run to run.

If I configure the SXSSF window to greater than the number of rows, I have no problems. If I set the window to fewer than the number of rows, then I get the "attempting to write a row ..." exception. However, in both cases the workbook is created. With an adequate window size, the known column ("HEADER 0") is included in the result. With an inadequate window size, the known column values are in the result (despite the exception), but the actual column text (again "HEADER 0") is missing.

I'd like to take an approach like this because the number of rows could number in the 100,000's and I want to flush the data to disk as necessary.

Is doing something like this even possible in JXLS? If so, are there ways to change the template without having to write any Java code that knows about the data?

Here is the code:

public class JxlsTest {

@Test
public void sxssfDynamicColumns() throws Exception {
    List<Map<String, Object>> lotsOfStuff = createLotsOfStuff();

    Context context = new PoiContext();
    context.putVar("lotsOStuff", lotsOfStuff);
    context.putVar("columns", new Columns());

    try (InputStream in = getClass().getClassLoader().getResourceAsStream("stuff_sxssf_template.xlsx")) {
        try (OutputStream os = new FileOutputStream("stuff_sxssf_out.xlsx")) {
            Workbook workbook = WorkbookFactory.create(in);
            PoiTransformer transformer = PoiTransformer.createSxssfTransformer(workbook, 5, false);

            AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
            List<Area> xlsAreaList = areaBuilder.build();
            Area xlsArea = xlsAreaList.get(0);
            xlsArea.applyAt(new CellRef("Result!A1"), context);
            SXSSFWorkbook workbook2 = (SXSSFWorkbook) transformer.getWorkbook();
            workbook2.write(os);
        }
    }
}

private List<Map<String, Object>> createLotsOfStuff() {
    Map<String, Object> stuff1 = new LinkedHashMap<>();
    Map<String, Object> stuff2 = new LinkedHashMap<>();

    stuff1.put("header0", "stuff_1_value0");
    stuff1.put("header1_dynamic", "stuff_1_value1");
    stuff1.put("header2_dynamic", "stuff_1_value2");
    stuff1.put("header3_dynamic", "stuff_1_value3");

    stuff2.put("header0", "stuff_2_value0");
    stuff2.put("header1_dynamic", "stuff_2_value1");
    stuff2.put("header2_dynamic", "stuff_2_value2");
    stuff2.put("header3_dynamic", "stuff_2_value3");

    return Arrays.asList(stuff1, stuff2);
}

}

and the supporting "Columns" utility:

public class Columns {

public Collection<String> keyOf(List<Map<String, Object>> row) {
    return row.get(0).keySet().stream().filter(k -> k.endsWith("_dynamic")).collect(Collectors.toList());
}

public Collection<Object> valueOf(Map<String, Object> row) {
    return row.entrySet().stream()
            .filter(entry -> entry.getKey() != null && entry.getKey().endsWith("_dynamic"))
            .map(Entry::getValue)
            .collect(Collectors.toList());
}

}

and the template:

Output with adequate SXSSF window (notice HEADER 0 appears):

Output with inadequate SXSSF window (notice HEADER 0 does not appear):

Error from inadequate SXXF window:

18:33:20.653 [main] DEBUG org.jxls.area.XlsArea - Applying XlsArea at Result!A1
18:33:20.693 [main] ERROR org.jxls.area.XlsArea - Failed to transform Template!B1 into Result!B1
java.lang.IllegalArgumentException: Attempting to write a row[0] in the range [0,0] that is already written to disk.
    at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:115) ~[poi-ooxml-3.12.jar:3.12]
    at org.jxls.transform.poi.PoiTransformer.transform(PoiTransformer.java:112) ~[jxls-poi-1.0.8.jar:na]
    at org.jxls.area.XlsArea.transformTopStaticArea(XlsArea.java:232) [jxls-2.2.9.jar:na]
    at org.jxls.area.XlsArea.applyAt(XlsArea.java:134) [jxls-2.2.9.jar:na]

UPDATE I found that if I remove the dynamic headers (see template in B4 cell in the screenshot), the exception isn't thrown and everything works. So the row related templates are being evaluated first, then JXLS is coming back to evaluate the dynamic header template. Is there a way to make JXLS evaluate the header template first?

Answer 1

The problem is that Jxls processes static cells in two stages.

The first stage happens before applying the commands and it processes only cells before the top command row.

The second stage is triggered after all the commands are processed and it tries to process all the remaining static cells.

The second stage does not work well with SXSSF workbook since it cannot update the static cells located before the cells which were written during the commands processing.

Now there is a fix committed to Jxls master branch which should resolve the issue by processing the static cells differently if a streaming transformer is used. See also the related Jxls issue #160 .

The fix should be released in the upcoming Jxls 2.7.0.