Saturday, January 24, 2009

Java: Simple Program To Read Excel File

There are two simple methods, Below both programs produces same output

1st Method : Using POI (Note : Download POI to run this code)


import java.io.*;
import java.util.*;

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

public class ExcelReadIterator
{
public static void main(String[] args) throws Exception
{
InputStream ExcelFileToRead = new FileInputStream("ExcelFile.xls");
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);

HSSFSheet sheet=wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;

Iterator rows=sheet.rowIterator();

while (rows.hasNext())
{
row=rows.next();
Iterator cells=row.cellIterator();
while (cells.hasNext())
{
cell=cells.next();

if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+" ");
}
else
{
//U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}

}
}




2nd Method : Using Virtual DB (This is simple method, directly u can run this code)



import java.sql.*;

public class ReadExcel
{
public static void main (String[] args)
{
String ExcelFileToRead = "ExcelFile.xls";

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="+ExcelFileToRead);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("Select * from [Sheet1$]");


while(rset.next())
{
System.out.println(rset.getString(1)+" "+rset.getString(2)+" "+rset.getString(3)) ;
}



}
catch(Exception ex)
{
ex.printStackTrace();
}
}


}