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("");
  }
 }
}

No comments:

Post a Comment

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...