I'm having a problem reading an Excel, I really have about 10 different Excels, and I've made a code to read them all.
The operation is as follows, I am reading cell by cell of Excel and the data is put as STRING type in an array, when the function finishes, I send the array to a static method of the class to create the object. To create the object what I do is take the number of columns in Excel and divide the array between them.
If I fill ALL the Excel cells, the code works fine, the problem is when I do not fill any cell, because then the array leaves less length and therefore, you can not create the object well.
My intention was to create some kind of case Cell.CELL_TYPE_BLANK:
But since there is no cell, you can jump to while (rowIterator.hasNext()) or
while (cellIterator.hasNext ()) {
//Array que devuelvo
LinkedList<String> array = new LinkedList<String>();
String stringValue = "";
int cont = 0;
try {
InputStream inputStream = multipart.getInputStream();
// Recojo el Excel
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheet;
Row row = null;
Iterator<Cell> cellIterator = null;
sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
// Si hay filas ->
while (rowIterator.hasNext()) {
// Guardo las filas y las celdas
row = rowIterator.next();
cellIterator = row.cellIterator();
cont = 0;
// Recorro cada celda
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// No me interesan las cabeceras.
if (cell.getRowIndex() != 0) {
// Tipo de celda.
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
// Este Excel y sus campos son especiales.
if (typeFile.equals("MAN-5") && cont == 6 || cont == 7 || cont == 13) {
stringValue = readDate(String.valueOf(cell.getDateCellValue()));
} else {
if (String.valueOf(cell.getNumericCellValue()).endsWith(".0")) {
stringValue = String.valueOf(cell.getNumericCellValue());
stringValue = stringValue.split("\.")[0];
} else {
stringValue = String.valueOf(cell.getNumericCellValue());
}
}
// le pone un . a "2017."
if (cont == 0 || cont == 1) {
stringValue = removeDot(stringValue);
}
break;
case Cell.CELL_TYPE_STRING:
stringValue = cell.getStringCellValue();
break;
}
// Junto la columna 2 y 1 como un solo dato.
if (cont == 1) {
createPeriod(stringValue, array);
} else {
array.add(stringValue);
}
}
cont++;
}
}
} catch (Exception e) {
array = null;
}
return array;
}
Excel format - >
AÑO | MES | DATO1 | DATO2 | DATO3
2017 01 1 11
2018 02 2
2018 03 1
Now I create the object in another side ...
try {
for (int y = 0; y < array.size(); y++) {
if (y % 4 == 0) {
Article = new Article();
Article.setIdPeriod(array.get(y));
Article.setDato1(Double.parseDouble(array.get(y + 1).replace(',', '.')));
Article.setDato2(Double.parseDouble(array.get(y + 2).replace(',', '.')));
Article.setDato3(Double.parseDouble(array.get(y + 3).replace(',', '.')));
arrayArt.add(Article);
}
}
Explanations: As the Excel has 5 columns, and I by code together the 1st and 2nd, if all the data came the Array is divisible by 4 and would have 1 complete object with all the data.
The problem that I just found is that if I do not get ANY data, the array is created in this way - > Array[201701][1][11][201802][2][201803][1]
If I had to divide this array by 4 parts, only 1Object and a half are left and also setDato is valid - > 201802, a complete disaster ....
I would need to know which cell is empty, but I can not, because "it does not exist" For example for row 1 Data3 does not enter, it does the
directly while (cellIterator.hasNext()) {
Thanks, I hope you can help me.
Pd: I'm using - >
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>