Java的Excl表格的导出功能（代码全）

本文的项目框架为SSM框架
前端页面需要点击触发按钮进入controller。

@RequestMapping("/telExport")
    public void exportFile(HttpServletResponse response,HttpServletRequest request, String searchvalue, String commuId,String parentId, String treename, String type, String starttime,String endtime, String tn) {
//上方带入的是我所需要查找内容的参数，可删除编写你自己需要的参数
//下方代码 可忽略
        Subject currentUser = SecurityUtils.getSubject();
        String userid = currentUser.getSession().getAttribute("_USER_ID").toString();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        List<CloudRecord> clo = null;
        String value = null;
        if (StringUtils.isNotEmpty(searchvalue)) {
            try {
                value = URLDecoder.decode(searchvalue, "UTF-8");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
        Date st = null;
        Date et = null;
        if (StringUtils.isNotEmpty(starttime)) {
            try {
                st = df.parse(starttime);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        if (StringUtils.isNotEmpty(endtime)) {
            try {
                et = df.parse(endtime);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        switch (type) {// 查询需要导出的内容
        case "2":
            String cityId = parentId.substring(0, 4) + "00";
            clo = cloudRecordService.selectCityTelList(cityId, userid, type,
                    value, st, et);
            break;
        case "3":
            clo = cloudRecordService.selectCityTelList(parentId, userid, type,
                    value, st, et);
            break;
        case "4":
            clo = cloudRecordService.selectCityTelList(treename, userid, type,
                    value, st, et);
            break;
        case "5":
            clo = cloudRecordService.selectCityTelList(commuId, userid, type,
                    value, st, et);
            break;
        }
    //直至此处上方代码可忽略，上方的代码主要是请求数据库查找需要导出的内容，需更换为你自己的内容
    //下发为导出功能不可缺少的步骤
        OutputStream os = null;
        XSSFWorkbook xWorkbook = null;
        try {
            os = response.getOutputStream();
            response.reset();
            response.setContentType("application/msexcel");

            response.setHeader("Content-disposition", "attachment; filename="
                    + tn + df.format(new Date()) + ".xlsx");
            response.setContentType("application/msexcel;charset=UTF-8");// 设置类型
            response.setHeader("Pragma", "No-cache");// 设置头
            response.setHeader("Cache-Control", "no-cache");// 设置头
            response.setDateHeader("Expires", 0);// 设置日期头

            xWorkbook = new XSSFWorkbook();
            XSSFSheet xSheet = xWorkbook.createSheet("通话");

            // set Sheet页头部
            setSheetHeader(xWorkbook, xSheet);
            // set Sheet页内容
            setSheetContent(xWorkbook, xSheet, clo);

            xWorkbook.write(os);

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != os) {
                try {
                    os.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != xWorkbook) {
                try {
                    xWorkbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet xSheet) {
        //有多少列要导出 设置多少个
        xSheet.setColumnWidth(0, 20 * 256);
        xSheet.setColumnWidth(1, 20 * 256);
        xSheet.setColumnWidth(2, 20 * 256);
        xSheet.setColumnWidth(3, 20 * 256);
        xSheet.setColumnWidth(4, 20 * 256);
        xSheet.setColumnWidth(5, 20 * 256);
        xSheet.setColumnWidth(6, 20 * 256);


        CellStyle cs = xWorkbook.createCellStyle();
        // 设置水平垂直居中
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cs.setWrapText(true);// 是否自动换行
        XSSFRow xRow0 = xSheet.createRow(0);
        //下发代码为设置你需要导出内容的名称，内容自行填写
        XSSFCell xCell0 = xRow0.createCell(0);
        xCell0.setCellStyle(cs);
        xCell0.setCellValue("区县");

        XSSFCell xCell1 = xRow0.createCell(1);
        xCell1.setCellStyle(cs);
        xCell1.setCellValue("小区");

        XSSFCell xCell2 = xRow0.createCell(2);
        xCell2.setCellStyle(cs);
        xCell2.setCellValue("栋号");

        XSSFCell xCell3 = xRow0.createCell(3);
        xCell3.setCellStyle(cs);
        xCell3.setCellValue("单元号");

        XSSFCell xCell4 = xRow0.createCell(4);
        xCell4.setCellStyle(cs);
        xCell4.setCellValue("被叫电话号码");

        XSSFCell xCell5 = xRow0.createCell(5);
        xCell5.setCellStyle(cs);
        xCell5.setCellValue("通话时长(-1被叫未接通,0主叫未接通,大于0：通话时长)");

        XSSFCell xCell6 = xRow0.createCell(6);
        xCell6.setCellStyle(cs);
        xCell6.setCellValue("接通时间");
    }

        private void setSheetContent(XSSFWorkbook xWorkbook, XSSFSheet xSheet,
            List<CloudRecord> clo) {

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        CellStyle cs = xWorkbook.createCellStyle();
        cs.setWrapText(true);

        if (null != clo && clo.size() > 0) {
            for (int i = 0; i < clo.size(); i++) {
                XSSFRow xRow = xSheet.createRow(i + 1);
                CloudRecord cloudRecord = clo.get(i);

                for (int j = 0; j < 8; j++) {
                    XSSFCell xCell = xRow.createCell(j);
                    xCell.setCellStyle(cs);
                    switch (j) {
                    //switch 为取得你要导出的内容，注意case 后面的数字与上方你设置的标题名称对于
                    case 0:
                        xCell.setCellValue(cloudRecord.getAreaName());
                        break;
                    case 1:
                        xCell.setCellValue(cloudRecord.getAddressName());
                        break;
                    case 2:
                        xCell.setCellValue(cloudRecord.getBuildingno());
                        break;
                    case 3:
                        xCell.setCellValue(cloudRecord.getUnit());
                        break;
                    case 4:
                        xCell.setCellValue(cloudRecord.getTelPhone());
                        break;
                    case 5:
                        xCell.setCellValue(cloudRecord.getTelduration());
                        break;
                    case 6:
                        if (cloudRecord.getTime() != null
                                && !cloudRecord.getTime().equals(""))
                            xCell.setCellValue(sdf.format(cloudRecord.getTime()));
                        break;
                    default:
                        break;
                    }
                }
            }

        }

    }

这样一个完成的Excl表格导出功能就结束了。