Java Video to FLV (Flash Video) converter using FFmpeg

This post should have been written one or two years ago, with the Exit of YouTube and the big promise of the video content on the internet. But, as with every big buzz, the world has already taken it's next step towards smart phones and social networks.
YouTube and all the other video web sites allow uploading a video file. This video file can be shown on the web using a Flash based video player. The Flash video player can play FLV files. FLV file stands for: Flash Video File, which is a video file quite similar to WMV, MPG, AVI etc'.
So, as you can understand, the basic step towards building your own video site, is knowing to convert uploaded video files from all kinds to FLV. Doing the video conversion is a very complex job, requiring a great knowledge. Luckily for us, there is a great open source project named: FFmpeg, which, among the rest, allows us to convert almost any video from any type to any type. FFmpeg is a cross platform project. Whether you have Linux servers or Windows server (or any other popular operating system), FFmpeg will be available for you.
First, in order to do video conversion, we will have to download FFmpeg. If you are using Windows, you can find on the web compiled version ready for use. I found mine here.
FFmpeg is command line application. It has many options and parameters. In general, in order to convert a video file from any type to FLV we have to run FFmpeg with the following parameters:

ffmpeg -i "C:\filein.mp4" -ar 44100 -s 320x200 -qscale 5 "C:\fileout.flv"

This command takes a file named “filein.mp4” and converts it to FLV video file named: “fileout.flv”. The out video file dimensions is: 320x200. The parameter “qscale” defines the quality of the resulted video. Lower values give better quality. This parameter is not mandatory. Of course that the higher the quality of the video the higher the weight of the resulted file.

We will wrap the FFmpeg command line tool with Java code. We do it by simply executing the above command from Java. This will enable us to convert video to FLV file. These FLV files could be later played by some Flash Video Player (for example , to show the video on the web). A great free Flash based video player is: JW FLV Media Player. It supports many modern features required from a flash player. Some of them are:

• Full events support allowing to control the video and get notifications from JavaScrip.
• It allows using plugins.
• It supports playing a video from any point (just like YouTube).
• It can play videos directly from YouTube.

These are only some of the features this player has.

Let have a look at out Java Video to FLV converter. It’s code is very simple and straight forward:

package com.bashan.blog.video;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
public class FLVConverter {
    private String ffmpegApp;
    public FLVConverter(String ffmpegApp) {
        this.ffmpegApp = ffmpegApp;
    }
    public void convert(String filenameIn, String filenameOut, int width, int height) throws IOException, InterruptedException {
        convert(filenameIn, filenameOut, width, height, -1);
    }
    public int convert(String filenameIn, String filenameOut, int width, int height, int quality)
            throws IOException, InterruptedException {
        ProcessBuilder processBuilder;
        if (quality > -1) {
            processBuilder = new ProcessBuilder(ffmpegApp, "-i", filenameIn, "-ar", "44100",
                    "-s", width + "*" + height, "-qscale", quality + "", filenameOut);
        } else {
            processBuilder = new ProcessBuilder(ffmpegApp, "-i", filenameIn, "-ar", "44100",
                    "-s", width + "*" + height, filenameOut);
        }
        Process process = processBuilder.start();
        InputStream stderr = process.getErrorStream();
        InputStreamReader isr = new InputStreamReader(stderr);
        BufferedReader br = new BufferedReader(isr);
        String line;
        while ((line = br.readLine()) != null) ;
        {
        }
        return process.waitFor();
    }
}

And here is a small test program that does exactly what the above command line example is doing:

    public static void main(String[] args) throws Exception {
        FLVConverter FLVConverter = new FLVConverter("C:\\Users\\merdok\\IdeaProjects\\dev\\tools\\ffmpeg\\ffmpeg.exe");
        FLVConverter.convert("C:\\filein.mp4", "C:\\fileout.flv", 320, 200, 5);
    }

You can also download the converter here.

Generating random passwords with Java

There are times we we need to generate random passwords. For example, we want to send a user a new temporary password instead of an old one, or we just want to generate a strong password for the user, since users usually tend to choose weak password.
We will construct a random password generator written in Java. We will make it a bit smarter than simply producing a sequence of characters and numbers on a given size. The password generator, will be able to produce sequence of different sets of characters from different sizes and combine them together to a random password. For example, suppose we would like to generate 6 characters password combined from the following characters:

