Java自定义注解，优雅实现Excel导入导出

导入POI相关坐标依赖

<!-- POI相关依赖 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

创建自定义注解类

ExportExcelTitle

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
 * ExcelTitle
 * TODO - 自定义注解，导出到Excel文件名称信息
 *
 * @author Anhk丶
 * @version 1.0
 * @date 2021/9/18 11:43 星期六
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExportExcelTitle {
    /**
     * 导出字段个数
     * @return
     */
    int length();
    /**
     * 导出文件名
     * @return
     */
    String description();
}

ExportExcelField

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
 * ExcelField
 * TODO - 自定义注解，导出到Excel文件字段
 *
 * @author Anhk丶
 * @version 1.0
 * @date 2021/9/18 11:36 星期六
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExportExcelField {
    /**
     * 导出字段在Excel文件中的列索引
     *
     * @return
     */
    int index();
    /**
     * 描述，字段的中文描述
     *
     * @return
     */
    String description();
}

ImportExcelField

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
 * ImportExcelField
 * TODO - 自定义注解，导入Excel文件字段
 *
 * @author Anhk丶
 * @version 1.0
 * @date 2021/9/18 16:18 星期六
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ImportExcelField {
    /**
     * 导入字段在Excel文件的列索引
     * @return
     */
    int index();
}

创建相关实体类信息，并在需要导入导出的属性上面使用自定义的注解

import com.anhk.annotation.ExportExcelField;
import com.anhk.annotation.ExportExcelTitle;
import com.anhk.annotation.ImportExcelField;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
/**
 * User
 * TODO - 用户信息实体类
 *
 * @author Anhk丶
 * @version 1.0
 * @date 2021/9/17 10:25 星期五
 */
@TableName("db_user")
@ExportExcelTitle(length = 4, description = "用户信息")
public class User {
    /**
     * 主键ID
     */
    @TableId(type = IdType.UUID)
    private String id;
    /**
     * 用户名
     */
    @ImportExcelField(index = 0)
    @ExportExcelField(index = 0, description = "用户名")
    private String userName;
    /**
     * 密码
     */
    @ImportExcelField(index = 1)
    private String password;
    /**
     * 联系方式
     */
    @ImportExcelField(index = 2)
    @ExportExcelField(index = 1, description = "联系方式")
    private String mobile;
    /**
     * 邮箱
     */
    @ImportExcelField(index = 3)
    @ExportExcelField(index = 2, description = "邮箱")
    private String mail;
    /**
     * 住址
     */
    @ImportExcelField(index = 4)
    @ExportExcelField(index = 3, description = "住址")
    private String address;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getMobile() {
        return mobile;
    }
    public void setMobile(String mobile) {
        this.mobile = mobile;
    }
    public String getMail() {
        return mail;
    }
    public void setMail(String mail) {
        this.mail = mail;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public User() {
    }
    public User(String id, String userName, String password, String mobile, String mail, String address) {
        this.id = id;
        this.userName = userName;
        this.password = password;
        this.mobile = mobile;
        this.mail = mail;
        this.address = address;
    }
}

创建导入导出相关API接口

import com.anhk.exception.CustomerException;
import com.anhk.exception.ExceptionEnum;
import com.anhk.pojo.User;
import com.anhk.service.UserService;
import com.anhk.utils.ExcelUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
 * UserController
 * TODO - 用户信息控制层
 *
 * @author Anhk丶
 * @version 1.0
 * @date 2021/9/18 9:47 星期六
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;
    /**
     * 导出用户信息到Excel文件
     *
     * @param request  请求
     * @param response 响应
     * @param userName 用户名
     * @return 无
     */
    @RequestMapping("/exportUserToExcel")
    public ResponseEntity<Void> exportUserToExcel(HttpServletRequest request, HttpServletResponse response, String userName) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        if (!StringUtils.isEmpty(userName)) {
            queryWrapper.lambda().like(User::getUserName, userName);
        }
        List<User> userList = userService.list(queryWrapper);
        ExcelUtils.exportDataToExcel(request, response, userList, User.class);
        return ResponseEntity.ok().build();
    }
    /**
     * 从Excel文件导入用户信息
     *
     * @param file 文件
     * @return 无
     */
    @PostMapping("/importUserFromExcel")
    @Transactional(rollbackFor = Exception.class)
    public ResponseEntity<Void> importUserFromExcel(MultipartFile file) {
        InputStream inputStream = null;
        try {
            String filename = file.getOriginalFilename();
            inputStream = file.getInputStream();
            List<User> userList = ExcelUtils.importDataFromExcel(inputStream, filename, User.class);
            boolean saveBatch = userService.saveBatch(userList);
            if (!saveBatch) {
                throw new CustomerException(ExceptionEnum.IMPORT_DATA_ERROR);
            }
        } catch (CustomerException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomerException(ExceptionEnum.IMPORT_DATA_ERROR);
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return ResponseEntity.ok().build();
    }
}

