Save excel file to an array

Question

In the example below I have a class in java that will fetch the data of a given excel file. All this part of going to get the file the program does, but now needed that data to be stored in an array. I don’t know how to do it.

public class UniFederal {

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

    List<List<String>> vecSample = new ArrayList<List<String>>();
    double[] vect;
    int [][]matriz = new int[30][30];
    String[][] referencia = new String[][];

    File excel = new File("gestaoAlunos.xlsx");
    FileInputStream fich = new FileInputStream(excel);

    XSSFWorkbook workbook = new XSSFWorkbook(fich);
    XSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIt = sheet.iterator();
    while(rowIt.hasNext()){
        Row row  = rowIt.next();

        int i=0, j=0;
        Iterator<Cell> cellIterator = row.cellIterator();          
        while(cellIterator.hasNext()){
            Cell cell = cellIterator.next();
            System.out.print(cell.toString() + ";");
            //System.out.print(vecSample.get(i));
            i++;
        }

        System.out.println();
    }

    workbook.close();
    fich.close();

}

}

Answer 1

A 2d matrix is nothing more than a line-to-column crossing. In the case of Excel, this is easily visualized in the name that the cells receive: A1, B2, D5 etc. What we want then is, in each column, to sweep all the rows, and only move to the next column when there are no more rows.

Let’s go to the excerpt of the code that translates this:

Iterator<Row> rowIt = sheet.iterator();
int linha, coluna = 0; //nomes expressivos facilitam o entendimento
while(rowIt.hasNext()){ //entra na coluna
     Row row  = rowIt.next();
     Iterator<Cell> cellIterator = row.cellIterator();          
     while(cellIterator.hasNext()){ //começa a ver as linhas da coluna atual
        Cell cell = cellIterator.next();
        referencia[coluna][linha] = cell.toString();
        linha++; //acabou a linha atual, vamos pegar a próxima
     }

     coluna++ //acabamos a coluna, vamos varrer a próxima
}