user2306993 user2306993 - 11 months ago 81
Java Question

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

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 Source

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) {

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.