Reading Excel File
Hi All
I am using Jakarta POI to read Excel File.
Each Row in Excel file , corresponds to one input set.
Excel file has 2 sheets 1 sheet has input, 2nd sheet has output.
So I have to read from excel file Sheet1->Row1 ( Input, then Process) and verify against Sheet2->Row1
Have to do it for all Rows.
I wrote the program which reads a particular row, however I feel that there should be a better way, as I am reading the file for each row.
I am looking for a way, where I read the excel file only once and get the row I want in a sheet.
Any suggestions: Please let me know.
Here is my program:
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
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;
publicclass ExcelUtils{
public ExcelUtils(){
}
public String getRow( String fileName,int workBookNum,int rowNumber ){
InputStream input = ExcelUtils.class.getResourceAsStream(fileName);
POIFSFileSystem fs;
HashMap hm =new HashMap();
StringBuffer retString =new StringBuffer();
String retValue ="";
try{
fs =new POIFSFileSystem(input);
HSSFWorkbook wb =new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
if ( (rowNumber > -1)& (rowNumber <= sheet.getLastRowNum()){
HSSFRow row = sheet.getRow(rowNumber);
if (row ==null)returnnull;
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
HSSFCell cell = (HSSFCell) row.getCell((short) i);
if (cell ==null)returnnull;
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_NUMERIC:
hm.put(""+i, cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
hm.put(""+i,cell.getStringCellValue());
break;
default:
hm.put(""+i,"UnSupported Cell Value");
break;
}
}
}
}catch (IOException e){
// TODO Auto-generated catch block
e.printStackTrace();
}
}
publicvoid printExcel(String fileName){
try{
InputStream input = ExcelUtils.class.getResourceAsStream(fileName);
POIFSFileSystem fs =new POIFSFileSystem(input);
HSSFWorkbook wb =new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++){
HSSFRow row = sheet.getRow(j);
System.out.println("Row #" + j );
if (row !=null){
System.out.print("Last Cell --" + row.getLastCellNum());
System.out.println("");
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
HSSFCell cell = (HSSFCell) row.getCell((short) i);
if (cell !=null)
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_NUMERIC:
System.out.print(cell.getCellNum() +"-#-"+ cell.getNumericCellValue() +"\t");
break;
case HSSFCell.CELL_TYPE_STRING:
System.out.print(cell.getCellNum() +"-#-"+ cell.getStringCellValue() +"\t");
break;
default:
System.out.print(cell.getCellNum() +"-#-"+"unsuported cell type" +"\t");
break;
}
}
System.out.println("");
}
}
}catch (IOException ex){
ex.printStackTrace();
}
}
publicstaticvoid main(String[] args){
ExcelUtils eu =new ExcelUtils();
eu.printExcel("Cost_Categories.xls");
}
}
