Excel导入/导出

报表导入/导出整理

实战位置：需要报表统计的项目怎么少得了数据的导入导出

备注：导出的样式可以自由调整，各种合并预览针对需求定，优秀的项目的报表基本样式都不会很差

导入导出poi依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

一、导出模板

    @GetMapping(value = "/outPutExcelModel")
    public void outPutExcelModel() {
        String modelName = "导入模板.xls";
        File baseDir = new File(uploadFolder + "/excelModel/"); // 创建一个File对象
        String tempName;
        File tempFile;
        File[] files = baseDir.listFiles();
        if(files != null) {
            for(int i = 0; i < files.length; i++) {
                tempFile = files[i];
                tempName = tempFile.getName();
                if(tempName.equalsIgnoreCase(modelName)) {
                    log.info( "127.0.0.1/upload/" + "excelModel/" + modelName);
                }
            }
        }
        HSSFWorkbook wb = this.outPutModel();
        try {
            File saveFile = new File(uploadFolder + "/excelModel/" + modelName);
            saveFile.getParentFile().mkdirs();
            wb.write(saveFile);
        } catch(IOException e) {
            log.error("模板获取错误");
        }
        log.info( "获取模板成功:"+ "127.0.0.1/upload/"+ "excelModel/" + modelName);
    }

    private HSSFWorkbook outPutModel() {
        String excelType = "导入模板";
        HSSFWorkbook wb = new HSSFWorkbook();//创建excel文档对象
        HSSFSheet sheet = wb.createSheet(excelType);//创建excel表单(sheet带标题)
        HSSFRow row1 = sheet.createRow(0);//sheet里创建第0行，范围0~65535
        row1.setHeight((short) 400); //设置高度
        HSSFCell cell = row1.createCell(0);//创建单元格，范围0~255
        cell.setCellValue(excelType);//标题
        int lastCol = 3;
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastCol)); //起始行，截至行，起始列， 截至列
        HSSFCellStyle cellStyle = wb.createCellStyle();  //样式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cell.setCellStyle(cellStyle);
        HSSFRow row2 = sheet.createRow(1);  //在sheet里创建第1行
        for(int i = 1; i <= lastCol; i++) {
            sheet.setColumnWidth(i, 4000);
        }
        row2.createCell(0).setCellValue("序号"); //创建单元格并设置单元格内容
        row2.createCell(1).setCellValue("*姓名");
        row2.createCell(2).setCellValue("*手机号");
        row2.createCell(3).setCellValue("*真实姓名");
        return wb;
    }

二、导出数据列表

    @GetMapping("/outputExcel")
    public void outputExcel() {
        HSSFWorkbook wb;
        String excelType = "Excel标题";
        wb = this.excel(excelType);
        String path = IdWorker.getIdStr();
        try {
            synchronized(this) {
                File saveFile = new File(uploadFolder + "Excel/" + path + "列表.xls");
                saveFile.getParentFile().mkdirs();
                wb.write(saveFile);
            }
        } catch(IOException e) {
            log.error("导出错误");
        }
        log.info("导出成功:" + "127.0.0.1/upload/" + "Excel/" + path + "列表.xls");
    }
    private HSSFWorkbook excel(String excelType) {
        List <Admin> list = new ArrayList <>(); //导出的list
        list.add(new Admin().setUserName("张三").setMobile("666").setRealName("李四"));
        HSSFWorkbook wb = new HSSFWorkbook();//创建excel文档对象
        HSSFSheet sheet = wb.createSheet(excelType);//创建excel表单(sheet带标题)
        HSSFRow row1 = sheet.createRow(0);//sheet里创建第0行，范围0~65535
        row1.setHeight((short) 400); //设置高度
        HSSFCell cell = row1.createCell(0);//创建单元格，范围0~255
        cell.setCellValue(excelType);//标题
        int lastCol = 3;
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastCol));//起始行，截至行，起始列， 截至列
        HSSFCellStyle cellStyle = wb.createCellStyle();//设置样式
        cellStyle.setWrapText(true);//是否换行
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cell.setCellStyle(cellStyle);
        for(Cell r1 : row1) {
            r1.setCellStyle(cellStyle);//统一设置样式
        }
        HSSFRow row2 = sheet.createRow(1);//创建第1行
        row2.createCell(0).setCellValue("序号");
        row2.createCell(1).setCellValue("姓名");
        row2.createCell(2).setCellValue("手机号");
        row2.createCell(3).setCellValue("真实姓名");
        for(Cell r2 : row2) {
            r2.setCellStyle(cellStyle);//统一设置样式
        }
        for(int i = 0; i <= lastCol; i++) {
            sheet.setColumnWidth(i, 3500);//设置宽度
        }
        HSSFRow row;
        for(int i = 0; i < list.size(); i++) {//导出过长可限制
            row = sheet.createRow(i + 2);
            row.createCell(0).setCellValue(i + 1 + "");
            row.createCell(1).setCellValue(list.get(i).getUserName());
            row.createCell(2).setCellValue(list.get(i).getMobile());
            row.createCell(3).setCellValue(list.get(i).getRealName());
            for(Cell r : row) {
                r.setCellStyle(cellStyle);//统一设置样式
            }
        }
        HSSFRow rowCount = sheet.createRow(sheet.getLastRowNum() + 1);
        rowCount.createCell(0).setCellValue("总计");
        for(Cell rc : rowCount) {
            rc.setCellStyle(cellStyle);//统一设置样式
        }
        return wb;
    }

