java导出数据为Excel的具体分析和例子

前端页面中的参数：

导出excel表中的数据：

var getExcel = function () {

        var data = {

            'telPhone': $("#telPhone").val(),

            'companyName': $("#companyName").val(),

            'merchantName': $("#merchantName").val(),

            'status': $("#status").val(),

            'isFrozen': $("#isFrozen").val(),

            'merchantFirstTypeId': $("select[name='merchantFirstTypeId']").val(),

            'merchantSecondTypeId': $("select[name='merchantSecondTypeId']").val(),

            'positionExtendsId': getPositionVal(),

            'agentId': agentId

        };

        exportExcel("ajaxSubmit", "/web/merchants/getExport", data);

    };

java代码中的参数逻辑:controller

// 获取当前登录用户

SessionUserVO userBean = getLoginUser();

//记录日志

Result result = addSysLog(userBean,SystemOperateEnum.IMPORT_EXPORT.getType(),"商户导出");

//登录权限判断

//是否为管理员

if(Objects.equals(userBean.getSessionRoleVo().get(0).getRoleCode(), RoleEnum.ADMIN.getCode())){

//是否为代理商

}else if(Objects.equals(userBean.getSessionRoleVo().get(0).getRoleCode(), RoleEnum.CITY_AGENT.getCode()) || Objects.equals(userBean.getSessionRoleVo().get(0).getRoleCode(), RoleEnum.COUNTY_AGENT.getCode()) || Objects.equals(userBean.getSessionRoleVo().get(0).getRoleCode(), RoleEnum.COMMUNITY_AGENT.getCode())){

AgentDTO agentDTO = new AgentDTO();

agentDTO.setUserId(userBean.getId());

//查找当前级及下级代理商

List<AgentVO> agentList = iAgentService.getPositionGetNextAgentList(agentDTO);

if(agentList!=null && agentList.size()>0){

List<Long> ids = new ArrayList<Long>();

for(AgentVO vo:agentList){

ids.add(vo.getId());

}

merchantDTO.setAgentIds(ids);

}

}

merchantDTO.setIsDelete(merchantIsDelete.ZERO.getIndex());

List<MerchantVO> list = new ArrayList<MerchantVO>();

//判断日志是否已经记录

if(result.isSuccess()){

list = iMerchantService.getMerchantList(merchantDTO);

}

        if(list != null && list.size()>0){//处理状态码

        for(MerchantVO vo:list){

       if (vo.getStatus() == agentStatus.ZERO.getIndex()) {

               if(vo.getStatusLevel() == agentLevel.CITY_AGENT.getIndex()){

               vo.setStatusName("【"+agentLevel.CITY_AGENT.getName()+"】"+agentStatus.ZERO.getName());

               }else if(vo.getStatusLevel() == agentLevel.COUNTY_AGENT.getIndex()){

               vo.setStatusName("【"+agentLevel.COUNTY_AGENT.getName()+"】"+agentStatus.ZERO.getName());

               }else if(vo.getStatusLevel() == agentLevel.ADMIN.getIndex()){

               vo.setStatusName("【"+agentLevel.ADMIN.getName()+"】"+agentStatus.ZERO.getName());

               }

              } else if (vo.getStatus() == agentStatus.ONE.getIndex()) {

                  if(vo.getStatusLevel() == agentLevel.CITY_AGENT.getIndex()){

               vo.setStatusName("【"+agentLevel.CITY_AGENT.getName()+"】"+agentStatus.ONE.getName());

               }else if(vo.getStatusLevel() == agentLevel.COUNTY_AGENT.getIndex()){

               vo.setStatusName("【"+agentLevel.COUNTY_AGENT.getName()+"】"+agentStatus.ONE.getName());

               }else if(vo.getStatusLevel() == agentLevel.ADMIN.getIndex()){

               vo.setStatusName("【"+agentLevel.ADMIN.getName()+"】"+agentStatus.ONE.getName());

               }

              } else if (vo.getStatus() == agentStatus.TWO.getIndex()) {

               if(vo.getStatusLevel() == agentLevel.CITY_AGENT.getIndex()){

                vo.setStatusName("【"+agentLevel.CITY_AGENT.getName()+"】"+agentStatus.TWO.getName());

                }else if(vo.getStatusLevel() == agentLevel.COUNTY_AGENT.getIndex()){

                vo.setStatusName("【"+agentLevel.COUNTY_AGENT.getName()+"】"+agentStatus.TWO.getName());

                }else if(vo.getStatusLevel() == agentLevel.ADMIN.getIndex()){

                vo.setStatusName("【"+agentLevel.ADMIN.getName()+"】"+agentStatus.TWO.getName());

                }

              }

        if(vo.getIsFrozen() == merchantIsFrozen.ZERO.getIndex()){

        vo.setIsFrozenName(merchantIsFrozen.ZERO.getName());

        }else if(vo.getIsFrozen() == merchantIsFrozen.ONE.getIndex()){

        vo.setIsFrozenName(merchantIsFrozen.ONE.getName());

        }

        }

        }

        

        Map<String,String> headMap = new LinkedHashMap<String,String>();

        headMap.put("parentName","上级代理");

        headMap.put("companyName","企业名称");

        headMap.put("merchantName","门店名称");

        headMap.put("telPhone","商户账号");

        headMap.put("merchantTypeName","行业分类");

        headMap.put("positionName","所属区域");

        headMap.put("statusName","审核状态");

        headMap.put("isFrozenName","冻结状态");

        String title = "商户导出";

        GenerateXSSFExcel excel = new GenerateXSSFExcel();

        excel.downloadExcelFile(title, headMap, list, response);

    }

