Choose Your Language

Tuesday, 13 October 2015

create excel file and import data from database using java

/*
 * Aravind Sankaran Nair
 *
 */

package aravind.sankaran.excel.example;

import java.awt.Desktop;
import  java.io.*;
import  java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import  org.apache.poi.hssf.usermodel.HSSFSheet;
import  org.apache.poi.hssf.usermodel.HSSFWorkbook;
import  org.apache.poi.hssf.usermodel.HSSFRow;

public class CreateExcelFile{

public static void main(String[]args){

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
Calendar cal = Calendar.getInstance();
String time=dateFormat.format(cal.getTime());

String filename="D://folderName//data"+time+".xls";

try{
HSSFWorkbook hwb=new HSSFWorkbook();
HSSFSheet sheet =  hwb.createSheet("new sheet");
sheet.setDefaultColumnWidth(15);
sheet.setColumnWidth(6, 50);
HSSFRow rowhead=   sheet.createRow((short)0);
rowhead.createCell((short) 0).setCellValue("User ID");
rowhead.createCell((short) 1).setCellValue("User Name");
rowhead.createCell((short) 2).setCellValue("User Password");


Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseName", "databaseUserName", "databasePassword");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("Select * from usertable");
int i=1;
while(rs.next()){  
HSSFRow row=   sheet.createRow((short)i);
row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("userid")));
row.createCell((short) 1).setCellValue(rs.getString("username"));
row.createCell((short) 2).setCellValue(rs.getString("userpassword"));
i++;
}

FileOutputStream fileOut =  new FileOutputStream(filename);

hwb.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated!");

} catch ( Exception ex ) {
    System.out.println(ex);

}
    try {
           Desktop.getDesktop().open(new File(filename));        
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}


Jar File Needed
poi-3.9.jar

No comments:

Post a Comment