快捷導(dǎo)航

Java導(dǎo)出CSV文件的方法

更新時(shí)間：2020年12月31日 11:43:13 作者：陌上桑花開花

這篇文章主要為大家詳細(xì)介紹了Java導(dǎo)出CSV文件的方法，分頁(yè)查詢大數(shù)據(jù)量，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

本文實(shí)例為大家分享了Java導(dǎo)出CSV文件的具體代碼，供大家參考，具體內(nèi)容如下

Java導(dǎo)出csv文件：

控制層：

@Controller
@RequestMapping("/historyReport/")
public class HistoryStockReportController {
  private static final Logger LOGGER = LoggerFactory.getLogger(HistoryStockReportController.class);
 
  @Autowired
  private HistoryStockReportService historyStockReportService;
 
 /**
   * 下載歷史庫(kù)存報(bào)表
   * @param request
   * @param response
   */
  @RequestMapping("new/downLoadHistoryStockInfo.htm")
  @ResponseBody
  public ResultMsg<Map<String, Object>> downLoadHistoryStockInfo(HttpServletRequest request,
      HttpServletResponse response) {
    String reportName = "PP視頻_歷史庫(kù)存效果概況數(shù)據(jù)_" + DateUtils.getCurrentDateStr("yyyyMMddHHmmss");
    String[] header = Constant.PP_INDEX_DETAIL_HEAD_NAME_LIST;
    try {
      //點(diǎn)位/終端
      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);
      // 判斷接口參數(shù)
      if (!DateUtils.isDate(startDate) || !DateUtils.isDate(endDate)) {
        return ResultMsg.buildErrorMsg(Constant.DATE_ERROR_MSG);
      }
      //封裝查詢參數(shù)
      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);
      //導(dǎo)出csv
      exportBatch(response, condition, header, reportName);
    } catch (Exception e) {
      LOGGER.error("導(dǎo)出" + reportName + "發(fā)生錯(cuò)誤:", e);
    }
    return null;
  }
 
/**
   * 導(dǎo)出報(bào)表
   * @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("導(dǎo)出" + fileName + "發(fā)生錯(cuò)誤:", e);
    } finally {
      if (out != null) {
        out.close();
      }
    }
  }
}

備注：這里查詢list集合數(shù)據(jù)是按照分頁(yè)查詢，pageNo=1,pageSize=1000,這樣支持大數(shù)據(jù)量導(dǎo)出，比如導(dǎo)出10萬(wàn)條數(shù)據(jù)，分頁(yè)查詢是為了防止把庫(kù)查詢掛了，數(shù)據(jù)量過(guò)大會(huì)發(fā)生導(dǎo)出OOM

業(yè)務(wù)層：

@Service
public class HistoryStockReportServiceImpl extends BaseImpl implements HistoryStockReportService {
  private static final Logger LOGGER = LoggerFactory.getLogger(HistoryStockReportServiceImpl.class);
  //定義數(shù)據(jù)庫(kù)查詢字段
  private String[] columnArray = Constant.CHECK_PP_INDEX_COLUMN.split(",");
  @Autowired
  private DalClient dalClient;
 
/**
   * 下載歷史庫(kù)存報(bào)表
   * @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 {
  
  /**
   * 統(tǒng)一處理PP視頻歷史庫(kù)存、特殊渠道指標(biāo)報(bào)表的衍生指標(biāo)數(shù)據(jù)
   * @param list
   * @param columnArray
   */
  public static void coverPpInfo(List<Map<String, Object>> list, String[] columnArray) {
    for (Map<String, Object> map : list) {
      // 組裝處理rate參數(shù)
      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);
      // 處理數(shù)據(jù)值為null的單一指標(biāo)
      coverIndexInfoFromNull(map, columnArray);
    }
  }
 
 
   /**
   * 處理占比參數(shù)
   *
   * @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));  // 被除數(shù)
    BigDecimal divisor = BigDecimal.valueOf(MapUtils.getDoubleValue(map, divisorKey));   // 除數(shù)
    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) + "");
  }
 
  /**
   * 處理數(shù)據(jù)值為null的單一指標(biāo)
   * @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);
      }
    }
  }
 
 
}

導(dǎo)出數(shù)據(jù)處理工具類：ExportUtils

public class ExportUtils {
  
  /**
   * 處理下載指標(biāo)
   *
   */
  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("\"", "\"\"");
        }
        //將逗號(hào)轉(zhuǎn)義
        temp="\""+temp+"\"";
      }
      sb.append("\t").append(temp).append(",");
    }
  }
 
}

常量類：

//導(dǎo)出默認(rèn)分頁(yè)
public static final int PAGE_NO = 1;
public static final int PAGE_SIZE = 1000;
 
 /**
 * PP視頻
 * 歷史存儲(chǔ)、特殊渠道數(shù)據(jù)庫(kù)查詢字段
 */
public static final String CHECK_PP_INDEX_COLUMN =
      "requestNum,advertiserVvNum,responseNum,showNum,clickNum,theoryStockNum,fillFeeNum,"
          + "feePracticalShowNum,deliveryFillNum,deliveryPracticalShowNum,theoryStockRemainedNum,"
          + "systemExceptionLost,userExitLost,income";
 
 
 /**
   * 20190509
   * pp視頻歷史庫(kù)存、特殊渠道日志數(shù)據(jù)報(bào)表
   * 報(bào)表下載模板頭部(英文)
   */
  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視頻歷史庫(kù)存日志數(shù)據(jù)報(bào)表
 * 報(bào)表下載模板頭部(中文)
 */
public static final String[] PP_INDEX_DETAIL_HEAD_NAME_LIST = {"統(tǒng)計(jì)時(shí)間,","點(diǎn)位/終端編碼,","點(diǎn)位/終端名稱,","廣告位編碼,", "廣告位名稱,",
      "請(qǐng)求量,", "廣告vv量,","返回量,","曝光量,", "點(diǎn)擊量,", "理論庫(kù)存量,", "付費(fèi)填充量,", "付費(fèi)實(shí)際曝光量,", "配送填充量,",
      "配送實(shí)際曝光量,", "理論庫(kù)存余量,", "系統(tǒng)異常損失,", "用戶退出損失,","收入,", "付費(fèi)使用率,", "配送使用率,", "庫(kù)存使用率,",
      "庫(kù)存余量占比,"};

導(dǎo)出效果：