compare two strings of an excel having two words and write accordingly

Question

i want to compare two colums in and excel and write the result in the 3rd column.

input file is like this below

output file should be like this

This is wat i have done till now

import java.util.List;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.BeforeClass;

import java.io.FileInputStream;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.Pattern;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Test {

    /**
     * @param args
     */ //variable declarations

    String element;
    static String storedElement[][]=null;
    static String Element[][]=null;


    public static void main(String[] args) throws BiffException, IOException, RowsExceededException, WriteException {
        // TODO Auto-generated method stub

        FileInputStream fs=new FileInputStream("file location(.xls)");  
        Workbook wb=Workbook.getWorkbook(fs);
        Sheet sh=wb.getSheet(0);

        Workbook workbook = Workbook.getWorkbook(new File("file location(.xls)"));
        WritableWorkbook ww= Workbook.createWorkbook(new File("file location(.xls)RENAME THE FILE here "), workbook);
        WritableSheet ws=ww.getSheet(0);

        Label label;

        //retrieving values from excel and storing in array
        storedElement = new String[sh.getColumns()][sh.getRows()];
        Element = new String[sh.getColumns()][sh.getRows()];
        //for (int j = 0; j <sh.getColumns(); j++) 
        //{ 
        int j=0; //column #0
        int q=1; //column #1
        //StringBuilder sb = new StringBuilder();
        for (int i = 2; i < sh.getRows(); i++) //loop to run all rows

        {
            Cell cell = sh.getCell(j, i);
            Cell cell1=sh.getCell(q,i);

            storedElement[j][i] = cell.getContents();
            Element[q][i]=cell1.getContents();
            String elem =  storedElement[j][i];
            System.out.println(elem);
            String elem1= Element[q][i];
            System.out.println(elem1);
            String[] ob1=elem1.split("\n");
            System.out.println(ob1[i]);
            String[] ob = elem.split("\n");
            System.out.println(ob[i]);


        //for(int k=0;k<ob.length;k++){
            int k=0;
            while (k<ob.length){
            if (ob[k]==ob1[k]){
                    System.out.println(ob[k]);
                    System.out.println(ob1[k]);
                    label=new Label(2,i,"Valid");
                }
                else {
                    System.out.println(ob[k]);
                    System.out.println(ob1[k]);
                    label=new Label(2,i,"InValid");

                }
            k++;
            //add new line ??

                ws.addCell(label);
                ww.write();
                ww.close();
                workbook.close();


                }
        }

    }
}

i want to compare two columns in and excel and write the result in the 3rd column. here the cell contains two words where i need to write status for two words please help me in doing it

`ob[k] == ob1[k]` is probably the issue - and the solution is probably to use equals as discussed in the duplicate. — assylias, Aug 31 '15 at 13:28
I'm getting the same error ie in c,2 i can write only one invalid instead of valid and invalid — Sharath Samala, Aug 31 '15 at 13:43

score 1 · Accepted Answer · answered Sep 03 '15 at 08:55

Hi I have solved your issue. Use below code to get result in the format you said.

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Test {


    String element;
    static String storedElement[][] = null;
    static String Element[][] = null;


    public static void main(final String[] args) throws BiffException, IOException, RowsExceededException, WriteException {


        FileInputStream fs = new FileInputStream("file location(.xls)");
        Workbook wb = Workbook.getWorkbook(fs);
        Sheet sh = wb.getSheet(0);

        Workbook workbook = Workbook.getWorkbook(new File("file location(.xls)"));
        WritableWorkbook ww = Workbook.createWorkbook(new File("file location(.xls)RENAME THE FILE here"),
            workbook);
        WritableSheet ws = ww.getSheet(0);

        Label label = null;


        Test.storedElement = new String[sh.getColumns()][sh.getRows()];
        Test.Element = new String[sh.getColumns()][sh.getRows()];

        int j = 0; // column #0
        int q = 1; // column #1

        for (int i = 1; i < sh.getRows(); i++) {
            Cell cell = sh.getCell(j, i);
            Cell cell1 = sh.getCell(q, i);

            Test.storedElement[j][i] = cell.getContents();
            Test.Element[q][i] = cell1.getContents();
            String elem = Test.storedElement[j][i];
            System.out.println(elem);
            String elem1 = Test.Element[q][i];
            System.out.println(elem1);


            String[] ob = elem1.split("\n");
            String[] ob1 = elem.split("\n");

            String valid = "valid";
            String invalid = "invalid";
            String Status = "";
            if ((ob[0].equals(ob1[0])) && (ob[1].equals(ob1[1]))) {
                Status = valid + "\n" + valid;
            } else if ((!ob[0].equals(ob1[0])) && (ob[1].equals(ob1[1]))) {
                Status = invalid + "\n" + valid;
            } else if ((!ob[0].equals(ob1[0])) && (!ob[1].equals(ob1[1]))) {
                Status = valid + "\n" + invalid;
            } else {
                Status = invalid + "\n" + invalid;
            }

            label = new Label(2, i, Status);
            ws.addCell(label);


        }
        ww.write();
        ww.close();
        workbook.close();
    }
}

