Apache POI - the Java API for Microsoft Documents

Generation of Excel sheets and reports for Web App

Getting Started: 

• Create eclipse Java project
• Download Binary distribution from http://poi.apache.org/download.html#POI-3.8
• Unzip the downloaded package
• Create a folder in eclipse project named "lib"
• Copy *.jar files  (poi-3.8-20120326.jar, poi-examples-3.8-20120326.jar, poi-excelant-3.8-20120326.jar, poi-ooxml-3.8-20120326.jar, poi-ooxml-schemas-3.8-20120326.jar, poi-scratchpad-3.8-20120326.jar)
• Right Click on project and Select Build Path
• Add all these Libraries there.

Create Empty Work Book:

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.hslf.model.Sheet;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.ss.usermodel.Workbook;

public class GenerateReport {

 /**
  * @param args
  * @throws IOException 
  */
 public static void main(String[] args) throws IOException {
  System.out.println("Generating report......");
   // Create a workbook object.
   Workbook workbook = new HSSFWorkbook();
   // Create a FileOutputStream by passing the excel file name.
   FileOutputStream outputStream = new FileOutputStream("Reports.xls");
   // Write the FileOutputStream to workbook object.
   workbook.write(outputStream);
   // Finally close the FileOutputStream.
   outputStream.close();
    
        System.out.println("Report Generated");
 }

}

Note: When you Open the File: You will get File error: data may have been lost

 You can Ignore this error or you can remove the line outputStream.Close();

Adding Sheets to WorkBook:

/**
 * 
 */

/**
 * @author tshah
 *
 */

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hslf.model.Sheet;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.ss.usermodel.Workbook;

public class GenerateReport {

    /**
     * @param args
     * @throws IOException 
     */
    public static void main(String[] args) throws IOException {
        System.out.println("Generating report......");
         // Create a workbook object.
         Workbook workbook = new HSSFWorkbook();
         // Create two sheet by calling createSheet of workbook.
         workbook.createSheet("Sheet one");
         workbook.createSheet("Sheet two");
         // Create a FileOutputStream by passing the excel file name.
         FileOutputStream outputStream = new FileOutputStream("Report.xls");
         // Write the FileOutputStream to workbook object.
         workbook.write(outputStream);
         // Finally close the FileOutputStream.
         outputStream.close();
    
        System.out.println("Report Generated");
    }

}

And the result is :

Adding Values to Column

import org.apache.poi.ss.usermodel.*;

            //In main()

Sheet sheet = workbook.createSheet("REPORT 1");
   
         Row row = sheet.createRow(0);
         // Create a cell and put a value in it.
         Cell cell = row.createCell(0);
         cell.setCellValue("Value 1");
         // Or You can put in one line like this.
         row.createCell(1).setCellValue("Value 2");
         row.createCell(2).setCellValue("Value 3");

 // Create a FileOutputStream by passing the excel file name.
   FileOutputStream outputStream = new FileOutputStream("Report.xls");
   // Write the FileOutputStream to workbook object.
   workbook.write(outputStream);
   // Finally close the FileOutputStream.
   outputStream.close();

And the result is