Excel导入导出注解通用版

日常开发中常会遇到Excel导入导出的需求，得空写个前后端通用版的Excel导入导出demo，供之后开发效率事半功倍

功能实现

数据表

初始数据

CREATE TABLE `student` (
  `sno` varchar(10) NOT NULL,
  `sname` varchar(20) DEFAULT NULL,
  `sage` varchar(11) DEFAULT NULL,
  `ssex` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

注：字段全为字符类型，避免解析数据保存到数据库表时需过多的数据类型转换逻辑

依赖文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>spring-boot-project</artifactId>
        <groupId>cn.goitman</groupId>
        <version>0.0.1-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>poi-importAndExport-demo</artifactId>

    <dependencies>
        <!--Web 项目开发starter-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--进行数据解析Fastjson会自动处理对象中的泛型，将其解析成原来定义好的对象 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>

        <!-- 引入poi，解析workbook视图 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.6.1</version>
        </dependency>

        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <!--通用mapper-->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>RELEASE</version>
        </dependency>

        <!--mysql数据库-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    </dependencies>
</project>

配置文件

server:
  port: 8080
spring:
  datasource:
    url: jdbc:mysql:///数据库?characterEncoding=utf8&amp;useUnicode=true
    username: 
    password: 
    driver-class-name: com.mysql.jdbc.Driver

mybatis:
  # xml文件地址
  mapper-locations: classpath:mapper/*Mapper.xml
  # 打印mybatis的执行sql语句
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 开启驼峰自动转换
    map-underscore-to-camel-case: true

# 文件保存地址
file:
  filePath: E:/

引导类

package cn.goitman;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;

/**
 * @author Nicky
 * @version 1.0
 * @className PoiApplication
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 启动类
 * @date 2021/9/22 10:57
 */
@SpringBootApplication
@MapperScan("cn.goitman.mapper") // 指定扫描的Mapper类的包的路径，在每个Mapper类上添加注解@Mapper
public class ExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExcelApplication.class,args);
    }
}

四个自定义注解

EnableExport(允许导出类注解)

实体类上必须定义此注解，因工具类会判断当前类是否为允许导出

package cn.goitman.annotation;

import cn.goitman.enums.ColorEnum;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author Nicky
 * @version 1.0
 * @className EnableExport
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 允许导出类注解
 * @date 2021/9/22 10:48
 */
@Target(ElementType.TYPE) // 用于类
@Retention(RetentionPolicy.RUNTIME) // 运行时
public @interface EnableExport {

    // 标题/文件名
    String fileName();

    // 背景颜色，默认蓝色
    ColorEnum cellColor() default ColorEnum.BLUE;
}

EnableExportField(允许导出字段注解)

在实体类上，需导出数据的字段上定义此注解，可选

package cn.goitman.annotation;

import cn.goitman.enums.ColorEnum;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author Nicky
 * @version 1.0
 * @className EnableExportField
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 允许导出字段注解
 * @date 2021/9/22 11:10
 */
@Target(ElementType.FIELD)  // 用于字段上
@Retention(RetentionPolicy.RUNTIME) // 运行时
public @interface EnableExportField {
    // 设置宽度，默认100像素
    int colWidth() default 100;

    // 列名称
    String colName();

    // 导出设置get方法
    String useGetMethod() default "";

    // 背景颜色，默认蓝色
    ColorEnum cellColor() default ColorEnum.BLUE;
}

ImportIndex(导入字段注解)

必须在实体类上需导入数据的字段上定义此注解；定义字段索引属性时，必须与数据库相应字段的索引一致；实体类字段名必须与数据库字段名称一致，在本实例中未做转换逻辑

package cn.goitman.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author Nicky
 * @version 1.0
 * @className ImportIndex
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 导入字段注解
 * @date 2021/9/22 15:21
 */
@Target(ElementType.FIELD) // 用于字段
@Retention(RetentionPolicy.RUNTIME) // 运行时
public @interface ImportIndex {

