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();
  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  HSSFCellStyle style = wb.createCellStyle();
  style.setFont(font);
  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)
     cell.setCellValue(field.getFileFieldName());
    else {
     if (field.getValue() != null)
      cell.setCellValue((String) field.getValue());
     else
      cell.setCellValue("");
    }

    if (field.getMandatoryFieldFlag())
     cell.setCellStyle(style);

    colIndex++;
   }
   rowIndex++;
  }
  ByteArrayOutputStream bos = new ByteArrayOutputStream();
  wb.write(bos);
  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) {
    continue;
   }

   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 {
    Integer.parseInt(accountNumber);
   } 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());
      }
     }
    }

   }
   parsedrows.add(leadImportFields);

  }
  return parsedrows;
 }
 

 /** for the premise Survey Added **/

 public static InputStream getStandardTemplate() throws Exception {
  System.out.println("importing Site data from Excel file :"
    + STANDARD_TEMPLATE);
  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();
   wb.write(bos_cloned);
   byte clonedBytes[] = bos_cloned.toByteArray();
   in.close();
   bos_cloned.close();
   in = null;
   return clonedBytes;
  } catch (Exception e) {
   e.printStackTrace();
   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