Java Export Mysql Data To Excel

Export Mysql Data to Excel using java , we required Apache POI -
the Java API for Microsoft Documents. You can download it from poi.apache.org.
in this article I try to give an example of how to use it.
After download Poi ,Extract that File to your directory. You can find poi-3.7-20101029.jar
or highest version.

Next, add poi-3.7-20101029.jar  to JDK instalation directory. 
Example :C:\Program Files\Java\jdk1.6.0_37\jre\lib\ext
If using Netbeans or Eclipse not need add poi to JDK directory , enough add
poi-3.7-20101029.jar to lib folder in your project. dont forget to add
mysql-connector-java for connection Java and Mysql

This is code example using Eclipse

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;


public class ExportData {

    /**
     * @param args
     */
    public static void main(String[] args) {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
            Statement statement = con.createStatement();
            FileOutputStream fileOut;
            fileOut = new FileOutputStream("file.xls");
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("Sheet 0");
            Row row1 = worksheet.createRow((short)0);
            row1.createCell(0).setCellValue("Name");
            row1.createCell(1).setCellValue("Address");
            Row row2 ;
            ResultSet rs = statement.executeQuery("SELECT Name,Address FROM tablename");
            while(rs.next()){
                int a = rs.getRow();
                row2 = worksheet.createRow((short)a);
                row2.createCell(0).setCellValue(rs.getString(1));
                row2.createCell(1).setCellValue(rs.getString(2));
            }
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
            rs.close();
            statement.close();
            con.close();
            System.out.println("Export Success");
        }catch(ClassNotFoundException e){
            System.out.println(e);
        }catch(SQLException ex){
            System.out.println(ex);
        }catch(IOException ioe){
            System.out.println(ioe);
        }

    }

}


javareveal.blogspot.com