JXL导出

今天在项目中要做一个导出Excel的功能，首先想到的是使用POI，但是项目中要求使用JXL,所以最终使用了JXL。
这次的导出不是按传统的先创建表再设计各种样式，而是采用建立一个模板，导出时先获取模板，根据模板生成导出的Excel文件，然后添加数据。最终代码如下：

页面js方法：（注意：ajax的返回值类型是json,text,html,xml类型，或者可以说ajax的接收类型只能是string字符串，不是流类型，所以无法实现文件下载。）

    /*receiveOrderName 、paymentCityId 是页面条件查询的数据，因为请求路径拼接不能有空，所以下面做了判断，
值为空的时候拼一个0，后续再controller中会做处理*/
    function exportEntrustSocialSecurityFund() {
        $.messager.confirm('确认框', '将按排接单和缴费地区筛选导出', function (r) {
            if (r){
                var receiveOrderName = $('#search_receiveOrderName').val(); 
                var paymentCityId = $('#search_paymentCity').val();
                if ($.trim(receiveOrderName)){
                    if ($.trim(paymentCityId)) {
                        window.location.href='${ctx}/user/handleEntrustSocialSecurityFundIncrease/exportEntrustSocialSecurityFund/' + receiveOrderName + '/' + paymentCityId;
                    }else {
                        window.location.href='${ctx}/user/handleEntrustSocialSecurityFundIncrease/exportEntrustSocialSecurityFund/' + receiveOrderName + '/' + 0;
                    }
                }else if($.trim(paymentCityId)){
                    window.location.href='${ctx}/user/handleEntrustSocialSecurityFundIncrease/exportEntrustSocialSecurityFund/' + 0 + '/' + paymentCityId;
                }else {
                    window.location.href='${ctx}/user/handleEntrustSocialSecurityFundIncrease/exportEntrustSocialSecurityFund/' + 0 + '/' + 0;
                }
            }
        })
    }