    // 索引，从0开始
    int index();

    // 导入设置set方法
    String useSetMethodName() default "";
}

EnableSelectList(下拉列表注解)

在有下拉列表数据的字段定义此注解，可选

package cn.goitman.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @author Nicky
 * @version 1.0
 * @className EnableSelectList
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 下拉列表注解
 * @date 2021/9/22 14:56
 */
@Target(ElementType.FIELD) // 用于字段
@Retention(RetentionPolicy.RUNTIME) // 运行时
public @interface EnableSelectList {
}

枚举

package cn.goitman.enums;

import org.apache.poi.hssf.util.HSSFColor;

/**
 * @author Nicky
 * @version 1.0
 * @className ColorEnum
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 设置颜色枚举
 * @date 2021/9/22 16:05
 */
public enum ColorEnum {

    RED("红色", HSSFColor.RED.index),
    GREEN("绿色", HSSFColor.GREEN.index),
    BLANK("白色", HSSFColor.BLACK.index),
    YELLOW("黄色", HSSFColor.YELLOW.index),
    BLUE("蓝色", HSSFColor.BLUE.index);

    private String name;
    private Short index;

    ColorEnum(String name, Short index) {
        this.name = name;
        this.index = index;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Short getIndex() {
        return index;
    }

    public void setIndex(Short index) {
        this.index = index;
    }
}

数据层

package cn.goitman.mapper;

import cn.goitman.pojo.Student;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

/**
 * @author Nicky
 * @version 1.0
 * @className PoiMapper
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 数据层，继承MySqlMapper，获取批量插入方法
 * @date 2021/9/22 11:00
 */
@Repository
public interface ExcelMapper extends Mapper<Student>, MySqlMapper<Student> {
}

文件工具类

package cn.goitman.utils;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;

/**
 * @author Nicky
 * @version 1.0
 * @className ExcelService
 * @blog goitman.cn | blog.csdn.net/minkeyto
 * @description 文件工具类
 * @date 2021/9/22 16:09
 */
// 注入容器，获取配置文件数据
@Component
public class FileUtil {

    private static String filePath;

    @Value("${file.filePath}")
    public void setFilePath(String filePath) {
        FileUtil.filePath = filePath;
    }

    /**
     * 保存文件到本地， 保存后的文件路径（绝对路径）
     */
    public static String saveFileToLocal(MultipartFile file) {
        Date date = new Date();
        String str = "yyyMM";
        SimpleDateFormat sdf = new SimpleDateFormat(str);

        // 按年月分文件夹
        File file1 = new File(filePath + sdf.format(date) + File.separator);
        if (!file1.exists()) {
            file1.mkdirs();
        }
        String fileName = file.getOriginalFilename();
        // 获取文件后缀名
        String suffixFileName = fileName.substring(fileName.lastIndexOf("."));
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");
        String path = file1.getPath() + File.separator + uuid + suffixFileName;
        try {
            file.transferTo(new File(path));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return path;
    }
}

实例测试

导入数据

常规导入

前端请求
实体类

@EnableExport(fileName = "学生表")
public class Student {

    @ImportIndex(index = 0)
    private String sno;

    @EnableExportField(colName = "姓名", colWidth = 90)
    @ImportIndex(index = 1)
    private String sname;

    @EnableExportField(colName = "年龄", colWidth = 90, cellColor = ColorEnum.YELLOW)
    @ImportIndex(index = 2)
    private String sage;

