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

how to compare integers in two excel sheets using java program

Hi
I have two excel sheets
sheet 1 contains some numbers(sheet1 contains total 500k nos in excel)
sheet 2 contains same numbers(490k) but some of the nos in sheet 2 doesn't contain in sheet 1

i have to print the output like matching nos and unmatched nos
how to do using java program
0
srikotesh
Asked:
srikotesh
1 Solution
 
Sharon SethCommented:
That's done easily in excel , do you need to use a java program ?
0
 
krakatoaCommented:
I can't see either why you need Java at this stage anyway.

Just copy this formula into the cells of a third spreadsheet behind the other two :

=IF(Sheet1!A1=Sheet2!A1,"match","no match")

Open in new window

0
 
awking00Commented:
If for some reason you must use java, I would suggest using the POI api to loop through the first spreadsheet comparing the values found looping through the second spreadsheet. However, with 500,000 numbers, I suspect some will be duplicated in the same spreadsheet so, for example, if the number 33 shows up 7 times in one sheet and 4 times in the other, is that considered 4 matches and 3 mismatches or is the number 33 just considered a match? Are there other columns in the spreadsheets that can act as a key to comparing values? Perhaps some sample data for the two spreadsheets and your expected results would help clarify.
0
Technology Partners: 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!

 
krakatoaCommented:
How did all this data get into a spreadsheet?

No-one inputs that amount for a one-off analysis.
0
 
srikoteshAuthor Commented:
ya ,thru excel we can complete this task, one of my friend has written this program we can do this way also
can any one explain how he has converted excel sheet into .txt file
please see the code give some suggestion to do in the following way

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;
import java.util.StringTokenizer;

public class ReaddFile {

      public static void main(String[] args) {

            BufferedReader br = null;

            BufferedReader br1 = null;

            try {

                  String VMSString;

                  Set<String> vmsList = new HashSet<String>();
                  Set<String> vfsList = new HashSet<String>();

                  br = new BufferedReader(new FileReader(
                              "C:\Users\Smasikukku\Desktop\\VMS.txt"));

                  while ((VMSString = br.readLine()) != null) {
                        // System.out.println(VMSString);
                        StringTokenizer st2 = new StringTokenizer(VMSString, "|");

                        while (st2.hasMoreElements()) {
                              vmsList.add(((String) st2.nextElement()).trim());
                        }
                  }

                  String VFSString;

                  br1 = new BufferedReader(new FileReader(
                              "C:\Users\Smasikukku\Desktop\\VFS.txt"));

                  while ((VFSString = br1.readLine()) != null) {
                        // System.out.println(VFSString);
                        StringTokenizer st3 = new StringTokenizer(VFSString, "|");

                        while (st3.hasMoreElements()) {
                              vfsList.add(((String) st3.nextElement()).trim());
                        }

                  }

                  for (String val : vmsList) {
                        if (vfsList.contains(val.trim())) {
                              // System.out.println("Not find in VMS List  : " + val);
                        } else {
                              System.out.println("Not find in VFS List  : " + val);
                        }
                  }

            } catch (IOException e) {
                  e.printStackTrace();
            } finally {
                  try {
                        if (br != null)
                              br.close();
                  } catch (IOException ex) {
                        ex.printStackTrace();
                  }
            }

      }
}
0
 
krakatoaCommented:
An Excel spreadsheet can be saved in several other formats, one of which is CSV - comma separated values - text.
0

Featured Post

Technology Partners: 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!

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