BOMCompare - ConsolidatorDemo.java

package com.din.comp.compare;

import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.datatransfer.DataFlavor;
import java.awt.datatransfer.Transferable;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.List;

import javax.swing.BorderFactory;
import javax.swing.DefaultListCellRenderer;
import javax.swing.DefaultListModel;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JSplitPane;
import javax.swing.JTextArea;
import javax.swing.TransferHandler;
import javax.swing.UIManager;
import javax.swing.UIManager.LookAndFeelInfo;
import javax.swing.border.TitledBorder;
import javax.swing.filechooser.FileSystemView;



public class ConsolidatorDemo extends JPanel implements ActionListener {
    private static final long serialVersionUID = -4487732343062917781L;
    // JFileChooser fc;
    JButton clear,compare;
    JTextArea fc;

    JList dropZone;
    DefaultListModel listModel;
    JSplitPane childSplitPane, parentSplitPane;
    PrintStream ps;
    
    

    public ConsolidatorDemo() {
        super(new BorderLayout());

        /* fc = new JFileChooser();;
    fc.setMultiSelectionEnabled(true);
    fc.setDragEnabled(true);
    fc.setControlButtonsAreShown(false);
    fc.setFileSelectionMode(JFileChooser.FILES_ONLY);*/
        fc= new JTextArea();
        fc.setText("Rules:\n1. drop old html first.\n2. drop new html.\n3. click compare button.\n4. Wait until popup comes.\n5.Check output in the output.txt file.\nEnd\n\nIf errors -> Dinesh R");

        fc.setEditable(false);
        JPanel fcPanel = new JPanel(new BorderLayout());

        fcPanel.add(fc, BorderLayout.CENTER);


        compare = new JButton("Compare");
        compare.addActionListener(this);
        JPanel buttonPanel1 = new JPanel(new BorderLayout());
        buttonPanel1.setBorder(BorderFactory.createEmptyBorder(1,1,1,1));
        buttonPanel1.add(compare, BorderLayout.LINE_END);


        clear = new JButton("Clear All");
        clear.addActionListener(this);
        JPanel buttonPanel = new JPanel(new BorderLayout());
        buttonPanel.setBorder(BorderFactory.createEmptyBorder(1,1,1,1));
        buttonPanel.add(clear, BorderLayout.LINE_END);

        JPanel leftUpperPanel = new JPanel(new BorderLayout());
        leftUpperPanel.setBorder(BorderFactory.createEmptyBorder(3,3,3,3));
        leftUpperPanel.add(fcPanel, BorderLayout.CENTER);
        leftUpperPanel.add(buttonPanel1, BorderLayout.LINE_END);
        leftUpperPanel.add(buttonPanel, BorderLayout.PAGE_END);

        JScrollPane leftLowerPanel = new javax.swing.JScrollPane();
        leftLowerPanel.setBorder(BorderFactory.createEmptyBorder(3,3,3,3));

        listModel = new DefaultListModel();
        dropZone = new JList(listModel);
        dropZone.setCellRenderer(new FileCellRenderer());
        dropZone.setTransferHandler(new ListTransferHandler(dropZone));
        dropZone.setDragEnabled(true);
        dropZone.setDropMode(javax.swing.DropMode.INSERT);
        dropZone.setBorder(new TitledBorder("Drag and drop files here"));
        leftLowerPanel.setViewportView(new JScrollPane(dropZone));

        childSplitPane = new JSplitPane(JSplitPane.VERTICAL_SPLIT, leftLowerPanel,leftUpperPanel);
        childSplitPane.setDividerLocation(200);//400
        childSplitPane.setPreferredSize(new Dimension(300, 400));//480, 650

        /*console = new JTextArea();
    console.setColumns(40);
    console.setLineWrap(true);
    console.setBorder(new TitledBorder("Console"));

    parentSplitPane = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT,
                    childSplitPane, console);
    parentSplitPane.setDividerLocation(480);
    parentSplitPane.setPreferredSize(new Dimension(800, 650));*/

        add(childSplitPane, BorderLayout.CENTER);

    }

    public void setDefaultButton() {
        getRootPane().setDefaultButton(clear);
    }

    public void actionPerformed(ActionEvent e) {
        if (e.getSource() == clear) {
            listModel.clear();
            FileCellRenderer.files=new String[50];
        }else if (e.getSource() == compare) {
            //our function
        	if(FileCellRenderer.files[2]!=null || FileCellRenderer.files[0]==null||FileCellRenderer.files[1]==null)
    		{
    			JOptionPane.showMessageDialog (null, "Drag and drop 2 bom html reports to be compared", "Info", JOptionPane.INFORMATION_MESSAGE);
    			return;
    		}else{
        	try {
				BOMCompareWithShipment_without_d.perform(FileCellRenderer.files[0], FileCellRenderer.files[1]);
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }
        }
    }

    /**
     * Create the GUI and show it. For thread safety,
     * this method should be invoked from the
     * event-dispatching thread.
     */
    private static void createAndShowGUI() {
        //Make sure we have nice window decorations.
        JFrame.setDefaultLookAndFeelDecorated(true);
        try {
            //UIManager.setLookAndFeel("de.javasoft.plaf.synthetica.SyntheticaBlackStarLookAndFeel");
            for (LookAndFeelInfo info : UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        //Create and set up the window.
        JFrame frame = new JFrame("Bill of materials Comparer");
        frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);

        //Create and set up the menu bar and content pane.
        ConsolidatorDemo demo = new ConsolidatorDemo();
        demo.setOpaque(true); //content panes must be opaque
        frame.setContentPane(demo);

        //Display the window.
        frame.pack();
        frame.setVisible(true);
        demo.setDefaultButton();
    }

    public static void main(String[] args) {
        //Schedule a job for the event-dispatching thread:
        //creating and showing this application's GUI.
        javax.swing.SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                createAndShowGUI();
            }
        });
    }
}


