HAshMap to Excel file using APache POI

Posted on 2012-09-05
Last Modified: 2012-09-11
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);
      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                        HSSFRow myRow = (HSSFRow);
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                              HSSFCell myCell = (HSSFCell);
            }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();
                   /* 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( {
                    // Found
                    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();
                              boolean isExist = false;
                              if (rs1!=null) {
                                    while ({
                                            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();

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

                              //not sure of this part:

                  //  pstmt.close();
              catch(Exception 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

Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
Question by:gudii9
    LVL 86

    Expert Comment

    For such a simple thing, why not use csv?
    LVL 7

    Author Comment

    I found good link

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

    It describes nicely about creating excel using POI
    LVL 86

    Assisted Solution

    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
    LVL 20

    Accepted Solution

    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)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Title # Comments Views Activity
    matchUp  challenge 9 49
    for loop with Set 4 35
    How Complex Is This Java Course ? 9 51
    array11 challenge 16 40
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
    Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now