Excel Reader

import java.io.File;

import java.io.FileInputStream;

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;

public class ExcelReader {

    

     public static void main( String[] args) throws Exception{

          File excel = new File("C:\\Selenium\\Data.xls");

          FileInputStream fis = new FileInputStream(excel);

          HSSFWorkbook wb = new HSSFWorkbook(fis);

          HSSFSheet ws = wb.getSheet("Input") ;

         

              int rowNum = ws.getLastRowNum() + 1 ;

              int colNum = ws.getRow(0).getLastCellNum() ;

          String[][] data = new String[rowNum][colNum] ;

                  

          for  ( int i = 0 ; i < rowNum ; i++) {

              HSSFRow row = ws.getRow(i) ;

                   for ( int j = 0 ; j < colNum ; j++) {

                        HSSFCell cell = row.getCell(j) ;

                        String value = cellToString(cell);

                        data[i][j] = value ;

                        System.out.println("the value is " + value);

                   }

              }

     }

public static String cellToString(HSSFCell cell) {

             

              int type ;

              Object result ;

              type = cell.getCellType() ;

             

              switch (type) {

             

                   case 0 : // numeric value in Excel

                        result = cell.getNumericCellValue() ;

                        break ;

                   case 1 : // String Value in Excel

                        result = cell.getStringCellValue() ;

                        break ;

                   default : 

                        throw new RuntimeException("There are no support for this type of cell") ;                           

              }

                  

              return result.toString() ;

          }

}    


--------------------------------------------------------------

public class ReadWriteExcel
{
    public static void writeExcel(String[] args)
    {
        //Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        
        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("Employee Data");
         
        //This data needs to be written (Object[])
        Map data = new TreeMap();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});
         
        //Iterate over data and write to sheet
        Set keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr)
            {
               Cell cell = row.createCell(cellnum++);
               if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("howtodoinjava_demo.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }


 public static void readExcel(String[] args)
    {
        try
        {
            FileInputStream file = new FileInputStream(new File("howtodoinjava_demo.xlsx"));

            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows one by one
            Iterator rowIterator = sheet.iterator();
            while (rowIterator.hasNext())
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator cellIterator = row.cellIterator();
                
                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType())
                    {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                    }
                }
                System.out.println("");
            }
            file.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
   
    public static void formualCell(String[] args)
{
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Calculate Simple Interest");
 
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Pricipal");
    header.createCell(1).setCellValue("RoI");
    header.createCell(2).setCellValue("T");
    header.createCell(3).setCellValue("Interest (P r t)");
     
    Row dataRow = sheet.createRow(1);
    dataRow.createCell(0).setCellValue(14500d);
    dataRow.createCell(1).setCellValue(9.25);
    dataRow.createCell(2).setCellValue(3d);
    dataRow.createCell(3).setCellFormula("A2*B2*C2");
     
    try {
        FileOutputStream out =  new FileOutputStream(new File("formulaDemo.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("Excel with foumula cells written successfully");
         
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}
}