java接口导出csv

1、背景介绍

项目中需要导出数据质检结果，本来使用Excel，但是质检结果数据行数过多，导致用hutool报错，因此转为导出csv格式数据。

2、参考文档

https://blog.csdn.net/ityqing/article/details/127879556
工程环境：springboot 2.2.x

3、实现

controller

    @ApiOperation("导出质检结果csv")
    @GetMapping("/export/csv/{datasetId}")
    public void exportCsv( @PathVariable String datasetId,
                            HttpServletResponse response) {
        CheckDataParamVO checkDataParamVO = new CheckDataParamVO();
        checkDataParamVO.setDatasetId(datasetId);
        checkDataService.exportCsv(checkDataParamVO, response);
    }

service

 /**
     * 导出csv格式质检结果
     *
     * @param checkDataVO
     * @param response
     */
    @Override
    public void exportCsv(CheckDataParamVO checkDataVO, HttpServletResponse response) {
        List<CheckResultVO> res = getCheckResultAll(checkDataVO);
        String fileName = "质检结果表" + DateTime.now().getTime();
        writeCsv(response, fileName, res);
    }

    /**
     * CSV文件列分隔符
     */
    private static final String CSV_COLUMN_SEPARATOR = ",";
    /**
     * CSV文件行分隔符
     */
    private static final String CSV_ROW_SEPARATOR = System.lineSeparator();

    private static final List<String> titleName = Arrays.asList("序号", "异常类型", "图层名", "要素主键");

    /**
     * @param response 响应流
     * @param fileName 文件名称
     * @param dataList 数据源
     */
    private void writeCsv(HttpServletResponse response, String fileName, List<CheckResultVO> dataList) {
        OutputStream out = null;
        try {
            StringBuffer buf = new StringBuffer();
            out = response.getOutputStream();
            String lastFileName = fileName + ".csv";
            response.setContentType("application/msexcel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));

            // 组装表头
            for (String title : titleName) {
                buf.append(title).append(CSV_COLUMN_SEPARATOR);
            }
            buf.append(CSV_ROW_SEPARATOR);

            //组装行数据
            dataList.forEach(data -> {
                buf.append(Optional.ofNullable(data.getSid()).orElse(0)).append(CSV_COLUMN_SEPARATOR);
                buf.append(Optional.ofNullable(data.getCheckType()).orElse("")).append(CSV_COLUMN_SEPARATOR);
                buf.append(Optional.ofNullable(data.getSubname()).orElse("")).append(CSV_COLUMN_SEPARATOR);
                buf.append(Optional.ofNullable(data.getFid()).orElse("")).append(CSV_COLUMN_SEPARATOR);
                buf.append(CSV_ROW_SEPARATOR);
            });

            //添加bom，不加Excel打开中文会乱码
            out.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
            out.write(buf.toString().getBytes("UTF-8"));
        } catch (Exception e) {
            log.error("导出CSV异常", e);
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    log.error("导出CSV异常", e);
                }
            }
        }
    }

4、测试结果

可用

5、总结

以上是不使用第三方组件实现导出csv文件。测试成功。

6、补充

参考文章
https://blog.csdn.net/ityqing/article/details/127879556

6.1、bug

提到了一个中文乱码的bug
Excel 在读取 csv 的时候是通过读取文件头上的 bom 来识别编码的，这导致如果我们生成 csv 文件的平台输出无 bom 头编码的 csv 文件（例如 utf-8 ，在标准中默认是可以没有 bom 头的），Excel 只能自动按照默认编码读取，不一致就会出现乱码问题了。

6.2、解决：

写入的时候加上： out.write(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF });

6.3、其他方式

参考文章用了两种方法，另一种是引入第三方组件CSVWriter
pom.xml文件增加依赖

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

java代码如下，自己没测。只是抄过来

/**
* @param response 响应流
* @param fileName 文件名称
* @param dataList  数据源
*/
private void writeCsv2(HttpServletResponse response, String fileName, List<TaskAplusExpire> dataList) {
    String lastFileName = fileName + ".csv";
    response.setContentType("application/msexcel;charset=UTF-8");
    try {
        response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(lastFileName, "UTF-8"));
        PrintWriter out = response.getWriter();
        // 手动加上BOM标识
        out.write(new String(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF }));
        
        // 设置显示的顺序,数据源对象属性列表
        String[] columnMapping = { "pmid", "phone", "sendDate", "code", "message" };
       
        ColumnPositionMappingStrategy<TaskAplusExpire> mapper =
            new ColumnPositionMappingStrategy<TaskAplusExpire>();

        //数据源类型
        mapper.setType(TaskAplusExpire.class);
        mapper.setColumnMapping(columnMapping);

        // 写表头
        CSVWriter csvWriter = new CSVWriter(response.getWriter(), CSVWriter.DEFAULT_SEPARATOR,
                                            CSVWriter.NO_QUOTE_CHARACTER);
        String[] header = { "Pmid","Phone","Send Date","Code","Message"};
        csvWriter.writeNext(header);

        StatefulBeanToCsv beanToCsv = new StatefulBeanToCsvBuilder(out)
            .withMappingStrategy(mapper)
            .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
            .withSeparator(CSVWriter.DEFAULT_SEPARATOR)
            .withEscapechar('\\').build();
        beanToCsv.write(dataList);
        csvWriter.close();
        out.close();
    } catch (IOException e) {
        e.printStackTrace();
    }catch (CsvDataTypeMismatchException e) {
        e.printStackTrace();
    } catch (CsvRequiredFieldEmptyException e) {
        e.printStackTrace();
    }