EasyExcel模版下载导入导出

一、添加pom

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

二、用户实体类

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable {

    private static final long serialVersionUID = -5144055068797033748L;
    /**
     * 编号
     */
    @ExcelProperty(value = "id", index = 0)
    private Long id;
    /**
     * 用户名
     */
    @ExcelProperty(value = "用户名", index = 1)
    private String username;
    /**
     * 显示名称
     */
    @ExcelProperty(value = "名称", index = 2)
    private String nickname;
    /**
     * 密码
     */
    @ExcelProperty(value = "密码", index = 3)
    private String password;
    /**
     * 邮箱
     */
    @ExcelProperty(value = "邮箱", index = 4)
    private String email;
    /**
     * 头像
     */
    @ExcelProperty(value = "头像", index = 5)
    private String avater;

}

三、excel导出工具类

public class ExcelUtil {
    /**
     * 导出
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
    }
    
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }
}

四、模版下载

	/**
     * 下载Excel模板
     */
    @GetMapping("/excel/template")
    public void downloadTemplate(HttpServletResponse response) {
        String fileName = "导入用户模板";
        String sheetName = "导入用户模板";
        List<User> userList = new ArrayList<>();
        userList.add(new User(1L, "张三", "zhsngsan", "123456", "847064370@qq.com", "123"));
        userList.add(new User(2L, "李四", "lisi", "123456", "666666@qq.com", "456"));
        try {
            ExcelUtil.writeExcel(response, userList, fileName, sheetName, User.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

五、数据的导入导出

5.1导出数据

@GetMapping("/excel/export")
    public void exportData(HttpServletResponse response) {
        String fileName = "用户列表";
        String sheetName = "用户列表";
        List<User> userList = userService.findAll();
        List<User> userExcelList = new ArrayList<>();
        for (User user : userList) {
            User userExcel = User.builder()
                    .id(user.getId())
                    .username(user.getUsername())
                    .password(user.getPassword())
                    .nickname(user.getNickname())
                    .email(user.getEmail())
                    .avater(user.getAvater()).build();
            userExcelList.add(userExcel);
        }
        try {
            ExcelUtil.writeExcel(response, userExcelList, fileName, sheetName, User.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

5.2、导入数据

	/**
     * 导入：同步读，单sheet
     */
    @PostMapping("/excel/import")
    public void importData(MultipartFile file) throws Exception {
        List<User> userExcelList = null;
        // 1.excel同步读取数据
        try {
            userExcelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(User.class).sheet().doReadSync();
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 2.检查是否大于1000条
        if (userExcelList.size() > 2) {
            throw new RuntimeException("超过最多处理条数");
        }
        // 3.将 userExcelList 转成 userList
        List<User> userList = userExcelList2UserList(userExcelList);
        // 5.入库操作
        userService.insert(userList);
    }

    /**
     * userExcelList转成userList
     *
     * @param userExcelList
     * @return
     */
    private List<User> userExcelList2UserList(List<User> userExcelList) throws ParseException {
        //Date now = new Date();
        List<User> userList = new ArrayList<>();
        for (User userExcel : userExcelList) {
            User user = User.builder()
                    .id(userExcel.getId())
                    .username(userExcel.getUsername())
                    .password(userExcel.getPassword())
                    .nickname(userExcel.getNickname())
                    .email(userExcel.getEmail())
                    .avater(userExcel.getAvater()).build();
            userList.add(user);
        }
        return userList;
    }

六、UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhang.dao.UserMapper">

    <insert id="insert" parameterType="java.util.List">
        insert into user(id, username, nickname, password, email, avater)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.username},#{item.nickname},#{item.password},#{item.email},#{item.avater})
        </foreach>
    </insert>


    <select id="findAll" resultType="com.zhang.entity.User">
        select id, username, nickname, password, email, avater
        from user
    </select>
</mapper>

摘自华为云开发者联盟
原文地址：
https://huaweicloud.csdn.net/63876d03dacf622b8df8bbc3.html?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7Eactivity-1-119971484-blog-130336821.235%5Ev38%5Epc_relevant_anti_vip&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7Eactivity-1-119971484-blog-130336821.235%5Ev38%5Epc_relevant_anti_vip&utm_relevant_index=1