asked on

java progam which prepares simple excel output file from hash map containing username and password data

Hi,

I am looking for a sample java progam which prepares simple excel output file from hash map containing username and password data.
Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance

kristof1104

You can use Apache POI to create the Excel files.
All you need to do then is loop through the Hashmap and fill in the cells.

http://poi.apache.org/

regards Kristof

gudii9

ASKER

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class POIRIRaw4EEb {
public static void main( String [] args ) {
String fileName="C:\\File.xls";
Vector dataHolder=read(fileName);
checkDatabase(dataHolder);
}
public static Vector read(String fileName) {
Vector cellVectorHolder = new Vector();
try{
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();
while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
}catch (Exception e){e.printStackTrace(); }
return cellVectorHolder;
}
private static void checkDatabase(Vector dataHolder) {
String username="";
String password="";
List missingList = new ArrayList();

for (int i=1;i<dataHolder.size(); i++){
Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(0);
String stringCellValue = myCell.toString();
username = stringCellValue;

myCell = (HSSFCell)cellStoreVector.elementAt(1);
stringCellValue = myCell.toString();
password=stringCellValue;

try{

/* PreparedStatement ps = con.prepareStatement("select null from login where username=? and password=?");
//(in the loop)
ps.setString(1, username);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
// Found
}*/

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
// Statement stmt = con.createStatement();
// ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
//PreparedStatement psmt=null;
PreparedStatement psmt = con.prepareStatement("select null from login where username=? and password=?");
psmt.setString(1, username);
psmt.setString(2, password);
ResultSet rs1 = psmt.executeQuery();
System.out.println("rs1-->"+rs1);
boolean isExist = false;
if (rs1!=null) {
while (rs1.next()){
isExist = true;
String rec1 = rs1.getString(1);
String rec2 = rs1.getString(2);
System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
}
}

if (!isExist) {
Map mapDetail = new HashMap();
mapDetail.put("username",username);
mapDetail.put("password",password);
missingList.add(mapDetail);
}

// pstmt.close();
con.close();
}
catch(Exception e){

System.out.println("e-->"+e);
}
}

System.out.println("Missing List : "+missingList);
}
}

As in above program i am getting username and password details into

Map mapDetail = new HashMap();
mapDetail.put("username",username);
mapDetail.put("password",password);

How do I loop Hashmap and fill cells using POI. Any sample example, sample code to do it. Please advise

ASKER CERTIFIED SOLUTION

kristof1104

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gudii9

ASKER

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class POIRIRaw4EEb {
public static void main( String [] args ) {
String fileName="C:\\File.xls";
Vector dataHolder=read(fileName);
checkDatabase(dataHolder);
}
public static Vector read(String fileName) {
Vector cellVectorHolder = new Vector();
try{
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();
while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
}catch (Exception e){e.printStackTrace(); }
return cellVectorHolder;
}
private static void checkDatabase(Vector dataHolder) {
String username="";
String password="";
List missingList = new ArrayList();

for (int i=1;i<dataHolder.size(); i++){
Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(0);
String stringCellValue = myCell.toString();
username = stringCellValue;

myCell = (HSSFCell)cellStoreVector.elementAt(1);
stringCellValue = myCell.toString();
password=stringCellValue;

try{

/* PreparedStatement ps = con.prepareStatement("select null from login where username=? and password=?");
//(in the loop)
ps.setString(1, username);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
// Found
}*/

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
// Statement stmt = con.createStatement();
// ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
//PreparedStatement psmt=null;
PreparedStatement psmt = con.prepareStatement("select null from login where username=? and password=?");
psmt.setString(1, username);
psmt.setString(2, password);
ResultSet rs1 = psmt.executeQuery();
System.out.println("rs1-->"+rs1);
boolean isExist = false;
if (rs1!=null) {
while (rs1.next()){
isExist = true;
String rec1 = rs1.getString(1);
String rec2 = rs1.getString(2);
System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
}
}

if (rs1==null) {
Map mapDetail = new HashMap();
mapDetail.put("username",username);
mapDetail.put("password",password);

       int row = 0;
                              //Object obj;
                              For(Object obj:mapDetail.Keys){
                              String value = (String)mapDetail(obj);

                              //not sure of this part:
                              mySheet.getCells().get(row,0).setValue(value);
                              row++;
                              }
missingList.add(mapDetail);
}

// pstmt.close();
con.close();
}
catch(Exception e){

System.out.println("e-->"+e);
}
}

System.out.println("Missing List : "+missingList);
}
}

I modified program as above but is still not working.

I was checking this link as well to see if I can implement some code from there but seems like it is on as400 database. Please advise

gudii9

ASKER

sorry i forgot to put link
http://www.mysamplecode.com/2011/09/generate-large-excel-in-java-using.html