导出报表的实现

前端页面

这是和jqgrid结合的表单导出,第一步就是从表格中取出要传到后台的参数
<script type="text/javascript" th:inline="javascript">
    /*<![CDATA[*/
    function loanTransferExport(){
        // 取得要提交的参数
        var ids = $('#jqgrid').getDataIDs();//返回数据表的ID数组["66","39"..]
        var len = ids.length;
        var projectIds = new Array();
        for(var i=0; i<len; i++){
            var getRow = $('#jqgrid').getRowData(ids[i]);//获取当前的数据行
            projectIds.push(getRow.id);
        }
        var data = JSON.stringify(projectIds);
        $('<input type="text" name="projectIds" />').attr("value",data).appendTo("form");
        // 提交表单
        $("#cdzyForm").submit();
        $.successBox("出账划转数据Excel导出成功");
        // 注意return false取消链接的默认动作
        return false;
    }
    /*]]>*/
</script>

后台controller

/**
     * 导出excle数据
     *
     * @param projectIds response
     * @return
     */
    @RequestMapping(value = "exportCoreCustomer", method = {RequestMethod.GET, RequestMethod.POST})
    public void customerUIService(String projectIds,HttpServletResponse response) {

        List<Map<String,Object>> exportDataList = this.customerUIService.getCustomerExportData(projectIds);
        try{
            this.customerUIService.customerExportExcel(exportDataList,response);
        }catch (Exception e){
            LOGGER.error("银行导出客户信息数据Excel导出失败 case: " + e.getMessage(), e);
        }
    }

service