controller：

    /**
     * 功能：导出
     */
    @RequestMapping(value = "exportEntrustSocialSecurityFund/{receiveOrderName}/{paymentCityId}", method = RequestMethod.GET)
    @ResponseBody
    public void exportPhonecard(@PathVariable("receiveOrderName") String receiveOrderName,
                                @PathVariable("paymentCityId") String paymentCityId,
                                HttpServletRequest request,HttpServletResponse response) throws Exception {

        /*
         * 查询参数
         */
        Map<String, Object> searchParams = Maps.newHashMap();
        if (StringUtils.isNotBlank(receiveOrderName)&&!receiveOrderName.equals("0")){
            searchParams.put("LIKE_receiveOrder.name", DecodeUtil.decodeParam(receiveOrderName));
        }
        if (StringUtils.isNotBlank(paymentCityId)&&!paymentCityId.equals("0")) {
            searchParams.put("EQ_paymentArea.id", paymentCityId);
        }
        searchParams.put("EQ_handlePart", CommonStatus.HandlePart.EXTERNAL_SUPPLIERS.toString());
        //因为要导出的是两个sheet页，且数据不同，所以先将需要的数据查出来
        List<ProjectEmployeeVO> increase = entrustSocialSecurityFundService.getIncreaseEntrustSocialSecurityFund(searchParams);
        List<ProjectEmployeeVO> decrease = entrustSocialSecurityFundService.getDecreaseEntrustSocialSecurityFund(searchParams);
        if (increase.size()>0||decrease.size()>0){
            //获取路径
            String theWebPath = WebUtils.getRealPath(request.getSession().getServletContext(), "/");
            String templateDownloadDir = theWebPath + SysConstants.DOWNLOAD_ROOT + "/template/";
            String templateUploadDir = theWebPath + SysConstants.UPLOAD_ROOT + "/import/entrustSocialSecurityFund/";
            // 获取需要复制的模板
            String templatePath = templateDownloadDir + "exportEntrustSocialSecurityFund.xls";
            // 需要新生成的模板
            String exportPath = templateUploadDir + "exportEntrustSocialSecurityFund" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xls";
            String oriname = "增减员_";
            String downLoadName = new String(oriname.getBytes("utf-8"), "iso8859-1");
            String exportFile = FileUtil.getFileName(downLoadName); // 导出文件名
            entrustSocialSecurityFundService.exportPhonecard(templatePath, exportPath,increase,decrease);
            try {
                //相应信息文件名
                response.setHeader("Content-disposition", "attachment;filename=\"" + exportFile);
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            //设置数据种类，使客户端浏览器，区分不同种类的数据
            response.setContentType("application/msexcel");
            try {
                //读取文件
                FileInputStream fileInputStream = new FileInputStream(new File(exportPath));
                //输出流
                OutputStream out = response.getOutputStream();
                int i = 0;
                while ((i = fileInputStream.read()) != -1) {
                    //输出
                    out.write(i);
                }
                //关流
                fileInputStream.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

service:

    /**
     * 导出
     */
    public void exportPhonecard(String templatePath, String exportPath, List<ProjectEmployeeVO> increase, List<ProjectEmployeeVO> decrease) {
        Workbook wb = null;
        WritableWorkbook workbook = null;
        WritableSheet sheet = null;
        WritableSheet sheet2 = null;
        try {
            wb = Workbook.getWorkbook(new File(templatePath));
            //参照模板，创建要导出的
            workbook = Workbook.createWorkbook(new File(exportPath), wb);
            if (increase.size()>0){
                sheet = workbook.getSheet(0);
                writeToExcelSheet1(increase, sheet);
            }
            if (decrease.size()>0){
                sheet2 = workbook.getSheet(1);
                writeToExcelSheet2(decrease, sheet2);
            }
            workbook.write();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (WriteException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (wb != null) {
                wb.close();
            }
        }
    }

    /**
     * 写sheet1
     */
    private void writeToExcelSheet1(List<ProjectEmployeeVO> list, WritableSheet sheet) {
        Label label;
        try {
            // 向表中添加记录
            int rows = sheet.getRows();// 定义默认的行
            for (int i = 0; i < list.size(); i++) {
                ProjectEmployeeVO projectEmployeeVO = list.get(i);
                // 数据信息从第二行开始;一列一列插入行 Label(列号,行号,内容,风格)
                label = new Label(0, rows, i + 1 + ""); // 序号
                sheet.addCell(label);
                label = new Label(1, rows, projectEmployeeVO.getEmployeeName()); // 姓名
                sheet.addCell(label);
                label = new Label(2, rows, projectEmployeeVO.getEmployeeIdCard()); // 身份证号
                sheet.addCell(label);
                label = new Label(3, rows, projectEmployeeVO.getEmployeePhone()); // 电话号码
                sheet.addCell(label);
                if (StringUtils.isNotBlank(projectEmployeeVO.getHouseholdPlace())){
                    label = new Label(4, rows, projectEmployeeVO.getEmployeePhone()); // 户口所在地
                }else {
                    label = new Label(4, rows, "---"); // 户口所在地
                }
                sheet.addCell(label);
                if (projectEmployeeVO.getAccountNature()==2){
                    label = new Label(5, rows, "城镇户口"); // 2:城镇户口;
                }else if (projectEmployeeVO.getAccountNature()==3){
                    label = new Label(5, rows, "农村户口"); // 3:农村户口
                }
                sheet.addCell(label);
                if (projectEmployeeVO.getCensusRegisterType()==2){
                    label = new Label(6, rows, "本地户籍"); // 2:本地户籍;
                }else if (projectEmployeeVO.getCensusRegisterType()==3){
                    label = new Label(6, rows, "外地户籍"); // 3:外地户籍
                }
                sheet.addCell(label);
                label = new Label(7, rows, projectEmployeeVO.getPaymentAreaName()); // 缴费城市
                sheet.addCell(label);
                label = new Label(8, rows, projectEmployeeVO.getCheckIdCard()); // 身份证号校验
                sheet.addCell(label);
                label = new Label(9, rows, "---"); // 备注
                sheet.addCell(label);

                rows = rows + 1;
            }
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }
    //写sheet2
    private void writeToExcelSheet2(List<ProjectEmployeeVO> list, WritableSheet sheet) {
        Label label;
        try {
            // 向表中添加记录
            int rows = sheet.getRows();// 定义默认的行
            for (int i = 0; i < list.size(); i++) {
                ProjectEmployeeVO projectEmployeeVO = list.get(i);
                // 数据信息从第二行开始;一列一列插入行 Label(列号,行号,内容,风格)
                label = new Label(0, rows, i + 1 + ""); // 序号
                sheet.addCell(label);
                label = new Label(1, rows, projectEmployeeVO.getEmployeeName()); // 姓名
                sheet.addCell(label);
                label = new Label(2, rows, projectEmployeeVO.getEmployeeIdCard()); // 身份证号
                sheet.addCell(label);
                label = new Label(3, rows, projectEmployeeVO.getEmployeePhone()); // 电话号码
                sheet.addCell(label);
                label = new Label(4, rows, projectEmployeeVO.getPaymentAreaName()); // 缴费城市
                sheet.addCell(label);
                label = new Label(5, rows, projectEmployeeVO.getSsLastPayment()); // 社保最后缴费月
                sheet.addCell(label);
                label = new Label(6, rows, projectEmployeeVO.getPfLastPayment()); // 公积金最后缴费月
                sheet.addCell(label);
                label = new Label(7, rows, projectEmployeeVO.getCheckIdCard()); // 身份证号校验
                sheet.addCell(label);
                label = new Label(8, rows, "---"); // 备注
                sheet.addCell(label);

                rows = rows + 1;
            }
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

完成