Read Write Excel POI Example

Java Quick Notes

Refresh Your Java - Before Java Interview

We are Agile, believe in less Documentation - Only Quick notes (Java Interview Questions) of Java/J2ee Read more....

Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet

Go to SiteMap

Q)  Read Write Excel with Apache POI Example


Create Read ExcelSheet Example with Java By using Apache POI.

The following Example illustates how to create a ExcelSheet in Java
by using Apache Poi. Also how to read data from Excel Sheet by is using
Apache POI.

In this example is "OrderImportField" is a field which holds the Data and Name,
you need to create this your own.

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.webdav.lib.WebdavResource;

public class SalesOrderExcelFileHandler {

 private static final Log log = LogFactory.getLog(SalesOrderExcelFileHandler.class);

 public static String contextRealPath = null;

 public static byte[] createExcelFile(List rows) throws IOException {

  HSSFWorkbook wb = new HSSFWorkbook();
  HSSFSheet sheet = wb.createSheet("Orders");

  Iterator rowsIterator = rows.iterator();

  HSSFFont font = wb.createFont();
  HSSFCellStyle style = wb.createCellStyle();
  int rowIndex = 0;
  while (rowsIterator.hasNext()) {
   // for each, create excel row
   HSSFRow row = sheet.createRow((short) rowIndex);

   // Get the row, and iterate through the its field
   List temprow = (List) rowsIterator.next();
   int colIndex = 0;
   Iterator filedIterator = temprow.iterator();
   while (filedIterator.hasNext()) {
    HSSFCell cell = row.createCell((short) colIndex);
    OrderImportField field = (OrderImportField) filedIterator.next();
    if (rowIndex == 0)
    else {
     if (field.getValue() != null)
      cell.setCellValue((String) field.getValue());

    if (field.getMandatoryFieldFlag())

  ByteArrayOutputStream bos = new ByteArrayOutputStream();
  return bos.toByteArray();

 public static ArrayList readImportFiledsItms(InputStream in,
   LeadImpFile leadImpFile) throws IOException, BLException {

  log.debug("Entering getAllLeadImportFiledsItms");
  // FileInputStream fin = new FileInputStream(filename);
  // create a new org.apache.poi.poifs.filesystem.Filesystem
  POIFSFileSystem poifs = new POIFSFileSystem(in);
  HSSFWorkbook wb = new HSSFWorkbook(poifs);
  HSSFSheet hssfsheet = wb.getSheetAt(0);
  log.debug("first row:" + hssfsheet.getFirstRowNum());
  log.debug("last row:" + hssfsheet.getLastRowNum());
  // List fields = LeadImportField.getFieldList();
  // List fields = LeadImportField.getFieldList(leadImpFile);

  // Read each row
  // Exclude the first row as it contains the name of the Headers
  ArrayList parsedrows = new ArrayList();
  for (int i = hssfsheet.getFirstRowNum(); i <= hssfsheet.getLastRowNum(); i++) {
   HSSFRow row = hssfsheet.getRow(i);
   if (i == 0) {

   String accountName = "";
   String accountNumber = "";
   HSSFCell cell = row.getCell((short) 0);
   if (cell != null && HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
    accountName = String.valueOf(cell.getStringCellValue());
   cell = row.getCell((short) 1);
   if (cell != null && HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
    accountNumber = String.valueOf(cell.getStringCellValue());

   try {
   } catch (NumberFormatException e) {
    accountNumber = "0";

   // READ ALL THE Fields
   ArrayList leadImportFields = new ArrayList();
   StringBuffer sb = new StringBuffer();
   for (int columnIndex = 0; columnIndex < fields.size(); columnIndex++) {
    cell = row.getCell((short) columnIndex);
    if (cell != null && !fieldProps.getIgnoreFieldFlag()) {
     int cellType = cell.getCellType();
     if (cellType == HSSFCell.CELL_TYPE_STRING) {
      clvalue = cell.getStringCellValue();
     } else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
       clvalue = String.valueOf(new Double(cell.getNumericCellValue()).intValue());


  return parsedrows;

 /** for the premise Survey Added **/

 public static InputStream getStandardTemplate() throws Exception {
  System.out.println("importing Site data from Excel file :"
  String path = SiteImportServices.WEBDAV_PATH_SITE + STANDARD_TEMPLATE;
  System.out.println("File Path :" + path);
  // Create InputStream by using File
  InputStream content =

  return content;


 public static byte[] getStandardTemplateCopy() {
  try {
   InputStream in = getStandardTemplate();
   POIFSFileSystem poifs = new POIFSFileSystem(in);
   HSSFWorkbook wb = new HSSFWorkbook(poifs);
   ByteArrayOutputStream bos_cloned = new ByteArrayOutputStream();
   byte clonedBytes[] = bos_cloned.toByteArray();
   in = null;
   return clonedBytes;
  } catch (Exception e) {
   return null;


 private static HSSFCell getCell(HSSFSheet hssfsheet, int rowNumber,
   short colNumber) {
  HSSFRow row = hssfsheet.getRow(rowNumber);
  HSSFCell cell = row.getCell(colNumber);
  return cell;


Back to top

------------------------- We hope you got necessary Info On -----------------------------------------

Read Write Excel POI Example

Read Write Excel POI Example


Face Book
Request for a Mock Interview/Training

Get a PDF

Face Book
Same look (Read) on any device, this is Ads free