Read-Write Excel file in Java

Hi All,

Lets see you can read/write the data from/to Excel worksheet(.xls file) .  Its very useful when you provide the option to export and import the data in you web page.

package myExcel;
import java.io.File;
import java.io.FileInputStream;

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

public class ReadExcel {
  public static void main(String[] args) {
    try
    {
      File f = new File("C:\\Users\\dsharma\\Desktop\\Excel.xls");
      FileInputStream fin = new FileInputStream(f);
      HSSFWorkbook workbook = new HSSFWorkbook(fin);
      HSSFSheet tempSheet;
      HSSFRow tempRow;
      HSSFCell tempCell;
      for (int i = 0; i < workbook.getNumberOfSheets() ; i++) {
        tempSheet = workbook.getSheetAt(i);
        System.out.println("Sheet - \""+tempSheet.getSheetName()+"\"");
        /*
         *  remember getLastRowNum() returns the 0 based index
         *  so we have to use ( "<=" )
         *  
         *  in for loop  [ j <=  tempSheet.getLastRowNum() ]
         */                 
        for (int j = 0; j <= tempSheet.getLastRowNum() ; j++) {
          tempRow = tempSheet.getRow(j);      
          if(tempRow!=null)
          {
            for (int j2 = 0; j2 <tempRow.getLastCellNum(); j2++) {
              tempCell = tempRow.getCell(j2);
              System.out.printf("%-15s|",tempCell);
            }
          }          
          System.out.println("");
        }
        System.out.println("\n\n");
      }
    }
    catch(Exception ex)
    {
      ex.printStackTrace();
    }    
  }
}

This will read you excel file specified at line number 15. and will print the data in all the sheets inside your excel workbook.

And Lets see how can you create and write excel file(.xls) from your Java Code.

package myExcel;

import java.io.File;
import java.io.FileOutputStream;

import  org.apache.poi.hssf.usermodel.HSSFSheet;  
import  org.apache.poi.hssf.usermodel.HSSFWorkbook; 
import  org.apache.poi.hssf.usermodel.HSSFRow;

public class WriteExcel {
  public static void main(String[] args) {
    try{          
      HSSFWorkbook workbook = new HSSFWorkbook();      
      HSSFSheet tempSheet = workbook.createSheet("Created Sheet");
      
      //Create first row reference from the sheet "tempSheet"
      HSSFRow tempRow = tempSheet.createRow(0);
      
      //add the 3 column and value in first row
      tempRow.createCell(0).setCellValue("Roll No.");
      tempRow.createCell(1).setCellValue("Name");
      tempRow.createCell(2).setCellValue("Passed?");
      
      //adding 5 students details
      for (int i = 0; i < 5; i++) {
        //create second row. as first is header
        tempRow = tempSheet.createRow(i+1);
        
        //add three columns value
        tempRow.createCell(0).setCellValue(i+1);
        tempRow.createCell(1).setCellValue("Student "+(i+1));
        tempRow.createCell(2).setCellValue(i%2==1);
      }
        
      //outputstream object of the file to be created
      File f = new File("C:\\Users\\dsharma\\Desktop\\New_Excel.xls");    
      FileOutputStream fout = new FileOutputStream(f);
      workbook.write(fout);
    }
    catch(Exception ex){
      ex.printStackTrace();
    }
  }
}

You can see for both read-write we need few new reference, yes we need a library file. Called "Apache POI library",  don't worry you can download the that jar file from the below links.
Download POI

So this is the basic to read and write the Excel file in Java.  Now if you find its typical to implement it in you JSP-Servlet webproject?? get the below sample project(Eclipse Project.  jdk1.7  used).  Don't worry for the library for JSP project, I have included all the jar file inside the project.

Download Eclipse Project(.ZIP)

Happy Coding and Sharing.. :)