Farhana Rahman
asked on
How to compare two excels in eclipse and perform operations accordingly
I have two excel files which i read in eclipse with the help of apache poi. I want to compare between the two excel files and perform operations:
my first excel file is accessed by HSSFReadWrite.java
my second excel file is accessed by XSSFReadWrite.java
then I want to compare the column 1 of file 2 with column 5 of file 1. if the entries match then I want the row to stay and paste columns 7,8,9,10,11 of file 2 into file 1 columns 15,16,17,18,19 or if the entries doesnt match then I want the rows to delete from file 1. the final output should be the modified file 1
i already have vba macros for the same that runs well. But I have to do it in Java.
reading-BOM-using-Apache-poi.txt
reading-BOM-using-Apache-poi.txt
VBAcode1.txt
VBAcode2.txt
my first excel file is accessed by HSSFReadWrite.java
my second excel file is accessed by XSSFReadWrite.java
then I want to compare the column 1 of file 2 with column 5 of file 1. if the entries match then I want the row to stay and paste columns 7,8,9,10,11 of file 2 into file 1 columns 15,16,17,18,19 or if the entries doesnt match then I want the rows to delete from file 1. the final output should be the modified file 1
i already have vba macros for the same that runs well. But I have to do it in Java.
reading-BOM-using-Apache-poi.txt
reading-BOM-using-Apache-poi.txt
VBAcode1.txt
VBAcode2.txt
awking00
I would probably start this effort by creating a temporary workbook (of type file1) looping through file2 and deleting all rows where file1, column5 does not match file2, column1. It is then simple enough to look up where those values that match and pick up the five columns from file2 that you want to go into file1. If you could post some sample workbooks (and their extensions) for each of the tables and what you want the final file to look like, that would provide a nice test case for the experts to develop a viable solution for you. If your data is not too proprietary, actual excerpts from your workbooks would even be better. I've worked with the Java POI API on dozens of workbooks with hundreds of worksheets and thousands of rows and know that there can be a lot of nuances in comparing and manipulating them so the closer we can get to reality the better chance we have for a workable application. Good luck.
Sample data?
ASKER
I'm sorry but I can't open up the .xls file due to the Western European coding, which makes determining the matches (and not matches) not possible. Without two files to compare, I don't know that there is anything I can do to help. :-(
ASKER
why cant you open that :( I dont know what to do now
>>then I want to compare the column 1 of file 2 with column 5 of file 1. if the entries match then I want the row to stay and paste columns 7,8,9,10,11 of file 2 into file 1 columns 15,16,17,18,19 <<
I'm going to try another way of opening the files. In the mean time, which of the files (i.e. master.xlsx or Stuckliste-Tabelle.xls) represents file1 and which represents file2? Also, what are the column headings for columns 7,8,9,10,11 of file2 and columns 15,16,17,18,19 of file1?
I'm going to try another way of opening the files. In the mean time, which of the files (i.e. master.xlsx or Stuckliste-Tabelle.xls) represents file1 and which represents file2? Also, what are the column headings for columns 7,8,9,10,11 of file2 and columns 15,16,17,18,19 of file1?
ASKER
I start from the first:
1. I have Masterliste and Stuckliste tabelle
2. I compare the column A of Masterliste with column H of Stuckliste tabelle (the data form is different instead of '.' there is space ' ' in one list)
3. If there is a match the row stays and if no match then the entire row gets deleted in Stuckliste Tabelle.
4. After that in the rows that remain I copy Columns G,H,I,J,K from Masterliste to columns O,P,Q,R,S of stuckliste Tabelle.
5. The final output is the modified Stuckliste Tabelle
1. I have Masterliste and Stuckliste tabelle
2. I compare the column A of Masterliste with column H of Stuckliste tabelle (the data form is different instead of '.' there is space ' ' in one list)
3. If there is a match the row stays and if no match then the entire row gets deleted in Stuckliste Tabelle.
4. After that in the rows that remain I copy Columns G,H,I,J,K from Masterliste to columns O,P,Q,R,S of stuckliste Tabelle.
5. The final output is the modified Stuckliste Tabelle
I think I may have a solution but something has come up requiring my attention at the moment, Bear with me and I will get back to you when I can.
ASKER
thank you so much
Sorry I couldn't get back to you sooner but the government shutdown kept me out for three days. At any rate, I still have some questions about which of the Stuckliste-Tabelle rows get kept in the modified table (i.e. the Output table). It appears that a partial match of some type retains the row and an exact match not only retains the row but copies columns G-K in the Masterliste file to columns O-S in the Output file. The problem I'm having is with the partial match criteria. In most cases, a match of the first nine characters (without spaces or periods) seems to work, but not in all cases. For example, your output file shows two 5Q0 907 275 B numbers while the Masterliste show numbers of 5Q0.907.275_/B and 5Q0.907.275.B/C that partially match on the first nine characters but the output file also shows a 7P0 915 105 number without a corresponding match although there is a 4N0.915.105. Likewise, there is no match for the output number 4K0 959 754 but there is a match for 4N0.959.754. Is it possible these are typos or cut and paste errors or do just the seven numerical characters need to match (e.g. 0.915.105.x matches 0 915 105 y) ? If not, I would need to know the precise criteria for determining a partial match.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Accept: awking00 (https:#a42454443)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Accept: awking00 (https:#a42454443)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer