Java导出CSV文件的方法

本文实例为大家分享了Java导出CSV文件的具体代码，供大家参考，具体内容如下

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

导出效果：

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持易盾网络。