?
Solved

Output to excel

Posted on 2012-09-05
8
Medium Priority
?
696 Views
Last Modified: 2012-09-11
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
0
Comment
Question by:gudii9
  • 4
  • 4
8 Comments
 
LVL 7

Author Comment

by:gudii9
ID: 38369895
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
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 38369994
The code you posted can't be your real code - it wouldn't even compile
0
 
LVL 7

Author Comment

by:gudii9
ID: 38370148
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 86

Expert Comment

by:CEHJ
ID: 38370200
Several questions ago, i mentioned to you, and demonstrated why, that it's better to use PreparedStatement
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38370218
But not working some reason. Please advise
http://technojeeves.com/joomla/index.php/free/116-doesntwork
0
 
LVL 7

Author Comment

by:gudii9
ID: 38370227
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 38370254
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
 
LVL 7

Author Comment

by:gudii9
ID: 38370296
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses
Course of the Month15 days, 19 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question