POI+EL表达式 Excel导出功能

    在做项目的过程中，用Excel来导入导出数据是很常见的事情，这次负责的项目就有这么一个功能，用户想要的需求就是根据不同的查询条件来实现不同的导出效果，已便于读取与保存。

界面的效果如下：

     在界面上可以看到有很多的查询条件，实现的目标就是我点击搜索出现什么信息，那么你就必须得导出什么信息，其实蕴含的意思就是前期方向，搜索和导出Excel实现的功能是一样的。那么在项目中我们是如何应用的呢？

     首先也是分层开发，从页面-Controller-Service-Dao层，其实思路很简单，我们主要就是通过El表达式，通过Form窗体提交的方式将其赋值到导出Excel的按钮中，代码如下：

JSP层页面显示部分代码：

<label>订单号</label>
<input type="text" name="<span style="color:#ff0000;">search_LIKE_orderNo</span>" class="input-medium" maxlength="50"
                               value="<span style="color:#ff0000;">${param.search_LIKE_orderNo}</span>">  
<label>客户姓名</label>
<input type="text" name="search_LIKE_member.nick" class="input-medium"
                               maxlength="20"
                               value="${nick}">  
<label>手机号</label>
<input type="text" name="search_LIKE_member.mobile" class="input-medium" maxlength="11"
                               value="${mobile}">  

（只选择了三个简单的控件来表示）

Controller层代码：

     通过分页查询的方式，将JSP页面的关于search_方式的值触发显示：

@RequestMapping(value = "exportExcel")
    public void exportExcel( //分页查询的一些参数
            @RequestParam(value = "sortOrder", defaultValue = "desc") String sortOrder,
            @RequestParam(value = "sortName", defaultValue = "createDate") String sortName,
            @RequestParam(value = "page", defaultValue = "1") int page,
            @RequestParam(value = "pagesize", defaultValue = "50") int pageSize,
            @RequestParam(value = "messageTip", defaultValue = "") String messageTip,
            HttpServletRequest request, HttpServletResponse response,
            Model model) throws UnsupportedEncodingException {   //抛异常处理

        response.setContentType("application/vnd.ms-excel");
        String dateFile = DateUtil.formatDate(new Date());
        //导出Excel名称显示
        String codedFileName = new String(URLDecoder.decode("交易明细列表-", "UTF-8").getBytes("gb2312"), "ISO8859-1") + dateFile;
        response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
        // 产生工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //产生工作表对象
        HSSFSheet sheet = workbook.createSheet();
       <span style="color:#ff0000;"> Map<String, Object> searchParams = Servlets.getParametersStartingWith(request, "search_");    //获取jsp页面search_的一些触发</span>
        //对于时间的判断
        String createDate = (String) searchParams.get("EQ_createDate");
        if (createDate != null) {
            searchParams.remove("EQ_createDate");
            searchParams.put("GTE_createDate", DateUtil.strToDate(createDate.trim() + " 00:00:00"));
            searchParams.put("LTE_createDate", DateUtil.strToDate(createDate.trim() + " 23:59:59"));
        }
        //获取分页查询的列表
        Page<BankTransfer> transfers = bankTransferService.list(searchParams, page, Integer.MAX_VALUE, sortName, sortOrder);
        model.addAttribute("transfers", transfers);

        int count = 0;
        if (transfers != null && transfers.getContent() != null && transfers.getContent().size() > 0) {
            //列名
            List<BankTransfer> transferList = transfers.getContent();
            HSSFRow row0 = sheet.createRow(0);//创建一行
            HSSFCell cell0 = row0.createCell(0);//创建一列
            cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell0.setCellValue("序号");

            HSSFCell cell1 = row0.createCell(1);//创建一列
            cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell1.setCellValue("交易单号");

            HSSFCell cell2 = row0.createCell(2);//创建一列
            cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell2.setCellValue("客户姓名");

            ……

            HSSFCell cell10 = row0.createCell(10);//创建一列
            cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell10.setCellValue("交易备注");
<span style="white-space:pre">	</span>    //赋值处理
            for (BankTransfer transfer : transferList) {
                String orderNo = transfer.getOrderNo();//交易单号
                /*String nick = transfer.getMember().getNick();//客户姓名
                String mobile = transfer.getMember().getMobile();//客户手机号*/
                Double money;
                money = transfer.getMoney();//交易金额
                /*对money进行一下为空判断*/
                if (money == null) {
                    money = 0.0D;
                }
                Date time = transfer.getCreateDate();//交易时间
                String payType = transfer.getPayType();  //交易类型
                /*2:账单缴费，3：商城,  4：转账  5：退款*/
                String type = "";
                if (payType.equals("2")) {
                    type = "账单缴费";
                } else if (payType.equals("3")) {
                    type = "商城";
                } else if (payType.equals("4")) {
                    type = "转账";
                } else if (payType.equals("5")) {
                    type = "退款";
                }
                String statee = transfer.getStatus();//交易状态

                /*将数字转换成对应的汉字来显示到Excel中*/
                String states = "";
                if (statee.equals("0")) {
                    states = "转账中";
                } else if (statee.equals("1")) {
                    states = "成功";
                } else if (statee.equals("2")) {
                    states = "失败";
                } else if (statee.equals("3")) {
                    states = "交易单号不存在";
                }
                String description = transfer.getDescription();//交易备注
                String nick = "";
                String mobile = "";
                List<BankTransfer> TransferListItems = bankTransferService.findByMemberId(transfer.getMember().getId());
                if (TransferListItems != null && TransferListItems.size() > 0) {
                    for (BankTransfer TransferListItem : TransferListItems) {
                        Member bankTransfer = TransferListItem.getMember();//客户信息
                        nick = bankTransfer.getNick();//客户姓名
                        mobile = bankTransfer.getMobile();//客户手机号
                    }
                }

                String shopname = "";
                List<BankTransfer> bankTransfers = bankTransferService.findByShopId(transfer.getShopId());
                if (bankTransfers != null && bankTransfers.size() > 0) {
                    for (BankTransfer bankTransfer : bankTransfers) {
                        if (StringUtils.isNotBlank(bankTransfer.getShopId())) {
                            Shop bankTransferShop = shopService.findById(bankTransfer.getShopId());

                            shopname = bankTransferShop.getName();     //获取商家名称
                        }
                    }
                }

                String subdistrictname = "";
                List<BankTransfer> bankTransfers1 = bankTransferService.findBySubdistrictId(transfer.getSubdistrictId());
                if (bankTransfers1 != null && bankTransfers1.size() > 0) {
                    for (BankTransfer bankTransfer : bankTransfers1) {
                        if (StringUtils.isNotBlank(bankTransfer.getSubdistrictId())) {
                            Subdistrict bankTransferSubdistrict = subdistrictService.findById(bankTransfer.getSubdistrictId());
                            subdistrictname = bankTransferSubdistrict.getName();
                        }
                    }
                }
                count++;//序号

                HSSFRow row = sheet.createRow(count);//创建一行

                HSSFCell cell00 = row.createCell(0);//创建一列
                cell00.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell00.setCellValue("" + count);

                HSSFCell cell01 = row.createCell(1);//创建一列
                cell01.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell01.setCellValue(orderNo);

                …………

                HSSFCell cell11 = row.createCell(10);//创建一列
                cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell11.setCellValue(description);
            }
        }

        OutputStream os = null;
        try {
            os = response.getOutputStream();
            workbook.write(os);
        } catch (Exception e) {
            logger.error("", e);
        } finally {      //进行判断
            try {
                if (os != null) {
                    os.flush();
                    os.close();
                }
            } catch (IOException e) {
                logger.error("", e);
            }
        }
    }

     对于Service，只来显示一个List，分页查询的方法，其他的都是简单的一些传值，调用

