Apache POI : API to identify tables in the excel sheet and read them

Question

Is there any method which returns a list of tables present in the sheet? My requirement is to fetch data from multiple tables present on the sheet.

Answer 1

Let's assume that you are using the XSSF API for .xlsx excel files. If the tables were created by Insert->Table then you can read them by using this :

XSSFWorkbook workbook = new XSSFWorkbook(new File("test.xlsx"));
int numberOfSheets = workbook.getNumberOfSheets();
for(int sheetIdx = 0; sheetIdx < numberOfSheets; sheetIdx++) {
    XSSFSheet sheet = workbook.getSheetAt(sheetIdx);
    List<XSSFTable> tables = sheet.getTables();
    for(XSSFTable t : tables) {
        System.out.println(t.getDisplayName());
        System.out.println(t.getName());
        System.out.println(t.getNumerOfMappedColumns());
    }
}

If by table you mean anything that has a border then you have to create a non-trivial algorithm that reads all the cells of every sheet and checks the boundaries (e.g. leftBorderColor, rightBorderColor, topBorderColor, bottomBorderColor) and by defining what consists a table check if you've found it.

Answer 2

For all those who want to read tables from a java code, here is the working code.

XSSFWorkbook workbook = new XSSFWorkbook(new File("test.xlsx"));
    int numberOfSheets = workbook.getNumberOfSheets();
    for (int sheetIdx = 0; sheetIdx < numberOfSheets; sheetIdx++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIdx);
        List<XSSFTable> tables = sheet.getTables();
        for (XSSFTable t : tables) {
            System.out.println(t.getDisplayName());
            System.out.println(t.getName());
            System.out.println(t.getNumerOfMappedColumns());

            int startRow = t.getStartCellReference().getRow();
            int endRow = t.getEndCellReference().getRow();
            System.out.println("startRow = " + startRow);
            System.out.println("endRow = " + endRow);

            int startColumn = t.getStartCellReference().getCol();
            int endColumn = t.getEndCellReference().getCol();

            System.out.println("startColumn = " + startColumn);
            System.out.println("endColumn = " + endColumn);

            for (int i = startRow; i <= endRow; i++) {
                String cellVal = "";

                for (int j = startColumn; j <= endColumn; j++) {
                    XSSFCell cell = sheet.getRow(i).getCell(j);
                    if (cell != null) {
                        cellVal = cell.getStringCellValue();
                    }
                    System.out.print(cellVal + "\t");
                }
                System.out.println();
            }

        }
    }

    workbook.close();