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

Rich Ratel answered on November 18, 2021 Popularity 1/10 Helpfulness 1/10

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

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

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

Comment

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 + "]";
	}

}

Popularity 1/10 Helpfulness 1/10 Language java

Source: roytuts.com

Tags: apache-poi cell excel file java using

Link to this answer
Share Copy Link

Contributed on Nov 18 2021

Rich Ratel

0 Answers Avg Quality 2/10

Closely Related Answers

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

Comment

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;
	}

}

Popularity 1/10 Helpfulness 1/10 Language java

Source: roytuts.com

Tags: file java using apache-poi cell excel

Link to this answer
Share Copy Link

Contributed on Nov 18 2021

Rich Ratel

0 Answers Avg Quality 2/10

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

Comment

//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;
}

Popularity 1/10 Helpfulness 1/10 Language java

Source: roytuts.com

Tags: excel file java using apache-poi cell

Link to this answer
Share Copy Link

Contributed on Nov 18 2021

Rich Ratel

0 Answers Avg Quality 2/10

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

Comment

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

Popularity 1/10 Helpfulness 1/10 Language whatever

Source: roytuts.com

Tags: excel file using whatever apache-poi cell

Link to this answer
Share Copy Link

Contributed on Nov 18 2021

Rich Ratel

0 Answers Avg Quality 2/10

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

Comment

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);
		}
	}

}

Popularity 1/10 Helpfulness 1/10 Language java

Source: roytuts.com

Tags: excel file java using apache-poi cell

Link to this answer
Share Copy Link

Contributed on Nov 18 2021

Rich Ratel

0 Answers Avg Quality 2/10

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

Contents

More Related Answers

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

Closely Related Answers

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

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

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

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

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.