Writing To Excel File Using Apache POI

This is a java tutorial where I have demonstrated how to use Apache POI to write data to any excel/spread sheet. The program handles HSSF model of spreadsheet.

/**
 * @author Kushal Paudyal
 * www.sanjaal.com/java
 * Last Modified on 02/16/2008
 */
package com.kushal.util;

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CreateExcelFile {
 
    public static void main( String [] args ) {
    	/**Name of excel file that we are going to create**/
    	String fileName="C:\temp\testPOIWrite.xls";
    	writeDataToExcelFile(fileName);    
    }
    /** This method writes data to new excel file **/
	 private static void writeDataToExcelFile(String fileName) {
		 
	     String [][] excelData = preapreDataToWriteToExcel();		 
			 
		 HSSFWorkbook myWorkBook = new HSSFWorkbook();
		 HSSFSheet mySheet = myWorkBook.createSheet();
		 HSSFRow myRow = null;
		 HSSFCell myCell = null;
		
		 for (int rowNum = 0; rowNum < excelData[0].length; rowNum++){    
			 myRow = mySheet.createRow(rowNum);
			 
			 for (int cellNum = 0; cellNum < 4 ; cellNum++){
		         myCell = myRow.createCell(cellNum);
		         myCell.setCellValue(excelData[rowNum][cellNum]);      
		       
		     }
		 }
		 
		 try{
			 FileOutputStream out = new FileOutputStream(fileName);
			 myWorkBook.write(out);
			 out.close();
		 }catch(Exception e){ e.printStackTrace();}			
		
	}
	 /**Prepare some demo data as excel file content**/
	 public static String [][] preapreDataToWriteToExcel(){
		 String [][] excelData = new String [4][4];
		 excelData[0][0]="First Name";
		 excelData [0][1]="Last Name";
		 excelData[0][2]="Telephone";
		 excelData[0][3]="Address";
		 
		 excelData[1][0]="Kushal";
		 excelData[1][1]="Paudyal";
		 excelData[1][2]="000-000-0000";
		 excelData[1][3]="IL,USA";
		 
		 excelData[2][0]="Randy";
		 excelData[2][1]="Ram Robinson";
		 excelData[2][2]="111-111-1111";
		 excelData[2][3]="TX, USA";
		 
		 excelData[3][0]="Phil";
		 excelData[3][1]="Collins";
		 excelData[3][2]="222-222-2222";
		 excelData[3][3]="NY, USA";
		 
		 return excelData;
		 
	 }	
    
}

Reading Excel File Using Java And Apache POI

In this Java Tutorial, I will show you how to read the content of an Excel File. The tutorial will read a sample excel file and then print the content to the console. Making tutorials simpler to understand is one of my objectives in this blog. Hopefully you will find this tutorial extremely simple to understand how to use Apache POI to read any Excel File [.xls format].

I used the following apache POI API and is available for download at the publisher’s website.

  • poi-3.2-FINAL-20081019.jar

/**
 * @author Kushal Paudyal
 * www.sanjaal.com/java
 * Last Modified on 02/16/2008
 */
package com.kushal.util;

import java.io.FileInputStream;
import java.util.Iterator;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
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;

public class ReadExcelFile {

    public static void main( String [] args ) {

    	String fileName=&quot;C:\temp\testPOI.xls&quot;;
    	//Read an Excel File and Store in a Vector
    	Vector dataHolder=readExcelFile(fileName);
    	//Print the data read
    	printCellDataToConsole(dataHolder);
    }
	public static Vector readExcelFile(String fileName)
    {
    	/** --Define a Vector
    	 	--Holds Vectors Of Cells
    	 */
    	Vector cellVectorHolder = new Vector();

    	try{
    	/** Creating Input Stream**/
    	//InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName );
    	FileInputStream myInput = new FileInputStream(fileName);

    	/** Create a POIFSFileSystem object**/
    	POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

    	/** Create a workbook using the File System**/
         HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

         /** Get the first sheet from workbook**/
        HSSFSheet mySheet = myWorkBook.getSheetAt(0);

        /** We now need something to iterate through the cells.**/
          Iterator rowIter = mySheet.rowIterator();

          while(rowIter.hasNext()){
        	  HSSFRow myRow = (HSSFRow) rowIter.next();
        	  Iterator cellIter = myRow.cellIterator();
        	  Vector cellStoreVector=new Vector();
        	  while(cellIter.hasNext()){
        		  HSSFCell myCell = (HSSFCell) cellIter.next();
        		  cellStoreVector.addElement(myCell);
        	  }
        	  cellVectorHolder.addElement(cellStoreVector);
          }
    	}catch (Exception e){e.printStackTrace(); }
    	return cellVectorHolder;
    }

	private static void printCellDataToConsole(Vector dataHolder) {

		for (int i=0;i<dataHolder.size();i++) {                   
                      Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
			for (int j=0; j< cellStoreVector.size();j++){
				HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
				String stringCellValue = myCell.toString();
				System.out.print(stringCellValue+"t");
			}
			System.out.println();
		}
	}
}