在SpringBoot对CSV进行解析,写入(支持上传、下载)

工作中遇到了将所有数据整合成CSV文件并下载、上传CSV文件并解析,这两个需求。现在把处理方法记录下来,作为总结。

项目构建

jar包引入

CSV的解析和写入使用到的是commons-csv的包,pom中的定义如下

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-csv</artifactId>
            <version>1.7</version>
        </dependency>

工具类

我把CSV的上传、下载、解析、写入的通用方法,都整合在了CSVUtils类中。

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.CSVRecord;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: Denebola
 * @Date: 2019/7/18-16:48
 * @Description: CSV工具类
 **/

public class CSVUtils {
    private static Logger logger = LoggerFactory.getLogger(CSVUtils.class);
    //行尾分隔符定义
    private final static String NEW_LINE_SEPARATOR = "\n";
    //上传文件的存储位置
    private final static URL PATH = Thread.currentThread().getContextClassLoader().getResource("");

    /**
     * @return File
     * @Description 创建CSV文件
     * @Param fileName 文件名,head 表头,values 表体
     **/
    public static File makeTempCSV(String fileName, String[] head, List<String[]> values) throws IOException {
//        创建文件
        File file = File.createTempFile(fileName, ".csv", new File(PATH.getPath()));
        CSVFormat formator = CSVFormat.DEFAULT.withRecordSeparator(NEW_LINE_SEPARATOR);

        BufferedWriter bufferedWriter =
                new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "UTF-8"));
        CSVPrinter printer = new CSVPrinter(bufferedWriter, formator);

//        写入表头
        printer.printRecord(head);

//        写入内容
        for (String[] value : values) {
            printer.printRecord(value);
        }

        printer.close();
        bufferedWriter.close();
        return file;
    }

    /**
     * @return boolean 
     * @Description 下载文件
     * @Param response,file
     **/
    public static boolean downloadFile(HttpServletResponse response, File file) {
        FileInputStream fileInputStream = null;
        BufferedInputStream bufferedInputStream = null;
        OutputStream os = null;
        try {
            fileInputStream = new FileInputStream(file);
            bufferedInputStream = new BufferedInputStream(fileInputStream);
            os = response.getOutputStream();
            //MS产本头部需要插入BOM
            //如果不写入这几个字节,会导致用Excel打开时,中文显示乱码
            os.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
            byte[] buffer = new byte[1024];
            int i = bufferedInputStream.read(buffer);
            while (i != -1) {
                os.write(buffer, 0, i);
                i = bufferedInputStream.read(buffer);
            }
            return true;
        } catch (IOException e) {
            e.printStackTrace();

        } finally {
        	//关闭流
            if (os != null) {
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bufferedInputStream != null) {
                try {
                    bufferedInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            file.delete();
        }
        return false;
    }

    /**
     * @return File
     * @Description 上传文件
     * @Param multipartFile
     **/
    public static File uploadFile(MultipartFile multipartFile) {
        String path = PATH.getPath() + multipartFile.getOriginalFilename();
        try {
            File file = new File(path);
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            multipartFile.transferTo(file);
            logger.info("上传文件成功,文件名===>" + multipartFile.getOriginalFilename() + ", 路径===>" + file.getPath());
            return file;
        } catch (IOException e) {
            logger.error("上传文件失败" + e.getMessage(), e);
            return null;
        }

    }

    /**
     * @return List<List<String>>
     * @Description 读取CSV文件的内容(不含表头)
     * @Param filePath 文件存储路径,colNum 列数
     **/
    public static List<List<String>> readCSV(String filePath, int colNum) {
        BufferedReader bufferedReader = null;
        InputStreamReader inputStreamReader = null;
        FileInputStream fileInputStream = null;

        try {
            fileInputStream = new FileInputStream(filePath);
            inputStreamReader = new InputStreamReader(fileInputStream);
            bufferedReader = new BufferedReader(inputStreamReader);

            CSVParser parser = CSVFormat.DEFAULT.parse(bufferedReader);
//          表内容集合,外层List为行的集合,内层List为字段集合
            List<List<String>> values = new ArrayList<>();
            int rowIndex = 0;

            for (CSVRecord record : parser.getRecords()) {
//              跳过表头
                if (rowIndex == 0) {
                    rowIndex++;
                    continue;
                }
//              每行的内容
                List<String> value = new ArrayList<>(colNum + 1);
                for (int i = 0; i < colNum; i++) {
                    value.add(record.get(i));
                }
                values.add(value);
                rowIndex++;
            }
            return values;
        } catch (IOException e) {
            logger.error("解析CSV内容失败" + e.getMessage(), e);
        }finally {
        	//关闭流
            if (bufferedReader != null) {
                try {
                    bufferedReader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (inputStreamReader != null) {
                try {
                    inputStreamReader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
}

控制器

Controller.java

import com.denebola.learnspringboot.CSVUtils;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.util.List;

@Slf4j
@RestController
public class Controller {

    private static Logger logger = LoggerFactory.getLogger(Controller.class);

	//定义自己的服务类接口
    @Autowired
    private Service service;

    /**
     * @param response
     * @return
     * @Description 下载CSV
     **/
    @GetMapping("/downloadAll")
    public String downloadAllUserRoleCSV(HttpServletResponse response) {
        String[] head = service.getHead();
        List<String[]> values = service.getValues();
        String fileName = service.getName();
        
        File file = CSVUtils.makeTempCSV(fileName, head, values);
        response.setCharacterEncoding("utf-8");
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName +".csv");
        CSVUtils.downloadFile(response, file);
        return null;
    }

    /**
     * @return
     * @Description 上传CSV
     * @Param file
     **/
    @PostMapping(value = "/upload")
    public String upload(@RequestParam("file") MultipartFile multipartFile) {
        try {
        	//上传内容不能为空
            if (multipartFile.isEmpty()) {
                return "500";
            }
            File file = CSVUtils.uploadFile(multipartFile);
            List<List<String>> userRoleLists = CSVUtils.readCSV(file.getPath(), 2);
            service.doSth(userRoleLists);
            file.delete();
            return "200";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "500";
    }
}

Tips
需要值得注意的是,在使用Java生成CSV文件时,可能出现中文乱码的问题,这是由于微软的产品为了检测一个字节流是否是UTF-8编码,而添加了BOM标记。而有的文本编辑软件,比如Notepad、VScode、记事本等,不会做这种检测,会当作正常字符处理。所以产生的现象就是,Excel打开是乱码时,记事本打开不是乱码。

为了解决这种问题,我们可以手动的加入BOM。上述代码中添加的方式是os.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});,如果不使用Response的out进行输出的话,可以改用以下代码:

OutputStreamWriter osw = new OutputStreamWriter(resp.getOutputStream(), "UTF-8"); 
osw.write(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));   

结构图


原文:Spring-Boot写入、解析CSV,支持上传、下载_u013620635的博客-CSDN博客
作者: Regulus_Li