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

导出效果：

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