• 2 small letters.
• 2 capital letters.
• 2 numbers

An example for such password is: gH1I9s.

our password generator is constructed from 2 classes

• Main class: PasswordGenerator which responsible for generator random passwords according to desired logic.
• Static inner class: PasswordLogic, which defines a set of characters and number of characters to choose from.

The PasswordLogic class is used as an input for the PasswordGenerator class, giving a description of the nature of the password we want to construct. The nature of the password is a set of characters from which we would like to construct the password and the number of characters we would like to use for constructing the password. The PasswordGenerator accepts array of PasswordLogic instances, and use these instances to construct a sequence of characters. We can sum the actions of the PasswordGenerator in these 2 simple steps:

• First we scan each PasswordLogic instance, and produce from its characters array, a random characters set on the desired size.
• Then we order all the characters we produced on a random sequence and combine them to a single string.

Of course, this is a very simple password generator. I find it good enough for most cases. A more complex and sophisticated password generator can be built on this basic one.

Let’s look at the password generator code:

package com.bashan.blog.password;
import java.util.*;
public class PasswordGenerator {
  private Random random = new Random();
  public static final char[] SMALL_LETTERS = {'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n',
      'o', 'p', 'q', 'r', 's', 'u', 'v', 'w', 'x', 'y', 'z'};
  public static final char[] CAPITAL_LETTERS = {
      'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V',
      'W', 'X', 'Y', 'Z'};
  public static final char[] NUMBERS = {'0', '1', '2', '3', '4', '5', '6', '7', '8', '9'};
  private Set<Character> generateChars(char[] charArray, int size) {
    Set<Character> chars = new HashSet<Character>();
    int arrSize = charArray.length;
    for (int i = 0; i < size;) {
      char ch = charArray[random.nextInt(arrSize)];
      if (!chars.contains(ch)) {
        i++;
        chars.add(ch);
      }
    }
    return chars;
  }
  public String generate(PasswordLogic[] passwordLogics) {
    // Generate random characters
    List<Character> chars = new ArrayList<Character>();
    for (PasswordLogic passwordLogic : passwordLogics) {
      chars.addAll(generateChars(passwordLogic.chars, passwordLogic.numChars));
    }
    // Generate random sequence
    StringBuffer sb = new StringBuffer();
    int size = chars.size();
    Set<Integer> sequence = new HashSet<Integer>();
    for (int i = 0; i < size;) {
      int pos = random.nextInt(size);
      if (!sequence.contains(pos)) {
        i++;
        sb.append(chars.get(pos));
        sequence.add(pos);
      }
    }
    return sb.toString();
  }
  public static class PasswordLogic {
    private char[] chars;
    private int numChars;
    public PasswordLogic(char[] chars, int numChars) {
      this.numChars = numChars;
      this.chars = chars;
    }
    public char[] getChars() {
      return chars;
    }
    public void setChars(char[] chars) {
      this.chars = chars;
    }
    public int getNumChars() {
      return numChars;
    }
    public void setNumChars(int numChars) {
      this.numChars = numChars;
    }
  }
}

As you can see, the PasswordGenerator class defines 3 main sets of characters that can be used out-of-the box:

• Upper case characters.
• Small case characters.
• Number characters.

Of course, you can defined your own sets of characters from which you would like to construct passwords.

Here is a small test program showing how the PasswordGenerator can be used in order to construct 2 passwords:

• First password combined from 2 upper case characters, 2 small case characters and 2 numbers:


• Second password combined from 3 numbers, 3 small case letters and 3 characters from the following: ~,!,@,#,$,%,^,&,*,(,),_,+

  public static void main(String[] args) {
    PasswordGenerator passwordGenerator = new PasswordGenerator();
    System.out.println(passwordGenerator.generate(new PasswordLogic[]{
        new PasswordLogic(CAPITAL_LETTERS, 2), new PasswordLogic(SMALL_LETTERS, 2),
        new PasswordLogic(NUMBERS, 2)}));
    System.out.println(passwordGenerator.generate(new PasswordLogic[]{
        new PasswordLogic(NUMBERS, 3), new PasswordLogic(SMALL_LETTERS, 3),
        new PasswordLogic(new char[] { '~','!','@','#','$','%','^','&','*','(',')','_','+' }, 3)}));
  }

And sample output for this test program:

