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.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

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;

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

POIFSFileSystem fileSystem = null;

try
{
fileSystem = new POIFSFileSystem (inputStream);

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

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

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); “);
System.out.println(poiExample.query.toString());
}
}

3. OUTPUT

U will get output like this

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

When input excel file [ screenshot]

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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