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;
 
 
  @RequestMapping("new/downLoadHistoryStockInfo.htm")
  @ResponseBody
  public ResultMsg> 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 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;
  }
 

  private void exportBatch(HttpServletResponse response, Map 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> 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;
 

  public List> queryDownLoadHistoryStockInfo(Map condition) {
    List> resultList = dalClient
 .queryForList("historyStockData.queryDownLoadHistoryStockInfo", condition);
    if (!CollectionUtil.isEmptyList(resultList)) {
      IndexDataFormatUtils.coverPpInfo(resultList, columnArray);
    }
    return resultList;
  }
}

查询集合处理工具类：IndexDataFormatUtils

public class IndexDataFormatUtils {
  
  
  public static void coverPpInfo(List> list, String[] columnArray) {
    for (Map 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);
    }
  }
 
 
   
  public static void calculateRate(Map 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) + "");
  }
 
  
  public static void coverIndexInfoFromNull(Map 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 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 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;
 
 
public static final String CHECK_PP_INDEX_COLUMN =
      "requestNum,advertiserVvNum,responseNum,showNum,clickNum,theoryStockNum,fillFeeNum,"
   + "feePracticalShowNum,deliveryFillNum,deliveryPracticalShowNum,theoryStockRemainedNum,"
   + "systemExceptionLost,userExitLost,income";
 
 
 
  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";
 

public static final String[] PP_INDEX_DETAIL_HEAD_NAME_LIST = {"统计时间,","点位/终端编码,","点位/终端名称,","广告位编码,", "广告位名称,",
      "请求量,", "广告vv量,","返回量,","曝光量,", "点击量,", "理论库存量,", "付费填充量,", "付费实际曝光量,", "配送填充量,",
      "配送实际曝光量,", "理论库存余量,", "系统异常损失,", "用户退出损失,","收入,", "付费使用率,", "配送使用率,", "库存使用率,",
      "库存余量占比,"};

导出效果：

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