Solved

how to compare integers in two excel sheets using java program

Posted on 2014-03-10
6
1,210 Views
Last Modified: 2014-03-29
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
Comment
Question by:srikotesh
6 Comments
 
LVL 12

Expert Comment

by:Sharon Seth
ID: 39919820
That's done easily in excel , do you need to use a java program ?
0
 
LVL 16

Expert Comment

by:krakatoa
ID: 39920569
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
 
LVL 31

Expert Comment

by:awking00
ID: 39921134
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 16

Expert Comment

by:krakatoa
ID: 39921961
How did all this data get into a spreadsheet?

No-one inputs that amount for a one-off analysis.
0
 
LVL 1

Author Comment

by:srikotesh
ID: 39928506
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
 
LVL 16

Accepted Solution

by:
krakatoa earned 500 total points
ID: 39928750
An Excel spreadsheet can be saved in several other formats, one of which is CSV - comma separated values - text.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
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…
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.

706 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

18 Experts available now in Live!

Get 1:1 Help Now