调用
@PostMapping("/news-csv")
@ApiOperation(value = "查询数据导出CSV")
public void newsDataExportCsv(HttpServletResponse response, @ApiParam(value = "导出对象实体", required = true) @RequestBody PageEntity pageEntity) throws Exception {
logger.info(NewsConstant.MODULE_AUDIT_MODEL, NewsConstant.MODULE_MODEL_CONFIG, "查询数据导出CSV");
List<String> titles = new ArrayList<>();
List<String> keys = new ArrayList<>();
readMultiPeriodQueryService.getExpTitlesAndKeys(pageEntity, titles, keys);
pageEntity.setPageSize(0);
List<Map<String, Object>> mapList = readAuditDataShowService.queryNewDatas(pageEntity).getDatas();
String sheetTitle = StringUtils.isEmpty(pageEntity.getExportTitle())? "明细审定":pageEntity.getExportTitle();
ManagerImpOrExpUtils.exportCsvObj(response, sheetTitle, titles, keys, mapList);
}
导出方法
/**
* 导出csv格式文件
*
* @param response
* @param fileName 文件名
* @param titles 标题
* @param keys 键列 和标题一一对应
* @param values 值
* @throws IOException
*/
public static void exportCsvObj(HttpServletResponse response, String fileName, List<String> titles, List<String> keys, List<Map<String, Object>> values) throws IOException {
setExportFilename(response, fileName + ".csv");
OutputStream fos = response.getOutputStream();
fos.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
CSVFormat csvFormat = CSVFormat.EXCEL.withFirstRecordAsHeader();
OutputStreamWriter osw = new OutputStreamWriter(fos, StandardCharsets.UTF_8);
CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);
//处理code为数字时,过长导致excel打开时单元格式变成科学计数法
// values.stream().forEach(value->value.put("code",value.get("code")+"\t"));
try {
for (int i = 0; i < titles.size(); i++) {
csvPrinter.print(titles.get(i));
}
csvPrinter.println();
for (int i = 0; i < values.size(); i++) {
Map<String, Object> map = values.get(i);
for (int j = 0; j < keys.size(); j++) {
//处理code为数字时,过长导致excel打开时单元格式变成科学计数法
if("code".equals(keys.get(j))){
csvPrinter.print(map.get(keys.get(j))+"\t");
}else{
csvPrinter.print(map.get(keys.get(j)));
}
}
csvPrinter.println();
}
csvPrinter.flush();
} catch (Exception e) {
} finally {
fos.close();
csvPrinter.close();
}
}
/**
* 设置导出文件字符集
*
* @param response
* @param fileName
*/
public static void setExportFilename(HttpServletResponse response, String fileName) {
try {
fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + fileName);
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "No-cache");
response.setDateHeader("Expires", 0);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}
遇到的问题:导出csv文件数字会自动变科学计数法的解决方法
其实这个问题跟用什么语言导出csv文件没有关系。Excel显示数字时,如果数字大于12位,它会自动转化为科学计数法;如果数字大于15位,它不仅用于科学技术费表示,还会只保留高15位,其他位都变0。
解决这个问题:
只要把数字字段后面加上显示上看不见的字符即可,字符串前面或者结尾加上制表符"\t".