Java代码中参数逻辑Service层

 public void downloadExcelFile(String title,Map<String,String> headMap,List list,HttpServletResponse response){

        try {

         GenerateXSSFExcel g = new GenerateXSSFExcel();

            ByteArrayOutputStream os = new ByteArrayOutputStream();

            SheetBean sb = new SheetBean();//表格

         sb.setIndex(0);//工作表几个

    sb.setName(title);//每个工作表的命名

    List<RowBean> rowList = new ArrayList<RowBean>();//行定义

    List<HashMap> listMap = JSONObject.parseArray(JSONObject.toJSONString(list),HashMap.class);

    RowBean rowBean = new RowBean();//行实例化

     List<CellBean> cellList = new  ArrayList<CellBean>();//列数据

     if(headMap!=null && listMap!=null){

    for(Entry<String, String> vo : headMap.entrySet()){ //标题

    CellBean cellBean = new CellBean();//列数据

        cellBean.setValue(vo.getValue());//给当前设值

    cellList.add(cellBean);//一列多少数据

       }

    rowBean.setCellList(cellList);//把列装进行

    rowList.add(rowBean);//总共多少行

        for(HashMap<String,Object> map:listMap){//多少行

        rowBean = new RowBean();//行实例化

        cellList = new  ArrayList<CellBean>();//列数据

        for(Entry<String, String> vo : headMap.entrySet()){ //列

        CellBean cellBean = new CellBean();//列数据

//         if(map.get(vo.getKey())

//         type = vo.getClass().getSimpleName();

        if(vo.getKey().toLowerCase().indexOf("time")>0){

        cellBean.setValue(dateToStamp(String.valueOf(map.get(vo.getKey()))));//给当前设值

        }else{

        cellBean.setValue(map.get(vo.getKey()));//给当前设值

        }

    cellList.add(cellBean);//一列多少数据

           }

        rowBean.setCellList(cellList);//把列装进行

        rowList.add(rowBean);//总共多少行

        }

         //写进excel

     g.processWorkSheet(sb, rowList,null);

     rowList.clear();

            g.write(os);

     }else{

    

     }

            byte[] content = os.toByteArray();

            InputStream is = new ByteArrayInputStream(content);

            // 设置response参数，可以打开下载页面

            response.reset();

            response.setContentType("application/vnd.ms-excel;charset=utf-8");

            response.setHeader("Content-Disposition", "attachment;filename="

                + new String((title + ".xlsx").getBytes(), "iso-8859-1"));

            ServletOutputStream out = response.getOutputStream();

            BufferedInputStream bis = null;

            BufferedOutputStream bos = null;

       

            try {

              bis = new BufferedInputStream(is);

              bos = new BufferedOutputStream(out);

              byte[] buff = new byte[100000];

              int bytesRead;

              // Simple read/write loop.

              while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {

                bos.write(buff, 0, bytesRead);

              }

            } catch (Exception e) {

             e.printStackTrace();

            } finally {

             if (bis != null)

               bis.close();

             if (bos != null)

               bos.close();

            }

        }catch (Exception e) {

            e.printStackTrace();

        }

    }

通过数据条件查询出来的