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

Calculating Folder Size In Java

The following piece of java code demonstrates how we can calculate the size of a given folder using Java Application. This uses a recursive technique. We start with a root directory, and start listing the files/folder. If a file is found, we calculate its size and sum of total size and also increase the total number of files counted so far, if it is a directory, we further do the recursion.

This program has been fully compiled and tested to find the folder size in Java.

/**
 * @author Kushal Paudyal
 * www.sanjaal.com/java
 * Last Modified On: 30th August 2008
 */
package com.kushal.utilities;

import java.io.File;
import java.text.DecimalFormat;
import java.text.NumberFormat;

public class TopLevelFolderSize {

	int totalFolderCount=0;
	int totalFileCount=0;

	public static void main(String args [])
	{
		/**
		 * Define your folder here. This is the folder whose size
		 * statistics you want to find out.
		 */
		String folder = &quot;C:/Users/kushal/Desktop/tempKushal&quot;;

		try{
			DecimalFormat fmt =new DecimalFormat(&quot;#.##&quot;);

			TopLevelFolderSize fg=new TopLevelFolderSize();

			/**Calculating the file size. By default size in long
			 * is returned.
			 */
			long fileSizeByte=fg.getFileSize(new File(folder));

			/**
			 * Formatting the long value to calculate size in
			 * different units KB, MB and GB
			 */
			double fileSizeKB=Double.valueOf(fmt.format(fileSizeByte /1024));
			double fileSizeMB=Double.valueOf(fmt.format(fileSizeByte /(1024*1024)));
			double fileSizeGB=Double.valueOf(fmt.format(fileSizeByte /(1024*1024*1024)));

			/**Printing the statistics**/
			System.out.println(&quot;nn##############--Folder Statistics--#################&quot;);
			System.out.println(&quot;Total Folder Size: [&quot;+fileSizeByte+&quot; Bytes] ntt[&quot;
						+fileSizeKB+&quot; KB] ntt[&quot;
						+fileSizeMB+&quot; MB] ntt[&quot;
						+fileSizeGB+&quot; GB]&quot;);
			System.out.println(&quot;Total Number of Folders: &quot;+fg.getTotalFolderCount());
			System.out.println(&quot;Total Number of Files: &quot;+fg.getTotalFileCount());
			System.out.println(&quot;##########--End Of Folder Statistics--##############&quot;);

		}catch (Exception e)
		{
			System.out.println(&quot;Exception Occurred: &quot;+e.getMessage());
		}
	}

	/**
	 * This is a recursive method.
	 * If file is found, total file size is calculated.
	 * If it is a folder, we recurse further.
	 */
	public long getFileSize(File folder) {
		totalFolderCount++; //Counting the total folders

		System.out.println(&quot;Processing &quot; + folder.getName());
		long foldersize = 0;

		File[] filelist = folder.listFiles();
		for (int i = 0; i &amp;lt; filelist.length; i++) {
			if (filelist[i].isDirectory()) {
				foldersize += getFileSize(filelist[i]);
			} else {
				totalFileCount++; //Counting the total files
				foldersize += filelist[i].length();
			}
		}

		return foldersize;
	}

	/**
	 * @return the totalFolderCount
	 */
	public int getTotalFolderCount() {
		return totalFolderCount;
	}

	/**
	 * @return the totalFileCount
	 */
	public int getTotalFileCount() {
		return totalFileCount;
	}

}


———————————————————————
SAMPLE OUTPUT:
———————————————————————
Processing tempKushal
Processing cute pictures
Processing image col
Processing Moline Tornado
Processing send
Processing Party At Prabhakars
Processing images

##############–Folder Statistics–#################
Total Folder Size: [151850658 Bytes]
[148291.0 KB]
[144.0 MB]
[0.0 GB]
Total Number of Folders: 7
Total Number of Files: 154
##########–End Of Folder Statistics–##############