SXSSF with Excel table

Question

I'm trying to create an Excel table in a streamed workbook (SXSSFWorkbook). It is not supported directly by the API but I've had some success by accessing the underlying XSSFWorkbook (workbook.getXSSFWorkbook).

When I open the file in Excel (2007), it says "Excel found unreadable content in "test.xlsx". Do you want to recover the contents of this workbook?". Clicking yes successfully repairs the workbook and I get the correct result.

Log says "Repaired Records: Table from /xl/tables/table1.xml part (Table)".

Anyone has an idea on how I could avoid the Excel error?

Below is an example:

public class SXSSFTest {

    private static final int NB_ROWS = 5;
    private static final int NB_COLS = 5;

    public static void main(String[] args) throws Exception {

        try (SXSSFWorkbook workbook = new SXSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream("C:\\test.xlsx")) {

            SXSSFSheet sheet = workbook.createSheet();

            fillSheet(sheet);

            String dataRange = new AreaReference(
                    new CellReference(0, 0),
                    new CellReference(NB_ROWS - 1, NB_COLS - 1))
                    .formatAsString();

            CTTable cttable = workbook.getXSSFWorkbook()
                    .getSheetAt(0)
                    .createTable()
                    .getCTTable();

            CTTableStyleInfo tableStyle = cttable.addNewTableStyleInfo();
            tableStyle.setName("TableStyleMedium17");

            cttable.setRef(dataRange);
            cttable.setDisplayName("TABLE");
            cttable.setName("TABLE");
            cttable.setId(1L);

            CTTableColumns columns = cttable.addNewTableColumns();
            columns.setCount(NB_COLS);

            for (int c = 0; c < NB_COLS; c++) {
                CTTableColumn column = columns.addNewTableColumn();
                column.setName("Column" + c);
                column.setId(c + 1L);
            }

            cttable.setAutoFilter(CTAutoFilter.Factory.newInstance());

            workbook.write(outputStream);
        }
    }

    private static void fillSheet(SXSSFSheet sheet) {
        for (int rowNb = 0; rowNb < NB_ROWS; rowNb++) {
            SXSSFRow row = sheet.createRow(rowNb);

            for (int colNb = 0; colNb < NB_COLS; colNb++) {
                SXSSFCell cell = row.createCell(colNb);
                cell.setCellValue("Cell-" + colNb);
            }
        }
    }
}

Answer 1

When adding a table, make sure that the names of the column headers are not repeated. If you have duplicates column names Excel throw this error and will fix it by renaming the 2nd duplicated column by adding a number a the end. See example below:

| QTY | UNIT # | QTY2 |

See that 2nd QTY was renamed to QTY2 by excel recovery process.

Answer 2

Here's an updated version that fixes several usages of deprecated methods (tested with POI 4.1.2). Note that it doesn't require creating columns and settings IDs manually anymore, everything is done by createTable(dataRange):

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import java.io.FileOutputStream;

public class SXSSFTest {

    private static final int NB_ROWS = 5;
    private static final int NB_COLS = 5;

    public static void main(String[] args) throws Exception {

        try (SXSSFWorkbook workbook = new SXSSFWorkbook();
             FileOutputStream outputStream = new FileOutputStream("C:\\test.xlsx")) {

            SXSSFSheet sheet = workbook.createSheet();

            fillSheet(sheet);

            AreaReference dataRange = new AreaReference(
                    new CellReference(0, 0),
                    new CellReference(NB_ROWS - 1, NB_COLS - 1),
                    SpreadsheetVersion.EXCEL2007
            );

            CTTable cttable = workbook.getXSSFWorkbook()
                    .getSheetAt(0)
                    .createTable(dataRange)
                    .getCTTable();

            CTTableStyleInfo tableStyle = cttable.addNewTableStyleInfo();
            tableStyle.setName("TableStyleMedium17");

            cttable.setDisplayName("TABLE");
            cttable.setName("TABLE");

            CTTableColumns columns = cttable.getTableColumns();

            for (int c = 0; c < NB_COLS; c++) {
                CTTableColumn column = columns.getTableColumnArray(c);
                column.setName("Column title " + c);
            }

            cttable.setAutoFilter(CTAutoFilter.Factory.newInstance());

            workbook.write(outputStream);
        }
    }

    private static void fillSheet(SXSSFSheet sheet) {
        for (int rowNb = 0; rowNb < NB_ROWS; rowNb++) {
            SXSSFRow row = sheet.createRow(rowNb);

            for (int colNb = 0; colNb < NB_COLS; colNb++) {
                SXSSFCell cell = row.createCell(colNb);
                if (rowNb == 0) {
                    cell.setCellValue("Column title " + colNb); //first row are column names
                } else {
                    cell.setCellValue("Cell-" + colNb);
                }
            }
        }
    }
}

Answer 3

The cell values in the first row of the table must correspond with the column names.

Your code in main method names the columns Column0 ... Column4 but your code in fillSheet method writes "Cell-0" ... "Cell-4" into the cells of first row. This does not match.

You could change the fillSheet method like this:

...
    private static void fillSheet(SXSSFSheet sheet) {
        for (int rowNb = 0; rowNb < NB_ROWS; rowNb++) {
            SXSSFRow row = sheet.createRow(rowNb);

            for (int colNb = 0; colNb < NB_COLS; colNb++) {
                SXSSFCell cell = row.createCell(colNb);
                if (rowNb==0) cell.setCellValue("Column" + colNb); //first row are column names
                else cell.setCellValue("Cell-" + colNb);
            }
        }
    }
...