Read Data From Excel using Java, POI

1. Get poi*.jar from Download POI JAR
2. Use this Java Program to read Data in Excel file By Column by Column

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


import java.util.Iterator;

// This is a simple java program to generate sql script from excel file contains values

public class POIExcelReader
StringBuilder query = new StringBuilder(“INSERT INTO tablename (Name, URL, Age) VALUES \n\n”);

public POIExcelReader()

public void displayFromExcel (String xlsPath)
InputStream inputStream = null;

inputStream = new FileInputStream (xlsPath);
catch (FileNotFoundException e)
System.out.println (“File not found in the specified path.”);
e.printStackTrace ();

POIFSFileSystem fileSystem = null;

fileSystem = new POIFSFileSystem (inputStream);

HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);
HSSFSheet         sheet    = workBook.getSheetAt (0);
Iterator<HSSFRow> rows     = sheet.rowIterator ();

while (rows.hasNext ())
HSSFRow row = ();

HSSFCell nameCell = row.getCell((short)0);
HSSFCell ageCell = row.getCell((short)1);
HSSFCell urlCell = row.getCell((short)2);

query.append(“(‘”+nameCell.getStringCellValue()+”‘, “+
“‘”+urlCell.getStringCellValue()+”‘, “+
“”+Math.round(ageCell.getNumericCellValue() )+”),  \n”);

catch (IOException e)
e.printStackTrace ();

public static void main (String[] args)
POIExcelReader poiExample = new POIExcelReader();
String         xlsPath    = “c:\\pe_ip.xls”;

poiExample.displayFromExcel (xlsPath);

poiExample.query.append(” \n); “);


U will get output like this

INSERT INTO tablename (Name, URL, Age) VALUES
(‘aaa’, ‘’, 25),
(‘bbb’, ‘’, 25),
(‘ccc’, ‘’, 98)

When input excel file [ screenshot]


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s