java后台使用opencsv生成csv文件，并返回给前端下载（附前后端代码）

最近有一个需求，需要 前端页面选择相应条件，后端根据条件查询数据库，将查出的实体类集合转换为csv文件返回给前端，随后前端进行下载。

找了挺多的资料，现在进行一个整理和分享。

1.Controller层：

/**
 * 导出csv文件
 */
@RequestMapping("/exportcsv")
@RequiresPermissions("xxx:xxxx:xxx")
public String exportCsv(HttpServletResponse response) {
    return xxxService.exportCsvFile(response);
}

2.实现类部分：

  public void exportCsv(HttpServletResponse response) throws CsvRequiredFieldEmptyException, CsvDataTypeMismatchException, IOException {
    List<xxxx> xxxxList = new ArrayList<>();
    String[] header = {CsvHeaders.EATID, CsvHeaders.EATCOMPANYNAME, CsvHeaders.YEAR, CsvHeaders.EMFCOMPANYNAME,
      CsvHeaders.RESOURCENAME, CsvHeaders.EMISSIONNAME, CsvHeaders.SCOPE, CsvHeaders.CATEGORY, CsvHeaders.ACTIVITYITEM,
      CsvHeaders.ACTIVITYVOLUMEUNIT};
    CSVUtils.generateCsvFile(xxxxList , "exportResults.csv", header);
    CSVUtils.readCsvFileStream("exportResults.csv", response);
  }

3.核心util导出方法：

 /**
   * Generate a CSV file from the queried data.
   *
   * @param exportResults
   * @param fileName
   * @param header
   * @throws IOException
   * @throws CsvDataTypeMismatchException
   * @throws CsvRequiredFieldEmptyException
   */
  public static void generateCsvFile(List<xxxx> exportResults, String fileName, String[] header)
    throws IOException, CsvDataTypeMismatchException, CsvRequiredFieldEmptyException {
    Writer writer = new FileWriter(fileName);
    // write header
    try (CSVWriter csvWriter = new CSVWriter(writer, ICSVWriter.DEFAULT_SEPARATOR, ICSVWriter.NO_QUOTE_CHARACTER,
      ICSVWriter.NO_ESCAPE_CHARACTER, ICSVWriter.DEFAULT_LINE_END)) {
      csvWriter.writeNext(header);
      // write content
      StatefulBeanToCsv<xxxx> beanToCsv = new StatefulBeanToCsvBuilder<xxxx>(
        writer).build();
      beanToCsv.write(exportResults);
      csvWriter.close();
    }
    writer.close();
  }

  /**
   * Read the csv file stream and return to the front-end download.
   *
   * @param fileName
   * @param response
   * @throws UnsupportedEncodingException
   */
  public static void readCsvFileStream(String fileName, HttpServletResponse response)
    throws UnsupportedEncodingException {
    String myFileName = new String(fileName.getBytes("utf-8"), "gbk");
    File file = new File(myFileName);
    if (file.exists()) {
      response.setContentType("application/force-download");// Set force download not to open
      response.addHeader("Content-Disposition", "attachment;fileName=" + myFileName);// set filename
      response.setCharacterEncoding("UTF-8");
      byte[] uft8bom = {(byte) 0xef, (byte) 0xbb, (byte) 0xbf};
      byte[] buffer = new byte[1024];
      FileInputStream fis = null;
      BufferedInputStream bis = null;
      try {
        fis = new FileInputStream(file);
        bis = new BufferedInputStream(fis);
        OutputStream os = response.getOutputStream();
        os.write(uft8bom);
        int i = bis.read(buffer);
        while (i != -1) {
          os.write(buffer, 0, i);
          i = bis.read(buffer);
        }
      } catch (Exception e) {
        e.printStackTrace();
      } finally {
        if (bis != null) {
          try {
            bis.close();
          } catch (IOException e) {
            e.printStackTrace();
          }
        }
        if (fis != null) {
          try {
            fis.close();
          } catch (IOException e) {
            e.printStackTrace();
          }
        }
      }
    }
    if (file.delete()) {
      LogUtil.info(file.getName() + " file has been deleted！");
    } else {
      LogUtil.info("File deletion failed！");
    }
  }

前端选择相应条件，查询数据库，并把查询出的数据导出成csv文件。

其中有一个问题是生成csv文件返回给前端下载之后，使用execl打开出现了乱码。这边选择的解决方法是：在将文件写入文件流放入response的时候，文件流增加bom头。

4.前端接受文件流，进行下载

$.ajax({
                            url: process.env.WEB_ENV + '/exportcsv',
                            type: 'GET',
                            headers: {
                                Authorization: `Bearer ${app.config.globalProperties.$keycloak.token}`
                            },
                            data: params,
                            // The type of data sent to the server
                            contentType: 'application/json;charset=utf-8',
                            // Specify the type returned by the server, because we want to return a file stream, so the type is binary data
                            dataType: 'binary',
                            // The properties of the native XMLHttpRequest, set the response type to blob, and receive the file stream
                            xhrFields: {
                                responseType: 'blob'
                            },
                            success: function (result, status, xhr) {
                                // Browser Compatible
                                const download_URL = (
                                    window.URL || window.webkitURL
                                ).createObjectURL(result)

                                // Create a tag, simulate click to download
                                const a_link = document.createElement('a')
                                a_link.href = download_URL
                                // Use the download attribute of the a tag to specify the file name
                                a_link.download =
                                    labels.company?.[params.companyId] +
                                    '_' +
                                    params.enableYear +
                                    '_算定ロジック_' +
                                    Moment(new Date()).format('YYYYMMDDHHmmss').valueOf() +
                                    '.csv'
                                document.body.appendChild(a_link)
                                a_link.click()

                                setTimeout(function () {
                                    // Remove temporary file paths in memory and a tags created for download
                                    URL.revokeObjectURL(download_URL)
                                    a_link.remove()
                                }, 10000)
                            },
                            error: function (xhr, textStatus, errorMessage) {
                                const errorInfo = decodeURIComponent(
                                    xhr.getResponseHeader('errorInfo')
                                )
                                // display error messages
                                alert(errorInfo)
                            }
                        })

这样就实现了前端调用后端接口生成下载csv文件的一个完整需求。