How to generating Excel file in Java ?

The ApacheSW Jakarta POI project, located at http://jakarta.apache.org/poi/ is a JavaSW library that allow you to manipulate Microsoft document formats. Within the POI project, POI-HSSF allows you to read, modify, and write Excel documents. The HSSF Quick Guide at http://jakarta.apache.org/poi/hssf/quick-guide.html is a great resouce for quickly getting up to speed with POI-HSSF.
Let's create a small Java class that writes some data to an Excel (xls) file. You can download the POI jar file from the Jakarta POI website and add it to your project, as illustrated below.

Please see the following class which generate a sample excel  :-

WriteExcelFile.java

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.hssf.util.HSSFColor;

/**
 *
 * @author Prasobh.K
 */
public class WriteExcelFile {
   public static void main(String[] args) throws IOException {
        try {
            FileOutputStream fileOut = new FileOutputStream("sample.xls");
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("My Worksheet");

            // index from 0,0... cell A1 is cell(0,0)
            HSSFRow row1 = worksheet.createRow(0);

            HSSFCell cellA1 = row1.createCell( 0);
            cellA1.setCellValue("Hello");
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellA1.setCellStyle(cellStyle);

            HSSFCell cellB1 = row1.createCell( 1);
            cellB1.setCellValue("Goodbye");
            cellStyle = workbook.createCellStyle();
            cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellB1.setCellStyle(cellStyle);

            HSSFCell cellC1 = row1.createCell( 2);
            cellC1.setCellValue(true);

            HSSFCell cellD1 = row1.createCell( 3);
            cellD1.setCellValue(new Date());
            cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(HSSFDataFormat
                    .getBuiltinFormat("m/d/yy h:mm"));
            cellD1.setCellStyle(cellStyle);

            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

Output

Reading Excel Files

Please see following example, which reads the sample.xls we already created.


ReadExcelFile.java

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;
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;

/**
 *
 * @author Prasobh.K
 */
public class   ReadExcelFile {
   public static void main(String[] args) throws IOException {
        try {
            FileInputStream fileInputStream = new FileInputStream("sample.xls");
            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
            HSSFSheet worksheet = workbook.getSheet("My Worksheet");
            HSSFRow row1 = worksheet.getRow(0);
            HSSFCell cellA1 = row1.getCell(0);
            String a1Val = cellA1.getStringCellValue();
            HSSFCell cellB1 = row1.getCell(1);
            String b1Val = cellB1.getStringCellValue();
            HSSFCell cellC1 = row1.getCell(2);
            boolean c1Val = cellC1.getBooleanCellValue();
            HSSFCell cellD1 = row1.getCell(3);
            Date d1Val = cellD1.getDateCellValue();

            System.out.println("A1: " + a1Val);
            System.out.println("B1: " + b1Val);
            System.out.println("C1: " + c1Val);
            System.out.println("D1: " + d1Val);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

Output :

A1: Hello
B1: Goodbye
C1: true
D1: Wed Aug 17 14:58:31 IST 2011


Jar file used:

poi-3.6-20091214.jar