Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc.
Let's create a Excel file and insert some data into this file and give this file name as "JavaIsEasy.xls".
Now we need to add library files to execute this program, as follows.
Add above jar in eclipse.
Now create a java class named as "ReadExcelFile.java".
*******************************************************************************
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile {
public static void main(String[] args) throws IOException {
String fname = "C:/DATA/Software/LIFERAY/liferay data/Blogs Material/Core Java/JavaIsEasy.xls";
//********************************************************************
InputStream inp = new FileInputStream(fname);
String fileExtn = GetFileExtension(fname);
Workbook wb_xssf;
Workbook wb_hssf;
Sheet sheet = null; // sheet can be used as common for XSSF and HSSFWorkBook
if (fileExtn.equalsIgnoreCase("xlsx"))
{
wb_xssf = new XSSFWorkbook(fname);
log("xlsx="+wb_xssf.getSheetName(0));
sheet = wb_xssf.getSheetAt(0);
}
if (fileExtn.equalsIgnoreCase("xls"))
{
POIFSFileSystem fs = new POIFSFileSystem(inp);
wb_hssf = new HSSFWorkbook(fs);
log("xls="+wb_hssf.getSheetName(0));
sheet = wb_hssf.getSheetAt(0);
}
Iterator rows = sheet.rowIterator(); // Now we have rows ready fromthe sheet
while (rows.hasNext())
{
Row row = (Row) rows.next();
log("row#="+row.getRowNum()+"");
log("**********************");
//log(row.getPhysicalNumberOfCells()+"");
Iterator cells = row.cellIterator();
String cellValue=null;
while (cells.hasNext())
{
Cell cell = (Cell) cells.next();
switch ( cell.getCellType() )
{
case Cell.CELL_TYPE_STRING:
log(cell.getRichStringCellValue().getString());
cellValue=cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
log(cell.getDateCellValue()+"");
cellValue=cell.getDateCellValue().toString();
} else {
System.out.println(cell.getNumericCellValue());
cellValue=String.valueOf(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
log(cell.getBooleanCellValue()+"");
cellValue=cell.getBooleanCellValue()+"";
break;
case Cell.CELL_TYPE_FORMULA:
log(cell.getCellFormula());
cellValue=cell.getCellFormula();
break;
default:
}
}
}
inp.close();
}
private static void log(String message)
{
System.out.println(message);
}
private static String GetFileExtension(String fname2)
{
String fileName = fname2;
String fname="";
String ext="";
int mid= fileName.lastIndexOf(".");
fname=fileName.substring(0,mid);
ext=fileName.substring(mid+1,fileName.length());
return ext;
}
}
*******************************************************************************
Now execute this program.
Output:-
*******************************************************************************
xls=Sheet1
row#=0
**********************
Programming Language
Projects Done
Last Used
*
*
*
row#=4
**********************
PHP
0.0
Not Used
*******************************************************************************