?
Solved

Excel Output report of missing records

Posted on 2012-08-30
2
Medium Priority
?
606 Views
Last Modified: 2012-09-03
Hi,
I would like to get the records from excel spread sheet as attached and check if those records are present in mysql database or not. If they are not I would like to generate missing records report and email to the user.

I wrote following java application which I built with reference to following link


http://www.roseindia.net/tutorial/java/poi/insertExcelFileData.html


I modified program to as follows

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.Iterator;
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 POIRIRaw4EE {
      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="";
            PreparedStatement pstmt = null;      
            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;
                  
                  //System.out.println("value username--->"+username);
                  //System.out.println("value password-->"+password);
                  
                  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+"'");
                        System.out.println("rs1-->"+rs1);
                        while (rs1.next()){
                              String rec1 = rs1.getString(1);
                              String rec2 = rs1.getString(2);
                              System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
                        }


                        pstmt.close();
                        con.close();
                  }
                  catch(Exception e){
                        
                        System.out.println("e-->"+e);
                  }
            }
      }
}




Console output i got like

rs1-->com.mysql.jdbc.JDBC4ResultSet@1006d75
rec1 is--jim---rec2 is---yyy
e-->java.lang.NullPointerException
rs1-->com.mysql.jdbc.JDBC4ResultSet@1ccce3c
rec1 is--bill---rec2 is---zzz
e-->java.lang.NullPointerException


I created mysql login table using command

CREATE TABLE test.Login
(
username VARCHAR(50)
password VARCHAR(50)
);


I need to enhance this application so that it generates output excel report of missing records ( which are absent in mysql login table compared to input excel file 'File.xls'). How do I modify to generate the missing records excel output report from above program which is checking againest database and printing information to console.
0
Comment
Question by:gudii9
  • 2
2 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 38351379
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38360233
:)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
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.
Suggested Courses

807 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