POI API for writing data from excel file to a text file.

Many times you will come across situations, wherein you will need to read data from excel files and do some processing.

POI API comes to the rescue here :)

Download the latest POI jar file and add it to your project library.

import java.io.File;
import java.io.FileInputStream;
import java.io.PrintWriter;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ConvertFile 
{
   public static void main(String[] args) 
   {
      System.out.println("Start of Program");
      try
      {
FileInputStream file = new FileInputStream(new File("D:\\Test_Data_Automation.xls"));

//Get the workbook instance for XLS file 
HSSFWorkbook workbook = new HSSFWorkbook(file);

//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(2);

//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();

String fileName = "";
PrintWriter writer = null;

        while(rowIterator.hasNext())
         {
            Row row = rowIterator.next();
       
            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
             
            try{
                 writer = new PrintWriter("D:\\Output_Data.txt", "UTF-8");
            }catch(Exception ex){ex.printStackTrace();}
                          
            while(cellIterator.hasNext()) 
            {
               Cell cell = cellIterator.next();
                       
            if(cell.getRowIndex() > 3)      //Start reading from the 3rd row.
            {
               switch(cell.getCellType()) 
               {
                  case Cell.CELL_TYPE_BOOLEAN:
                  System.out.print(cell.getBooleanCellValue() + "\t\t");
                  writer.println(String.valueOf(cell.getBooleanCellValue()));
                  break;
                  
                   case Cell.CELL_TYPE_NUMERIC:
                  System.out.print(cell.getNumericCellValue() + "\t\t");
                  writer.println(String.valueOf(cell.getNumericCellValue()));
                  break;
                   
                   case Cell.CELL_TYPE_STRING:
                  System.out.print(cell.getStringCellValue() + "\t\t");
                  writer.println(String.valueOf(cell.getStringCellValue()));
                  break;
                   
                   case Cell.CELL_TYPE_FORMULA:
                  cellValue=String.valueOf(cell.getStringCellValue());
                  break;
                   
                   case Cell.CELL_TYPE_ERROR:
                  cellValue=String.valueOf(cell.getStringCellValue());
                  break;
                  }
            }
             }
             
             try{
             System.out.println("Close the file");
                 writer.close();
             }catch(Exception ex){ex.printStackTrace();}
         }

         System.out.println("End of Program");

     }
     catch(Exception ex) {ex.printStackTrace();}
   }
}   //End of Main


Some Important syntax->
1) To read any row apart from the current row, use the following.
     Row nextRow = sheet.getRow(row.getRowNum() + 1);
     Row prevRow = sheet.getRow(row.getRowNum() - 1);
     Row next2Row = sheet.getRow(row.getRowNum() + 2);
     Row prev2Row = sheet.getRow(row.getRowNum() - 2);
     etc.

2) To read any column in any particular row, use the following.
     row.getCell(1);
     row.getCell(7);
     row.getCell(11);
     etc.