6x5YmK
e^+819#an

Note, that if you don't like looking at so much characters in your eyes, you can simply rewrite this code to work with Strings instead.

Extract image Exif information using Java and ExifTool

Exif is: Exchangeable Image File Format. It is a collection of meta information stored in image files taken by cameras. Sometimes we would like to read and present this information. The amount of Exif information stored today by cameras is quite impressive. It even may include the location where the picture was taken. Among all the Exif information stored in the picture, there is a group of parameters that really interests photographers. Parameters like the focal length in which the picture was taken, the Exposure mode, F-Number, Did the Flash fired when the picture was taken? and so on.

Extracting Exif information is a hard task, since there is very big amount of Camera manufactures and image types and there is no unified standard. Luckily for us, there is a great tool written Perl named: ExifTool (written by Harvey). This tool is available as Windows executable file (can be downloaded from the site). If you are using linux Debian (I develop in Windows, but my server runs under Linux) there is a ready package named: libimage-exiftool-perl

You can install it in Debian like this:

apt-get install libimage-exiftool-perl

and use the command like: exiftool in order to run it.

We will write a Java code that will use ExifTool in order to extract important Exif information from images.

The code is constructed from 2 classes:

• ExifToolExtractor – This class extracts Exif information from an image using ExifTool and produces an ExifInfo class filled with Exif important information.
• ExifInfo – This class stores image Exif information.

ExifToolExtractor class is very simple. it uses Process class in order to execute the ExifTool. Then it reads the ExifTool output and parses it. The output is a long list if Exif information, ordered in a key-value manner. For example, this is a partial output of ExifTool:

Make                            : NIKON CORPORATION
Camera Model Name               : NIKON D300
Orientation                     : Horizontal (normal)
X Resolution                    : 300
Y Resolution                    : 300
Resolution Unit                 : inches
Software                        : Ver.1.10
Modify Date                     : 2009:04:18 10:15:35
Artist                          :
Y Cb Cr Positioning             : Co-sited
Copyright                       :
Exposure Time                   : 1/1000
F Number                        : 7.1
Exposure Program                : Aperture-priority AE
ISO                             : 200
Exif Version                    : 0221
Date/Time Original              : 2009:04:18 10:15:35
Create Date                     : 2009:04:18 10:15:35
Components Configuration        : Y, Cb, Cr, -
Compressed Bits Per Pixel       : 2
Exposure Compensation           : -1/3
Max Aperture Value              : 4.8
Metering Mode                   : Multi-segment
Light Source                    : Unknown
Flash                           : No Flash
Focal Length                    : 65.0 mm
Maker Note Version              : 2.10
Quality                         : Normal
White Balance                   : Auto
Focus Mode                      : AF-S
Flash Setting                   : Normal
Flash Type                      :
White Balance Fine Tune         : 0 0
WB RB Levels                    : 1.4765625 1.3359375 1 1
Program Shift                   : 0
Exposure Difference             : 0
Compression                     : JPEG (old-style)
Preview Image Start             : 9128

Note, that this is a partial list. The complete list is much longer and changed from camera to camera. The class ExifToolExtractor doesn’t really care what data is outputted. it simply stores all the data to a HashMap. The HashMap doesn’t actually belongs to the class ExifToolExtractor but to the class ExifInfo which is responsible for storing all Exif information. This is how the class ExifToolExtractor looks like:

package com.bashan.blog.exif;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
public class ExifToolExtractor {
  private String exifToolApp;
  public ExifToolExtractor(String exifToolApp) {
    this.exifToolApp = exifToolApp;
  }
  public ExifInfo getExifInfo(String image) throws IOException, InterruptedException {
    ProcessBuilder processBuilder = new ProcessBuilder(exifToolApp, image);
    Process process = processBuilder.start();
    BufferedReader stdInput = new BufferedReader(new
        InputStreamReader(process.getInputStream()));
    BufferedReader stdError = new BufferedReader(new
        InputStreamReader(process.getErrorStream()));
    String line;
    ExifInfo exifInfo = new ExifInfo();
    while ((line = stdInput.readLine()) != null) {
      exifInfo.parseLine(line);
      System.out.println(line);
    }
    while ((line = stdError.readLine()) != null) {
    }
    process.waitFor();
    return exifInfo;
  }
}

