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();
}
}
}