Thanks for your help. I have made changes accordingly and it worked :) . — Sharath Samala, Sep 03 '15 at 13:02

score 0 · Answer 2 · answered Aug 31 '15 at 13:39

0

Did you even debug your code? You should use equals to compare Strings

So replace if (ob[k]==ob1[k]){ by if (ob[k].equals(ob1[k])){

(I didn't actually test your code, but this is probably the major issue)

answered Aug 31 '15 at 13:39

Manu

1,474
1
12
18

look in the above screenshots correctly in the same cell i want two words and when im writing the second word(invalid) the first word is overwriting(valid) – Sharath Samala Aug 31 '15 at 16:46

score 0 · Answer 3 · answered Sep 03 '15 at 13:04

    package com.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Test {


    String element;
    static String storedElement[][] = null;
    static String Element[][] = null;


    public static void main(final String[] args) throws BiffException, IOException, RowsExceededException, WriteException {


        FileInputStream fs = new FileInputStream("D:/app/sweta/Book1.xls");
        Workbook wb = Workbook.getWorkbook(fs);
        Sheet sh = wb.getSheet(0);

        Workbook workbook = Workbook.getWorkbook(new File("D:/app/sweta/Book1.xls"));
        WritableWorkbook ww = Workbook.createWorkbook(new File("D:/app/sweta/Book4.xls"),
            workbook);
        WritableSheet ws = ww.getSheet(0);

        Label label = null;


        Test.storedElement = new String[sh.getColumns()][sh.getRows()];
        Test.Element = new String[sh.getColumns()][sh.getRows()];

        int j = 0; // column #0
        int q = 1; // column #1



        WritableCellFormat cellFormat = new WritableCellFormat(ws.getColumnFormat(j));
        cellFormat.setAlignment(Alignment.LEFT);
        cellFormat.setWrap(true);
        System.out.println(sh.getRows());
        for (int i = 1; i < sh.getRows(); i++) {

            Cell cell = sh.getCell(j, i);
            Cell cell1 = sh.getCell(q, i);

            Test.storedElement[j][i] = cell.getContents();
            Test.Element[q][i] = cell1.getContents();
            String elem = Test.storedElement[j][i];
            System.out.println(elem);
            String elem1 = Test.Element[q][i];
            System.out.println(elem1);


            String[] ob = elem1.split("\n");
            String[] ob1 = elem.split("\n");
            int l= ob.length;
         //   String valid = "valid";
        //    String invalid = "invalid";
            String Status = "";
            StringBuffer sb=new StringBuffer();
            for(int s=0;s<l;s++){
                if (ob[s].equals(ob1[s])) {
                    sb.append("valid");
                } else {
                    sb.append("invalid");
                }
                if(s==l-1){

                }else{
                    sb.append("\n");
                }

            }

            Status = sb.toString();

            label = new Label(2, i, Status,cellFormat);
            ws.addCell(label);


        }
        ww.write();
        ww.close();
        workbook.close();
    }
}

compare two strings of an excel having two words and write accordingly

3 Answers3