Java导出CSV文件(分页查询大数据量)

最新推荐文章于 2024-10-16 11:07:46 发布

陌上桑花开花

最新推荐文章于 2024-10-16 11:07:46 发布

阅读量8.1k

点赞数 2

分类专栏：导出(工作案例)

本文链接：https://blog.csdn.net/u014079773/article/details/93742872

版权

导出(工作案例) 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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量,","返回量,","曝光量,", "点击量,", "理论库存量,", "付费填充量,", "付费实际曝光量,", "配送填充量,",
            "配送实际曝光量,", "理论库存余量,", "系统异常损失,", "用户退出损失,","收入,", "付费使用率,", "配送使用率,", "库存使用率,",
            "库存余量占比,"};

导出效果：