/分页list方法
    public Page<BankTransfer> list(Map<String, Object> searchParams, int page, int pageSize, String sortName, String sortOrder) {
        PageRequest pageRequest = buildPageRequest(page, pageSize, sortName, sortOrder);
        Specification<BankTransfer> spec = buildSpecification(searchParams);

        return bankTransferDao.findAll(spec, pageRequest);
    }

    /**
     * 创建分页请求.
     */

    private PageRequest buildPageRequest(int pageNumber, int pagzSize, String sortName, String sortOrder) {
        Sort sort = null;
        if (StringUtils.isBlank(sortOrder)) {
            sortOrder = "desc";
        }
        if (StringUtils.isBlank(sortName)) {
            sortName = "id";
        }
        if (StringUtils.equalsIgnoreCase(sortOrder, "desc")) {
            sort = new Sort(Sort.Direction.DESC, sortName);
        } else {
            sort = new Sort(Sort.Direction.ASC, sortName);
        }
        return new PageRequest(pageNumber - 1, pagzSize, sort);
    }

    /**
     * 创建动态查询条件组合.
     */
    private Specification<BankTransfer> buildSpecification(Map<String, Object> searchParams) {
        Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
        Specification<BankTransfer> spec = DynamicSpecifications.bySearchFilter(filters.values(), BankTransfer.class);
        return spec;
    }

          这样主要代码就完工了，其实实现的方式很简单，通过EL 表达式的关键字段，比如search_EQ,search_LIKE等相等或模糊查询来实现想要的功能。这样我们就完成了我们想要的功能。不管是点击搜索按钮，还是导出Excel按钮，其实实现的效果是一样的，只是数值存放的位置发生了小小的变化。

     通过El表达式，${},EQ,LIKE等直接方便了我们代码的开发，提高了开发效率，值得好好总结。