Excel file data extractor

Hello dear coders,

in my current #project I need to extract a data table within a #Excel file.
Therefore, I need to read in the #Excel file and store each cell.

I decided to represent the data table in Java from the #Excel file by a two-dimensional #ArrayList. To reach this goal I use the POI-3.17 library from #Apache.

Get it here

Before the cells are read out and stored in a #2D-ArrayList, a iterator will check the cells in each row if there is a "BLANK", ERROR" or "NONE" cell and replace the cell as a String cell and fill it was proper value you set.

After this, the whole userdata in the first spreadsheet in the Excel file is read out and saved into the #2D-ArrayList. The #2D-ArrayList is returned by the function.

#HSSF is meaning a Excel file >= 97 & <= 2005
#XSSF is meaning a Excel file > 2005

Just simply alter #HSSF to #XSSF to support newer Excel formats, or just create two proper functions.

public ArrayList<ArrayList<String>> readInUserDataTableHSSF (String pathToExcelFile) {
    HSSFWorkbook workbook;
    HSSFSheet spreadsheet;
    Iterator< Row > rowIterator;
    Iterator< Cell > cellIterator;
    HSSFRow row;
    Cell cell;
    ArrayList<ArrayList<String>> userDataTable = new ArrayList<>();
    int numberOfRows;
    int numberOfColumns;
    int rowNumber = 0;
    Alert alert = new Alert(Alert.AlertType.WARNING);
    
    try (FileInputStream excelFileInputStream = new FileInputStream(pathToExcelFile)) {
        workbook = new HSSFWorkbook(excelFileInputStream); //Create a virtual copy of the Excel file
        
        spreadsheet = workbook.getSheetAt(0); //Create a virtual copy of the first spreadsheet in the Excel file
        numberOfRows = spreadsheet.getPhysicalNumberOfRows();
        numberOfColumns = spreadsheet.getRow(0).getLastCellNum();
        userDataTable = new ArrayList<>(numberOfRows);
        
        //Create a virtual copy of each row within the spreadsheet and put it in a interator object called "rowIterator" 
        //rowIterator is needed for pointing on each row in a spreadsheet
        rowIterator = spreadsheet.iterator(); 

        //Execute the loop until hasNext() returns "false
        while (rowIterator.hasNext()) {
            userDataTable.add(new ArrayList<>());
            //"next()" puts the next row in "rowIterator"
            //every time the while-loop was executed
            row = (HSSFRow) rowIterator.next();
            
            //The following for-loop is needed to eliminate/uncover all "BLANK-cells" and fill them with Strings.
            //This loop is executed before the second while-loop below starts,
            //else "cellIterator" will ignore the BLANK-cells.
            for (int column = 0; column < numberOfColumns; column++) {
                if(row.getCell(column, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL) == null) { //Identify BLANK-cells
                    row.createCell(column, CellType.STRING); //Transform a BLANK-cell into a String-cell
                    row.getCell(column).setCellValue("BLANK"); //Set the text of the cell
                }
            }
            
            cellIterator = row.cellIterator(); 
            while(cellIterator.hasNext()) { //Execute the loop until hasNext() returns "false"
                
                cell = cellIterator.next();
                
                CellType cellType = cell.getCellTypeEnum();
                switch (cellType) {
                    case STRING:
                        userDataTable.get(rowNumber).add(cell.getStringCellValue());
                        break;
                        
                    case _NONE:
                        System.out.println("case NONE");
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue("NONE");
                        alert.setTitle("WARNING!");
                        alert.setHeaderText("CellType '_NONE' discoverd."); 
                        alert.setContentText("Cell will formated as String and text set to 'NONE'.");
                        alert.show();
                        break;
                        
                    case ERROR:
                        System.out.println("case ERROR");
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue("ERROR");
                        alert.setTitle("WARNING!");
                        alert.setHeaderText("CellType 'ERROR' discoverd."); 
                        alert.setContentText("Cell will formated as String and text set to 'ERROR'.");
                        alert.show();
                        break;
                        
                    case NUMERIC:
                        //if(floatingPoint == 44) //44 is ASCII ","
                        //if(floatingPoint == 46) {//46 is ASCII "."
                        userDataTable.get(rowNumber).add(String.valueOf(cell.getNumericCellValue()));
                        break;
                        
                    default:
                        System.out.println("DEFAULT");
                        alert.setTitle("WARNING!");
                        alert.setHeaderText("Internal error occured - [DEFAULT switch case region in readInUserDataTable()]");
                        alert.setContentText("Contact the creator of this program.");
                        alert.show();
                        break;
                }
            }
            rowNumber++;
        }
        //Close the underlying file of the FileInputStream
        excelFileInputStream.close();
    } 
    catch (IOException ex) {
        Logger.getLogger(FileReader.class.getName()).log(Level.SEVERE, null, ex);
    }
    return userDataTable;
}

Hope I could help you. :-)

Happy coding