SpringBoot整合easyexcel实现导入导出

一、excel读写测试

1.导入依赖

<properties>
    <easyexcel.version>2.2.0-beta2</easyexcel.version>
</properties>
 <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.version}</version>
</dependency>

2.创建实体类

@Data
public class User {

    @ExcelProperty(index = 0,value = "用户编号")
    private int id;

    @ExcelProperty(index = 1,value = "用户姓名")
    private String userName;
}

3.写入excel文件

public class WriteTest {

    public static void main(String[] args) {

      List<User> list=new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            User user=new User();
            user.setId(i);
            user.setUserName("candy"+i);
            list.add(user);
        }
        String path="F:\\git\\ShangGuiGu\\excel\\01.xlsx";
        EasyExcel.write(path,User.class)
                .sheet("用户信息")
                .doWrite(list);

    }
}

4.读excel文件

4.1.读之前要添加监听器

public class EventListener extends AnalysisEventListener<User>{

    /**
     * 从第二行开始一行一行读取
     * @param user 实体类
     * @param analysisContext 内容
     */
    @Override
    public void invoke(User user, AnalysisContext analysisContext) {
        System.out.println(user);
    }


    /**
     * 读取第一行
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("头信息"+headMap);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

}

4.2读excel文件

public class ReadTest {

    public static void main(String[] args) {
        String file="F:\\git\\ShangGuiGu\\excel\\01.xlsx";
       EasyExcel.read(file,User.class, new EventListener()).sheet().doRead();
    }
}

二、excel整合SpringBoot

1.编写实体类

@Data
public class DictEeVo {

	/**
	* 属性上添加注解，设置表头内容
	*/
	@ExcelProperty(value = "id" ,index = 0)
	private Long id;

	@ExcelProperty(value = "上级id" ,index = 1)
	private Long parentId;

	@ExcelProperty(value = "名称" ,index = 2)
	private String name;

	@ExcelProperty(value = "值" ,index = 3)
	private String value;

	@ExcelProperty(value = "编码" ,index = 4)
	private String dictCode;

}

2.编写mapper文件

@Repository
public interface DictMapper extends BaseMapper<Dict> {
}

3.编写监听器

/**
 * @FileName: DictListener
 * @Author Steven
 * @Date: 2021/3/17
 */

public class DictListener extends AnalysisEventListener<DictEeVo>{

    @Autowired
    DictMapper dictMapper;

    public DictListener(DictMapper dictMapper) {
        this.dictMapper=dictMapper;
    }

    @Override
    public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
        Dict dict=new Dict();
        BeanUtils.copyProperties(dictEeVo,dict);
        dictMapper.insert(dict);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

3.编写service接口

public interface DictService extends IService<Dict>{
  /**
     * 导出
     *  @param response
     */
    void exportData(HttpServletResponse response);

    /**
     * @param multipartFile 导入字典
     */
    void importDictData(MultipartFile multipartFile);
}

3.编写service实现类

@Service
public class DictServiceImpl extends ServiceImpl<DictMapper, Dict> implements DictService {
@Override
    public void exportData(HttpServletResponse response) {
        try {
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            String fileName = URLEncoder.encode("数据字典", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            List<Dict> dictList = dictMapper.selectList(null);
            List<DictEeVo> dictVos = new ArrayList<>(dictList.size());
            for (Dict dict : dictList) {
                DictEeVo dictVo = new DictEeVo();
                BeanUtils.copyProperties(dict, dictVo);
                dictVos.add(dictVo);
            }
            EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVos);
        } catch (Exception e) {
            throw new YyghException("数据导出失败！！！", 123);
        }
    }

    /**
     * 调用后清空所有方法
     *
     * @param multipartFile 导入字典
     */
    @CacheEvict(value = "dict", allEntries = true)
    @Override
    public void importDictData(MultipartFile multipartFile) {
        try {
            EasyExcel.read(multipartFile.getInputStream(), DictEeVo.class, new DictListener(baseMapper)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

4.编写controller

@Api(tags = "数据字典接口")
@RestController
@RequestMapping("/admin/cmn/dict")
public class DictController {
@ApiOperation("数据导出")
    @GetMapping("/exportData")
    public Result exportData(HttpServletResponse response){
        dictService.exportData(response);
        return Result.ok();
    }

    @ApiOperation("导入字典数据")
    @PostMapping("/importData")
    public Result importData(MultipartFile file){
        dictService.importDictData(file);
        return Result.ok();
    }
}