Java实现CSV文件的读写

痛定思痛。 2022-05-08 10:48 1014阅读 0赞

## 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个空格】][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();
    }

[t_t_tab_8]: https://blog.csdn.net/gaoweijiegwj/article/details/52949895