java - 比较不同文件(CSV、Excel)中两列的日期 Java Apache commons 和 poi 以了解日期的相似之处
问题描述
我有一个 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();
}
}
您还想了解什么?请问我!我将非常感谢
解决方案
可以通过重新格式化和比较String
s 本身或将这些String
s 解析为LocalDate
s 并比较它们来比较日期是否相等。
第一个选项是重新格式化一个String
以使其具有与另一个相同的格式,但都保持String
s。
第二种可能性是从两者中创建一个现代时间表示,并通过该表示的内置比较来比较它们。
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);
}
}
请阅读代码中的所有注释,因为它们是对代码功能的解释。
推荐阅读
- kubernetes - 来自 StatefulSet 的 Pod 卡在 ContainerCreating 状态 - FailedCreatePodSandBox
- php - PHP - 无法为 PayPal REST API 设置送货地址
- android - 如何创建导航标签栏每个标签都有每个堆栈历史记录
- sql - 滥用聚合:COUNT()
- c# - 如何在 DataGridView 中编辑通过 DataRelation 添加的父列?
- r - 如何计算来自csv的数据组的RMSE
- autodesk-forge - 基于 dbID 创建传感器覆盖的问题
- sql - 使用分组集返回 0 总计
- android - 保护 SD 卡并通过应用程序访问它
- haskell - 泛型 - 创建组合所有字段的记录产品