• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3433
  • Last Modified:

HAshMap to Excel file using APache POI

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 have above program which reads File.xls for username and password info like above. From the missedList hashmap I need to send the username,password information to a excel sheet using apache POI. Please advise on how to send achieve it. Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance  

I checked folowing link but does not have what I am looking

http://www.mysamplecode.com/2011/09/generate-large-excel-in-java-using.html 

Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
0
gudii9
Asked:
gudii9
  • 2
2 Solutions
 
CEHJCommented:
For such a simple thing, why not use csv?
0
 
gudii9Author Commented:
I found good link
http://sanjaal.com/java/105/java-file/writing-to-excel-file-using-apache-poi/

challenge is it does not talk how to convert from hashmap to excel though.

It describes nicely about creating excel using POI
0
 
CEHJCommented:
There's no reason to use poi for such a thing, just as there's no reason to make it into a Word file with a table
0
 
Amitkumar PSr. ConsultantCommented:
Agree with CEHJ.

A CSV file will work rather than a XLS.

Here, you have "missingList" that contains a HashMap in each element.

You will have to do the following
1. iterate thru a list
2. get the hashmap on each iteration
3. get the values from the hash map
4. write it to a file (CSV/XLS whatever)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now