Comment gérer la valeur Null Ligne à l'aide de Apache POI?

Je suis en utilisant Apache POI à lire xlsx fichier, il fonctionne bien. J'ai une question à vous lorsque la ligne est trouvée, null, comment je suis capable de le gérer? Mon fichier contient 500 ligne, mais il montrer 105667 ligne, le reste de la ligne trouvée nulle.

utilisé code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author SAMEEK
*/
public class readXLSXFile {
public int getNumberOfColumn(String fileName, int sheetIndex) throws FileNotFoundException, IOException {
File inputFile = null;
FileInputStream fis = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
XSSFRow row = null;
int lastRowNum = 0;
int lastCellNum = 0;
//Open the workbook
inputFile = new File(fileName);
fis = new FileInputStream(inputFile);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(sheetIndex);
lastRowNum = sheet.getLastRowNum();
for (int i = 0; i < lastRowNum; i++) {
row = sheet.getRow(i);
if (row != null) {
if (row.getLastCellNum() > lastCellNum) {
lastCellNum = row.getLastCellNum();
}
}
}
return lastCellNum;
}
public int getNumberOfRow(String fileName, int sheetIndex) throws FileNotFoundException, IOException {
File inputFile = null;
FileInputStream fis = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
int lastRowNum = 0;
//Open the workbook
inputFile = new File(fileName);
fis = new FileInputStream(inputFile);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(sheetIndex);
lastRowNum = sheet.getLastRowNum();
return lastRowNum;
}
public String[] getSheetName(String fileName) throws FileNotFoundException, IOException {
int totalsheet = 0;
int i = 0;
String[] sheetName = null;
File inputFile = null;
FileInputStream fis = null;
XSSFWorkbook workbook = null;
//Open the workbook
inputFile = new File(fileName);
fis = new FileInputStream(inputFile);
workbook = new XSSFWorkbook(fis);
totalsheet = workbook.getNumberOfSheets();
sheetName = new String[totalsheet];
while (i < totalsheet) {
sheetName[i] = workbook.getSheetName(i);
i++;
}
return sheetName;
}
public int getNumberOfSheet(String fileName) throws FileNotFoundException, IOException {
int totalsheet = 0;
File inputFile = null;
FileInputStream fis = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
int lastRowNum = 0;
//Open the workbook
inputFile = new File(fileName);
fis = new FileInputStream(inputFile);
workbook = new XSSFWorkbook(fis);
totalsheet = workbook.getNumberOfSheets();
return totalsheet;
}
public String[][] getSheetData(String fileName, int sheetIndex) throws FileNotFoundException, IOException, InvalidFormatException {
String[][] data = null;
int i = 0;
int j = 0;Cell cell=null;
long emptyrowcount = 0;
InputStream inputStream = new FileInputStream(
fileName);
//Create a workbook object.
Workbook wb = WorkbookFactory.create(inputStream);
wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
Sheet sheet = wb.getSheetAt(sheetIndex);
//Iterate over all the row and cells
int noOfColumns = getNumberOfColumn(fileName, sheetIndex);
System.out.println("noOfColumns::" + noOfColumns);
int noOfRows = getNumberOfRow(fileName, sheetIndex) + 1;
System.out.println("noOfRows::" + noOfRows);
data = new String[noOfRows][noOfColumns];
for (int k = 0; k < noOfRows; k++) {
Row row = sheet.getRow(k);
if (row == null) {
} else {
j = 0;
for (int l = 0; l < noOfColumns; l++) {
//Cell cell = cit.next();
cell = row.getCell(j);
if (cell.getCellType() == cell.CELL_TYPE_BLANK) {
cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
}
data[i][j] = getCellValueAsString(cell);
j++;
}
i++;
}
}
return data;
}
/**
* This method for the type of data in the cell, extracts the data and
* returns it as a string.
*/
public static String getCellValueAsString(Cell cell) {
String strCellValue = null;
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
strCellValue = cell.toString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat dateFormat = new SimpleDateFormat(
"dd/MM/yyyy");
strCellValue = dateFormat.format(cell.getDateCellValue());
} else {
Double value = cell.getNumericCellValue();
Long longValue = value.longValue();
strCellValue = new String(longValue.toString());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
strCellValue = new String(new Boolean(
cell.getBooleanCellValue()).toString());
break;
case Cell.CELL_TYPE_BLANK:
strCellValue = "";
break;
}
}
return strCellValue;
}
public static void main(String s[]) {
try {
readXLSXFile readXLSxFile = new readXLSXFile();
String[][] sheetData = readXLSxFile.getSheetData("F:/work.xlsx", 0);
int columnLength = 0;
columnLength = readXLSxFile.getNumberOfColumn("F:/work.xlsx", 0);
int rowLength = 0;
rowLength = readXLSxFile.getNumberOfRow("F:/work.xlsx", 0);
int h = 0;
int j = 0;
while (j < rowLength) {
h = 0;
while (h < columnLength) {
System.out.print("\t     " + sheetData[j][h]);
h++;
}
System.out.println("");
j++;
}
} catch (InvalidFormatException ex) {
Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);
} catch (FileNotFoundException ex) {
Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);
}
}
}

Merci de m'aider comment gérer la valeur null ligne dans la feuille excel?

OriginalL'auteur Sameek Mishra | 2012-02-07