    @EnableExportField(colName = "性别", colWidth = 90)
    @ImportIndex(index = 3)
    private String ssex;
    ......
}

接口调用：入参名称必须与前端传参名称一致

/**
* @method  uploadFile
* @description 常规导入
* @param  [file]
* @return  boolean
*/
@PostMapping("/uploadFile")
public boolean uploadFile(@RequestParam("fileName") MultipartFile file) {
    return excelService.uploadFile(file);
}

逻辑层：parseExcelToList方法，没有校验@EnableSelectList注解和ImportIndex注解中的useSetMethodName属性

public boolean uploadFile(MultipartFile file) {
        String path = FileUtil.saveFileToLocal(file);
        File fileDirPath = new File(path);

        List<Student> students = (List<Student>) ExcelUtils.parseExcelToList(fileDirPath, Student.class);
        return excelMapper.insertList(students) > 0;
    }

ExcelUtils工具类方法
此时实体类没有定义ImportIndex注解的useSetMethodName属性

/**
 * @param [excel 文件, clazz pojo类型]
 * @return java.util.List<?>
 * @method parseExcelToList
 * @description 导入，将Excel数据转换为集合对象
 */
public static List<?> parseExcelToList(File excel, Class clazz) {
    List<Object> res = new ArrayList<>();
    // 创建输入流
    InputStream is = null;
    // 创建工作表
    Sheet sheet = null;
    try {
        is = new FileInputStream(excel.getAbsolutePath());
        if (is != null) {
            // 创建工作簿
            Workbook workbook = WorkbookFactory.create(is);
            // 获取第一个工作表
            sheet = workbook.getSheetAt(0);
            if (sheet != null) {
                int i = 1;
                String values[];
                // 获取第二行数据，第一行为标题
                Row row = sheet.getRow(i);
                while (row != null) {
                    // 获取总列数
                    int cellNum = row.getPhysicalNumberOfCells();
                    values = new String[cellNum];
                    for (int j = 0; j <= cellNum; j++) {
                        // 获取单元格数据
                        Cell cell = row.getCell(j);
                        String value = null;
                        if (cell != null) {
                            // 将单元格数据类型设置为字符串
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            value = cell.getStringCellValue() == null ? null : cell.getStringCellValue();
                            values[j] = value;
                        }
                    }
                    // 反射获取类中所有声明字段
                    Field[] fields = clazz.getDeclaredFields();
                    Object obj = clazz.newInstance();
                    for (Field f : fields) {
                        // 判断ImportIndex注解是否在此字段上，true为存在
                        if (f.isAnnotationPresent(ImportIndex.class)) {
                            ImportIndex annotation = f.getAnnotation(ImportIndex.class);
                            // 获取索引值
                            int index = annotation.index();
                            // 获取方法名
                            String useSetMethodName = annotation.useSetMethodName();
                            if (!"".equals(useSetMethodName)) {
                                // fastjson TypeUtils工具类，实现常用数据类型和对象间的相互转换
                                Object val = TypeUtils.cast(values[index], f.getType(), null);
                                // 取消Java语言访问检查
                                f.setAccessible(true);
                                // 参数一：方法名，参数二：方法参数数组
                                Method method = clazz.getMethod(useSetMethodName, new Class[]{f.getType()});
                                // 暴力访问
                                method.setAccessible(true);
                                // 返回值是Object接收，参数一：对象是谁，参数二：调用该方法的实际参数
                                method.invoke(obj, new Object[]{val});
                            } else {
                                f.setAccessible(true);
                                Object val = TypeUtils.cast(values[index], f.getType(), null);
                                // 将指定对象上此 Field字段设置为新值。参数一：指定对象，参数二：新值
                                f.set(obj, val);
                            }
                        }
                    }
                    // 将对象数据保存至集合
                    res.add(obj);
                    i++;
                    // 遍历下一行数据
                    row = sheet.getRow(i);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return res;
}

执行结果

控制台信息

数据库数据

下拉列表注解的数据转化

前端请求
以实体类中性别字段为例

接口调用

/**
* @method  uploadFileSelectList
* @description 演示EnableSelectList注解、ImportIndex注解useSetMethodName属性的使用
* @param  [file]
* @return  boolean
*/
@PostMapping("/uploadFileSelectList")
public boolean uploadFileSelectList(@RequestParam("fileName") MultipartFile file) {
    try {
        return excelService.uploadFileSelectList(file);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    return false;
}

逻辑层：此处的ALL_SELECT_LIST_MAP预存数据硬编码不灵活，改善的方式很多，在此不衍生讨论啦

import static cn.goitman.utils.ExcelUtils.ALL_SELECT_LIST_MAP;

public boolean uploadFileSelectList(MultipartFile file) throws FileNotFoundException {
        // 预存数据，配合EnableSelectList注解使用
        Map<String, String> selsctList = new HashMap<>();
        selsctList.put("1","男");
        selsctList.put("2","女");
        ALL_SELECT_LIST_MAP.put(3,selsctList);

        String path = FileUtil.saveFileToLocal(file);
        FileInputStream inputStream = new FileInputStream(path);

        List<Student> students = (List<Student>) ExcelUtils.parseExcelToList(inputStream, Student.class);
        return excelMapper.insertList(students) > 0;
    }

ExcelUtils工具类方法

/**
 * @param [excel 文件输入流, clazz pojo类型]
 * @return java.util.List<?>
 * @method parseExcelToList
 * @description 导入，将Excel数据转换为集合对象，另校验EnableSelectList注解或ImportIndex注解useSetMethodName属性
 */
public static List<?> parseExcelToList(InputStream excel, Class clazz) {
    List<Object> res = new ArrayList<>();
    InputStream is = null;
    Sheet sheet = null;
    try {
        is = excel;
        if (is != null) {
            Workbook workbook = WorkbookFactory.create(is);
            sheet = workbook.getSheetAt(0);
            if (sheet != null) {
                int i = 1;
                String values[];
                Row row = sheet.getRow(i);
                while (row != null) {
                    int cellNum = row.getPhysicalNumberOfCells();
                    values = new String[cellNum];
                    for (int j = 0; j < cellNum; j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String value = cell.getStringCellValue() == null ? null : cell.getStringCellValue();
                            values[j] = value;
                        }
                    }
                    Field[] fields = clazz.getDeclaredFields();
                    Object obj = clazz.newInstance();
                    for (Field f : fields) {
                        if (f.isAnnotationPresent((ImportIndex.class))) {
                            ImportIndex annotation = f.getAnnotation(ImportIndex.class);
                            int index = annotation.index();
                            Object value = values[index];
                            // 判断EnableSelectList注解是否在此字段上，true为存在
                            if (f.isAnnotationPresent(EnableSelectList.class)) {
                                // 根据索引获取下拉列表值
                                value = getKeyByValue(ALL_SELECT_LIST_MAP.get(index), String.valueOf(value));
                            }
                            String useSetMethodName = annotation.useSetMethodName();
                            if (!"".equals(useSetMethodName)) {
                                Object val = TypeUtils.cast(value, f.getType(), null);
                                f.setAccessible(true);
                                Method method = clazz.getMethod(useSetMethodName, new Class[]{f.getType()});
                                method.setAccessible(true);
                                method.invoke(obj, new Object[]{val});
                            } else {
                                f.setAccessible(true);
                                Object val = TypeUtils.cast(value, f.getType(), null);
                                f.set(obj, val);
                            }
                        }
                    }
                    res.add(obj);
                    i++;
                    row = sheet.getRow(i);
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return res;
}

执行结果

数据表还原回初始数据，此代码段将性别字段数据做了转化处理

控制台信息

数据库数据

useSetMethodName属性的数据转化

前端请求、接口调用和ExcelUtils工具类方法与上述下拉列表注解的数据转化相同

还是以实体类中性别字段为例，注销EnableSelectList注解，setSex方法将性别数据做了转化
逻辑层：注销预存数据ALL_SELECT_LIST_MAP代码块
执行结果

还原回初始数据，此反射代码解析useSetMethodName属性

控制台信息

数据库数据

常规导出

前端请求：此链接为本地测试链接
实体类：以导出姓名、年龄、性别三个字段数据为例

接口调用

/**
* @method  downloadFile
* @description 常规导出
* @param  [response]
* @return  void
*/
@GetMapping("/downloadFile")
public void downloadFile(HttpServletResponse response) {
    excelService.downloadFile(response);
}

逻辑层

public void downloadFile(HttpServletResponse response) {
    List<Student> students = excelMapper.selectAll();
    ExcelUtils.exportExcel(response, students, Student.class, null, null);
}

ExcelUtils工具类方法

/**
 * @param [outputStream 输出流, dataList 导出的数据, clazz 导出数据的pojo类型, selectMap 下拉列表的列, exportTitle 标题]
 * @return void
 * @method exportExcel
 * @description 导出Excel
 */
public static void exportExcel(HttpServletResponse response, List dataList, Class clazz, Map<Integer, Map<String, String>> selectMap, String exportTitle) {
    // 创建工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 创建工作表
    HSSFSheet sheet = workbook.createSheet();
    // 设置工作表行的默认高度
    sheet.setDefaultRowHeight((short) (20 * 20));
    // 判断当前类是否允许导出
    if (clazz.isAnnotationPresent(EnableExport.class)) {
        EnableExport export = (EnableExport) clazz.getAnnotation(EnableExport.class);
        // 所有列标题名称
        List<String> colNames = new ArrayList<>();
        // 所有列标题背景颜色
        List<ColorEnum> colors = new ArrayList<>();
        // 允许导出的字段
        List<Field> fieldList = new ArrayList<>();
        for (Field field : clazz.getDeclaredFields()) {
            if (field.isAnnotationPresent(EnableExportField.class)) {
                EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
                colNames.add(enableExportField.colName());
                colors.add(enableExportField.cellColor());
                fieldList.add(field);
            }
        }
        // 设置每列的宽度
        for (int i = 0; i < fieldList.size(); i++) {
            Field field = fieldList.get(i);
            sheet.setColumnWidth(i, field.getAnnotation(EnableExportField.class).colWidth() * 20);
        }

        HSSFRow hssfRow = null; // 表行
        HSSFCell hssfCell = null; // 单元格

        // 设置列标题
        String fileName = export.fileName();
        if (exportTitle != null) {
            fileName = exportTitle;
        }
        // 绘制标题，可选
        createTitle(workbook, hssfRow, hssfCell, sheet, colNames.size() - 1, fileName, export.cellColor());
        // 创建表头列名
        createHeadRow(workbook, hssfRow, hssfCell, sheet, colNames, colors);

        try {
            // 绘制单元格样式
            HSSFCellStyle cellStyle = getBasicCellStyle(workbook);
            // 插入数据
            int i = 0;
            for (Object obj : dataList) {
                // 表头标题和列名已创建，所以从第三行开始
                hssfRow = sheet.createRow(i + 2);
                for (int j = 0; j < fieldList.size(); j++) {
                    Field field = fieldList.get(j);
                    field.setAccessible(true);
                    Object value = field.get(obj);
                    EnableExportField enableExportField = field.getAnnotation(EnableExportField.class);
                    String getMethodName = enableExportField.useGetMethod();
                    if (!"".equals(getMethodName)) {
                        Method method = clazz.getMethod(getMethodName, new Class[]{field.getType()});
                        method.setAccessible(true);
                        method.invoke(obj, new Object[]{value});
                    }
                    if (field.isAnnotationPresent(EnableSelectList.class)) {
                        if (selectMap != null && selectMap.get(j) != null) {
                            value = selectMap.get(j).get(value);
                        }
                    }
                    setCellValue(value, hssfCell, hssfRow, cellStyle, j);
                }
                i++;
            }

            // 提供下载框，并设置文件名
            response.setContentType("octets/stream");
            // 防止中文文件名称乱码，需encode，并设置字符集
            response.setHeader("Content-Disposition", "attachment;filename=" +  java.net.URLEncoder.encode(fileName, "UTF-8") + ".xls");
            OutputStream outputStream = null;
            // 获取响应流
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }