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:

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");
}
}
Sample Console Output:
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
Sample Excel file used in this program is attached below:

Sample EXCEL File (cc_manual.xlsx)

 

 

 

 

 

 

 

 

 

223 total views, 1 views today

Leave a Reply

Your email address will not be published. Required fields are marked *