CSV文件怎么利用Java导出?很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
Java导出csv文件:
控制层:
@Controller @RequestMapping("/historyReport/") public class HistoryStockReportController { private static final Logger LOGGER = LoggerFactory.getLogger(HistoryStockReportController.class); @Autowired private HistoryStockReportService historyStockReportService; /** * 下载历史库存报表 * @param request * @param response */ @RequestMapping("new/downLoadHistoryStockInfo.htm") @ResponseBody public ResultMsg<Map<String, Object>> downLoadHistoryStockInfo(HttpServletRequest request, HttpServletResponse response) { String reportName = "PP视频_历史库存效果概况数据_" + DateUtils.getCurrentDateStr("yyyyMMddHHmmss"); String[] header = Constant.PP_INDEX_DETAIL_HEAD_NAME_LIST; try { //点位/终端 String pointLocation = request.getParameter(Constant.POINT_LOCATION_CODE); //广告位 String positionScreenType = request.getParameter(Constant.POSITION_SCREEN_TYPE_CODE); String startDate = request.getParameter(Constant.START_DATE); String endDate = request.getParameter(Constant.END_DATE); // 判断接口参数 if (!DateUtils.isDate(startDate) || !DateUtils.isDate(endDate)) { return ResultMsg.buildErrorMsg(Constant.DATE_ERROR_MSG); } //封装查询参数 Map<String, Object> condition = new HashMap<>(); condition.put(Constant.POINT_LOCATION_CODE, pointLocation); condition.put(Constant.POSITION_SCREEN_TYPE_CODE, positionScreenType); condition.put(Constant.START_DATE, startDate); condition.put(Constant.END_DATE, endDate); //导出csv exportBatch(response, condition, header, reportName); } catch (Exception e) { LOGGER.error("导出" + reportName + "发生错误:", e); } return null; } /** * 导出报表 * @param response * @param header * @param fileName * @throws IOException */ private void exportBatch(HttpServletResponse response, Map<String, Object> condition, String[] header, String fileName) throws IOException { response.setContentType("application/vnd.ms-excel;charset=GBK"); response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("GBK"), "ISO8859-1") + "." + "csv"); StringBuilder sb = new StringBuilder(); for (String s : header) { sb.append(s); } sb.append("\n"); PrintWriter out = null; try { out = response.getWriter(); out.print(sb.toString()); int pageNumber = Constant.PAGE_NO; int pageSize = Constant.PAGE_SIZE; int dataLength = pageSize; while (dataLength == pageSize) { int startIndex = (pageNumber - 1) * pageSize; condition.put("startIndex", startIndex); condition.put("maxCount", pageSize); List<Map<String, Object>> resultList = historyStockReportService .queryDownLoadHistoryStockInfo(condition); dataLength = resultList.size(); String[] columns = Constant.PP_DETAIL_COLUMN.split(","); for (int i = 0; i < dataLength; i++) { out.print(ExportUtils.handleExportData(resultList.get(i), columns)); } out.flush(); pageNumber++; } } catch (IOException e) { LOGGER.error("导出" + fileName + "发生错误:", e); } finally { if (out != null) { out.close(); } } } }
备注:这里查询list集合数据是按照分页查询,pageNo=1,pageSize=1000,这样支持大数据量导出,比如导出10万条数据,分页查询是为了防止把库查询挂了,数据量过大会发生导出OOM
业务层:
@Service public class HistoryStockReportServiceImpl extends BaseImpl implements HistoryStockReportService { private static final Logger LOGGER = LoggerFactory.getLogger(HistoryStockReportServiceImpl.class); //定义数据库查询字段 private String[] columnArray = Constant.CHECK_PP_INDEX_COLUMN.split(","); @Autowired private DalClient dalClient; /** * 下载历史库存报表 * @param condition * @return */ public List<Map<String, Object>> queryDownLoadHistoryStockInfo(Map<String, Object> condition) { List<Map<String, Object>> resultList = dalClient .queryForList("historyStockData.queryDownLoadHistoryStockInfo", condition); if (!CollectionUtil.isEmptyList(resultList)) { IndexDataFormatUtils.coverPpInfo(resultList, columnArray); } return resultList; } }
查询集合处理工具类:IndexDataFormatUtils
public class IndexDataFormatUtils { /** * 统一处理PP视频历史库存、特殊渠道指标报表的衍生指标数据 * @param list * @param columnArray */ public static void coverPpInfo(List<Map<String, Object>> list, String[] columnArray) { for (Map<String, Object> map : list) { // 组装处理rate参数 calculateRate(map, Constant.FEE_PRACTICAL_SHOW_NUM, Constant.THEORY_STOCK_NUM, Constant.FILLFEE_RATE); calculateRate(map, Constant.DELIVERY_PRACTICAL_SHOW_NUM, Constant.THEORY_STOCK_NUM, Constant.DELIVERY_FILL_RATE); calculateRate(map, Constant.SHOW_NUM, Constant.THEORY_STOCK_NUM, Constant.THEORY_STOCK_RATE); calculateRate(map, Constant.THEORY_STOCK_REMAINED_NUM, Constant.THEORY_STOCK_NUM, Constant.THEORY_STOCK_REMAINED_RATE); // 处理数据值为null的单一指标 coverIndexInfoFromNull(map, columnArray); } } /** * 处理占比参数 * * @param map * @param dividendKey * @param divisorKey * @param quotientKey */ public static void calculateRate(Map<String, Object> map, String dividendKey, String divisorKey, String quotientKey) { if (StringUtils.isBlank(MapUtils.getString(map,dividendKey)) || StringUtils.isBlank(MapUtils.getString(map,divisorKey))) { map.put(quotientKey,"-"); return; } BigDecimal dividend = BigDecimal.valueOf(MapUtils.getDoubleValue(map, dividendKey)); // 被除数 BigDecimal divisor = BigDecimal.valueOf(MapUtils.getDoubleValue(map, divisorKey)); // 除数 BigDecimal quotient = BigDecimal.valueOf(0.00); // =0 相等 >0前者大于后者 ,反之 <0 前者小于后者 if(dividend.compareTo(BigDecimal.ZERO) != 0 && divisor.compareTo(BigDecimal.ZERO) != 0){ quotient = dividend.multiply(BigDecimal.valueOf(100)).divide(divisor,2,BigDecimal.ROUND_HALF_UP); } map.put(quotientKey, quotient.setScale(2) + ""); } /** * 处理数据值为null的单一指标 * @param map * @param columnArray */ public static void coverIndexInfoFromNull(Map<String, Object> map, String[] columnArray) { for (String columnName : columnArray) { String columnValue = MapUtils.getString(map,columnName); if (StringUtils.isBlank(columnValue)) { map.put(columnName,"-"); }else { map.put(columnName,columnValue); } } } }
导出数据处理工具类:ExportUtils
public class ExportUtils { /** * 处理下载指标 * */ public static String handleExportData(Map<String,Object> reportData, String[] columns){ StringBuilder sb = new StringBuilder(); for (String columnName:columns) { addStringBuffer(sb,reportData,columnName); } sb.append("\n"); return sb.toString(); } public static void addStringBuffer(StringBuilder sb, Map<String, Object> map,String name){ if(map.get(name) == null ){ sb.append("-,"); }else{ String value = String.valueOf(map.get(name)); String temp = value.replaceAll("\r", "").replaceAll("\n", ""); if(temp.contains(",")){ if(temp.contains("\"")){ temp=temp.replace("\"", "\"\""); } //将逗号转义 temp="\""+temp+"\""; } sb.append("\t").append(temp).append(","); } } }
常量类:
//导出默认分页 public static final int PAGE_NO = 1; public static final int PAGE_SIZE = 1000; /** * PP视频 * 历史存储、特殊渠道数据库查询字段 */ public static final String CHECK_PP_INDEX_COLUMN = "requestNum,advertiserVvNum,responseNum,showNum,clickNum,theoryStockNum,fillFeeNum," + "feePracticalShowNum,deliveryFillNum,deliveryPracticalShowNum,theoryStockRemainedNum," + "systemExceptionLost,userExitLost,income"; /** * 20190509 * pp视频历史库存、特殊渠道日志数据报表 * 报表下载模板头部(英文) */ public static final String PP_DETAIL_COLUMN = "countDate,pointLocationCode,pointLocationName,positionScreenTypeCode,positionScreenTypeName," + "requestNum,advertiserVvNum,responseNum,showNum,clickNum,theoryStockNum,fillFeeNum,feePracticalShowNum,deliveryFillNum," + "deliveryPracticalShowNum,theoryStockRemainedNum,systemExceptionLost,userExitLost,income,fillFeeRate,deliveryFillRate," + "theoryStockRate,theoryStockRemainedRate"; /** * pp视频历史库存日志数据报表 * 报表下载模板头部(中文) */ public static final String[] PP_INDEX_DETAIL_HEAD_NAME_LIST = {"统计时间,","点位/终端编码,","点位/终端名称,","广告位编码,", "广告位名称,", "请求量,", "广告vv量,","返回量,","曝光量,", "点击量,", "理论库存量,", "付费填充量,", "付费实际曝光量,", "配送填充量,", "配送实际曝光量,", "理论库存余量,", "系统异常损失,", "用户退出损失,","收入,", "付费使用率,", "配送使用率,", "库存使用率,", "库存余量占比,"};
导出效果:
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。