Text header and blank rows not working with Excel table

[apoorva-sriv]

When I use both a text header and a table, Excel complains that it has to repair the file, and it is no longer formatted as a table (i.e. sort/filter arrows on top and header frozen on scrolling down) after repairing.

Also, if there are 0 rows, an error is thrown in the code if the table is added, but there is no error without using a table.

[firegloves]

Hi there, I'm on vacation, I'll be back in the end of September

[firegloves]

Solved in v1.9.2 I wasn't able to reproduce the error when dealing with 0 length result set.

[firegloves]

@apoorva-sriv I wasted a lot of time trying to reproduce the issue, discovering finally that you were using an approach different from mine. Next time please attach the code to reproduce the error

[apoorva-sriv]

I'm getting the same issues even with 1.9.2. This is the (modified) code I used:

public InputStream downloadExcel() {
    String sql = "SELECT* FROM TABLE";
    try (Workbook workbook = new XSSFWorkbook();
        Connection connection= template.getJdbcTemplate().getDataSource().getConnection()) {
        // Closed internally by MemPOI
        PreparedStatement ps = getPreparedStatement(sql, sqlParams, connection);

        MempoiTableBuilder mempoiTableBuilder = MempoiTableBuilder.aMempoiTable()
            .withWorkbook(workbook)
            .withAreaReferenceSource("A1:F100"); 
/* The above line is able to download the spreadsheet but Excel shows a warning while opening the spreadsheet; however, using withAllSheetData(true) throws 
java.util.concurrent.ExecutionException: it.firegloves.mempoi.exception.MempoiException: Null Workbook received while skipping first row of the AreaReference Al:J3
    at java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:357)
    at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1908)*/

        MempoiSheet mempoiSheet = MempoiSheetBuilder.aMempoiSheet()
            .withPrepStmt(ps)
            .withSimpleHeaderText("HEADER") // Problematic line
            .withMempoiTableBuilder(mempoiTableBuilder)
            .build();

        MemPOI memPOI = MempoiBuilder.aMemPOI()
            .withNullValuesOverPrimitiveDetaultOnes(true)
            .withAdjustColumnWidth(true)
            .withWorkbook(workbook)
            .addMempoiSheet(mempoiSheet)
            .build();

        MempoiReport mempoiReport = memPOI.prepareMempoiReport().get();

        return new ByteArrayinputStream(mempoiReport.getBytes());   
    }
}

[firegloves]

@apoorva-sriv if you want to provide manually the area reference relative to the table, you have to keep in mind that by adding the text header you are shifting data of 1 row. Something like:

.withAreaReferenceSource("A2:F100"); or .withAreaReferenceSource("A2:F101");

I'll double-check the implementation using withAllSheetData(true) ASAP