Thursday, February 14, 2019

Retrieve data from xls/xlsx file by Apache POI

 import java.io.File;  
 import java.io.FileInputStream;  
 import java.io.IOException;  
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
 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 PoiEx {  
      public void readExcel(String filePath, String filename,String sheetName) throws IOException{  
           File file=new File(filePath+"\\"+filename);  
           FileInputStream fileInputStream=new FileInputStream(file);  
           Workbook workbook=null;  
           String fileextensionname=filename.substring(filename.indexOf("."));  
           if(fileextensionname.equals(".xlsx")){  
                workbook=new XSSFWorkbook(fileInputStream);  
           }else if(fileextensionname.equals(".xls")){  
                workbook=new HSSFWorkbook(fileInputStream);  
           }  
           //Get sheet by name  
           Sheet sheet=workbook.getSheet(sheetName);  
           //Count rows in the sheet  
           int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();  
           for(int i=0;i<rowCount+1;i++){  
                Row row=sheet.getRow(i);  
                int lastCellNumber=row.getLastCellNum();  
                for(int j=0;j<lastCellNumber;j++){  
                     System.out.print(row.getCell(j).getStringCellValue()+" ");  
                }  
                System.out.println();  
           }  
      }  
      public static void main(String[] args) throws IOException {  
           // TODO Auto-generated method stub  
                PoiEx ex=new PoiEx();  
                String filePath="C:\\Users\\Mutturaj\\Desktop";  
                ex.readExcel(filePath, "muttu.xlsx", "muttu");  
      }  
 }