Reading Empty / Blank Cell in Excel using Java

Reading Empty / Blank Cell in Excel using Java:

Somedays back I published how to read excel file in java, but one of the user reported me that it is not working as expected when it come across the empty/blank cell(s) in the excel sheet.

So I rewritten the excel reading program in java (which also reads or handles empty/blank cell properly) after going through the apache poi documentation for these scenario. Full reading empty / blank cell in excel using java program is given below.

Reading Empty / Blank Cell in Excel using Java Full Source Code:

[java]
package in.javadomain;

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelBlankCells {
public static void main(String[] args) {
System.out.println(“Reading Excel File .xlsx Starts”);
try {
FileInputStream xlsxfile = new FileInputStream(
new File(“C:\\cc_manual.xlsx”));
// Creating XSSFWorkbook
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(xlsxfile);

// Getting the contents from the first sheet of the workbook
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
for (Row row : xssfSheet) {
for (int cell_number = 0; cell_number < row.getLastCellNum(); cell_number++) {
Cell cell = row.getCell(cell_number, Row.CREATE_NULL_AS_BLANK);
// Print the cell for debugging
System.out.println(“Row : “+row.getRowNum()+” Cell : ” + cell_number + ” value –> ” + cell.toString());
}
}
xlsxfile.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(“Reading Excel File .xlsx Completed”);
}
}
[/java]

Sample Console Output:

[plain]
Reading Excel File .xlsx Starts
Row : 0 Cell : 0 value –> Title
Row : 0 Cell : 1 value –> Desc
Row : 0 Cell : 2 value –> Expiry_Date (dd/mm/yyyy)
Row : 0 Cell : 3 value –> Coupon
Row : 0 Cell : 4 value –> Store_Home_URL
Row : 1 Cell : 0 value –> Sample Title
Row : 1 Cell : 1 value –> Sample Desc
Row : 1 Cell : 2 value –> 04-May-2017
Row : 1 Cell : 3 value –> TEST
Row : 1 Cell : 4 value –> www.test.com
Row : 2 Cell : 0 value –> Sample Title1
Row : 2 Cell : 1 value –>
Row : 2 Cell : 2 value –> 31/4/2017
Row : 2 Cell : 3 value –>
Row : 2 Cell : 4 value –> www.test.in
Row : 3 Cell : 0 value –> Sample Title2
Row : 3 Cell : 1 value –>
Row : 3 Cell : 2 value –> 16/4/2017
Row : 3 Cell : 3 value –> TEST2
Row : 3 Cell : 4 value –> www.test.co.in
Row : 4 Cell : 0 value –> Sample Title3
Row : 4 Cell : 1 value –> Sample Desc3
Row : 4 Cell : 2 value –> 04-Feb-2017
Row : 4 Cell : 3 value –>
Row : 4 Cell : 4 value –> www.test.biz
Row : 5 Cell : 0 value –> Sample Title4
Row : 5 Cell : 1 value –>
Row : 5 Cell : 2 value –>
Row : 5 Cell : 3 value –>
Row : 5 Cell : 4 value –> www.test.net
Row : 6 Cell : 0 value –> Sample Title5
Row : 6 Cell : 1 value –> Sample Desc3
Row : 6 Cell : 2 value –>
Row : 6 Cell : 3 value –>
Row : 6 Cell : 4 value –> www.test.net.in
Row : 7 Cell : 0 value –> Sample Title6
Row : 7 Cell : 1 value –>
Row : 7 Cell : 2 value –>
Row : 7 Cell : 3 value –> TEST2
Row : 7 Cell : 4 value –> shop.test.co.in
Reading Excel File .xlsx Completed
[/plain]

Sample Excel file used in this program is attached below:

Sample EXCEL File (cc_manual.xlsx)