Showing posts with label Excel Read in java POI jar POI jars for Reading excel. Show all posts
Showing posts with label Excel Read in java POI jar POI jars for Reading excel. Show all posts

Wednesday, 14 May 2014

Reading xls and xlsx in java using POI jars

Below Jars are Required for implemnetation:
   dom4j-1.6.1.jar
   poi-3.10-FINAL-20140208.jar
   poi-ooxml-3.10-FINAL-20140208.jar
   poi-ooxml-schemas-3.10-FINAL-20140208.jar
   stax-api-1.0.1.jar
   xmlbeans-2.3.0.jar
   commons-lang-2.6.jar

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
public class ExcelMainProgram {
 public static void main(String[] args) throws Exception {
  //
  // An excel file name. You can create a file name with a full path
  // information.
  //
  System.out.println(new Date());
  String filename = "Template.xls";//xls or xlsx
  //
  // Create an ArrayList to store the data read from excel sheet.
  //
  ArrayList<ArrayList<Cell>> sheetData = new ArrayList<ArrayList<Cell>>();
  FileInputStream fis = null;
  try {
   //
   // Create a FileInputStream that will be use to read the excel file.
   //
   fis = new FileInputStream(filename);
   //
   // Create an excel workbook from the file system.
   //
   Workbook workbook = WorkbookFactory.create(fis);;
   //
   // Get the first sheet on the workbook.
   //
   Sheet sheet = workbook.getSheetAt(0);
   //
   // When we have a sheet object in hand we can iterator on each
   // sheet's rows and on each row's cells. We store the data read
   // on an ArrayList so that we can printed the content of the excel
   // to the console.
   //
   Iterator<Row> rows = sheet.rowIterator();
   while (rows.hasNext()) {
    Row row =rows.next();
    if(row.getRowNum()==2)
    {continue;}
   }
   while (rows.hasNext()) {
    Row row =rows.next();
    Iterator<Cell> cells = row.cellIterator();
    System.out.println(row.getRowNum());
    ArrayList<Cell> data = new ArrayList<Cell>();
    while (cells.hasNext()) {
     Cell cell =  cells.next();
     data.add(cell);
    }
    sheetData.add(data);
   }
  } catch (IOException e) {
   e.printStackTrace();
  } finally {
   if (fis != null) {
    fis.close();
   }
  }
  showExelData(sheetData);
  System.out.println(new Date());
 }
 private static void showExelData(List<ArrayList<Cell>> sheetData) {
  //
  // Iterates the data and print it out to the console.
  //
  for (int i = 0; i < sheetData.size(); i++) {
   List<Cell> list = (List<Cell>) sheetData.get(i);
   for (int j = 0; j < list.size(); j++) {
    Cell cell =list.get(j);
    cell.setCellType(Cell.CELL_TYPE_STRING);//To get the cell value as string set the cell type as String
    System.out.print(cell.getStringCellValue() + "\t\t");
    if (j < list.size() - 1) {
     //System.out.print("SPACE");
    }
   }
   System.out.println("");
  }
 }
}

Run Postman API remote or code or command line

POSTMAN is used to run the APIs generally. What if we want to create a collection in postman and run it from some other code or command l...