Apache POI: API for defining tables on an excel sheet and reading them

Question

Is there any method that returns a list of tables present in a sheet? My requirement is to extract data from several tables available on the sheet.

Answer 1

Suppose you are using the XSSF API for .xlsx excel files. If tables were created using Insert->Table , you can read them using the following:

 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 everything that has a border, then you need to create a nontrivial algorithm that reads all the cells of each sheet and checks the borders (for example, leftBorderColor , rightBorderColor , topBorderColor , bottomBorderColor ) and determines what consists of checking the table if you found her.

Answer 2

For all those who want to read tables from 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(); 

Answer 3

I wrote this to use it with the Pojo class and annotations:

 import java.io.File; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.lang3.reflect.FieldUtils; import org.apache.commons.lang3.tuple.ImmutablePair; import org.apache.commons.lang3.tuple.Pair; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFTable; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.madx.finance.data.utils.exels.read.ExelColumn; public class Main { public static void main(String[] args) throws Exception { readExel(ExamplePojo.class); } public static class ExamplePojo { public ExamplePojo(){} @ExelColumn(columnName="Column 1 String Name On Exel") protected String column1; @ExelColumn(columnName="Column 2 String Name On Exel") protected String column2; } public static <T> List<T> readExel(Class<T> clazz) throws Exception { List<T> rows = new ArrayList<T>(); 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); // Loads all the annotated fields and builds a map for: // columnName, field List<Field> annotatedFields = FieldUtils.getFieldsListWithAnnotation(clazz, ExelColumn.class); Map<String, Field> annotatedFieldsMap = new HashMap<String, Field>(); for (Field field : annotatedFields) annotatedFieldsMap.put(field.getAnnotation(ExelColumn.class).columnName(), field); // Reads the used header names List<Pair<Integer, Field>> consideredColumns = new ArrayList<Pair<Integer, Field>>(); for (int j = startColumn; j <= endColumn; j++) { XSSFCell cell = sheet.getRow(startRow).getCell(j); if (cell != null) { String columnName = cell.getStringCellValue(); Field field = annotatedFieldsMap.get(columnName); if (field != null) { Integer cellColumn = j; Pair<Integer, Field> p = new ImmutablePair<Integer, Field>(cellColumn, field); consideredColumns.add(p); } } } for (int i = startRow + 1; i <= endRow; i++) { try { T row = clazz.newInstance(); for (Pair<Integer, Field> pair : consideredColumns) { XSSFCell cell = sheet.getRow(i).getCell(pair.getKey()); if (cell != null) { Field field = pair.getValue(); field.setAccessible(true); Class<?> fieldClass = field.getType(); if(Number.class.isAssignableFrom(fieldClass)){ field.set(row, cell.getNumericCellValue()); } else { field.set(row, cell.getStringCellValue()); } } } rows.add(row); } catch (InstantiationException | IllegalAccessException e) { e.printStackTrace(); } } } } workbook.close(); return rows; } }