三、数据导入

　　常见异常：Your file appears not to be a valid OLE2 document

　　问题原因：公司内部的加密软件,加密了,所以它不能被读取

    @GetMapping("/inPutExcel")
    public void inPutExcel(@RequestParam("file") MultipartFile file) {
        if(file.isEmpty()) {
            log.info("未找到文件");
        }
        String fileName = file.getOriginalFilename();
        String suffixName = fileName.substring(fileName.lastIndexOf("."));
        if(!(".xls".equals(suffixName) || ".xlsx".equals(suffixName))) {
            log.info("格式错误，请选择Excel格式文件");
        }
        this.inPutData(file);
    }
    private void inPutData(MultipartFile file) {
        List <Admin> userList = new ArrayList();
        InputStream inputStream;
        try {
            inputStream = file.getResource().getInputStream();
            Workbook wb0 = new HSSFWorkbook(inputStream);
            Sheet sht0 = wb0.getSheetAt(0);
            if(sht0.getLastRowNum() <= 1) log.info("导入数据不能为空");
            Admin admin;
            for(Row r : sht0) {
                admin = new Admin();
                if(r.getRowNum() < 2) {//如果当前行的行号（从0开始）未达到2（第三行）则从新循环
                    continue;
                }
                int num = 0;        //空单元格的数量
                for(int i = 1; i < 4; i++) {
                    Cell cell = r.getCell(i);
                    if(null == cell) {        //判断这个行是否为空
                        num++;
                    } else if("BLANK".equals(isString(r, i))) {  //空值
                        num++;
                    }
                }
                if(num == 3) {
                    continue;
                }
                if(r.getCell(1) != null && r.getCell(2) != null && r.getCell(3) != null) {
                    if("BLANK".equals(isString(r, 1)) || "BLANK".equals(isString(r, 2)) || "BLANK".equals(isString(r, 3))) {
                        log.info("必填项不能留空");
                    }
                } else {
                    log.info("必填项不能留空");
                }
                if(r.getCell(1) != null) {
                    if(!"BLANK".equals(isString(r, 1))) {
                        admin.setUserName(isString(r, 1).equals("STRING") ? r.getCell(1).getStringCellValue().trim() : String.valueOf(r.getCell(1).getNumericCellValue()).trim());
                    }
                }
                if(r.getCell(2) != null) {
                    if(!"BLANK".equals(isString(r, 2))) {
                        admin.setMobile(isString(r, 2).equals("STRING") ? r.getCell(2).getStringCellValue().trim() : new DecimalFormat("0").format(r.getCell(2).getNumericCellValue()));
                    }
                }

                if(r.getCell(3) != null) {
                    if(!"BLANK".equals(isString(r, 3))) {
                        admin.setRealName(isString(r, 3).equals("STRING") ? r.getCell(3).getStringCellValue().trim() : String.valueOf(r.getCell(3).getNumericCellValue()).trim());
                    }
                }
                userList.add(admin);
            }
            //todo 获取到的userList然后为所欲为
        } catch(Exception e) {
            e.printStackTrace();
            log.error("表格格式错误或内容填写不规范，请按照模板导入");
        }
        log.info("导入成功");
    }
    //代码抽取
    private String isString(Row r, int i) {
        return r.getCell(i).getCellTypeEnum().toString();
    }