EasyExcel入门使用

EasyExcel入门使用

1、引入依赖

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

2、Conroller层

@RestController
@RequestMapping(value = "/admin/product/category")
public class CategoryController {

    @Autowired
    private CategoryService categoryService;

    /**
     * 导出数据
     * @param response
     */
    @GetMapping("/exportData")
    public void export(HttpServletResponse response){
        categoryService.exportData(response);
    }

    /**
     * 导入数据
     * @param file
     * @return
     */
    @PostMapping("/importData")
    public Result importData(MultipartFile file){
        categoryService.importData(file);
        return Result.success();
    }
}

3、Service层

public interface CategoryService {
	/**
     * 导出数据
     * @param response
     */
    void exportData(HttpServletResponse response);

    /**
     * 导入数据
     * @param file
     * @return
     */
    void importData(MultipartFile file);
}

@Service
public class CategoryServiceImpl implements CategoryService {

    @Autowired
    private CategoryMapper categoryManager;

    /**
     * 导入数据
     *
     * @param file
     * @return
     */
    @Override
    public void importData(MultipartFile file) {

        try {
            ExcelListener<CategoryExcelVo> excelListener = new ExcelListener<>(categoryManager);

            EasyExcel.read(file.getInputStream(), CategoryExcelVo.class, excelListener).sheet().doRead();
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomException(ResultCodeEnum.DATA_ERROR);
        }
    }

    /**
     * 导出数据
     *
     * @param response
     */
    @Override
    public void exportData(HttpServletResponse response) {

        try {
            // 设置响应结果类型
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("分类数据", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            //获取并封装数据
            List<Category> categoryList = categoryManager.findAllCategory();

            List<CategoryExcelVo> categoryExcelVos = new ArrayList<>();

            if (!CollectionUtils.isEmpty(categoryList)) {
                for (Category category : categoryList) {
                    CategoryExcelVo categoryExcelVo = new CategoryExcelVo();
                    BeanUtils.copyProperties(category, categoryExcelVo);
                    categoryExcelVos.add(categoryExcelVo);
                }
            }

            EasyExcel.write(response.getOutputStream(), CategoryExcelVo.class).sheet("分类数据").doWrite(categoryExcelVos);
        } catch (Exception e) {
            e.printStackTrace();
            throw new CustomException(ResultCodeEnum.DATA_ERROR);
        }
    }
}

4、CategoryExcelVo

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CategoryExcelVo {

	@ExcelProperty(value = "id" ,index = 0)
	private Long id;

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

	@ExcelProperty(value = "图片url" ,index = 2)
	private String imageUrl ;

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

	@ExcelProperty(value = "状态" ,index = 4)
	private Integer status;

	@ExcelProperty(value = "排序" ,index = 5)
	private Integer orderNum;

}

5、ExcelListener

package com.atguigu.spzx.manager.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.atguigu.spzx.manager.mapper.CategoryMapper;
import com.atguigu.spzx.model.vo.product.CategoryExcelVo;

import java.util.List;

public class ExcelListener<T> implements ReadListener<T> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private final CategoryMapper categoryManager;

    public ExcelListener(CategoryMapper categoryManager) {
        this.categoryManager = categoryManager;
    }


    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        cachedDataList.add(t);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (!cachedDataList.isEmpty()) {
            saveData();
        }
    }

    /**
     * 存储数据库
     */
    private void saveData() {
        categoryManager.insertBatch((List<CategoryExcelVo>) cachedDataList);
    }
}