poi导入导出

为什么要使用poi呢，为什么不适用easypoi和easyexcel呢，那肯定是这两个工具类不能实现功能，所以才采用poi啊；一般的导入导出，就采用上面两个工具就可以了，简单方便，但是如果你要对单元格进行复杂的操作的时候，需要对特定的单元格进行操作（比如导入参数校验，给错误的单元格添加批注，更改颜色等等）。

1. 技术架构

1.springBoot 
2.mybatis-plus
3.java
4.hutool
5.lombok
6.mysql

2.依赖注入

本次选取的是4.1.0版本的poi

  <!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.0</version>
        </dependency>

3.excel导出

首先要知道的是，poi的主要操作类型是HSSF、XSSF对应的版本是xls和xlsx,本次演示采用的是XSSF。
要知道一些定义：

1. workbook 操作对象
2. sheet 工作蒲
3. row 行
4. cell 单元格

主要就是这个4个。

(1).数据库

建表：t_user
建表语句：

CREATE TABLE `user` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `nickname` varchar(255) DEFAULT NULL COMMENT '昵称',
  `phone` varchar(255) DEFAULT NULL COMMENT '电话',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) DEFAULT '0' COMMENT '是否删除 0 不 1 删除',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1557644078290403331 DEFAULT CHARSET=utf8mb4;

上面可以看到，主要的字段就是nickname,phone,password

(2).实体类

注意：mybatis-plus 的配置文件需要在yml里面配置好

@Data
@TableName("user")
public class User {
    @TableId
    private Long userId;

    private String nickname;

    private String phone;

    private String password;

    private Date createTime;

    private Date updateTime;

    @TableLogic
    private Integer isDeleted;
}

(3).控制层

我这里导出采用了两种方式，一种是把流返回到请求体中，直接下载；一种是改为base64字节流，方便restful代码管理

 private final UserService userService;

    /**
     * 导出文件放在响应体中（必须用void不然会有异常，虽然不报错，但是日志一直会打印）
     *
     * @param response 响应体
     */
    @GetMapping("exportByResponse")
    public void exportUserByResponse(HttpServletResponse response) {

        this.userService.exportUserByResponse(response);
    }

    /**
     * 导出文件，base64
     *
     * @param response 请求体
     * @return 文件base64
     * @throws IOException 异常
     */
    @GetMapping("exportByBase64")
    public CommonResult<Object> exportUserByBase64(HttpServletResponse response) throws IOException {

        Map<String, Object> result = this.userService.exportUserByBase64();
        return CommonResult.success(result);
    }

(4). 服务层

1.接口：

  /**
     * 数据导出
     *
     * @param response 响应体
     */
    void exportUserByResponse(HttpServletResponse response);

    /**
     * 导出，以字节码返回
     *
     * @return 字节码
     * @throws IOException 异常
     */
    Map<String, Object> exportUserByBase64() throws IOException;

