DataCompare

package excel;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

public class DataCompare {
    private final static String excel2003L = ".xls";    //2003以下版本的excel
    private final static String excel2007U = ".xlsx";   //2007以上版本的excel
    private static FormulaEvaluator evaluator;
    //excel compare excel
    public static void ExcelCompareExcel(String path1, String path2) throws Exception
    {
        try{
            int index = 0;
            File file1 = new File(path1);
            Workbook work1 = getWorkbook(file1);
            Sheet sheet1 = GetSheet(index, work1);
            evaluator=work1.getCreationHelper().createFormulaEvaluator();

            File file2 = new File(path2);
            Workbook work2 = getWorkbook(file2);
            Sheet sheet2 = GetSheet(index, work2);

            //遍历当前sheet中的所有行
            int startRow1 = 0;
            Row row1 = null;
            Cell cell1 = null;

            int startRow2 = 0;
            Row row2 = null;
            Cell cell2 = null;

            for (int i = startRow1; i <= sheet1.getLastRowNum(); i++)
            {
                row1 = sheet1.getRow(i);
                if (null == row1)
                {
                    System.out.println("path="+path1+",row="+i+" is null");
                    continue;
                }

                if (i <= sheet2.getLastRowNum())
                {
                    row2 = sheet2.getRow(i);
                    if (null == row2)
                    {
                        System.out.println("path="+path2+",row="+i+" is null");
                        continue;
                    }
                }

                //遍历所有的列
                for (int y = row1.getFirstCellNum(); y < row1.getLastCellNum(); y++)
                {
                    cell1 = row1.getCell(y);
                    if (y < row2.getLastCellNum())
                    {
                        cell2 = row2.getCell(y);
                    }

                    if (null == cell1)
                    {
                        System.out.println("path="+path1+",row="+i+",cell="+y+" is null");
                        continue;
                    }

                    if (null == cell2)
                    {
                        System.out.println("path="+path2+",row="+i+",cell="+y+" is null");
                        continue;
                    }

                    //compare value
                    if (!getCellValue(cell1).equals(getCellValue(cell2)))
                    {
                        SetHighLight(work1, cell1);
                        SetHighLight(work2, cell2);
                    }
                }
            }

            WriteBackStyle(path1, work1);
            WriteBackStyle(path2, work2);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void WriteBackStyle(String path, Workbook work) throws IOException {
        try{
            FileOutputStream fileOut = null;
            fileOut = new FileOutputStream(path);
            work.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static void SetHighLight(Workbook work, Cell cell) {
        CellStyle cellStyle = work.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); // 前景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle); // 设置单元格样式
    }

    public static Workbook getWorkbook(File file) throws Exception {
        Workbook wb = null;
        String fileType = file.getName().substring(file.getName().lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(new FileInputStream(file));  //2003以下
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(new FileInputStream(file));  //2007以上
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    private static Sheet GetSheet(int index, Workbook work) throws Exception
    {
        if (null == work) {
            throw new Exception("Excel为空！");
        }

        // 获取需要的excel工作簿
        Sheet sheet = work.getSheetAt(index);
        if (sheet == null) {
            throw new Exception("Excel工作薄为空！");
        }
        return sheet;
    }

    public static Object getCellValue(Cell cell)
    {
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
        Object value = null;
        switch (cell.getCellTypeEnum()) { // 不同的数据类型
            case STRING:
                value = cell.getStringCellValue();
                break; // 字符串类型
            case NUMERIC:
                //double numericCellValue2 = cell.getNumericCellValue();
                //double numericCellValue = numericCellValue2;
                //value = numericCellValue;
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    value = df2.format(cell.getNumericCellValue());
                }
                break; // 数值类型
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break; // 布尔类型
            case FORMULA:
                CellValue cellValue = evaluator.evaluate(cell);
                boolean isNumeric = cellValue.getCellTypeEnum() == CellType.NUMERIC;
                value = (isNumeric) ? cellValue.getNumberValue() : cellValue.getStringValue();
                if (isNumeric && value.toString().equals("0.0")) {
                    value = cell.getNumericCellValue();
                }
                break; // 公式类型
            case _NONE:
                break; // 未知类型
            case ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break; // 错误类型
            case BLANK:
                break; // 空白类型
        }

        if(null == value)
        {
            value = "";
        }

        if (value.equals("") || value == null || null==cell)
        {
            value = "";
        }
        return value;
    }


    //csv compare csv
    public static void CsvCompareCsv(String path1, String path2) throws Exception
    {
        try{
            BufferedReader reader1 = new BufferedReader(new FileReader(path1));
            //reader.readLine();//第一行信息，为标题信息，不用，如果需要，注释掉

            BufferedReader reader2 = new BufferedReader(new FileReader(path2));
            //reader.readLine();

            while (true) {
                String line1 = reader1.readLine();
                String line2 = reader2.readLine();
                if (null != line1 && null!=line2){
                    String[] item1 = line1.split(",");
                    String[] item2 = line2.split(",");
                    int itemLength = item1.length>item2.length ? item2.length:item1.length;
                    for (int i=0; i<itemLength; ++i)
                    {
                        if (item1[i].equals(item2[i])){
                            //set hight light
                        }
                    }
                    if (item1.length != item2.length)
                    {
                        System.out.println("warning:length not equal");
                    }
                }
                else
                {
                    System.out.println("warning:csv end");
                    break;
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    //excel compare csv
    public static void ExcelCompareCsv(String excelPath, String csvPath) throws Exception
    {
        try{
            int index = 0;
            File excelFile = new File(excelPath);
            Workbook excelWork = getWorkbook(excelFile);
            Sheet sheet1 = GetSheet(index, excelWork);
            evaluator=excelWork.getCreationHelper().createFormulaEvaluator();

            BufferedReader reader1 = new BufferedReader(new FileReader(csvPath));

            //遍历当前sheet中的所有行
            int startRow1 = 0;
            Row row1 = null;
            Cell cell1 = null;

            for (int i = startRow1; i <= sheet1.getLastRowNum(); i++)
            {
                row1 = sheet1.getRow(i);
                if (null == row1)
                {
                    System.out.println("path="+excelPath+",row="+i+" is null");
                    continue;
                }

                String line1 = reader1.readLine();
                if (null != line1)
                {
                    String[] item1 = line1.split(",");
                    int itemLength = item1.length;
                    for (int y = row1.getFirstCellNum(); y < row1.getLastCellNum(); y++)
                    {
                        cell1 = row1.getCell(y);
                        if (null == cell1)
                        {
                            System.out.println("path="+excelPath+",row="+i+",cell="+y+" is null");
                            continue;
                        }

                        if (y < itemLength)
                        {
                            if (!getCellValue(cell1).equals(item1[y]))
                            {
                                SetHighLight(excelWork, cell1);
                            }
                        }
                    }
                }
                else
                {
                    System.out.println("warning:csv end");
                    break;
                }
            }

            WriteBackStyle(excelPath, excelWork);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    //txt compare txt
    public static void main(String argv[]) throws Exception {
        String excelPath1= "E:\\work\\javaProject\\ExcelCompare\\src\\main\\java\\excel\\data_org1.xlsx";
        String excelPath2= "E:\\work\\javaProject\\ExcelCompare\\src\\main\\java\\excel\\data_org2.xlsx";
        String csvPath1 = "E:\\work\\javaProject\\ExcelCompare\\src\\main\\java\\excel\\data_new.csv";;
        String csvPath2 = "E:\\work\\javaProject\\ExcelCompare\\src\\main\\java\\excel\\data_new2.csv";;
        ExcelCompareExcel(excelPath1, excelPath2);
        CsvCompareCsv(csvPath1, csvPath2);
        ExcelCompareCsv(excelPath1, csvPath1);
    }
}