创建导入导出相关工具类

import com.anhk.annotation.ExportExcelField;
import com.anhk.annotation.ExportExcelTitle;
import com.anhk.annotation.ImportExcelField;
import com.anhk.exception.CustomerException;
import com.anhk.exception.ExceptionEnum;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
 * ExcelUtils
 * TODO - Excel导入导出工具类
 *
 * @author Anhk丶
 * @version 1.0
 * @date 2021/9/18 11:18 星期六
 */
public class ExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    /**
     * 导出数据到Excel文件
     *
     * @param request  请求
     * @param response 响应
     * @param dataList 数据集合
     * @param <T>      泛型
     */
    public static <T> void exportDataToExcel(HttpServletRequest request, HttpServletResponse response, List<T> dataList, Class<T> clazz) {
        //定义工作簿对象
        Workbook workbook = new SXSSFWorkbook();
        //定义字节输出流
        ByteArrayOutputStream byteArrayOutputStream = null;
        try {
            ExportExcelTitle exportExcelTitle = clazz.getAnnotation(ExportExcelTitle.class);
            if (exportExcelTitle == null) {
                throw new CustomerException(ExceptionEnum.CAN_NOT_EXPORT_THIS_TYPE_DATA);
            }
            //定义页
            Sheet sheet = workbook.createSheet(exportExcelTitle.description());
            //定义第一行，标题行
            Row row = sheet.createRow(0);
            //定义第一行的列
            Cell cell = row.createCell(0);
            //设置标题列
            cell.setCellValue(exportExcelTitle.description());
            //合并标题行， 参数1：起始行 参数2：结束行 参数3：起始列 参数4：结束列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, exportExcelTitle.length() - 1));
            //定义第二行，表格头
            row = sheet.createRow(1);
            //定义字段集合，用来存放需要导出的字段信息
            ArrayList<Field> fields = new ArrayList<>();
            //获取所有字段信息
            Field[] filedArr = clazz.getDeclaredFields();
            //遍历字段信息
            for (Field field : filedArr) {
                //获取字段上的ExportExcelField注解
                ExportExcelField exportExcelField = field.getAnnotation(ExportExcelField.class);
                //判断，是否含有该注解
                if (exportExcelField != null) {
                    //根据注解上的值，创建单元格，并给单元格赋值
                    cell = row.createCell(exportExcelField.index());
                    cell.setCellValue(exportExcelField.description());
                    //将字段保存到集合中
                    fields.add(field);
                }
            }
            //定义行序号
            int rowNum = 2;
            //遍历数据集合，生成数据表格
            for (T t : dataList) {
                //建立数据行
                row = sheet.createRow(rowNum++);
                //遍历需要导出的字段信息集合
                for (Field field : fields) {
                    ExportExcelField ExportExcelField = field.getAnnotation(ExportExcelField.class);
                    //获取字段名称
                    String fieldName = field.getName();
                    //处理字段名称，组装为该字段的get方法
                    String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    //获取该方法
                    Method method = clazz.getDeclaredMethod(methodName);
                    //执行get方法，拿到返回值
                    Object cellValue = method.invoke(t);
                    //判断字段类型，将返回值转换为对应的类型
                    /*if (field.getType() == String.class) {
                        cell = row.createCell(ExportExcelField.index());
                        cell.setCellValue(String.valueOf(cellValue));
                    } else if (field.getType() == Integer.class) {
                        cell = row.createCell(ExportExcelField.index());
                        cell.setCellValue(Integer.parseInt(String.valueOf(cellValue)));
                    }*/
                    cell = row.createCell(ExportExcelField.index());
                    setCellValue(workbook, cell, cellValue);
                }
            }
            byteArrayOutputStream = new ByteArrayOutputStream();
            //将工作簿写入输出流流
            workbook.write(byteArrayOutputStream);
            //导出下载文件
            DownLoadUtil.download(byteArrayOutputStream, exportExcelTitle.description() + ".xlsx", request, response);
        } catch (CustomerException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomerException(ExceptionEnum.EXPORT_DATA_ERROR);
        } finally {
            if (byteArrayOutputStream != null) {
                try {
                    byteArrayOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 从Excel文件导入数据
     *
     * @param inputStream 输入流
     * @param fileName    文件名
     * @param clazz       导入的实体类
     * @param <T>         泛型
     * @return 导入数据集合
     */
    public static <T> List<T> importDataFromExcel(InputStream inputStream, String fileName, Class<T> clazz) {
        //判断输入流是否为空
        if (inputStream == null) {
            return null;
        }
        Workbook workbook = null;
        ArrayList<T> dataList = null;
        try {
            //获取文件后缀
            String suffix = fileName.substring(fileName.lastIndexOf("."));
            if (".xlsx".equals(suffix)) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (".xls".equals(suffix)) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                throw new CustomerException(ExceptionEnum.IMPORT_FILE_TYPE_ERROR);
            }
            //获取工作簿第一页
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                throw new CustomerException(ExceptionEnum.FILE_DATA_ERROR);
            }
            //获取最后一行，数据条数
            int lastRowNum = sheet.getLastRowNum();
            //定义数据集合，集合长度为数据条数
            dataList = new ArrayList<>(lastRowNum);
            //获取class的所有字段信息
            Field[] fieldArr = clazz.getDeclaredFields();
            //遍历，从第2行开始，到最后一行（第1行为表格头信息）
            Row row = null;
            Cell cell = null;
            //遍历，知道该页的最后一行数据
            for (int i = 1; i < lastRowNum; i++) {
                //获取行
                row = sheet.getRow(i);
                //实例化对象信息
                T t = clazz.newInstance();
                for (Field field : fieldArr) {
                    //获取注解信息
                    ImportExcelField importExcelField = field.getAnnotation(ImportExcelField.class);
                    //判断该字段是否为导入字段
                    if (importExcelField != null) {
                        //获取该列
                        cell = row.getCell(importExcelField.index());
                        //设置私有字段强制性访问
                        field.setAccessible(true);
                        //判断字段类型，并赋值
                        if (field.getType() == Integer.class) {
                            //处理数值类型的转换，默认是double类型
                            int intVal = (int) Math.round(cell.getNumericCellValue());
                            if (Double.parseDouble(intVal + ".0") == cell.getNumericCellValue()) {
                                field.set(t, intVal);
                            }
                        } else if (field.getType() == Double.class) {
                            field.set(t, cell.getNumericCellValue());
                        } else if (field.getType() == Long.class) {
                            //处理数值类型的转换，默认是double类型
                            long longVal = Math.round(cell.getNumericCellValue());
                            field.set(t, longVal);
                        } else if (field.getType() == Boolean.class) {
                            field.set(t, cell.getBooleanCellValue());
                        } else if (field.getType() == Date.class) {
                            field.set(t, cell.getDateCellValue());
                        } else {
                            field.set(t, cell.getStringCellValue());
                        }
                    }
                }
                //将对象添加进集合汇总
                dataList.add(t);
            }
        } catch (CustomerException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomerException(ExceptionEnum.IMPORT_DATA_ERROR);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return dataList;
    }
    /**
     * 设置Excel列值
     * @param workbook 工作簿对象
     * @param cell 列对象
     * @param fieldVal 值信息
     */
    public static void setCellValue(Workbook workbook, Cell cell, Object fieldVal) {
        // 生成一个格式化工具
        DecimalFormat df = new DecimalFormat("######0.000");
        if (fieldVal instanceof Date) {
            //设置单元格格式
            DataFormat dataFormat = workbook.createDataFormat();
            CellStyle cellStyle = text(workbook);
            cellStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd HH:mm:ss"));
            cell.setCellStyle(cellStyle);
            //转换日期类型，并写进excel对象
            cell.setCellValue((Date) fieldVal);
        } else if (fieldVal instanceof Integer) {
            int intVal = (Integer) fieldVal;
            // 写进excel对象
            cell.setCellValue(intVal);
            cell.setCellStyle(text(workbook));
        } else if (fieldVal instanceof Float) {
            Float fVal = (Float) fieldVal;
            BigDecimal b1 = new BigDecimal(df.format(fVal) + "");
            // style2.setDataFormat(format.getFormat("0.00")); // 两位小数
            cell.setCellValue(
                    Float.parseFloat(String.valueOf(b1).substring(0, String.valueOf(b1).indexOf(".") + 3)));
            cell.setCellStyle(text(workbook));
        } else if (fieldVal instanceof Double) {
            double dVal = (Double) fieldVal;
            BigDecimal b1 = new BigDecimal(df.format(dVal) + "");
            // style2.setDataFormat(format.getFormat("0.00"));
            cell.setCellValue(Double
                    .parseDouble(String.valueOf(b1).substring(0, String.valueOf(b1).indexOf(".") + 3)));
            cell.setCellStyle(text(workbook));
        } else if (fieldVal instanceof Long) {// Long
            long longValue = (Long) fieldVal;
            cell.setCellValue(longValue);
            cell.setCellStyle(text(workbook));
        } else {
            // 其它数据类型都当作字符串简单处理
            if (fieldVal != null) {
                cell.setCellValue(fieldVal.toString());
            } else {
                cell.setCellValue("");
            }
            cell.setCellStyle(text(workbook));
        }
    }
    /**
     * 大标题的样式
     *
     * @param wb
     * @return
     */
    public static CellStyle bigTitle(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        //设置字体样式
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16); //字体大小
        font.setBold(true);//字体加粗
        style.setFont(font);
        //设置边框样式
        style.setAlignment(HorizontalAlignment.CENTER); // 横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
        return style;
    }
    /**
     * 小标题样式
     *
     * @param wb
     * @return
     */
    public static CellStyle title(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        //设置字体样式
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 14); //字体大小
        style.setFont(font);
        //设置边框样式
        style.setAlignment(HorizontalAlignment.CENTER); // 横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
        style.setBorderTop(BorderStyle.THIN); // 上细线
        style.setBorderBottom(BorderStyle.THIN); // 下细线
        style.setBorderLeft(BorderStyle.THIN); // 左细线
        style.setBorderRight(BorderStyle.THIN); // 右细线
        return style;
    }
    /**
     * 文字样式
     *
     * @param wb
     * @return
     */
    public static CellStyle text(Workbook wb) {
        CellStyle style = wb.createCellStyle();
        //设置字体样式
        Font font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 12); //字体大小
        style.setFont(font);
        //设置边框样式
        style.setAlignment(HorizontalAlignment.LEFT); // 横向居左
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 纵向居中
        style.setBorderTop(BorderStyle.THIN); // 上细线
        style.setBorderBottom(BorderStyle.THIN); // 下细线
        style.setBorderLeft(BorderStyle.THIN); // 左细线
        style.setBorderRight(BorderStyle.THIN); // 右细线
        return style;
    }
}

