用excelpoi写一个下载模板

用excelpoi写一个下载模板。

1.用法

Row row = sheet1.getRow(43);//获取行数
Cell cell = row.getCell(2);//获取列数
cell.setCellValue(String.valueOf(CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_TYPE)));
cell.setCellValue("1");//在里边赋值
Row row =sheet1.createRow(i);//创建类
row.createCell(1);//创建行
XSSFSheet sheet1 = workbook.getSheet("sheet2");//获取想要在第几个sheet页中添加数据。

2.下载文件

 @PassToken
    public ResMsg downBatchImportDutyTemplate1(HttpServletResponse response) {
        //log.info("Controller downloadFinanceExcel start.");
        try {
        	//在template中创建一个叫device.xlsx的文件。
            ClassPathResource classPathResource = new ClassPathResource("tempLate/device.xlsx");
            InputStream fis = classPathResource.getInputStream();
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            String fileName = java.net.URLEncoder.encode("device.xlsx", "UTF-8");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);
            ServletOutputStream out = null;
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
            return ResMsg.newInstance(CodeEnum.OK,"设备导出成功");
        } catch (Exception e) {
            return new ResMsg("-1","设备下载模板失败");
        }
    }

3.在sheet页中操作sheet页，去添加

    @PassToken
    @GetMapping(value = "/downDevice1")
    public ResMsg downBatchImportDutyTemplate1(HttpServletResponse response) {
        //log.info("Controller downloadFinanceExcel start.");
        try {
            ClassPathResource classPathResource = new ClassPathResource("tempLate/device.xlsx");
            InputStream fis = classPathResource.getInputStream();
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet1 = workbook.getSheet("sheet2");
            //1.获取设备分类
            //从数据库/redis中获取缓存
            List<AuthCodeList> codeList = 				    	CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_TYPE);
            for (int i = 43; i <63; i++) {
            //创建行数（不能覆盖已经写的数据，否则将
                Row row =sheet1.createRow(i);
            }

            for (int rownum = 43, a = 0,b =codeList.size(); rownum <= 63 ;
                 rownum++,a++,b--) {
                //Row row = sheet1.createRow(rownum);
                XSSFRow row1 = sheet1.getRow(rownum);
                row1.createCell(1);
                row1.createCell(2);
                Cell cell = row1.getCell(1);
                Cell cell1 = row1.getCell(2);
                if(b == 0){
                    break;
                }else {
                    cell.setCellValue(String.valueOf(codeList.get(a).getCodeId()));
                    cell1.setCellValue(String.valueOf(codeList.get(a).getCodeName()));

                }
            }
            //2.获取设备类型
            List<AuthCodeList> codeList1 = CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_KND);
            for (int rownum = 43, a = 0,b =codeList1.size(); rownum <= 63 ;
                 rownum++,a++,b--) {
                XSSFRow row1 = sheet1.getRow(rownum);
                row1.createCell(4);
                row1.createCell(5);
                Cell cell = row1.getCell(4);
                Cell cell1 = row1.getCell(5);
                if(b == 0){
                    break;
                }else {
                    cell.setCellValue(String.valueOf(codeList1.get(a).getCodeId()));
                    cell1.setCellValue(String.valueOf(codeList1.get(a).getCodeName()));
                }
            }
            //3.获取资源网络域
            List<AuthCodeList> codeList2 = CodeListUtil.getCodeList(CodeListUtil.AUTH_DEVICE_DOMAIN);
            for (int rownum = 43, a = 0,b =codeList2.size(); rownum <= 63 ;
                 rownum++,a++,b--) {
                XSSFRow row = sheet1.getRow(rownum);
                row.createCell(7);
                row.createCell(8);
                Cell cell = row.getCell(7);
                Cell cell1 = row.getCell(8);
                if(b == 0){
                    break;
                }else {
                    cell.setCellValue(String.valueOf(codeList2.get(a).getCodeId()));
                    cell1.setCellValue(String.valueOf(codeList2.get(a).getCodeName()));
                }
            }
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            String fileName = java.net.URLEncoder.encode("device.xlsx", "UTF-8");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);
            ServletOutputStream out = null;
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
            return ResMsg.newInstance(CodeEnum.OK,"设备导出成功");
        } catch (Exception e) {
            return new ResMsg("-1","设备下载模板失败");
        }
    }