skip to main | skip to sidebar

Java Programs and Examples with Output

Pages

▼
 
  • RSS
  • Twitter
Sunday, September 16, 2012

Advanced excel data extraction using SS User Model

Posted by Admin at 12:39 AM – 0 comments
 

/**
 * 
 */

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class SpreadSheetReader {
 Object row = null;
 Cell cell = null;

 public static int Cell_Total = 0;
 public static int Cell_Actual = 0;

 public static void main(String args[]) {
  SpreadSheetReader read_Obj = new SpreadSheetReader();
  try {
   read_Obj.readFile();
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  }

 }

 /*
  * To read the file
  */

 private void readFile() throws FileNotFoundException {
  File inputFile = new File("D:\\EPIW.xls");
  FileInputStream fileIStream = new FileInputStream(inputFile);
  try {
   Workbook workbook = WorkbookFactory.create(fileIStream);
   for (int Sh_Count = 0; Sh_Count < workbook.getNumberOfSheets(); Sh_Count++) {
    Sheet sheet = workbook.getSheetAt(Sh_Count);
    System.out.println("Sheet Name :"
      + workbook.getSheetName(Sh_Count));
    
    read_Rows(sheet);
   
    read_Columns(sheet);

   }

  } catch (IOException e) {

   e.printStackTrace();
  } catch (InvalidFormatException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

 }

 /*
  * Reading column wise
  */
 @SuppressWarnings("deprecation")
 private void read_Columns(Sheet sheet) {
  Iterator rows = sheet.rowIterator();
  int i = 0;
  while (i <= Cell_Total) {
   try {
    while (rows.hasNext()) {
     try {
      row = rows.next();
      Cell_Actual =  ((org.apache.poi.ss.usermodel.Row) row).getLastCellNum();

      if (Cell_Total < Cell_Actual) {
       Cell_Total = Cell_Actual;
      
      }

      cell = ((org.apache.poi.ss.usermodel.Row) row).getCell(i);
      getcellValue(cell);
     } catch (RuntimeException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
    }
    i++;
    rows = sheet.rowIterator();
   } catch (RuntimeException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
  Cell_Total = 0;
 }

 /*
  * Reading Row wise
  */

 private void read_Rows(Sheet sheet) {
  System.out.println("Reading Rows....");
  Iterator rows = sheet.rowIterator();
  while (rows.hasNext()) {
   try {
    row =rows.next();
    Iterator cells = ((org.apache.poi.ss.usermodel.Row) row).cellIterator();
    while (cells.hasNext()) {
     cell = (Cell) cells.next();
     getcellValue(cell);
    }
   } catch (RuntimeException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }

 /*
  * Handling various types of cells
  * 
  */
 private void getcellValue(Cell cell2) {
  try {

   if ((cell2.getCellType() == Cell.CELL_TYPE_STRING)) {
    System.out.println(cell2.getStringCellValue());
   }  else if (cell2.getCellType() == Cell.CELL_TYPE_NUMERIC) {
   } else if (cell2.getCellType() == Cell.CELL_TYPE_FORMULA) {

   } else if (cell2.getCellType() == Cell.CELL_TYPE_BLANK) {

   } else {
   }

  } catch (Exception e) {
   e.getMessage();
  }

 }
}

Labels: Excel Example

Leave a Reply

Newer Post Older Post
Subscribe to: Post Comments ( Atom )
  • Popular
  • Recent
  • Archives
Powered by Blogger.
 
 
 
© 2011 Java Programs and Examples with Output | Designs by Web2feel & Fab Themes

Bloggerized by DheTemplate.com - Main Blogger