创建下载工具类

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
public class DownLoadUtil {
    /**
     * 下载文件
     *
     * @param byteArrayOutputStream 文件的字节输出流
     * @param returnName            文件名
       @param request               请求
     * @param response              响应
     * @throws IOException
     */
    public static void download(ByteArrayOutputStream byteArrayOutputStream, String returnName, HttpServletRequest request, HttpServletResponse response) throws IOException {
        response.setContentType("application/octet-stream;charset=utf-8");
        //解决IE下导出中文乱码问题
        request.setCharacterEncoding("UTF-8");
        String header = request.getHeader("User-Agent").toUpperCase();
        if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
            returnName = URLEncoder.encode(returnName, "utf-8");
            returnName = returnName.replace("+", "%20");    //IE下载文件名空格变+号问题
        } else {
            returnName = new String(returnName.getBytes(), "ISO8859-1");
        }
        //保存的文件名必须和页面编码一致，否则乱码
        //returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1"));
        response.addHeader("Content-Disposition", "attachment;filename=\"" + returnName + "\"");
        response.setContentLength(byteArrayOutputStream.size());
        //获取输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //写进输出流
        byteArrayOutputStream.writeTo(outputStream);
        //刷新数据
        byteArrayOutputStream.close();
        outputStream.flush();
    }
}