How to read data from excel file using poi and java

Apache poi
Apache poi






Apache POI is the api to create and modify Microsoft office files.
But here i will discuss about how to read data from excel sheets(spreadsheet).

Below jar files are require to read excel sheets.

1. ooxml-schemas-1.3.jar
2. poi-3.14.jar
3. poi-ooxml-3.14.jar
4. xmlbeans-2.6.0.jar

code to read data from spreadsheet.

package com.javaant;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadFromEXcel {


	static String filepath = null;
	static Object[][] data = null;

	public static void main(String ar[]) {
		ReadFromEXcel rw = new ReadFromEXcel("D:\\Java_Ant_Post\\readWritexls-poi\\ReadWriteExcel\\excels\\abc.xlsx");
		data = rw.readDataFromExcel();
		// rw.readDataFromExcel();

	}

	public ReadFromEXcel(String filepath) {
		this.filepath = filepath;
	}

	public Object[][] readDataFromExcel() {
		final DataFormatter df = new DataFormatter();
		try {

			FileInputStream file = new FileInputStream(getFile());
			// Create Workbook instance holding reference to .xlsx file
			XSSFWorkbook workbook = new XSSFWorkbook(file);

			// Get workbook
			XSSFSheet sheet = workbook.getSheetAt(0);

			// Iterate through each rows one by one
			Iterator rowIterator = sheet.iterator();

			int rownum = 0;
			int colnum = 0;
			// ignore the first row , it may be header
			Row r = rowIterator.next();

			int rowcount = sheet.getLastRowNum();
			int colcount = r.getPhysicalNumberOfCells();
			data = new Object[rowcount][colcount];

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();

				Iterator cellIterator = row.cellIterator();
				colnum = 0;
				while (cellIterator.hasNext()) {

					Cell cell = cellIterator.next();
					// Check the cell type and format accordingly
					data[rownum][colnum] = df.formatCellValue(cell);
					System.out.print(" " + df.formatCellValue(cell));
					colnum++;
				}
				rownum++;
				System.out.println("");
			}
			file.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return data;
	}

	public File getFile() throws FileNotFoundException {
		File here = new File(filepath);
		return new File(here.getAbsolutePath());

	}	
}

download Code

Profile photo of Nirmal Dhara
About Nirmal Dhara 27 Articles
Java Developer

4 Comments on How to read data from excel file using poi and java

  1. Admiring the hard work you put into your website and detailed information you offer.
    It’s great to come across a blog every once in a while that isn’t the same outdated rehashed material.
    Wonderful read! I’ve saved your site and I’m adding
    your RSS feeds to my Google account.

Leave a Reply

Your email address will not be published.


*


Skip to toolbar