/Java/Deal with empty or blank cell in excel file using apache poi

Deal with empty or blank cell in excel file using apache poi

package com.roytuts.java.apache.poi.excel.deal.empty.blank.cell;

public class Info {

	private String name;
	private String mobile;
	private String phone;
	private String permAddress;
	private String commAddress;

	//getters and setters

	@Override
	public String toString() {
		return "Info [name=" + name + ", mobile=" + mobile + ", phone=" + phone + ", permAddress=" + permAddress
				+ ", commAddress=" + commAddress + "]";
	}

}

Source: roytuts.com

Deal with empty or blank cell in excel file using apache poi

mobileCell.setCellType(Cell.CELL_TYPE_STRING);
info.setMobile(mobileCell.getStringCellValue());

Source: roytuts.com

Deal with empty or blank cell in excel file using apache poi

package com.roytuts.java.apache.poi.excel.deal.empty.blank.cell;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
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.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class ExcelUtil {

	private ExcelUtil() {
	}

	public static List<Info> extractInfo(final String file) {
		List<Info> infoList = new ArrayList<Info>();
		Workbook wb = null;

		try {
			wb = new XSSFWorkbook(new FileInputStream(new File(file)));
			Sheet sheet = wb.getSheetAt(0);

			boolean skipHeader = true;

			for (Row row : sheet) {
				if (skipHeader) {
					skipHeader = false;
					continue;
				}

				List<Cell> cells = new ArrayList<Cell>();

				int lastColumn = Math.max(row.getLastCellNum(), 5);// because my
																	// excel
																	// sheet has
																	// max 5
																	// columns,
																	// in case
																	// last
																	// column is
																	// empty
																	// then
																	// row.getLastCellNum()
																	// will
																	// return 4
				for (int cn = 0; cn < lastColumn; cn++) {
					Cell c = row.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
					cells.add(c);
				}

				Info info = extractInfoFromCell(cells);
				infoList.add(info);
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

		return infoList;
	}

	private static Info extractInfoFromCell(List<Cell> cells) {
		Info info = new Info();

		Cell nameCell = cells.get(0);
		if (nameCell != null) {
			info.setName(nameCell.getStringCellValue());
		}

		Cell mobileCell = cells.get(1);
		if (mobileCell != null) {
			switch (mobileCell.getCellType()) {
			case NUMERIC:
				info.setMobile(NumberToTextConverter.toText(mobileCell.getNumericCellValue()));
				break;
			case BLANK:
				break;
			default:
				break;
			}
		}

		Cell phoneCell = cells.get(2);
		if (phoneCell != null) {
			switch (phoneCell.getCellType()) {
			case NUMERIC:
				info.setPhone(NumberToTextConverter.toText(phoneCell.getNumericCellValue()));
				break;
			case BLANK:
				break;
			default:
				break;
			}
		}

		Cell permAddressCell = cells.get(3);
		if (permAddressCell != null) {
			info.setPermAddress(permAddressCell.getStringCellValue());
		}

		Cell commAddressCell = cells.get(4);
		if (commAddressCell != null) {
			info.setCommAddress(commAddressCell.getStringCellValue());
		}

		return info;
	}

}

Source: roytuts.com

Deal with empty or blank cell in excel file using apache poi

package com.roytuts.java.apache.poi.excel.deal.empty.blank.cell;

import java.util.List;

public class BlankCellHandlerApp {

	public static void main(String[] args) {
		List<Info> infoList = ExcelUtil.extractInfo("info.xlsx");

		for (Info info : infoList) {
			System.out.println(info);
		}
	}

}

Source: roytuts.com

Deal with empty or blank cell in excel file using apache poi

//If you are using apache dependency version 3.12 then you need to handle different way. The switch case statement should be replaced in the following way for numeric cell types.

switch (mobileCell.getCellType()) {
case NUMERIC:
	info.setMobile(NumberToTextConverter.toText(mobileCell.getNumericCellValue()));
	break;
case BLANK:
	break;
default:
	break;
}

Source: roytuts.com

Similar codes

0 vote270 views7 answers
Convert code python to java
Java
0 vote286 views1 answer
Keytool error: java.io.FileNotFoundException: my-release-key.keystore (Access is denied) java.io.FileNotFoundException: my-release-key.keystore (Access is denied)
Java
0 vote265 views1 answer
Flutter doctor –android-licenses gives a java error
Java
0 vote266 views2 answers
How to convert int array to Integer List in java
Java
0 vote288 views2 answers
How to use asynx task to make api connection java
Java
8 votes237 views1 answer
Sha key android
Java
3 votes273 views5 answers
Math ceil java
Java
1 vote267 views2 answers
Change image size
Java
9 votes272 views4 answers
How to wait time in java
Java
0 vote280 views1 answer
Predict the output of the given code: void method2(char c) { System.out.println( (int) c); } What will be the output of the given code snippet if i) if 'x' is passed to c i) if ‘0’ is passed to c
Java