SpringBoot2(七)：引入easyExcel，导入导出e

引入依赖

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

编写工具类

/**
 * @Description: easyExcel工具类
 * @Author along
 * @Date 2022/9/16 17:56
 */
public class EasyExcelUtils {

    //浏览器中输出excel
    public static void export2Web(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
    }
}

应用

导出Excel

构建导出对象实体

/**
 * @Description: excel导出对象
 * @Author along
 * @Date 2022/9/16 17:11
 */
@Data
public class ExportTaskObjectVo {

    /**
     * value:定义表头名称，下面是简单表头，复杂表头写法：value={"aaa","bbb"}
     * index:定义位置，0代表第一列，不写就默认从上往下排序
     */
    @ColumnWidth(20)
    @ExcelProperty(value = "集团编号",index = 0)  
    private String customersNum;

    @ColumnWidth(20)
    @ExcelProperty(value = "集团名称",index = 1)
    private String customersName;

    @ColumnWidth(20)
    @ExcelProperty(value = "专线编号",index = 2)
    private String lineId;

    @ColumnWidth(20)
    @ExcelProperty(value = "专线类型",index = 3)
    private String lineType;
}

导出逻辑代码，下面是伪代码，意思一下

// 获取数据源
List<Demo> list = demoDao.list();

// 填充导出对象ExportTaskObjectVo
List<ExportTaskObjectVo> exportVos = new ArrayList<>();
for (VTopicDialTestInternetTask e : list) {
    ExportTaskObjectVo v = new ExportTaskObjectVo();
    v.setCustomersNum(e.getCustomersNum());
    v.setCustomersName(e.getCustomersName());
    v.setLineId(e.getLineId());
    v.setLineType(e.getLineType());
    exportVos.add(v);
}

// 调用工具类导出Excel
EasyExcelUtils.export2Web(response,
        "demo", // 导出的excel文件的文件名
        "sheet1", // excel第一个sheet的名称
        ExportTaskObjectVo.class,
        exportVos
);

完事，导出就这样简单，基本一看就能用

导入Excel并解析

导入的excel模板如下：

根据模板Excel内容构建与之匹配的接收类

@Data
public class DemoExcelModel {

    @ExcelProperty(value = "专线编号", index = 0)
    private String lineId;

    @ExcelProperty(value = "专线类型", index = 1)
    private String lineType;
}

下面是Controller里的接收逻辑

@RestController
@RequestMapping("/demo")
@CrossOrigin
@Slf4j
public class DemoController {

    @PostMapping("/parsing/excel")
    public void excel2Object(@RequestParam("file") MultipartFile file) throws IOException {
        log.info("接收并解析excel");
        InputStream inputStream = file.getInputStream();
        List<Object> excelModels = EasyExcel.read(inputStream)
                .head(DemoExcelModel.class)
                .sheet() // 设置sheet,默认读取第一个
                .headRowNumber(1) // 设置标题所在行数
                .doReadSync();
        log.info("Excel解析完成：{}", excelModels);

        // 解析完毕，下面想怎么处理这个数据就怎么处理

        for (Object object : excelModels) {
            // 强转
            DemoExcelModel model = (DemoExcelModel) object;
            // do something
        }
    }
}

完事