class FileCellRenderer extends DefaultListCellRenderer {
	static String files[] = new String[50];
	//int i=0;
    public Component getListCellRendererComponent(JList list,Object value,int index,boolean isSelected,boolean cellHasFocus) {

        Component c = super.getListCellRendererComponent(list,value,index,isSelected,cellHasFocus);

        if (c instanceof JLabel && value instanceof File) {
            JLabel l = (JLabel)c;
            File f = (File)value;
            l.setIcon(FileSystemView.getFileSystemView().getSystemIcon(f));
            l.setText(f.getName());
            //l.setText(f.getAbsolutePath());
            l.setToolTipText(f.getAbsolutePath());
            files[index]= f.getAbsolutePath();
        }

        return c;
    }
}

class ListTransferHandler extends TransferHandler {

    private JList list;

    ListTransferHandler(JList list) {
        this.list = list;
    }

    @Override
    public boolean canImport(TransferHandler.TransferSupport info) {
        // we only import FileList
        if (!info.isDataFlavorSupported(DataFlavor.javaFileListFlavor)) {
            return false;
        }
        return true;
    }

    @Override
    public boolean importData(TransferHandler.TransferSupport info) {
        if (!info.isDrop()) {
            return false;
        }

        // Check for FileList flavor
        if (!info.isDataFlavorSupported(DataFlavor.javaFileListFlavor)) {
            displayDropLocation("List doesn't accept a drop of this type.");
            return false;
        }

        // Get the fileList that is being dropped.
        Transferable t = info.getTransferable();
        List<File> data;
        try {
            data = (List<File>)t.getTransferData(DataFlavor.javaFileListFlavor);
        }
        catch (Exception e) { return false; }
        DefaultListModel model = (DefaultListModel) list.getModel();
        for (Object file : data) {
            model.addElement((File)file);
        }
        return true;
    }

    private void displayDropLocation(String string) {
        System.out.println(string);
    }
}

BOMCompareWithShipment_without_d.java

package com.din.comp.compare;


import java.awt.Desktop;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Scanner;
import java.util.Set;

import javax.swing.JOptionPane;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

// duplicates are removed
/**
 * Author Dinesh R_24_oct_2016
 * required lib
 * jsoup-1.10.1.jar
 * */
public class BOMCompareWithShipment_without_d  {
	/*	public static void main(String[] args) throws IOException {
	 *//**
	 * update 3 paths
	 * 2 input path and 
	 * 1 output path
	 *//*
		//comment it in testing mode Remember 
		Scanner scan = new Scanner(System.in);
		System.out.println("Drag and drop old BOM compliance html report:");
		String firstinput = scan.next();

		System.out.println("Drag and drop new BOM compliance html report:");
		String secondInput = scan.next();
		System.out.println("Drag and drop Output Folder:");
		String outputPath = scan.next();


		String firstinput = FileCellRenderer.files[0];
		String secondInput = FileCellRenderer.files[1];
		String outputPath = FileCellRenderer.files[2];;



		// Uncomment only for testing locally
		String firstinput = "D:/PT/Projects/current/1/NET/delta1/old_doc/RBEI_OSS-Compliance_Report_ST-CO_IRIS-Net_Delta.html";
		String secondInput = "D:/PT/Projects/current/1/NET/delta1/doc/RBEI_OSS-Compliance_Report_ST-CO_IRIS-Net_Delta1.html";
		String outputPath = "D:/PT/Projects/current/1/NET/delta1/doc/";

		perform(firstinput, secondInput, outputPath);
		//scan.close();
	}*/

