skip to main | skip to sidebar

Java Programs and Examples with Output

Pages

▼
 
  • RSS
  • Twitter
Saturday, October 13, 2012

Exporting the data from the Database using java

Posted by Raju Gupta at 3:00 AM – 0 comments
 
This Program is used to retrive the data from the Database .

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelFile {
 public static void main(String[] args) {   
  // TODO Auto-generated method stub   
  Connection con=null;   
  try {     
   String action,connString=null;   
   String start,end;   
   String smartcardquery=null;   
   ResultSet rssmartdata=null;   
   PreparedStatement pssmartdata=null;   
   Scanner sc=new Scanner(System.in);   
   System.out.print("Enter username"); // Enter the user name to connect the DB  
   String user=sc.next();   
   System.out.print("Enter password");  // Enter the password to connect the DB 
   String pwd= sc.next();   
   Class.forName("oracle.jdbc.OracleDriver");   
   System.out.println("Oracle JDBC driver loaded ok.");   
   connString = "jdbc:oracle:thin:" + user + "/" + pwd   
     + "@localhost:port no";   
   con = DriverManager.getConnection(connString);   
   System.out.println("Enter number of sheets to be created");   
   int numOfSheets=sc.nextInt();   
   String[] sheets = new String[numOfSheets];   
   HSSFWorkbook wb = new HSSFWorkbook();    
   for (int i=0; i<=sheets.length-1; i++)   
   {   
    System.out.println("1.ADD ITEM\n2.DELETE ITEM\n");   
    System.out.print("Enter the action to which report is needed ");   
    action=sc.next();   
    HSSFSheet sheet = wb.createSheet(action);              
    HSSFRow rowhead = sheet.createRow((short) 0);              
    rowhead.createCell((short) 0).setCellValue("ITEM NAME");                           
    rowhead.createCell((short) 1).setCellValue("QUANTITY)");                           
    rowhead.createCell((short) 2).setCellValue(" COST");                           
    smartcardquery = "select ITEMNAME,QUANTITY,COST from INVENTORY where action=?;    
      pssmartdata = con.prepareStatement(smartcardquery);   
    pssmartdata.setString(1, action);   
    rssmartdata=pssmartdata.executeQuery();    
    while (rssmartdata.next()) {                                   
     HSSFRow row = sheet.createRow((short) index);                                   
     row.createCell((short) 0).setCellValue(rssmartdata.getString(1));                                   
     row.createCell((short) 1).setCellValue(rssmartdata.getString(2));                                   
     row.createCell((short) 2).setCellValue(rssmartdata.getString(3));    
    }    
   }   
   FileOutputStream fileOut = new FileOutputStream("Report.xls");                           
   wb.write(fileOut);   
   fileOut.close();                           
   System.out.println("Data is saved in excel file.");                           
   rssmartdata.close();                           
   con.close();                   
  }    
  catch (Exception e) {   
   e.printStackTrace();   

  }   

 }
}


Labels: Excel Example , Java Database Example , JDBC

Leave a Reply

Newer Post Older Post
Subscribe to: Post Comments ( Atom )
  • Popular
  • Recent
  • Archives
Powered by Blogger.
 
 
 
© 2011 Java Programs and Examples with Output | Designs by Web2feel & Fab Themes

Bloggerized by DheTemplate.com - Main Blogger