As said before, The class ExifInfo stores a map of all Exif information. But, actually, from all the big amount of information, there are several parameters that are a real interest for Photographers. The class ExifInfo has ready methods for getting these specific information. Take a look at the method parseLine of this class. This is the method that is responsible for taking a line of information from ExifTool and convert it to key-value pair. Here is how the class ExifInfo looks like:

package com.bashan.blog.exif;
import java.util.HashMap;
import java.util.Map;
public class ExifInfo {
  private Map<String, String> exifMap = new HashMap<String, String>();
  public static final String COMPANY = "Make";
  public static final String MODEL = "Camera Model Name";
  public static final String EXPOSURE_TIME = "Exposure Time";
  public static final String F_NUMBER = "F Number";
  public static final String ISO = "ISO";
  public static final String EXPOSURE_COMPENSATION = "Exposure Compensation";
  public static final String FOCAL_LENGTH = "Focal Length";
  public static final String EXPOSURE_PROGRAM = "Exposure Program";
  public static final String DATE_TAKEN = "Create Date";
  public static final String METERING_MODE = "Metering Mode";
  public static final String FLASH = "Flash";
  public static final String FLASH_EXPOSURE_COMPENSATION = "Flash Exposure Compensation";
  public static final String LENS = "Lens ID";
  public void parseLine(String line) {
    int pos = line.indexOf(":");
    if (pos != -1) {
      exifMap.put(line.substring(0, pos).trim(), line.substring(pos + 1).trim());
    }
  }
  public String getCompany() {
    return exifMap.get(COMPANY);
  }
  public String getModel() {
    return exifMap.get(MODEL);
  }
  public String getExposureTime() {
    return exifMap.get(EXPOSURE_TIME);
  }
  public String getFNumber() {
    return exifMap.get(F_NUMBER);
  }
  public String getISO() {
    return exifMap.get(ISO);
  }
  public String getExposureCompensation() {
    return exifMap.get(EXPOSURE_COMPENSATION);
  }
  public String getFocalLength() {
    return exifMap.get(FOCAL_LENGTH);
  }
  public String getExposureProgram() {
    return exifMap.get(EXPOSURE_PROGRAM);
  }
  public String getDateTaken() {
    return exifMap.get(DATE_TAKEN);
  }
  public String getMeteringMode() {
    return exifMap.get(METERING_MODE);
  }
  public String getFlash() {
    return exifMap.get(FLASH);
  }
  public String getFlashExposureCompensation() {
    return exifMap.get(FLASH_EXPOSURE_COMPENSATION);
  }
  public String getLens()
  {
    return exifMap.get(LENS);
  }
  public String toString() {
    return "Company: " + getCompany() + "\n" +
        "Model: " + getModel() + "\n" +
        "Exposure Time: " + getExposureTime() + "\n" +
        "Date Taken: " + getDateTaken() + "\n" +
        "F-Number: " + getFNumber() + "\n" +
        "ISO: " + getISO() + "\n" +
        "Exposure Compensation: " + getExposureCompensation() + "\n" +
        "Metering Mode: " + getMeteringMode() + "\n" +
        "Focal Length: " + getFocalLength() + "\n" +
        "Exposure Program: " + getExposureProgram() + "\n" +
        "Flash: " + getExposureProgram() + "\n" +
        "Flash Exposure Compensation: " + getExposureProgram() + "\n" +
        "Lens: " + getLens();
  }
}

Notice, that ExifTool if quite a comprehensive Exif extraction tool and also supplies information of the Lens used to shoot the picture, which is a nice piece of information for photographers.

This is a small program showing how these 2 classes are working together:

  public static void main(String[] args) throws Exception {
    ExifToolExtractor exifToolExtractor = new ExifToolExtractor("C:\\exiftool(-k).exe");
    ExifInfo exifInfo = exifToolExtractor.getExifInfo("C:\\DSC_0001.JPG");
    System.out.println(exifInfo);
  }

An here is an example of the information extracted from a picture:

Company: NIKON CORPORATION
Model: NIKON D300
Exposure Time: 1/1000
Date Taken: 2009:04:18 10:15:35.68
F-Number: 7.1
ISO: 200
Exposure Compensation: -1/3
Metering Mode: Multi-segment
Focal Length: 65.0 mm (35 mm equivalent: 97.0 mm)
Exposure Program: Aperture-priority AE
Flash: Aperture-priority AE
Flash Exposure Compensation: Aperture-priority AE
Lens: Tamron AF 28-300mm f/3.5-6.3 XR Di VC LD Aspherical [IF] MACRO