	public static void perform(String firstinput, String secondInput) throws FileNotFoundException, IOException {


		System.out.println(firstinput);
		System.out.println(secondInput);
		//System.out.println(outputPath);

		String oldBOMFilePath = firstinput ;
		String newBOMFilePath = secondInput;
		//		String outputFilePath = pathComponent(secondInput)+"\\Output.txt";
		String outputFilePath = pathComponent(secondInput)+"\\Comparison_Report.xlsx";


		if(!oldBOMFilePath.endsWith(".html") || !newBOMFilePath.endsWith(".html") || (!oldBOMFilePath.endsWith(".html") && !newBOMFilePath.endsWith(".html")) ){
			JOptionPane.showMessageDialog (null, "Drag and drop 2 bom reports to be compared", "Info", JOptionPane.INFORMATION_MESSAGE);
			return;
		}
		System.out.println("Check the Output.xlsx for result");
		//		System.setOut(new PrintStream(new FileOutputStream(outputFilePath)));
		FileOutputStream outputStream = new FileOutputStream(outputFilePath);
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("Same_comps_different_versions"); //<------------------------ create sheet
		int rowCount = 0, columnCount = 0;
		ArrayList<String> head = new ArrayList<String>();
		head.add("    S.no    ");
		head.add("  Component  ");
		head.add("  Version  ");
		/*head.add("Home Page");
		head.add("Component Comment");*/
		//head.add("License");
		head.add("  Usage  ");
		/*head.add("Ship Status");
		head.add("File name");*/
		org.apache.poi.ss.usermodel.Row row = null;
		org.apache.poi.ss.usermodel.Row rownew = null;
		org.apache.poi.ss.usermodel.Row rowcommon = null;
		org.apache.poi.ss.usermodel.Row rowremoved = null;

		CellStyle stylehead = workbook.createCellStyle();
		CellStyle stylebold = workbook.createCellStyle();
		CellStyle stylecolor = workbook.createCellStyle();
		stylecolor.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
		stylecolor.setFillPattern(CellStyle.SOLID_FOREGROUND);
		CellStyle stylewrap = workbook.createCellStyle();
		stylewrap.setWrapText(true); 
		stylehead.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
		stylehead.setFillPattern(CellStyle.SOLID_FOREGROUND);
		XSSFFont font = workbook.createFont();
		font.setBold(true);
		stylehead.setFont(font);
		stylebold.setFont(font);
		stylecolor.setFont(font);
		stylecolor.setWrapText(true);

		Cell cell = null;
		
		//excel sheet title ends
		row = sheet.createRow(rowCount++);//<----------------------------------------------------------------------- create row
		columnCount = 0;
		cell = row.createCell(columnCount++);
		cell.setCellValue("Common components having different versions in both BOMs");
		cell.setCellStyle(stylecolor);
		sheet.addMergedRegion(new CellRangeAddress(
	            0, //first row (0-based)
	            0, //last row  (0-based)
	            0, //first column (0-based)
	            3  //last column  (0-based)
	    ));
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		columnCount = 0;
		//excel sheet title ends
		row = sheet.createRow(rowCount++);//<----------------------------------------------------------------------- create row
		
		for (String h : head) {
			cell = row.createCell(columnCount++); //<--------------------------------------------------------------- create cell
			cell.setCellValue((String) h); //<---------------------------------------------------------------------- set value to the cell
			cell.setCellStyle(stylehead); 
			CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		}//int k=0,l1=0,m=0,n1=0,o=0,p=0,q=0,r=0,s=0;
		


		//code for comparison starts
		File oldBOMFile = new File(oldBOMFilePath);
		File newBOMFile = new File(newBOMFilePath);
		Collection<String> listOne = new ArrayList<String>();
		Collection<String> listOnev = new ArrayList<String>();
		Collection<String> listOnes = new ArrayList<String>();
		Collection<String> listTwo = new ArrayList<String>();
		Collection<String> listTwov = new ArrayList<String>();
		Collection<String> listTwos = new ArrayList<String>();

		ArrayList<String> finallistold = new ArrayList<String>();
		ArrayList<String> finallistnew = new ArrayList<String>();


		org.jsoup.nodes.Document document =  Jsoup.parse(oldBOMFile, "UTF-8", "");
		//check external id
		Elements elements;
		String text = null;
		elements = document.select(".bomTable th:eq(7)");
		if (elements.size() == 0) {
			text = "NA";
		} else {
			//				 bomversiontext = elements.get(1).text();

			text = elements.get(0).text();
			//System.out.println("7th position element"+text);
		}
		//check external id


		Elements nextTurns = document.select(".bomTable td:eq(2)");   
		/*String Titleold = document.select(".labeledTable td:eq(0)").first().text();
	System.out.println(Titleold);*/
		//System.out.println("================BOM1======================");
		System.out.println("===========================Total================================");
		for (Element nextTurn : nextTurns) {
			//	System.out.println(nextTurn.text());
			listOne.add(nextTurn.text());                           //list1
		}
		System.out.printf("Total components in old BOM: %s (Including project name)%n",listOne.size());

		ArrayList<String> newlistOne = new ArrayList<String>(listOne);

		String[] old = newlistOne.toArray(new String[newlistOne.size()]);

		//=============
		Elements nextTurnsv = document.select(".bomTable td:eq(3)");
		//System.out.println("================Version1======================");
		for (Element nextTurn : nextTurnsv) {
			//	System.out.println(nextTurn.text());
			listOnev.add(nextTurn.text());                           //list1v
		}
		//System.out.println("TOTAL OLD BOM versions:"+listOnev.size());
		ArrayList<String> newlistOnev = new ArrayList<String>(listOnev);
		String[] oldv = newlistOnev.toArray(new String[newlistOnev.size()]);
		Elements nextshipstatus ;

		//play
		if (text.equalsIgnoreCase("External IDs")) {
			nextshipstatus = document.select(".bomTable td:eq(9)");
		}else{
			System.out.println("no external id present");
			nextshipstatus = document.select(".bomTable td:eq(8)");
		}

		//System.out.println("================Ship status old======================");
		for (Element nextTurn : nextshipstatus) {
			//	System.out.println(nextTurn.text());
			listOnes.add(nextTurn.text());                           //list1v
		}
		//System.out.println("TOTAL OLD BOM shipstatus:"+listOnes.size());
		ArrayList<String> newlistOnes = new ArrayList<String>(listOnes);
		String[] olds = newlistOnes.toArray(new String[newlistOnes.size()]);
		//play


		if ( old.length ==  oldv.length) {
			for (int i = 0; i < old.length; i++) {
				old[i] = old[i]+" ="+oldv[i]+" ="+olds[i];//<---------- computation
			}
			List<String> ewlistOne = (List<String>) Arrays.asList(old) ;
			/*Titleold = Titleold+"_Unspecified";
		ewlistOne.remove(Titleold);*/
			finallistold.addAll(ewlistOne);                           //list 1 final
		} else {
			System.out.println("size mismatch");
		}

		//=========================================================file2 ==================================================
		org.jsoup.nodes.Document document1 =  Jsoup.parse(newBOMFile, "UTF-8", "");

		//check external id
		Elements elements2;
		String text1 = null;
		elements2 = document1.select(".bomTable th:eq(7)");
		if (elements2.size() == 0) {
			text1 = "NA";
		} else {
			//				 bomversiontext = elements.get(1).text();
			text1 = elements2.get(0).text();
		}
		//check external id



		Elements nextTurns1 = document1.select(".bomTable td:eq(2)");
		String Titlenew = document1.select(".labeledTable td:eq(0)").text();
		//System.out.println("================BOM2======================");
		for (Element nextTurn1 : nextTurns1) {
			//System.out.println(nextTurn1.text());
			listTwo.add(nextTurn1.text());                            //list2
		}


		System.out.printf("Total components in new BOM: %s (Including project name)%n",listTwo.size());
		System.out.println("NOTE: For correct output always drag & drop old html BOM report first");
		System.out.println("");
		//System.out.println("================================================================");
		//System.out.printf("");
		ArrayList<String> newlistTwo = new ArrayList<String>(listTwo);
		String[] old1 =  newlistTwo.toArray(new String[newlistTwo.size()]);


		Elements nextTurnsv1 = document1.select(".bomTable td:eq(3)");
		//System.out.println("================VERSION2======================");
		for (Element nextTurn : nextTurnsv1) {
			//	System.out.println(nextTurn.text());
			listTwov.add(nextTurn.text());                           //list2v
		}
		//System.out.println("TOTAL New BOM versions:"+listTwov.size());
		ArrayList<String> newlistTwov = new ArrayList<String>(listTwov);
		String[] old1v = (String[]) newlistTwov.toArray(new String[newlistTwov.size()]);

		Elements newshipstatus ;
		//play
		if (text1.equalsIgnoreCase("External IDs")) {
			newshipstatus = document1.select(".bomTable td:eq(9)");
		}else{
			newshipstatus = document1.select(".bomTable td:eq(8)");
		}

		//System.out.println("================Ship status new======================");
		for (Element nextTurn : newshipstatus) {
			//	System.out.println(nextTurn.text());
			listTwos.add(nextTurn.text());                           //list1v
		}
		//System.out.println("TOTAL OLD BOM shipstatus:"+listOnes.size());
		ArrayList<String> newlistTwos = new ArrayList<String>(listTwos);
		String[] olds1 = newlistTwos.toArray(new String[newlistTwos.size()]);
		//play


		if ( old1.length ==  old1v.length) {
			for (int i = 0; i < old1.length; i++) {
				old1[i] = old1[i]+" ="+old1v[i]+" ="+olds1[i];//<---------- computation
			}
			List<String> ewlistTwo = (List<String>) Arrays.asList(old1);
			/*Titlenew = Titlenew+"_Unspecified";
		ewlistTwo.remove(Titlenew);*/
			finallistnew.addAll(ewlistTwo);                           //list 2 final
		} else {
			System.out.println("size mismatch 2nd");
		}
		/**Author Dinesh R_24_oct_2016*/

		//========================================================temp======================================================


		Collection<String> tempOLD = new ArrayList<String>(finallistold);
		Collection<String> tempNEW = new ArrayList<String>(finallistnew);

		//Collection<String> tempOLD1 = new ArrayList<String>(finallistold);
		Collection<String> tempcommoninnew = new ArrayList<String>(finallistnew);

		tempOLD.removeAll(finallistnew);   //removed
		tempNEW.removeAll(finallistold);   //newly added
		tempcommoninnew.retainAll(finallistold);   //retains
		//System.out.println(Arrays.toString(tempNEW.toArray()));
		//System.out.println(Arrays.toString(tempOLD.toArray()));

		int othersize =0;
		for (String string : tempcommoninnew) {
			if(string.contains("Used at development time")){
				othersize = othersize +1;
			}

		}
		for (String string : tempNEW) {
			if(string.contains("Used at development time")){
				othersize = othersize +1;
			}

		}
		//System.out.println("Total Other developments:"+othersize);




		//6/23/2017===================================same component but different version
		//System.out.println("========================testoldbom=================================");
		System.out.println("");
		String data[] = new String[3];
		HashMap<String, String> cv = new HashMap<String, String>();
		HashMap<String, String> cvs = new HashMap<String, String>();


		for (String string : finallistold) {
			string = string.replace(" ", "");
			data = string.split("="); 
			cv.put(data[0], data[1]);
			cvs.put(data[0]+data[1],data[2]);
			//System.out.printf("%s%n",string);
		}

		//System.out.println("========================testoldbom=================================");


		//System.out.println("========================testnewbom=================================");
		String data1[] = new String[3];
		HashMap<String, String> cv1= new HashMap<String, String>();
		HashMap<String, String> cvs1 = new HashMap<String, String>();


		for (String string : finallistnew) {
			string = string.replace(" ","");
			data1 = string.split("=");
			cv1.put(data1[0], data1[1]);
			cvs1.put(data1[0]+data1[1],data1[2]);
			//System.out.printf("%s%n",string);
		}


		System.out.println("===========Same components having different versions============");
		System.out.println("Common components having different versions in both BOMs");
		System.out.println("along with their shipment status");
		System.out.println("================================================================");

		//==========computation
		int t = 0,counter=0;
		
		

		for (HashMap.Entry<String, String> entryold : cv.entrySet())
		{
			for (HashMap.Entry<String, String> entrnew : cv1.entrySet())
			{
				if(entryold.getKey().equals(entrnew.getKey()) && !(entryold.getValue().equals(entrnew.getValue()))){
					t=t+1;
					//System.out.println(t+":");
					//System.out.println("oldbom-> "+entryold.getKey()+" ,"+entryold.getValue()+" ,"+cvs.get(entryold.getKey()+entryold.getValue()));
					//System.out.println("newbom-> "+entrnew.getKey()+" ,"+entrnew.getValue()+" ,"+cvs1.get(entrnew.getKey()+entrnew.getValue()));
					/*//excel sheet title ends
					row = sheet.createRow(rowCount++);//<----------------------------------------------------------------------- create row
					columnCount = 0;
					cell = row.createCell(columnCount++);
					cell.setCellValue("Common components having different versions in both BOMs");
					cell.setCellStyle(stylecolor);
					sheet.addMergedRegion(new CellRangeAddress(
				            0, //first row (0-based)
				            0, //last row  (0-based)
				            0, //first column (0-based)
				            3  //last column  (0-based)
				    ));
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					columnCount = 0;
					//excel sheet title ends
*/

					row = sheet.createRow(rowCount++); //<------------------------------------------------------create row
					for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
						cell = row.createCell(f); //<-----------------------------------------------------------create cell
						switch (f) {
						case 0://list

							cell.setCellValue("old"); //<----------------set cell value
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 1://list1
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entryold.getKey());
							//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 2://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entryold.getValue());
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 3://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) cvs.get(entryold.getKey()+entryold.getValue()));
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;

						default:
							break;
						}
					}
					row = sheet.createRow(rowCount++); //<------------------------------------------------------create row

					for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
						cell = row.createCell(f); //<-----------------------------------------------------------create cell
						switch (f) {
						case 0://list

							cell.setCellValue("new"); //<----------------set cell value
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 1://list1
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entrnew.getKey());
							//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 2://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entrnew.getValue());
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 3://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) cvs1.get(entrnew.getKey()+entrnew.getValue()));
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;

						default:
							break;
						}
					}	
					row = sheet.createRow(rowCount++);
				}
				if((!(entryold.getKey().equals(entrnew.getKey())) && (entryold.getKey().equalsIgnoreCase(entrnew.getKey()))) && !(entryold.getValue().equals(entrnew.getValue()))){
					t=t+1;
					//System.out.println(t+":");
					//System.out.println("oldbom-> "+entryold.getKey()+" ,"+entryold.getValue()+" ,"+cvs.get(entryold.getKey()+entryold.getValue()));
					//System.out.println("newbom-> "+entrnew.getKey()+" ,"+entrnew.getValue()+" ,"+cvs1.get(entrnew.getKey()+entrnew.getValue()));
					row = sheet.createRow(rowCount++); //<-------------------------------------------------------create row
					for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
						cell = row.createCell(f); //<------------------------------------------------------------create cell
						switch (f) {
						case 0://list

							cell.setCellValue(String.valueOf("old")); //<----------------set cell value
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 1://list1
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entryold.getKey());
							//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 2://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entryold.getValue());
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 3://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) cvs.get(entryold.getKey()+entryold.getValue()));
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;

						default:
							break;
						}
					}
					row = sheet.createRow(rowCount++); //<---------------------------------------------------------create row

					for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
						cell = row.createCell(f); //<-------------------------------------------------------------create cell
						switch (f) {
						case 0://list

							cell.setCellValue(String.valueOf("new")); //<----------------set cell value
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 1://list1
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entrnew.getKey());
							//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 2://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) entrnew.getValue());
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;
						case 3://list2
							//cell = row.createCell(columnCount++);
							cell.setCellValue((String) cvs1.get(entrnew.getKey()+entrnew.getValue()));
							CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
							break;

						default:
							break;
						}
					}	
					row = sheet.createRow(rowCount++);
				}
			}
		}
		if(t==0){
			row = sheet.createRow(rowCount++); //<---------------------------------------------------------------create row
			for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
				cell = row.createCell(f); //<----------------------------------------------------------------create cell
				switch (f) {
				case 0://list

					cell.setCellValue(0); //<----------------set cell value
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 1://list1
					//cell = row.createCell(columnCount++);
					cell.setCellValue("No such components present");
					//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 2://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue("NA");
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 3://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue("NA");
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;

				default:
					break;
				}
		}
			}

		System.out.printf("%n%n%n");
		//==========computation


		//6/23/2017===================================same component but different version





		System.out.println("========================Newly Added=================================");
		System.out.printf("Newly Added: %s (Excluding Project name)  %nNewly Added: %s (including project name)%n",tempNEW.size()-1 , tempNEW.size());
		System.out.println("NOTE: Project name is included in below list & duplicates are removed");
		System.out.println("====================================================================");
		System.out.printf("(s.no, component_name ,component_version ,shipping_status):%n");
		XSSFSheet sheetnew = workbook.createSheet("Newly Added"); //<----------------------------------------------- create sheet
		rowCount=0;
		columnCount = 0;
		//excel sheet title ends
				row = sheetnew.createRow(rowCount++);//<----------------------------------------------------------------------- create row
				columnCount = 0;
				cell = row.createCell(columnCount++);
				cell.setCellValue("Newly added components - includes 1 project name");
				cell.setCellStyle(stylecolor);
				sheetnew.addMergedRegion(new CellRangeAddress(
			            0, //first row (0-based)
			            0, //last row  (0-based)
			            0, //first column (0-based)
			            3  //last column  (0-based)
			    ));
				CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
				columnCount = 0;
				//excel sheet title ends
		
		
		
		rownew = sheetnew.createRow(rowCount++);//<----------------------------------------------------------------------- create row
		Cell cellnew = null;
		for (String h : head) {
			cellnew = rownew.createCell(columnCount++); //<--------------------------------------------------------------- create cell
			cellnew.setCellValue((String) h); //<---------------------------------------------------------------------- set value to the cell
			cellnew.setCellStyle(stylehead);
			CellUtil.setAlignment(cellnew, HorizontalAlignment.CENTER);
		}

		int x = 0, y = 0, Z=0;
		//==remove duplicates
		//List<String> al = new ArrayList<>();
		// add elements to al, including duplicates
		Set<String> hsnew = new HashSet<>();
		hsnew.addAll(tempNEW);
		tempNEW.clear();
		tempNEW.addAll(hsnew);
		ArrayList<String> tempn = new ArrayList<String>(tempNEW);
		Collections.sort(tempn); 
		//==remove duplicates
		String part[] = new String[3];
		for (String string : tempn) {
			//System.out.printf("%s 		%s%n",++x, string.replaceAll("=",","));

			part = string.split("=");

			rownew = sheetnew.createRow(rowCount++); //<---------------------------------------------------------------create row

			for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
				cell = rownew.createCell(f); //<----------------------------------------------------------------create cell
				switch (f) {
				case 0://list

					cell.setCellValue(String.valueOf(++x)); //<----------------set cell value
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 1://list1
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) part[0]);
					//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 2://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) part[1]);
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 3://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) part[2]);
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;

				default:
					break;
				}
			}	
			//rownew = sheetnew.createRow(rowCount++);

		}
		System.out.println("");
		System.out.println("");
		System.out.println("");
		System.out.println("=========================Removed================================");
		System.out.printf("Removed components: %s (Excluding Project name)%nRemoved components: %s (including Project name)%n", tempOLD.size()-1, tempOLD.size());
		System.out.println("NOTE: Project name is included in below list & duplicates are removed");
		System.out.println("================================================================");
		System.out.printf("(s.no, component_name ,component_version ,shipping_status):%n");
		XSSFSheet sheetremoved = workbook.createSheet("Removed"); //<----------------------------------------------- create sheet
		rowCount=0;
		columnCount = 0;
		//excel sheet title ends
				row = sheetremoved.createRow(rowCount++);//<----------------------------------------------------------------------- create row
				columnCount = 0;
				cell = row.createCell(columnCount++);
				cell.setCellValue("Removed components - includes 1 project name");
				cell.setCellStyle(stylecolor);
				sheetremoved.addMergedRegion(new CellRangeAddress(
			            0, //first row (0-based)
			            0, //last row  (0-based)
			            0, //first column (0-based)
			            3  //last column  (0-based)
			    ));
				CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
				columnCount = 0;
				//excel sheet title ends
		rowremoved = sheetremoved.createRow(rowCount++);//<----------------------------------------------------------------------- create row
		Cell cellremoved = null;
		for (String h : head) {
			cellremoved = rowremoved.createCell(columnCount++); //<--------------------------------------------------------------- create cell
			cellremoved.setCellValue((String) h); //<---------------------------------------------------------------------- set value to the cell
			cellremoved.setCellStyle(stylehead); 
			CellUtil.setAlignment(cellremoved, HorizontalAlignment.CENTER);
		}
		//==remove duplicates
		//List<String> al = new ArrayList<>();
		// add elements to al, including duplicates
		Set<String> hs = new HashSet<>();
		hs.addAll(tempOLD);
		tempOLD.clear();
		tempOLD.addAll(hs);
		ArrayList<String> tempo = new ArrayList<String>(tempOLD);
		Collections.sort(tempo); 
		//==remove duplicates
		String partremoved[] = new String[3];
		for (String string : tempo) {
			//System.out.printf("%s 		%s%n",++y, string.replaceAll("=",","));

			partremoved = string.split("=");
			rowremoved = sheetremoved.createRow(rowCount++); //<---------------------------------------------------------------create row

			for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
				cell = rowremoved.createCell(f); //<----------------------------------------------------------------create cell
				switch (f) {
				case 0://list

					cell.setCellValue(String.valueOf(++y)); //<----------------set cell value
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 1://list1
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partremoved[0]);
					//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 2://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partremoved[1]);
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 3://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partremoved[2]);
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;

				default:
					break;
				}
			}	
			//rowremoved = sheetremoved.createRow(rowCount++);

		}
		System.out.println("");
		System.out.println("");
		System.out.println("");
		System.out.println("==========================Common================================");
		System.out.println("Common components having same versions in both BOMs");
		System.out.println("with same shipment status. Here duplicates are removed");
		//System.out.println("");
		System.out.printf("Total such components: %s %n",  tempcommoninnew.size());
		System.out.println("================================================================");
		System.out.printf("(s.no, component_name ,component_version ,shipping_status):%n");
		XSSFSheet sheetcommon = workbook.createSheet("same_comp_same_version"); //<----------------------------------------------- create sheet
		rowCount=0;
		columnCount = 0;
		//excel sheet title ends
				row = sheetcommon.createRow(rowCount++);//<----------------------------------------------------------------------- create row
				columnCount = 0;
				cell = row.createCell(columnCount++);
				cell.setCellValue("Common components having same versions in both BOMs");
				cell.setCellStyle(stylecolor);
				sheetcommon.addMergedRegion(new CellRangeAddress(
			            0, //first row (0-based)
			            0, //last row  (0-based)
			            0, //first column (0-based)
			            3  //last column  (0-based)
			    ));
				CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
				columnCount = 0;
				//excel sheet title ends
		rowcommon = sheetcommon.createRow(rowCount++);//<----------------------------------------------------------------------- create row
		Cell cellCommon = null;
		for (String h : head) {
			cellCommon = rowcommon.createCell(columnCount++); //<--------------------------------------------------------------- create cell
			cellCommon.setCellValue((String) h); //<---------------------------------------------------------------------- set value to the cell
			cellCommon.setCellStyle(stylehead); 
			CellUtil.setAlignment(cellCommon, HorizontalAlignment.CENTER);
		}
		//==remove duplicates
		//List<String> al = new ArrayList<>();
		// add elements to al, including duplicates
		Set<String> hscommon = new HashSet<>();
		hscommon.addAll(tempcommoninnew);
		tempcommoninnew.clear();
		tempcommoninnew.addAll(hscommon);
		ArrayList<String> tempc = new ArrayList<String>(tempcommoninnew);
		Collections.sort(tempc);
		//==remove duplicates
		String partcommon[] = new String[3];
		for (String string : tempc) {
			//System.out.printf("%s 		%s%n",++Z, string.replaceAll("=",","));
			partcommon = string.split("=");
			rowcommon = sheetcommon.createRow(rowCount++); //<---------------------------------------------------------------create row

			for (int f = 0; f < head.size(); f++) { //0 sno,1 name,2 version,3 usage
				cell = rowcommon.createCell(f); //<----------------------------------------------------------------create cell
				switch (f) {
				case 0://list

					cell.setCellValue(String.valueOf(++Z)); //<----------------set cell value for sno
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 1://list1
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partcommon[0]); //<----------------set cell value for name
					//CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 2://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partcommon[1]); //<----------------set cell value for version
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;
				case 3://list2
					//cell = row.createCell(columnCount++);
					cell.setCellValue((String) partcommon[2]); //<----------------set cell value for usage
					CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
					break;

				default:
					break;
				}
			}	
			//rowcommon = sheetcommon.createRow(rowCount++);
		}

		int total = tempcommoninnew.size() + (tempNEW.size()-1) - othersize;
		System.out.println();

		System.out.println("================================================================");
		System.out.println("Total Other developments(not shipped)    : "+othersize);
		System.out.println("Total bom in obligations report should be: "+total);
		System.out.println("================================================================");
		XSSFSheet sheetother = workbook.createSheet("Overall Details"); //<---------------------------------- create sheet
		
		rowCount=0;
		columnCount = 0;
		rowcommon = sheetother.createRow(rowCount++);//<----------------------------------------------------- create row
		cell = rowcommon.createCell(columnCount++); //<----------------------------------------------------- create cell
		cell.setCellValue("Total components in old BOM");//<----------------------------------------------------- Set value to cell
		cell.setCellStyle(stylebold);
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue(listOne.size()+"");
		cell.setCellStyle(stylebold);
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		
		rowcommon = sheetother.createRow(rowCount++);//<------------------------------------------------------ create row
		columnCount = 0;
		cell = rowcommon.createCell(columnCount++); //<----------------------------------------------------- create cell
		cell.setCellValue("Total components in new BOM");//<----------------------------------------------------- Set value to cell
		cell.setCellStyle(stylebold);
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue(listTwo.size()+"");
		cell.setCellStyle(stylebold);
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		
		rowcommon = sheetother.createRow(rowCount++);//<------------------------------------------------------ create row
		columnCount = 0;
		cell = rowcommon.createCell(columnCount++); //<----------------------------------------------------- create cell
		cell.setCellValue("NOTE: For correct output always drag & drop old html report first");//<-------------- Set value to cell
		cell.setCellStyle(stylecolor);
		
		
		
		rowcommon = sheetother.createRow(rowCount++);//<-------------------------------------------------------- create row
		columnCount = 0;
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue("Total Newly added");//Total Newly added (including project name)
		//cell.setCellStyle(stylebold);
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue((tempNEW.size()-1)+"");
		cell.setCellStyle(stylebold);
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		
		
		rowcommon = sheetother.createRow(rowCount++);//<--------------------------------------------------------- create row
		columnCount = 0;
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue("Total Removed");//Total Removed (including project name )
		//cell.setCellStyle(stylebold);
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue((tempOLD.size()-1)+"");
		cell.setCellStyle(stylebold);
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		
		rowcommon = sheetother.createRow(rowCount++);//<---------------------------------------------------------- create row
		columnCount = 0;
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue("Total Common components with same version");
		//cell.setCellStyle(stylebold);
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue(tempcommoninnew.size()+"");
		cell.setCellStyle(stylebold);
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		
		rowcommon = sheetother.createRow(rowCount++);//<----------------------------------------------------------- create row
		columnCount = 0;
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue("Total Other developments(not shipped)");
		//cell.setCellStyle(stylebold);
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue(othersize+"");
		cell.setCellStyle(stylebold);
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		
		rowcommon = sheetother.createRow(rowCount++);//<------------------------------------------------------------create row
		columnCount = 0;
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue("Total components in obligations report should be: ");
		//cell.setCellStyle(stylebold);
		cell = rowcommon.createCell(columnCount++);
		cell.setCellValue(total+"");
		cell.setCellStyle(stylebold);
		CellUtil.setAlignment(cell, HorizontalAlignment.CENTER);
		
		
		
		sheetother.autoSizeColumn(0);
		sheetother.autoSizeColumn(1);
		
		sheet.autoSizeColumn(0);
		sheet.autoSizeColumn(1);
		sheet.autoSizeColumn(2);
		sheet.autoSizeColumn(3);
		sheetnew.autoSizeColumn(0);
		sheetnew.autoSizeColumn(1);
		sheetnew.autoSizeColumn(2);
		sheetnew.autoSizeColumn(3);
		sheetremoved.autoSizeColumn(0);
		sheetremoved.autoSizeColumn(1);
		sheetremoved.autoSizeColumn(2);
		sheetremoved.autoSizeColumn(3);
		sheetcommon.autoSizeColumn(0);
		sheetcommon.autoSizeColumn(1);
		sheetcommon.autoSizeColumn(2);
		sheetcommon.autoSizeColumn(3);
	
		if(t!=0){
			sheet.setAutoFilter(new CellRangeAddress(1,t*2+(t-1) , 0, 3)); //(Total comp + gaps)
		}
		sheetnew.setAutoFilter(new CellRangeAddress(1,tempn.size() , 0, 3));
		sheetremoved.setAutoFilter(new CellRangeAddress(1,tempo.size() , 0, 3));
		if (tempc.size()>0) {
			sheetcommon.setAutoFilter(new CellRangeAddress(1,tempc.size() , 0, 3));
		} else {
			sheetcommon.setAutoFilter(new CellRangeAddress(1,2 , 0, 3));
		}
		
		workbook.setSheetOrder("Overall Details", 0);//
		workbook.setSheetOrder("Same_comps_different_versions", 4);
		workbook.setActiveSheet(0);
		workbook.write(outputStream);
		outputStream.close();
		workbook.close();

		JOptionPane.showMessageDialog (null, "Check output in "+ outputFilePath, "Info", JOptionPane.INFORMATION_MESSAGE);
		Desktop.getDesktop().open(new File(outputFilePath));

	}
	public static String pathComponent(String filename) {
		int i = filename.lastIndexOf(File.separator);
		return (i > -1) ? filename.substring(0, i) : filename;
	}
}
/**
 * Author Dinesh R_24_oct_2016
 * 
 * */