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*/@Overridepublic 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();}
