opencsv

简述

java读取csv的类库主要有两种，opencsv和javacsv，研究发现，javacsv在2014-12-10就不维护了。opencsv是apache的项目，并且至今仍在维护，所以项目中决定使用opencsv。

针对于maven 项目依赖 pom.xml 引入jar 如下：

<dependency>
  <groupId>com.opencsv</groupId>
  <artifactId>opencsv</artifactId>
  <version>5.4</version>
</dependency>

 csv文件，全名 comma separated values，默认以逗号分隔，是纯文本文件。可以用记事本或者nodepad 打开看原始的文件内容。

例如：

name,age,birthday

jan,18,2002-09-09

macel,20,2001-08-09

读取方式：

• 逐行读取：

//read line by line
reader = new CSVReader(new FileReader("employees.csv"));
String[] nextLine;
while ((nextLine = reader.readNext()) != null) {
    System.out.println(nextLine[0] + "," + nextLine[1] + "," + nextLine[2] + "," + nextLine[3]);
}
reader.close();

• 全部读取：

CSVReader reader = new CSVReader(new FileReader("employees.csv"));
List<String[]> rows = reader.readAll();
for (String[] row : rows) {
    System.out.println(row[0] + "," + row[1] + "," + row[2] + "," + row[3]);
}

• 转换Java object ：

先写一个java类，例如：

public class Employee {
    public String firstName;
    public String lastName;
    public String email;
    public String department;


    //getters and setters

    @Override
    public String toString() {
        return "Employee{" +
                "firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", department='" + department + '\'' +
                '}';
    }
}

List<Employee> employees = new CsvToBeanBuilder(new FileReader("employees.csv"))
        .withType(Employee.class)
        .build()
        .parse();
System.out.println(employees);

注解形式：

 @CsvBindByPosition  //该注解通过csv标题的位置打印数据

@CsvBindAndSplitByName(column = "Email"） //文件中标题的名字

@CsvNumber("###.##")  //针对于数值的验证输出

@CsvDate("yyyy-MM-dd") //日期格式的转换

等等

public class Employee {
    public String firstName;
    public String lastName;
    @CsvBindByName(column = "Email")
    public String contact;
...
...
}

写入方式 :

• 逐行写入

CSVWriter csvWriter = new CSVWriter(new FileWriter("list.csv"));
csvWriter.writeNext(new String[]{"John", "Doe", "jdoe@example.com", "Sales"});
csvWriter.writeNext(new String[]{"Jane", "Doe", "jane.doe@example.com", "HR"});
csvWriter.close();

• 全部写入：

List<String[]> lines = getRecordsAsList();
csvWriter = new CSVWriter(new FileWriter("list.csv"));
csvWriter.writeAll(lines);
csvWriter.close();

• 按sql查询写入：

csvWriter = new CSVWriter(new FileWriter("list.csv"));
Connection connection = DriverManager.getConnection("");
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery("select * from employees");
csvWriter.writeAll(resultSet, true);
csvWriter.close();
connection.close();

• java 类写入：

List<Employee> employees = getListOfEmployeesFromSomewhere();
Writer writer = new FileWriter("employees.csv");
StatefulBeanToCsv beanToCsv = new StatefulBeanToCsvBuilder(writer).build();
beanToCsv.write(employees);
writer.close();

实战：

一行一行写入 + java类 

过滤器（CsvToBeanFilter）：opencsv提供了过滤器，可以过滤某些行，比如page header、page footer等

/**
	 * 该 Filter 用于过滤到统计数据的部分，并单独存储在变量中以共后续的查询核对
	 */
	public class OrderReadToBeanFilter implements CsvToBeanFilter {
		/**
		 * 文件中的总交易笔数/金额数据
		 */
		String[] billSummaryInfo;

		@Override
		public boolean allowLine(String[] line) {
//最后一行的列长度为2 
			if (line.length > 2) {
				return true;
			} else if (line.length == 2) {
				billSummaryInfo = line;
			}
			return false;
		}
	}

读取文件中内容示例： 

部分代码块：	


//获取文件流
InputStream ins = sftpUtils.getFile("文件路径");

OrderReadToBeanFilter myFilter = new OrderReadToBeanFilter();
		//获取记录信息 转成实体类 Order
		List<Order> OrderList = new CsvToBeanBuilder(new InputStreamReader(ins, StandardCharsets.UTF_8)).withSkipLines(1).withSeparator(',')
				.withFilter(myFilter).withType(Order.class).build().parse();
		if (CollectionUtils.isEmpty(OrderList)) {
			String message = "信息为空！";
			moveFailFile(sftpUtils, channelNextDirDD, fileName, readingFilePath, message);
			return null;
		}
		//计算记录总和
		long amountSum = OrderList.stream().mapToLong(OrderList::getTransAmount).sum();
		//获取文件中汇总信息
		String[] billSummaryInfo = myFilter.billSummaryInfo;

写文件csv 示例：

//创建本地文件
			File file = new File("文件路径");
			if (!file.getParentFile().exists()) {
				//如果父目录不存在，创建父目录
				file.getParentFile().mkdirs();
			}
			file = new File(fullPath);
			file.createNewFile();
			log.info("file local path:{}", file.getAbsolutePath());

			//将数据写入csv文件
			CSVWriter csvWriter = new CSVWriter(new FileWriter(fullPath, StandardCharsets.UTF_8));
			// 先写标题
			csvWriter.writeNext("{"交易时间", "金额","流水号"}");
			//写入记录数据
			StatefulBeanToCsv beanToCsv = new StatefulBeanToCsvBuilder(csvWriter).build();
			beanToCsv.write(resultList);
			//写入汇总数据
			csvWriter.writeNext("{"总条数", "总额"}");
			//业务订单总额
			long sum = resultList.stream().mapToLong(Order::getTransAmount).sum();
			csvWriter.writeNext(new String[]{String.valueOf(resultList.size()), String.valueOf(sum)});

			csvWriter.close();

引用链接：OpenCSV for Reading and Writing CSV file in Java - SpringHowLet’s take a look at reading and writing CSV files in java using OpenCSV with a few examples. First of all, OpenCSV is a CSV file (comma-separated values)https://springhow.com/opencsv/