java - 比较不同文件(CSV，Excel)Java Apache commons 和 poi 中两列的日期以了解日期相似的地方

Question

我有一个 Excel 和一个 CSV 文件。这两个文件的第一列包含日期。

我想比较这些日期，看看它们在哪里相同，就像在大 excel 文件中一样。但是我对这两个文件之间的日期格式差异有疑问。

我只是想让 java 知道这些日期在哪里相似，所以我可以稍后使用导出的 CSV 文件中的值更新 excel 文件。

两个文件的链接: https://1drv.ms/f/s!AphATk_r_LQkl0HBC2r_9nt_AYIg

我的代码:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CsvToExcelConverter {

    public static final String SAMPLE_XLSX_FILE_PATH = "C:/Users/blawand/Desktop/CSV_to_Excel/export-excel-test.xlsx";
    public static final String SAMPLE_CSV_FILE_PATH = "C:/Users/blawand/Desktop/CSV_to_Excel/export-csv-test.csv";
    public static List<String> dates_csv = new ArrayList<>();
    public static List<String> dates_excel = new ArrayList<>();

    public static void main(String[] args) throws IOException, InvalidFormatException {

        try (Reader reader = Files.newBufferedReader(Paths.get(SAMPLE_CSV_FILE_PATH));
                CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT);) {

            for (CSVRecord csvRecord : csvParser) {
                // Accessing Values by Column Index
                String name = csvRecord.get(0);
                dates_csv.add(name);
            }

            dates_csv.remove(0);
        }

        FileInputStream fsIP = new FileInputStream(new File(SAMPLE_XLSX_FILE_PATH));

        /*
         * ================================================================== 
         * Iterating
         * over all the rows and columns in a Sheet (Multiple ways)
         * ==================================================================
         */

        // Getting the Sheet at index zero
        XSSFWorkbook workbook = new XSSFWorkbook(fsIP);
        XSSFSheet sheet = workbook.getSheetAt(0);
        DataFormatter dataFormatter = new DataFormatter();

        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                Cell cell = row.getCell(0); // getColumn(0)
                if (cell != null) {
                    // Found column and there is value in the cell.
                    // String cellValueMaybeNull = cell.getStringCellValue();
                    String cellValueMaybeNull = dataFormatter.formatCellValue(cell);

                    // String to number set
                    dates_excel.add(cellValueMaybeNull);

                }
            }
        }

        System.out.println(dates_csv);
        System.out.println(dates_csv.size());
        System.out.println(dates_excel);
        System.out.println(dates_excel.size());

        while (dates_excel == dates_excel) {
            System.out.println("Yes");
            break;
        }

        fsIP.close();
        FileOutputStream output_file = new FileOutputStream(new File(SAMPLE_XLSX_FILE_PATH));
        workbook.write(output_file);
        output_file.close();

    }
}

你还想知道什么？请问我!我将不胜感激

Answer 1

比较日期是否相等可以通过重新格式化和比较 String 本身或将那些 String 解析为 LocalDate 和比较那些。

第一个选项是重新格式化一个 String 以使其具有与另一个相同的格式，但两者都保留为 String。

第二种可能性是从两者中创建一个现代表示，并通过该表示的内置比较来比较它们。

public class TryOutClass {

    /**
     * Compares two dates and checks if they are equal using {@link String} comparison.
     * <p>
     * <strong>Important note:</strong><br>
     * This assumes the first date given is of the format "EEE, dd/MM/yy" and 
     * the second one is of the format "dd.MM.yyyy".
     * </p>
     * 
     * @param dateOne the first date to be compared to the second
     * @param dateTwo the second date to be compared to the first
     * @return <code>true</code> if the dates are equal, otherwise <code>false</code>
     */
    public static boolean datesEqual(String dateOne, String dateTwo) {
        // cut off the leading weekday abbreviation
        String processedDateOne = dateOne.substring(4, 12);

        // split the String by its delimiter (/)
        String[] temp = processedDateOne.split("/");

        // concatenate the parts to fit the format of the second date
        String comparableDateOne = temp[0] + "." + temp[1] + ".20" + temp[2];

        // return the result of the String comparison
        return comparableDateOne.equals(dateTwo);
    }

    /**
     * Compares two dates and checks if they are equal using the comparison of {@link LocalDate}.
     * <p>
     * <strong>Important note:</strong><br>
     * This assumes the first date given is of the format "EEE, dd/MM/yy" and 
     * the second one is of the format "dd.MM.yyyy".
     * </p>
     * 
     * @param dateOne the first date to be compared to the second
     * @param dateTwo the second date to be compared to the first
     * @return <code>true</code> if the dates are equal, otherwise <code>false</code>
     */
    public static boolean equalDates(String dateOne, String dateTwo) {
        // define two formattings
        DateTimeFormatter firstFormatter = DateTimeFormatter.ofPattern("dd/MM/yy");
        DateTimeFormatter secondFormatter = DateTimeFormatter.ofPattern("dd.MM.yyyy");

        // create two LocalDates, the first one still needs to have the weekday abbreviation cut off
        LocalDate firstDate = LocalDate.parse(dateOne.substring(4, 12), firstFormatter);
        LocalDate secondDate = LocalDate.parse(dateTwo, secondFormatter);

        // return the result of the LocalDate comparison
        return firstDate.equals(secondDate);
    }

    /**
     * The main method that just executes both possibilities 
     * of comparing differently formatted date {@link String}s
     * @param args (unused) command line arguments
     */
    public static void main(String[] args) {
        String dateOne = "Wed 01/08/18";
        String dateTwo = "01.08.2018";

        System.out.println("#### datesEqual ####");
        String datesEqual = datesEqual(dateOne, dateTwo) ?
                "The dates are equal" : "The dates differ";
        System.out.println(datesEqual);

        System.out.println("#### equalDates ####");
        String equalDates = equalDates(dateOne, dateTwo) ?
                "The dates are equal" : "The dates differ";
        System.out.println(equalDates);
    }
}

Please read all the comments in the code as they are the explanation of what the code does.