java将数据导出到Excel中

/**

页面上点击下载Excel按钮，触发事件，将请求发送到Controller

**/

/**
     * 
     * 〈一句话功能简述〉 〈功能详细描述〉 获取符合查询条件的所有订单列表并且存入excel中
     * [页面上点击下载按钮跳到此controller对应的requestMapping]
     * @param order
     * @param response
     * @param request
     * @throws ParseException
     * @see [相关类/方法]（可选）
     * @since [产品/模块版本] （可选）
     */
    @RequestMapping(value = "/getOrderXls", method = RequestMethod.GET)
    public void getExportExcelList(Order order, HttpServletResponse response, HttpServletRequest request)
            throws ParseException {
        File file = null;
        Map<String, Object> map = new HashMap<String, Object>();
        //FIXME

         map.put("条件",页面上传过来的参数);
        // 获取excel
        file = orderSearchService.getExportExcelList(map);
        returnFileToResp(response, file);
        logger.info(new Gson().toJson(order));
    }

 

/**
     * 若查询到并生成了所需要的报表文件，则返回到response对象；
     * 
     * @param response
     * @param file
     */
    private void returnFileToResp(HttpServletResponse response, File file) {
        if (null == file) {
            try {
                response.setContentType("text/html;charset=GBK");
                response.getWriter().print("<script type='text/javascript'>alert('下载文件失败');</script>");
            } catch (IOException e) {
                logger.info(e.getMessage());
                e.printStackTrace();
            }
            return;
        }
        String fileName = file.getName();
        try {
            InputStream fis = new BufferedInputStream(new FileInputStream(file));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();

            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes()));
            response.addHeader("Content-Length", "" + file.length());

            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (FileNotFoundException e) {
            logger.info(e.getMessage());
            e.printStackTrace();
        } catch (IOException e) {
            logger.info(e.getMessage());
            e.printStackTrace();
        }
    }

 

/**对应其servcie层对数据的处理**/

getExportExcelList()  方法实现如下：


    @Override
    public File getExportExcelList(Map<String, Object> map) {
        // 获取符合条件的订单列表
        List<B2GOrder> list = dalClient.queryForList("staff.getExport_ExcelList", map, B2GOrder.class);
        if (list.size() == 0) {
            return null;
        } else {
            return getReportFile(map, list);
        }
    }

 

public File getReportFile(Map<String, Object> map, List<B2GOrder> list) {
        // 定义一个时间格式
        DateFormat timeFormat = new DateFormat(MartConstants.YYYY_MM_DD_HH_MM_SS);
        WritableCellFormat wcf_time = new WritableCellFormat(timeFormat);
        SimpleDateFormat sdf = new SimpleDateFormat(MartConstants.YYYY_MM_DD_HH_MM_SS);
        String beginTimeStr = (String) map.get("orderStart");
        String endTimeStr = (String) map.get("orderEnd");
        String name = new StringBuffer().append("[").append("Outter").append("]").append(beginTimeStr.split(" ")[0])
                .append("~").append(endTimeStr.split(" ")[0]).append(".xls").toString();
        String path = Thread.currentThread().getContextClassLoader().getResource("").toString();
        logger.info(path);
        path = path.substring(5);
        logger.info(path);
        File file = new File(path + name);

        if (file.exists()) {
            logger.info("已经存在excel文件");
            file.delete();// 删除已经存在的文件
        }
        try {
            file.createNewFile();
        } catch (IOException e) {
            logger.info(e.getMessage());
            return null;
        }
        try {
            long start = System.currentTimeMillis();
            WritableWorkbook workbook = Workbook.createWorkbook(file);
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            // 获取excel表格的title

 

          //MartConstants.B2G_EXCEL_TITLE定义为

 public static final String B2G_EXCEL_TITLE = "单号,联系人姓名,联系人手机号码,乘机人姓名,手机号码,部门,商品,下单时间,起飞时间,到达时 间,数量,机票金额,保险金额,订单状态,使用状态"; 
            String[] title = MartConstants.B2G_EXCEL_TITLE.split(",");
            for (int row = 0; row < title.length; row++) {
                Label label = new Label(row, 0, title[row]);
                sheet.addCell(label);
            }
            WritableCellFormat wcf = new WritableCellFormat();
            wcf.setWrap(true);
            for (B2GOrder order : list) {
                order.setAirPrice(MathUtil.sub(
                        MathUtil.add(MathUtil.add(order.getPrice(), order.getTax()).doubleValue(), order.getFee())
                                .doubleValue(), order.getAdjustment()).doubleValue());
            }
            for (int row = 0; row < list.size(); row++) {
                B2GOrder tmp = list.get(row);
                int column = 0;

            sheet.addCell(new Label(column++, row + 1, "要添加的值");// 添加要添加的数据
            workbook.write();
            workbook.close();
            logger.info("生成excel耗时：" + String.valueOf(System.currentTimeMillis() - start));

        } catch (IOException e) {
            logger.info(e.getMessage());
            e.printStackTrace();
        } catch (RowsExceededException e) {
            logger.info(e.getMessage());
            e.printStackTrace();
        } catch (WriteException e) {
            logger.info(e.getMessage());
            e.printStackTrace();
        } catch (ParseException e) {
            logger.info(e.getMessage());
            e.printStackTrace();
        }
        return file;
    }