<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>x
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>
<!--文件上传-->
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.4</version>
</dependency>

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
@HeadRowHeight(30)  // 标题行高
public class User {
    /**
     * @ExcelProperty
     *      value：列名称
     *      index：写：写内容到第几列，读：第几列的内容放到这个字段
     *      {}：给头在加一个头，所有都加，就会合并
     *
     * @ExcelIgnore
     *      导入或导出时候，不要这个字段
     *
     */
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelProperty(value = {"用户信息表","ID"},index = 5)
    @ExcelIgnore
    private Integer id;
    @ExcelProperty(value = {"用户信息表","姓名"},index = 1)
    @ColumnWidth(20)
    private String name;
    @ExcelProperty(value = {"用户信息表","年龄"},index = 2)
    private Integer age;
    @ExcelProperty(value = {"用户信息表","性别"},index = 3)
    private Integer sex;
    @ExcelProperty(value = {"用户信息表","日期"},index = 4)
    @ColumnWidth(20)
    private Date dateTime;
}

package com.zsh.esayexcel.domain.po;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserVO {
    private Integer sum;
    private Integer avg;
}

package com.zsh.esayexcel.mapper;
/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author KaiLangJiaoJiao
 * @since 2024-02-12
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

package com.zsh.esayexcel.service;
/**
 * <p>
 *  服务类
 * </p>
 *
 * @author KaiLangJiaoJiao
 * @since 2024-02-12
 */
public interface IUserService extends IService<User> {
}

package com.zsh.esayexcel.service.impl;
/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author KaiLangJiaoJiao
 * @since 2024-02-12
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}

package com.zsh.esayexcel.controller;
/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author KaiLangJiaoJiao
 * @since 2024-02-12
 */
@RestController
@RequestMapping("/user")
public class UserController {
}

package com.zsh.esayexcel.listener;
/**
 * 读取文档的监听器类
 */
public class UserListener extends AnalysisEventListener<User> {
    /**
     * 读监听器，每读一行内容，都会调用一次该对象的invoke，在invoke可以操作使用读取到的数据
     * @param user
     * @param analysisContext
     */
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        System.out.println("user = " + user);
    }
    /**
     * 读取完整个文档之后调用的方法
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

package com.zsh.esayexcel.listener;
@Scope("prototype") // 官方要求，这个类需要是多例对象，不能是单例
public class WebUserListener extends AnalysisEventListener<User> {
//    @Autowired
//    private IUserService userService;
    List<User> userList = new ArrayList<>();
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        userList.add(user);
        // 判断条数为5时添加一次
        if (userList.size() % 5 == 0) {
            IUserService userService = new UserServiceImpl();
            // 添加
            userService.saveBatch(userList);
            // 添加完清空数组
            userList.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 剩下数据不足5条时添加
        if (userList.size() != 0) {
            IUserService userService = new UserServiceImpl();
            // 添加
            userService.saveBatch(userList);
            // 添加完清空数组
            userList.clear();
        }
    }
}

package com.zsh.esayexcel;
@SpringBootTest
public class ExcelTest {
    /**
     * 读excel
     */
    @Test
    public void test(){
        /**
         * pathName:要读取的文件路径
         *
         * head:文件中每一行数据要存储到的实体类行的class
         *
         * readListener:读监听器，每读一行内容，都会调用一次该对象的invoke，在invoke可以操作使用读取到的数据
         */
        // 获得一个工作簿对象
        ExcelReaderBuilder readWorkBook = EasyExcel.read("C:\\Users\\开朗觉觉\\Desktop\\用户表.xlsx", User.class, new UserListener());
        // 获得工作簿对象
        ExcelReaderSheetBuilder sheet = readWorkBook.sheet();
        /**
         * headRowNumber()
         * 从第几行开始读，默认是1
         */
        // 读取工作表中内容
        sheet.headRowNumber(1).doRead();
    }
    
}

package com.zsh.esayexcel;
@SpringBootTest
public class ExcelTest {
    /**
     * 写excel
     */
    @Test
    public void test2(){
        // 工作簿对象
        // .xml会直接放到Module里
        ExcelWriterBuilder writeWorkBook = EasyExcel.write("学生表的写操作.xlsx", User.class); 
        // 工作表对象
        ExcelWriterSheetBuilder sheet = writeWorkBook.sheet();
        // 准备数据
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User(i,"张三"+i,10,2,new Date());
            userList.add(user);
        }
        // 写
        sheet.doWrite(userList);
    }
}

package com.zsh.esayexcel.controller;
/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author KaiLangJiaoJiao
 * @since 2024-02-12
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private IUserService userService;
    /**
     * 测试
     * @return
     */
    @GetMapping("/test")
    private String test(){
        return "ok";
    }
    /**
     * Excel导入
     * @param uploadExcel
     * @return
     */
    @PostMapping("/read")
    public String readExcel(@RequestBody MultipartFile uploadExcel){
        try {
            // 工作簿
            ExcelReaderBuilder readWorkBook = EasyExcel.read(uploadExcel.getInputStream(), User.class, new WebUserListener());
            // 工作表
            ExcelReaderSheetBuilder sheet = readWorkBook.sheet();
            // 读
            sheet.doRead();
            return "success";
        } catch (IOException e) {
            e.printStackTrace();
            return "fail";
        }
    }
    
}

package com.zsh.esayexcel.controller;
/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author KaiLangJiaoJiao
 * @since 2024-02-12
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private IUserService userService;
    /**
     * Excel导出
     * @param response
     */
    @GetMapping("/write")
    @ResponseBody
    public void writeExcel(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码
        String fileName = URLEncoder.encode("学生表测试", "UTF-8");
        response.setHeader("Content-Disposition","attachment; filename*=UTF-8''" + fileName + ".xlsx");
        // 收集要写的数据装进一个工作簿中
        ServletOutputStream outputStream = response.getOutputStream();
        // 获取工作簿
        ExcelWriterBuilder writeWrokBook = EasyExcel.write(outputStream, User.class);
        // 获取工作表
        ExcelWriterSheetBuilder sheet = writeWrokBook.sheet();
        // 获取数据
        List<User> userList = userService.list(null);
        // 写
        sheet.doWrite(userList);
    }
}

package com.zsh.esayexcel;
@SpringBootTest
public class ExcelTest {
    @Autowired
    private IUserService userService;
    /**
     * 混合导出
     */
    @Test
    public void test3(){
        // 准备模板
        String tempalte = "用户表模板.xlsx";
        /**
         * withTemplate()
         *      使用模板创建表
         */
        // 创建一个工作簿对象
        ExcelWriter workBook = EasyExcel.write("用户信息表.xlsx", User.class).withTemplate(tempalte).build();
        // 创建工作表对象
        WriteSheet sheet = EasyExcel.writerSheet().build();
        // 准备数据
//        IUserService userService = new UserServiceImpl();
        List<User> userList = userService.list(null);
        UserVO userVO = new UserVO();
        // 总人数
        userVO.setSum(userList.size());
        // 年龄总和
        Integer agesum = userList.stream()
                .map(user -> user.getAge())
                .reduce(0, (result, element) -> result + element);
        // 平均年龄
        userVO.setAvg(agesum / userList.size());
        // 数据填充
        FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();// 因为多组填充的数据不确定，需要在多组数据填充完之后另起一行
        // 多组填充
        workBook.fill(userList,fillConfig,sheet);
        // 单组填充
        workBook.fill(userVO,sheet);
        // 关闭流
        workBook.finish();
    }
}