Java实现CSV文件的读写

CSV文件介绍

• 逗号分隔值（Comma-Separated Values，CSV，有时也称为字符分隔值，因为分隔字符也可以不是逗号），其文件以纯文本形式存储表格数据（数字和文本）。
• 纯文本意味着该文件是一个字符序列，不含必须像二进制数字那样被解读的数据。CSV文件由任意数目的记录组成，记录间以某种换行符分隔；每条记录由字段组成，字段间的分隔符是其它字符或字符串，最常见的是逗号或制表符。
• 通常，所有记录都有完全相同的字段序列。通常都是纯文本文件。建议使用WORDPAD或是记事本（NOTE）来开启，再则先另存新档后用EXCEL开启，也是方法之一。

B/S项目使用

• 在pom文件中添加jar依赖

<dependency>
	<groupId>net.sourceforge.javacsv</groupId>
	<artifactId>javacsv</artifactId>
	<version>2.0</version>
</dependency>

样例一

package com.sto.pdd.util;

import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;

public class CSVUtil {
    public static char separator = ',';

    public static void main(String[] args) throws Exception {
        // 测试导出
        String filePath = "D:/scoreInfo.csv";
        List<String[]> dataList = new ArrayList<String[]>();
        //添加标题
        dataList.add(new String[]{"学号", "姓名", "分数"});
        for (int i = 0; i < 10; i++) {
            dataList.add(new String[]{"2010000" + i, "张三" + i, "8" + i});
        }
        createCSV(dataList, filePath);

        // 读取CSV文件
        readCSV(filePath);
    }

    /**
     * 读取CSV文件
     * @param filePath:全路径名
     */
    public static List<String[]> readCSV(String filePath) throws Exception {
        CsvReader reader = null;
        List<String[]> dataList = new ArrayList<String[]>();
        try {
            //如果生产文件乱码，windows下用gbk，linux用UTF-8
            reader = new CsvReader(filePath, separator, Charset.forName("GBK"));

            // 读取表头
            reader.readHeaders();
            String[] headArray = reader.getHeaders();//获取标题
            System.out.println(headArray[0] + headArray[1] + headArray[2]);

            // 逐条读取记录，直至读完
            while (reader.readRecord()) {
                // 读一整行
                System.out.println(reader.getRawRecord());
                // 读这行的第一列
                System.out.println(reader.get("学号"));
                // 读这行的第二列
                System.out.println(reader.get(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != reader) {
                reader.close();
            }
        }

        return dataList;
    }

    /**
     * 生成CSV文件
     * @param dataList:数据集
     * @param filePath:全路径名
     */
    public static boolean createCSV(List<String[]> dataList, String filePath) throws Exception {
        boolean isSuccess = false;
        CsvWriter writer = null;
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filePath, true);
            //如果生产文件乱码，windows下用gbk，linux用UTF-8
            writer = new CsvWriter(out, separator, Charset.forName("GBK"));
            for (String[] strs : dataList) {
                writer.writeRecord(strs);
            }
            isSuccess = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != writer) {
                writer.close();
            }
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return isSuccess;
    }
}

样例二

package cn.me.controller;

import com.csvreader.CsvReader;
import com.csvreader.CsvWriter;
import com.me.SpringbootApplication;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.IOException;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author yanyg
 * @Date 2020/7/13 13:02
 * @Descripetion admin
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringbootApplication.class)
@Slf4j
public class ExcelTest {
    static RSA rsa = RSA.getInstance();

    @Test
    public void decryptPhone() {
        String str = "+dWV/h0/ybRKSURkIF57Vw==";
        String decrypt = rsa.decrypt(str);
        System.out.println(decrypt);
    }

    @Test
    public void readAndCreateCsv() {
        // 读取文件
        List<List<String>> objects = readFromCSV(',', "D:\\order0711.csv");
        System.out.println(objects.size());
        // 生成文件（一般文件不写在C盘，C盘会提示拒绝访问）
        writeIntoCSV(',', "D:\\order20200711.csv", objects);
    }