Converting ResultSet to Excel table in Java using Apache POI

You can make a great reporting system with impressive UI, but there will always be someone that will ask you: Can you export to excel?

Excel offers many ways of easily manipulating and viewing data out of the box. Therefore, no matters how your reporting system is sophisticated, exporting the result to Excel is quite a basic feature.

Using Apache POI Project to produce Excel output is an easy task. This great project offers a wide support for generating Microsoft Format Files. We will use this open source project to dynamically generate Excel file from a ResultSet.

We will construct a class named: ResultSetToExcel. It’s constructor will get: ResultSet and Excel sheet name. A function named: generate will produce Excel sheet to an OutputStream or to a file. The content of the generated Excel sheet will be a table with headers and ResultSet data.

Building the ResultSetToExcel class introduces 2 main issues:

• From where do we get the titles for the report?
• How do we format Excel cells?

This is how we will deal with these 2 issues:

• Report titles will be taken from query columns by using the ResultSetMetaData class. For example, let’s have a look at this query:

  select first_name 'First Name', last_name 'Last Name', age 'Age' from person

  The names that will be used for our report are: First Name, Last Name and Age. If no specific name is given to a column, then the column name itself will be used.
• We will use 2 ways for formatting Excel columns:
• Explicit: In the class constructor, In addition to the ResultSet, we supply a parameter named: formatTypes. This parameter is an array of the static inner enum class named: FormatType. It contains all commonly used format types:
  
  
  • Text
  • Integer
  • Float
  • Date
  • Money
  Of course, the items in this array should correspond one-to-one to the columns in our ResultSet. We use this array to format Excel cells according to their nature. This makes the resulted Excel file more neat and professional. Note that these are the most commonly and basic types used. You can add more types quite easily.
• Automatic: If no formatTypes parameter supplied, we try to “guess” the type of the column, by examining the Java class used for every specific column. We do it by using the private method: getFormatType. Pay attention to 2 important things:
• The getFormatType method is pretty basic, it doesn’t handle all possible java classes, but you can easily refine it.
• The “automatic guessing” way is less preferable, because it is not able to give us more fine grained control over the way our cell is formatted. For example: we cannot format a cell as Money or Percentage.

2 last things before moving to code is:

• The method: writeCell. This method is used to write an excel cell in a formatted way. It formats the cell according to the values in the parameter: formatType. Note, that in order to format the cell the class: HSSFCellUtil is used. This class is not at the core of the POI project, but in additional JAR file named: POI Contrib. This class allows formatting Excel cells in a way that existing formats are automatically reused. It prevents from Excel to generate a new format for each cell causing an Excel formatting error in cases of big tables.
• After writing ResultSet cells we use the method: autoSizeColumn of the class: HSSFSheet. This method automatically sets the size of each column according to the longest (in that column). This makes the resulted Excel file ready to be viewed.
  

Well, finally, let’s have a look at the code of our ResultSetToExcel class:

