Output to excel

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


I have above java application which reads from File.xls for username and password and checks againest the database if that username and password is exists or not. If it does not exist puts in missingList and printis to console that info. Instead of printing tp console i want it to send to excel. I have a program(from link http://sanjaal.com/java/105/java-file/writing-to-excel-file-using-apache-poi/) that is writing to excel as follows


import java.io.FileOutputStream;
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;  
public class WriteExcelPOI3 {        
      public static void main( String [] args ) {      
            /**Name of excel file that we are going to create**/      
            String fileName="C:\\testPOIWrite5.xls";        
            writeDataToExcelFile(fileName);         }    
      /** This method writes data to new excel file **/  
      private static void writeDataToExcelFile(String fileName) {                  
            String [][] excelData = preapreDataToWriteToExcel();                              
            HSSFWorkbook myWorkBook = new HSSFWorkbook();        
            HSSFSheet mySheet = myWorkBook.createSheet();          
            HSSFRow myRow = null;        
            HSSFCell myCell = null;                  
            for (int rowNum = 0; rowNum < excelData[0].length; rowNum++){          
                  myRow = mySheet.createRow(rowNum);                            
                  for (int cellNum = 0; cellNum < 4 ; cellNum++){              
                        myCell = myRow.createCell(cellNum);                
                        myCell.setCellValue(excelData[rowNum][cellNum]);                        
                  }        
            }                    
            try{        
                  FileOutputStream out = new FileOutputStream(fileName);          
                  myWorkBook.write(out);              out.close();      
            }catch(Exception e){ e.printStackTrace();}                  
      }      /**Prepare some demo data as excel file content**/  
      public static String [][] preapreDataToWriteToExcel(){        
            String [][] excelData = new String [4][4];          
            excelData[0][0]="UserName";        
            excelData [0][1]="PassWord";      
                                     
            excelData[1][0]="Kushal";          
            excelData[1][1]="Paudyal";        
                   
                               
            excelData[2][0]="Randy";          
            excelData[2][1]="Ram Robinson";        
                     
                          
            excelData[3][0]="Phil";          
            excelData[3][1]="Collins";          
              
                        
            return excelData;                
            }        
      }




Can you please advise how do I integrate above 2 programs to get desired missinList output into an excel format
LVL 7
gudii9Asked:
Who is Participating?
 
CEHJConnect With a Mentor Commented:
The code you posted can't be your real code - it wouldn't even compile
0
 
gudii9Author Commented:
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);
  }
}


As shown in above program I am trying to loop through the Hashmap and fill in the cells. But not working some reason. Please advise
0
 
gudii9Author Commented:
Sorry,

following should compile and run fine. I tested working fine for me


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 POIRIRaw4EEC {
      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{      
                   
                   
                   
                   
                    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;
                  //  psmt=con.prepareStatement(sql)
                    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);
  }
}

please advise
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CEHJCommented:
Several questions ago, i mentioned to you, and demonstrated why, that it's better to use PreparedStatement
0
 
CEHJCommented:
But not working some reason. Please advise
http://technojeeves.com/joomla/index.php/free/116-doesntwork
0
 
gudii9Author Commented:
oh..i have several versions of code since i cannot post actual code. I am currently using prepared statement only. Please assume everything else is fine. My main concern now is to make missingReport list into an excel output after these steps

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


after catch block
near
    System.out.println("Missing List : "+missingList);

i would like to do something like below as given in link
http://sanjaal.com/java/105/java-file/writing-to-excel-file-using-apache-poi/

System.out.println("Missing List : "+missingList);
            String fileName="C:\\testPOIWrite5.xls";        
            writeDataToExcelFile(fileName);
      }


      
      
      
      
      
      
      
      
      
      
      public static String [][] preapreDataToWriteToExcel(){        
            String [][] excelData = new String [4][4];          
            excelData[0][0]="UserName";        
            excelData [0][1]="PassWord";      
                                     
            excelData[1][0]="Kushal";          
            excelData[1][1]="Paudyal";        
                   
                               
            excelData[2][0]="Randy";          
            excelData[2][1]="Ram Robinson";        
                     
                          
            excelData[3][0]="Phil";          
            excelData[3][1]="Collins";          
              
                        
            return excelData;                
            }      
      
      
      
      private static void writeDataToExcelFile(String fileName) {                  
            String [][] excelData = preapreDataToWriteToExcel();                              
            HSSFWorkbook myWorkBook = new HSSFWorkbook();        
            HSSFSheet mySheet = myWorkBook.createSheet();          
            HSSFRow myRow = null;        
            HSSFCell myCell = null;                  
            for (int rowNum = 0; rowNum < excelData[0].length; rowNum++){          
                  myRow = mySheet.createRow(rowNum);                            
                  for (int cellNum = 0; cellNum < 4 ; cellNum++){              
                        myCell = myRow.createCell(cellNum);                
                        myCell.setCellValue(excelData[rowNum][cellNum]);                        
                  }        
            }                    
            try{        
                  FileOutputStream out = new FileOutputStream(fileName);          
                  myWorkBook.write(out);              out.close();      
            }catch(Exception e){ e.printStackTrace();}                  
      }
      


please advise
0
 
CEHJCommented:
oh..i have several versions of code since i cannot post actual code

Please don't post code that you're not running. That just wastes everyone's time. If there are sensitive details, then mask them
You have a List<Map>. You can write them as csv as i advised. You seem to be reluctant for some reason ...
0
 
gudii9Author Commented:
since i am reading from POI i want to write using POI.

I am willing to write them as csv as well. But i do not know how to do it. Please provide me sample code or links, resources to do that. I will definitely try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.