    /**
     * Read from CSV
     *
     * @param separator 分隔符
     * @param filePath  文件路径
     * @return
     */
    public static <T> List<T> readFromCSV(Character separator, String filePath) {
        CsvReader reader = null;
        List<T> result = new ArrayList<>();
        try {
            //如果生产文件乱码，windows下用gbk，linux用UTF-8
            reader = new CsvReader(filePath, separator, Charset.forName("UTF-8"));

            // 读取标题
            reader.readHeaders();
            // 逐条读取记录，直至读完
            while (reader.readRecord()) {
                //读取指定名字的列
                String waybillNo = reader.get("waybillNo");
                String orderChannelCode = reader.get("orderChannelCode");
                String orderCreateTime = reader.get("orderCreateTime");
                String orderLogisticsCode = reader.get("orderLogisticsCode");
                String recipientProvName = reader.get("recipientProvName");
                String recipientCityName = reader.get("recipientCityName");
                String recipientMobile = reader.get("recipientMobile");
                recipientMobile = rsa.decrypt(recipientMobile);
                String recipientAddress = reader.get("recipientAddress");
                String recipientName = reader.get("recipientName");

                List<String> list = new ArrayList<>();
                list.add(waybillNo);
                list.add(orderChannelCode);
                list.add(orderCreateTime);
                list.add(orderLogisticsCode);
                list.add(recipientProvName);
                list.add(recipientCityName);
                list.add(recipientMobile);
                list.add(recipientAddress);
                list.add(recipientName);
                result.add((T) list);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != reader) {
                reader.close();
            }
        }

        return result;
    }

    /**
     * Write into CSV
     *
     * @param separator 分隔符
     * @param filePath  文件路径
     * @param strList   对应CSV中的一行记录
     */
    public static void writeIntoCSV(Character separator, String filePath, List<List<String>> strList) {
        CsvWriter csvWriter = null;
        try {
            // 创建CSV写对象
            csvWriter = new CsvWriter(filePath, separator, Charset.forName("UTF-8"));
            // 写标题
            String[] headers = {"waybillNo", "orderChannelCode", "orderCreateTime", "orderLogisticsCode", "recipientProvName", "recipientCityName", "recipientMobile", "recipientAddress", "recipientName"};
            csvWriter.writeRecord(headers);

            for (List<String> list : strList) {
                String[] writeLine = new String[list.size()];
                // 集合转数组
                list.toArray(writeLine);
                csvWriter.writeRecord(writeLine);
            }

            csvWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != csvWriter) {
                csvWriter.close();
            }
        }
    }
}

写入CSV文件注意问题

问题描述

Java后台将数据写入CSV文件时踩过的坑：写入一些数据（例如我碰到过的：订单日期、联行行号、（收款）银行账号、金额）时候，由于数字比较多会导致显示成缩略形式或者科学计数法。

解决方案

在写入字段时候，在可能发生缩略的地方加上"\t"【"\t"为“转义字符”,代表的是一个tab，也就是8个空格】

乱码问题

问题描述

一个同事遇到utf-8 导出到csv文件时出现乱码，上网查了一下，需要一个BOM头。

解决方案

private static void writeBcp() throws IOException {
    //Create bcp file if not exist  
    File bcpFile = new File("test.csv");
    //bcpFile.delete();  
    byte[] bom = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};
    //boolean newFile = false;  
    FileOutputStream bcpFileWriter = new FileOutputStream(bcpFile);
    bcpFileWriter.write(bom);
    //bcpFile.delete();  
    String title = "\"MD5\",\"扫描文件名\",\"扫描时间\"," +
            "\"是否病毒\",\"安全等级\",\"病毒英文名称\"," +
            "\"病毒变种\",\"病毒类型\",\"病毒影响\"," +
            "\"感染系统\",\"传播方式\",\"备注\"";

    bcpFileWriter.write((new String(title.getBytes(), "utf-8")).getBytes());
    bcpFileWriter.write("\n".getBytes());

    String appStr = "\"" + 123 + "\","
            + "\"" + 123 + "\","
            + 123 + ","
            + 123 + ","
            + 123 + ","
            + "\"" + 123 + "\","
            + "\"\","
            + 123 + ","
            + "\"" + 123 + "\","
            + "\"" + 123 + "\","
            + "\"" + 123 + "\","
            + "\"" + 123 + "\"\n";

    bcpFileWriter.write(appStr.getBytes());
    bcpFileWriter.close();
}