package com.bashan.blog.excel;
import org.apache.commons.lang.exception.NestableException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Timestamp;
public class ResultSetToExcel {
  private HSSFWorkbook workbook;
  private HSSFSheet sheet;
  private HSSFFont boldFont;
  private HSSFDataFormat format;
  private ResultSet resultSet;
  private FormatType[] formatTypes;
  public ResultSetToExcel(ResultSet resultSet, FormatType[] formatTypes, String sheetName) {
    workbook = new HSSFWorkbook();
    this.resultSet = resultSet;
    sheet = workbook.createSheet(sheetName);
    boldFont = workbook.createFont();
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    format = workbook.createDataFormat();
    this.formatTypes = formatTypes;
  }
  public ResultSetToExcel(ResultSet resultSet, String sheetName) {
    this(resultSet, null, sheetName);
  }
  private FormatType getFormatType(Class _class) {
    if (_class == Integer.class || _class == Long.class) {
      return FormatType.INTEGER;
    } else if (_class == Float.class || _class == Double.class) {
      return FormatType.FLOAT;
    } else if (_class == Timestamp.class || _class == java.sql.Date.class) {
      return FormatType.DATE;
    } else {
      return FormatType.TEXT;
    }
  }
  public void generate(OutputStream outputStream) throws Exception {
    try {
      ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
      if (formatTypes != null && formatTypes.length != resultSetMetaData.getColumnCount()) {
        throw new IllegalStateException("Number of types is not identical to number of resultset columns. " +
            "Number of types: " + formatTypes.length + ". Number of columns: " + resultSetMetaData.getColumnCount());
      }
      int currentRow = 0;
      HSSFRow row = sheet.createRow(currentRow);
      int numCols = resultSetMetaData.getColumnCount();
      boolean isAutoDecideFormatTypes;
      if (isAutoDecideFormatTypes = (formatTypes == null)) {
        formatTypes = new FormatType[numCols];
      }
      for (int i = 0; i < numCols; i++) {
        String title = resultSetMetaData.getColumnName(i + 1);
        writeCell(row, i, title, FormatType.TEXT, boldFont);
        if (isAutoDecideFormatTypes) {
          Class _class = Class.forName(resultSetMetaData.getColumnClassName(i + 1));
          formatTypes[i] = getFormatType(_class);
        }
      }
      currentRow++;
      // Write report rows
      while (resultSet.next()) {
        row = sheet.createRow(currentRow++);
        for (int i = 0; i < numCols; i++) {
          Object value = resultSet.getObject(i + 1);
          writeCell(row, i, value, formatTypes[i]);
        }
      }
      // Autosize columns
      for (int i = 0; i < numCols; i++) {
        sheet.autoSizeColumn((short) i);
      }
      workbook.write(outputStream);
    }
    finally {
      outputStream.close();
    }
  }
  public void generate(File file) throws Exception {
    generate(new FileOutputStream(file));
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType) throws NestableException {
    writeCell(row, col, value, formatType, null, null);
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType, HSSFFont font) throws NestableException {
    writeCell(row, col, value, formatType, null, font);
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType,
                         Short bgColor, HSSFFont font) throws NestableException {
    HSSFCell cell = HSSFCellUtil.createCell(row, col, null);
    if (value == null) {
      return;
    }
    if (font != null) {
      HSSFCellStyle style = workbook.createCellStyle();
      style.setFont(font);
      cell.setCellStyle(style);
    }
    switch (formatType) {
      case TEXT:
        cell.setCellValue(value.toString());
        break;
      case INTEGER:
        cell.setCellValue(((Number) value).intValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("#,##0")));
        break;
      case FLOAT:
        cell.setCellValue(((Number) value).doubleValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("#,##0.00")));
        break;
      case DATE:
        cell.setCellValue((Timestamp) value);
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("m/d/yy")));
        break;
      case MONEY:
        cell.setCellValue(((Number) value).intValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook,
            HSSFCellUtil.DATA_FORMAT, format.getFormat("($#,##0.00);($#,##0.00)"));
        break;
      case PERCENTAGE:
        cell.setCellValue(((Number) value).doubleValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook,
            HSSFCellUtil.DATA_FORMAT, HSSFDataFormat.getBuiltinFormat("0.00%"));
    }
    if (bgColor != null) {
      HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.FILL_FOREGROUND_COLOR, bgColor);
      HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.FILL_PATTERN, HSSFCellStyle.SOLID_FOREGROUND);
    }
  }
  public enum FormatType {
    TEXT,
    INTEGER,
    FLOAT,
    DATE,
    MONEY,
    PERCENTAGE
  }
}

This class can be downloaded here.

The JARs for this class can be downloaded from: Apache POI Project. Both POI and POI Contrib Jars are needed.

This is a small example of how this class can be used:

  public void createExecl(Connection conn) throws Exception
  {
    PreparedStatement stmt = conn.prepareStatement("select first_name 'First Name', last_name 'Last Name', age 'Age', " +
        "salary 'Salary'");
    ResultSet resultSet = stmt.executeQuery();
    ResultSetToExcel resultSetToExcel = new ResultSetToExcel(resultSet,
        new ResultSetToExcel.FormatType[] { ResultSetToExcel.FormatType.TEXT, ResultSetToExcel.FormatType.TEXT,
            ResultSetToExcel.FormatType.INTEGER, ResultSetToExcel.FormatType.MONEY }, "Employee List");
    resultSetToExcel.generate(new File("c:\\employees.xls"));
  }