项目笔记：导出Excel功能分sheet页插入数据

　　导出Excel功能分sheet页处理数据：

/*导出EXCEL*/

public void createExcel() {

    log.info("导出Excel功能已经启动-BEGIN");

    JxlUtil jsl = new JxlUtil();

    List<Device> dataList =new ArrayList<Device>();

    List<DeviceExport> list = new ArrayList<DeviceExport>();

    // 构建路径

    String downLoadPath = "/WEB-INF/download/asset/";

    String rootPath = getServletContext().getRealPath(downLoadPath);

    String fileName = "";

    File file=new File(rootPath);    

    try {

        if(!(file.exists()||file.isDirectory())){

            file.mkdirs();

        }

        if (null != getRequest().getParameter("ids")) {

            String ids[] = getRequest().getParameter("ids").split(",");

            for (int i = ; i < ids.length; i++) {

                if(null!=ids[i]){

                    device = deviceService.queryById(ids[i].trim());

                    dataList.add(device);

                }

            }

          }else{

              dataList = deviceService.queryForExcel();

          }

        if(dataList!=null){

            for (Device device : dataList) {

                DeviceExport deviceExport= new DeviceExport();

                if(null!=device.getId()){

                    deviceExport.setId(device.getId());

                     }

                if(null!=device.getIp()){

                    deviceExport.setIp(device.getIp());

                }

                if(null!=device.getMac()){

                    deviceExport.setMac(device.getMac());

                }

                if(null!=device.getName()){

                    deviceExport.setName(device.getName());

                }

                if(null!=device.getOrganization()){

                    Organization organization=device.getOrganization();

                    String Aname =organization.getName();

                    String name= getAname(organization, Aname);

                    deviceExport.setOrganizationName(name);

                }

                if(null!=device.getRegState()){

                        deviceExport.setRegState(device.getRegState());

                }

                if(null!=device.getUser()){

                    deviceExport.setUserName(device.getUser().getName());

                }

                if(null!=device.getProtectState()){

                    deviceExport.setProtectState(device.getProtectState());

                }

                if(null!=device.getIsOpened()){

                    deviceExport.setIsOpened(device.getIsOpened());

                }

                String osName = MessageUtils.getMessage(device.getOs().getName());

                deviceExport.setOsName(osName);

                String deviceType = MessageUtils.getMessage(device.getDeviceType().getName());

                deviceExport.setDeviceTypeName(deviceType);

                list.add(deviceExport);

            }

        }

        String interBase = "sys.column.name.device";

        //String inter_value_key = "#isOpened#roamState#protectState#";

        String inter_value_key = "#isOpened#protectState#regState#";

        String[] inter_value_ary = { "isOpened.0", "isOpened.1", "protectState.0", "protectState.1","regState.0","regState.1"};

        //导出不显示漫游状态

        //String[] inter_value_ary = { "isOpened.0", "isOpened.1","roamState.0", "roamState.1","protectState.0", "protectState.1"};

        fileName = jsl.getInter(interBase.replace("column", "table"))

                + new Date().getTime();

        String targetfile = rootPath + System.getProperty("file.separator")

                + fileName + ".xls";

        //分sheet页处理

        int total = dataList.size();//总数

        int max = ;//每sheet页允许最大数

        int avg = total / max;//sheet页个数

        // 创建可写入的Excel工作薄

        WritableWorkbook wwb;

        wwb = Workbook.createWorkbook(new File(targetfile));

        for(int i=;i<avg+;i++){

            // 创建Excel工作表

            WritableSheet ws = wwb.createSheet("已注册设备"+(i+), i);

            int num = i * max;

            int index = ;

            List<DeviceExport> exportList = new ArrayList<DeviceExport>();

            for(int m = num; m < total; m++){//m即为每个sheet页应该开始的数

                if(index == max){//判断 index = max 的时候跳出里层的for循环

                    break;

                }

                DeviceExport deviceExport=list.get(m);

                exportList.add(deviceExport);//从总的list数据里面取出该处于哪个sheet页的数据，然后加进exportList，exportList即为当前sheet页应该有的数据

                index++;

            }

            // 获取需要内容国际化的字段

            jsl.creatDeviceExcel(ws, exportList, interBase, inter_value_key,inter_value_ary);

        }

        // 写入Exel工作表

        wwb.write();

        // 关闭Excel工作薄对象

        wwb.close();

        getResponse().setContentType(getServletContext().getMimeType(fileName));

        getResponse().setHeader("Content-Disposition", "attachment;fileName="+new String(fileName.getBytes("gb2312"), "ISO8859-1")+".xls");

        String fullFileName = getServletContext().getRealPath(downLoadPath + fileName+ ".xls");

        InputStream in = new FileInputStream(fullFileName);

        OutputStream out = getResponse().getOutputStream();

        int b;

        while((b=in.read())!= -){

            out.write(b);

        }

        in.close();

        out.close();

        /*ServletActionContext.getRequest().setAttribute("downLoadPath",

                downLoadPath);

        ServletActionContext.getRequest().setAttribute("fileName",

                fileName + ".xls");*/

        this.msg = RESULT_SUCCESS;

        log.info("导出EXCEL提示信息为:"+this.msg);

    } catch (Exception e) {

        log.error("导出EXCEL失败:" + e.getMessage());

    }

    log.info("导出Excel功能已经启动-END");

    /*return "downLoadUI";*/

}

　　导出效果如下：

项目笔记：导出Excel功能分sheet页插入数据的相关教程结束。