Compare .xls files

Product Forums Katalon Studio
,
1

I am not getting the exact result after comparing two excel files which are csv supported files in .xls format. I have used csvreader and copied new files in a workbook and then comparing the workbook and I have used excelkeywods plugin also

2

hello,

this is one approach to use .csv format

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.CSVRecord;

import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;



String originalPath = System.getProperty("user.dir")+"\\ExcelFiles\\original.csv";
String comparePath = System.getProperty("user.dir")+"\\ExcelFiles\\compare.csv";
String differencesPath = System.getProperty("user.dir")+"\\ExcelFiles\\differences.csv";

//createCSVFile();
int[] rowsCols = getRowsColsNo(originalPath);
int[] rowsCols2 = getRowsColsNo(comparePath);
System.out.println(rowsCols[0]); //count of rows
System.out.println(rowsCols[1]); //count of columns

File file = new File(differencesPath);
if(file.delete())
{
	System.out.println("File deleted successfully");
}
else
{
	System.out.println("Failed to delete the file");
}
createCSVFile(differencesPath);//create new file

String mess = "original.csv does not match with compare.csv ";
//start from row 0 (header)
writeToDiffFile(differencesPath, null, mess, 0, 0);//write header
String[] strDiff = new String[rowsCols[0]];
for (int i = 0; i < rowsCols[0]; i++) {
	String line = Files.readAllLines(Paths.get(originalPath)).get(i);
	String line2 = Files.readAllLines(Paths.get(comparePath)).get(i);
	String diff = getDiff(line, line2);
	//println diff
	strDiff[0] = diff;
	strDiff[1] = "in row "+i;
	writeToDiffFile(differencesPath, strDiff, null, 1, i);
	
}


public static int[] getRowsColsNo(String fileName) {
	Scanner scanIn = null;
	int rows = 0;
	int cols = 0;
	String InputLine = "";
	try {
		scanIn = new Scanner(new BufferedReader(
				new FileReader(fileName)));
		scanIn.useDelimiter(",");
		while (scanIn.hasNextLine()) {
			InputLine = scanIn.nextLine();
			String[] InArray = InputLine.split(",");
			rows++;
			cols = InArray.length;
		}

	} catch (Exception e) {
		System.out.println(e);
	}
	//scanIn.close();
	return [rows, cols] ;
}

public static void createCSVFile(String fileName) throws IOException {
	//String[] HEADERS = { "author", "title","year"};
	CSVPrinter printer = null;
	FileWriter out = new FileWriter(fileName);
	try {
		printer = new CSVPrinter(out, CSVFormat.DEFAULT);
		//        .withHeader(HEADERS));
		printer.printRecord();
	} catch (IOException e) {
		e.printStackTrace();
	}
	printer.flush();
	out.close();
}


public static void writeToDiffFile(String fileName, String[] values, String val, int counter, int rowNum) throws IOException {
	CSVPrinter csvPrinter;
	//FileWriter out = new FileWriter(fileName);
	Writer writer = Files.newBufferedWriter(Paths.get(fileName), StandardOpenOption.APPEND);
	//BufferedWriter writer = Files.newBufferedWriter(Paths.get(fileName), StandardOpenOption.APPEND);
	if(counter == 1) {
		csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT);
		 //       .withHeader("author", "title", "year"));
		csvPrinter.printRecord(Arrays.asList(values));
	}
	else{
		csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT);
		csvPrinter.printRecord(val);
	}
	csvPrinter.flush();
	writer.close();
}

public static String getDiff(String str1, String str2){
	String[] strList1 = str1.split(",");
	String[] strList2 = str2.split(",");

	List<String> list1 = Arrays.asList(strList1);
	List<String> list2 = Arrays.asList(strList2);

	// Prepare a union
	List<String> union = new ArrayList<>(list1);
	union.addAll(list2);

	// Prepare an intersection
	List<String> intersection = new ArrayList<>(list1);
	intersection.retainAll(list2);

	// Subtract the intersection from the union
	union.removeAll(intersection);
	List<String> arr = new ArrayList<>();
	for (String s : union) {
		arr.add(s);
	}
	return String.join(",", arr);
}