How To handle Null Row using Apache POI?

If you fetch a row, and get back null then that means there is no data stored in the file for that row - it's completely blank.

Up vote 0 down vote favorite share g+ share fb share tw.

I am using Apache POI to read xlsx file,it works well. I have question to you when row is found null,how i'm able to handle it. My file contain 500 row,but it show 105667 row,rest of row found null.

Used code: import java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io. File; import java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io. FileInputStream; import java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io.

FileNotFoundException; import java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io. IOException; import java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">java.io" rel="nofollow">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 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 Stringtotalsheet; while (i 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 StringnoOfRowsnoOfColumns; for (int k = 0; k GetCell(j); if (cell.getCellType() == cell.

CELL_TYPE_BLANK) { cell = row. GetCell(j, Row. CREATE_NULL_AS_BLANK); } dataij = 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 Print("\t " + sheetDatajh); 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); } } } plesae help me how to handle null row in excel sheet.

Thanks apache-poi link|improve this question asked Feb 7 at 6:22sam654321 95% accept rate.

If you fetch a row, and get back null, then that means there is no data stored in the file for that row - it's completely blank. POI by default gives you what's in the file. With Cells, you can set a MissingCellPolicy to control how missing and blank cells are handled.

With rows, they're either there or not.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.