/**
     * 查询需要导出excle的数据集
     * @param projectIds
     * @return
     */
    public List<Map<String, Object>> getCustomerExportData(String projectIds) {
        List<Map<String,Object>> loanApplyList = new ArrayList<Map<String,Object>>();
        String json = restTemplate().postForObject(createUrl("std-basic", "/basic/customer/getCustomerExportData?customerIds="+projectIds),null,String.class);
        JavaType javaType =  getCollectionType(ArrayList.class, Map.class);
        try {
            loanApplyList = objectMapper.readValue(json,javaType);
        } catch (IOException e) {
            LOGGER.error("银行导出客户信息数据Excel导出List转换失败 case: " + e.getMessage(), e);
        }
        return loanApplyList;

    }

    /**
     * 生成excle表格
     * @param exportDataList
     * @param response
     * @return
     */
    public void customerExportExcel(List<Map<String, Object>> exportDataList, HttpServletResponse response) {
        OutputStream ouputStream = null;
        try {
            response.reset();
            HSSFWorkbook workbook = this.createExcelWorkbook(exportDataList);
            String fileName= "白名单"+ DateUtils.formatDate(new Date(),DateUtils.DATE_FULL)+".xls";
            response.setContentType("APPLICATION/OCTET-STREAM");
            response.setHeader("Content-disposition", "attachment;filename=" + new String((fileName ).getBytes(), "iso-8859-1"));
            ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        } catch (Exception e){
            LOGGER.error("导出客户信息Excel导出失败 case: " + e.getMessage(), e);
            if (ouputStream != null) {
                try {
                    ouputStream.close();
                } catch (IOException e1) {
                    LOGGER.error("导出客户信息Excel导出失败 I/O流 close异常 case: " + e1.getMessage(), e1);
                }
            }
        }
    }

    /**
     * 导出客户数据excel workbook
     *
     * @param exportDataList
     * @return
     */
    private HSSFWorkbook createExcelWorkbook(List<Map<String,Object>> exportDataList) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("");
        HSSFRow rowTitle = sheet.createRow(0);
        HSSFCell cell0 = rowTitle.createCell(0);
        HSSFCell cell1 = rowTitle.createCell(1);
        HSSFCell cell2 = rowTitle.createCell(2);
        HSSFCell cell3 = rowTitle.createCell(3);
        HSSFCell cell4 = rowTitle.createCell(4);
        HSSFCell cell5 = rowTitle.createCell(5);
        HSSFCell cell6 = rowTitle.createCell(6);
        HSSFCell cell7 = rowTitle.createCell(7);
        HSSFCell cell8 = rowTitle.createCell(8);
        HSSFCell cell9 = rowTitle.createCell(9);
        HSSFCell cell10 = rowTitle.createCell(10);
        HSSFCell cell11 = rowTitle.createCell(11);
        HSSFCell cell12 = rowTitle.createCell(12);

        cell0.setCellValue("");
        cell1.setCellValue("");
        cell2.setCellValue("");
        cell3.setCellValue("");
        cell4.setCellValue("");
        cell5.setCellValue("");
        cell6.setCellValue("");
        cell7.setCellValue("");
        cell8.setCellValue("");
        cell9.setCellValue("");
        cell10.setCellValue("");
        cell11.setCellValue("");
        cell12.setCellValue("");
        int row = 0;
        for(Map<String,Object> customer : exportDataList) {
            row++;
            HSSFRow rowData = sheet.createRow(row);
            if (customer.get("financingName") != null && !"".equals(customer.get("financingName"))){
                HSSFCell financingName = rowData.createCell(0);
                financingName.setCellValue(customer.get("financingName").toString());
            }
            if (customer.get("welinkCode") != null && !"".equals(customer.get("welinkCode"))){
                HSSFCell welinkCode = rowData.createCell(1);
                welinkCode.setCellValue(customer.get("welinkCode").toString());
            }
            if (customer.get("coreName") != null && !"".equals(customer.get("coreName"))){
                HSSFCell coreName = rowData.createCell(2);
                coreName.setCellValue(customer.get("coreName").toString());
            }
            if (customer.get("customerCode") != null && !"".equals(customer.get("customerCode"))){
                HSSFCell customerCode = rowData.createCell(3);
                customerCode.setCellValue(customer.get("customerCode").toString());
            }
            if (customer.get("name") != null && !"".equals(customer.get("name"))){
                HSSFCell name = rowData.createCell(4);
                name.setCellValue(customer.get("name").toString());
            }
            if (customer.get("divideType") != null && !"".equals(customer.get("divideType"))){
                HSSFCell divideType = rowData.createCell(5);
                divideType.setCellValue(customer.get("divideType").toString());
            }
            if (customer.get("legalPerson") != null && !"".equals(customer.get("legalPerson"))){
                HSSFCell legalPerson = rowData.createCell(6);
                legalPerson.setCellValue(customer.get("legalPerson").toString());
            }
            if (customer.get("contacts") != null && !"".equals(customer.get("contacts"))){
                HSSFCell contacts = rowData.createCell(7);
                contacts.setCellValue(customer.get("contacts").toString());
            }
            if (customer.get("contactNumber") != null && !"".equals(customer.get("contactNumber"))){
                HSSFCell contactNumber = rowData.createCell(8);
                contactNumber.setCellValue(customer.get("contactNumber").toString());
            }
            if (customer.get("contactsPhone") != null && !"".equals(customer.get("contactsPhone"))){
                HSSFCell contactsPhone = rowData.createCell(9);
                contactsPhone.setCellValue(customer.get("contactsPhone").toString());
            }
            if (customer.get("email") != null && !"".equals(customer.get("email"))){
                HSSFCell email = rowData.createCell(10);
                email.setCellValue(customer.get("email").toString());
            }
            if (customer.get("address") != null && !"".equals(customer.get("address"))){
                HSSFCell address = rowData.createCell(11);
                address.setCellValue(customer.get("address").toString());
            }
            if (customer.get("code31") != null && !"".equals(customer.get("code31"))){
                HSSFCell code31 = rowData.createCell(12);
                code31.setCellValue(customer.get("code31").toString());
            }
        }
        return workbook;
    }

注意:返回的数据是list