2.具体服务实现类

    @Override
    public void exportUserByResponse(HttpServletResponse response) {

        List<User> userList = this.list();
        //创建workbook工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建工作簿
        Sheet sheet = workbook.createSheet("用户信息");
        //默认行高度
        sheet.setDefaultRowHeightInPoints(20F);
        //默认列宽度
        sheet.setDefaultColumnWidth(20);
        sheet.setColumnWidth(20, 50 * 256);
        //创建标题
        //创建行，从第一行开始
        Row firstRow = sheet.createRow(0);
        firstRow.setHeightInPoints(26.25f);

        // 字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);

        // 单元格内容样式
        CellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setLocked(true);
        normalStyle.setWrapText(true);
        normalStyle.setFont(font);

        //处理第一行；标题列
        List<String> headCellNames = Lists.newArrayList("姓名", "手机号", "密码");
        this.handleCellContent(firstRow, headCellNames);

        //处理表格内容
        List<String> cellValues;
        for (int i = 0; i < userList.size(); i++) {
            User record = userList.get(i);
            cellValues = CollUtil.toList(record.getNickname(), record.getPhone(),
                    record.getPassword());
            Row sheetRow = sheet.createRow(i + 1);
            this.handleCellContent(sheetRow, cellValues);
        }

        try {
            String filename = "record.xlsx";
            // 自定义输出文件名
            String typeName = new String(filename.getBytes("UTF-8"), "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename="
                    + typeName);
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();


        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    @Override
    public Map<String, Object> exportUserByBase64() throws IOException {
        List<User> userList = this.list();
        //创建workbook工作簿
        Workbook workbook = new XSSFWorkbook();
        //创建工作簿
        Sheet sheet = workbook.createSheet("用户信息");
        //默认行高度
        sheet.setDefaultRowHeightInPoints(20F);
        //默认列宽度
        sheet.setDefaultColumnWidth(20);
        sheet.setColumnWidth(20, 50 * 256);
        //创建标题
        //创建行，从第一行开始
        Row firstRow = sheet.createRow(0);
        firstRow.setHeightInPoints(26.25f);
        //创建单元格从第一个开始
        Cell firstRowCell = firstRow.createCell(0);


        // 字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);

        // 单元格内容样式
        CellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setLocked(true);
        normalStyle.setWrapText(true);
        normalStyle.setFont(font);

        //处理第一行；标题列
        List<String> headCellNames = Lists.newArrayList("姓名", "手机号", "密码");
        this.handleCellContent(firstRow, headCellNames);

        //处理表格内容
        List<String> cellValues;
        for (int i = 0; i < userList.size(); i++) {
            User record = userList.get(i);
            cellValues = CollUtil.toList(record.getNickname(), record.getPhone(),
                    record.getPassword());
            Row sheetRow = sheet.createRow(i + 1);
            this.handleCellContent(sheetRow, cellValues);
        }

        Map<String, Object> resultMap = new HashMap<>(10);
        resultMap.put("fileName", "用户信息表");

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);
            String baseString = Base64.getEncoder().encodeToString(os.toByteArray());
            resultMap.put("stream", baseString);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            os.flush();
            os.close();
        }

        return resultMap;
    }


/**
     * 导出处理一行的值
     *
     * @param hssfRow    行
     * @param cellValues 列值
     */
    private void handleCellContent(Row hssfRow, List<String> cellValues) {
        Sheet sheet = hssfRow.getSheet();
        Workbook workbook = sheet.getWorkbook();
        // 字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 15);
        font.setFontName("楷体");
        //加粗
        font.setBold(false);

        // 单元格内容样式
        CellStyle normalStyle = workbook.createCellStyle();
        normalStyle.setLocked(true);
        normalStyle.setWrapText(true);
        normalStyle.setFont(font);
        for (int i = 0; i < cellValues.size(); i++) {
            Cell cell = hssfRow.createCell(i);
            cell.setCellValue(cellValues.get(i));
            cell.setCellStyle(normalStyle);
        }
    }

导出的效果


以上的代码需要注意的就是这个表格：姓名、手机号、密码；这些是写死的。
看到上面的代码可以发现，明明使用easypoi、easyexcel使用注解就可以简单的实现标题头部的问题，根本不需要进行自己手动写，而且内容也是，只要把列表放到workbook里面就可以直接导出一份excel，为什么要这样写呢。
多学点还是有用处的。

4.excel导入

首先导入我这里做了参数校验，如果有错会显示批注。(这里的excel导出采用的返回响应体中，你也可以采用base64返回，我没写，而且我个人感觉这部分代码写的，有很大的提示空间，但是目前没时间改，如果后面有时间改，我会好好改的)

（1）.实体类对象

1.解析对象

@Data
public class ExportUserVO {
    /**
     * 用户id
     */
    private Long userId;
    /**
     * 昵称
     */
    private String nickname;
    /**
     * 电话
     */
    private String phone;
    /**
     * 密码
     */
    private String password;
}

2.错误提示对象

@Data
public class FailErrorVO {
    /**
     * 表格内容
     */
    List<String> excelInfo;

    /**
     * 错误信息
     */
    List<String> errorMsg;
}

(2).控制层

    /**
     * 导入数据，参数校验结果返回到文件流中
     *
     * @param response 请求体
     */
    @PostMapping("importUserErrorByResponse")
    public CommonResult<Object> importUserErrorByResponse(@RequestParam(name = "file") MultipartFile file, HttpServletResponse response) throws IOException {

        this.userService.importUserErrorByResponse(file, response);
        return CommonResult.success();
    }

（3）.服务类

1.接口

 /**
     * 导入数据，参数校验错误返回到响应体中
     *
     * @param file     文件
     * @param response 响应体
     * @throws IOException 异常
     */
    void importUserErrorByResponse(MultipartFile file, HttpServletResponse response) throws IOException;

2.具体实现

  @Override
    public void importUserErrorByResponse(MultipartFile file, HttpServletResponse response) throws IOException {
        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        List<ExportUserVO> exportUserVos = this.parseExcel(workbook);
        System.out.println(exportUserVos);
        //保存到数据库
        List<User> insertUser = exportUserVos.stream().map(vo -> BeanUtil.toBean(vo, User.class))
                .collect(Collectors.toList());
        this.saveBatch(insertUser);

        System.out.println(failErrorList);
        //创建workbook工作簿
        Workbook writeWorkbook = new XSSFWorkbook();
        //创建工作簿
        Sheet sheet = writeWorkbook.createSheet("用户信息");
        //默认行高度
        sheet.setDefaultRowHeightInPoints(20F);
        //默认列宽度
        sheet.setDefaultColumnWidth(20);
        sheet.setColumnWidth(20, 50 * 256);
        //创建标题
        //创建行，从第一行开始
        Row firstRow = sheet.createRow(0);
        firstRow.setHeightInPoints(26.25f);
        //创建单元格从第一个开始
        Cell firstRowCell = firstRow.createCell(0);


        //处理第一行；标题列
        List<String> headCellNames = Lists.newArrayList("姓名", "手机号", "密码");
        this.handleCellContent(firstRow, headCellNames);

        //处理表格内容
        for (int i = 0; i < failErrorList.size(); i++) {
            Row sheetRow = sheet.createRow(i + 1);
            this.handelFailCellContent(sheetRow, failErrorList.get(i));
        }

        //自动调整列宽；这里的4是需要设置自适应的列
        for (int i = 0; i < 4; i++) {
            sheet.autoSizeColumn(i);
            //手动调整列宽，解决中文不能自适应问题
            //单元格单行最长支持 255* 256 宽度（每个单元格样式已经设置自动换行，超出即换行）
            //设置最低列宽度，列宽约六个中文字符
            int width = Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 12 / 10));
            sheet.setColumnWidth(i, width);
        }

        try {
            String filename = "record-error.xlsx";
            // 自定义输出文件名
            String typeName = new String(filename.getBytes("UTF-8"), "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename="
                    + typeName);
            OutputStream outputStream = response.getOutputStream();
            writeWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();


        } catch (IOException e) {
            e.printStackTrace();
        }

    }


    /**
     * 处理错误的表格信息，并添加注释
     *
     * @param hssfRow     行
     * @param errorUserVo 对象
     */
    private void handelFailCellContent(Row hssfRow, FailErrorVO errorUserVo) {
        List<String> excelInfo = errorUserVo.getExcelInfo();
        List<String> errorMsg = errorUserVo.getErrorMsg();
        Sheet sheet = hssfRow.getSheet();
        //创建绘图对象
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        //获取批注对象
        //(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
        //前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
        ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);

        Workbook workbook = sheet.getWorkbook();
        // 错误字体
        Font errorFont = workbook.createFont();
        errorFont.setFontHeightInPoints((short) 15);
        errorFont.setFontName("宋体");
        //加粗
        errorFont.setBold(true);

        // 错误单元格内容样式
        CellStyle errorStyle = workbook.createCellStyle();
        errorStyle.setLocked(true);
        errorStyle.setWrapText(true);
        errorStyle.setFont(errorFont);
        errorStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        errorStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置表格内容
        for (int i = 0; i < excelInfo.size(); i++) {
            Cell cell = hssfRow.createCell(i);
            //如果错误信息不为空，设置批注，和表格颜色
            if (StrUtil.isNotBlank(errorMsg.get(i))) {
                Comment cellComment = drawingPatriarch.createCellComment(anchor);
                cellComment.setString(new XSSFRichTextString(errorMsg.get(i)));
                cell.setCellComment(cellComment);
                cell.setCellStyle(errorStyle);
            }
            cell.setCellValue(excelInfo.get(i));
        }
    }


    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private List<ExportUserVO> parseExcel(Workbook workbook) {
        List<ExportUserVO> resultDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            //判断是否存在多个sheet表格
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据（一般为表头）（判断是否符合正确的模板）
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                log.error("解析Excel失败，在第一行没有读取到任何数据！");
            } else {
                int rowNum = firstRow.getRowNum();
                //不包含空
                short firstCellNum = firstRow.getFirstCellNum();
                //包含空
                short lastCellNum = firstRow.getLastCellNum();
                //实际多少就是多少
                int physicalNumberOfCells = firstRow.getPhysicalNumberOfCells();
                Cell cell = firstRow.getCell(0);
                //校验表格是否正确 TODO

            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);

                if (null == row) {
                    continue;
                }

                ExportUserVO resultData = convertRowToData(row);
                if (ObjectUtil.isEmpty(resultData)) {
                    log.error("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                resultDataList.add(resultData);
            }
        }

        return resultDataList;
    }


    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * <p>
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null（行数错误的时候返回错的的行，记录错误的内容,返回null）
     */
    private ExportUserVO convertRowToData(Row row) {
        ExportUserVO resultData = new ExportUserVO();
        FailErrorVO failErrorVO = new FailErrorVO();
        List<String> failStr = new ArrayList<>();
        List<String> errorMsgStr = new ArrayList<>();

        //定义单元格，从第一个单元格开始
        Cell cell;
        int cellNum = 0;
        int fail = 0;

        // 获取姓名
        cell = row.getCell(cellNum++);
        String name = convertCellValueToString(cell);

        failStr.add(name);

        if (StrUtil.isBlank(name)) {
            errorMsgStr.add("姓名不能为空");
            fail++;
        } else if (name.length() > 20) {
            errorMsgStr.add("姓名字段最多只能填写20个字符");
        } else {
            errorMsgStr.add(" ");
        }
        resultData.setNickname(name);

        // 获取手机号
        cell = row.getCell(cellNum++);
        String phoneStr = convertCellValueToString(cell);
        failStr.add(phoneStr);

        if (StrUtil.isBlank(phoneStr)) {
            errorMsgStr.add("联系方式不能为空");
            fail++;
        } else {
            Pattern pattern = Pattern.compile(PHONE);
            Matcher matcher = pattern.matcher(phoneStr);
            // 字符串是否与正则表达式相匹配
            boolean rs = matcher.matches();
            if (!rs) {
                errorMsgStr.add("手机号格式错误！");
                fail++;
            } else {
                errorMsgStr.add(" ");
            }
        }
        resultData.setPhone(phoneStr);

        // 获取密码
        cell = row.getCell(cellNum++);
        String password = convertCellValueToString(cell);
        failStr.add(password);
        resultData.setPassword(password);

        if (StrUtil.isBlank(password)) {
            errorMsgStr.add("密码不能为空");
            fail++;
        } else {
            errorMsgStr.add(" ");
        }

        if (fail > 0) {
            failErrorVO.setErrorMsg(errorMsgStr);
            failErrorVO.setExcelInfo(failStr);
            failErrorList.add(failErrorVO);
            return null;
        } else {
            return resultData;
        }
    }


    /**
     * 将单元格内容转换为字符串（可以按照具体的类型进行设置，那就不需要这个方法了）
     *
     * @param cell 单元格
     * @return 字符串
     */
    private String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            //数字
            case NUMERIC:
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            //字符串
            case STRING:
                returnValue = cell.getStringCellValue();
                break;
            //布尔
            case BOOLEAN:
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            // 空值
            case BLANK:
                break;
            // 公式
            case FORMULA:
                returnValue = cell.getCellFormula();
                break;
            // 故障
            case ERROR:
                break;
            default:
                break;
        }
        return returnValue;
    }

符合条件的数据入库，不符合的重新改成excel返回给用户。

5.后记

上面的代码，我也是在网上东凑西凑才写出来了，有很大的部分需要改进，因为只是一个demo没有用到实际的项目中，所以只供参考，我以后有空会把这个完善好，优化好。
目前就这样了。