easy excel 导入导出

1、参考

• 使用说明
• 使用EasyExcel导入导出Excel
• easyExcel简单excel导出以及多sheet页导出
• EasyExcel自定义导出样式

2、首先导入依赖

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

3、实体类

@Data
@ColumnWidth(18)//设置固定列宽
public class ScriptExcel {
    @ExcelProperty(index = 0, value = "Code")
    private String code;
    @ExcelProperty(index = 1, value = "Catalogue")
    private String category;
    @ExcelProperty(index = 2, value = "Type")
    private String type;
    @ExcelProperty(index = 3, value = "Instruction")
    private String desc;
    @ExcelProperty(index = 4, value = "Language")
    private String language;
    @ExcelProperty(index = 5, value = "Content")
    private String value;
    @ExcelProperty(index = 6, value = "Scene")
    private String feature;
}

4、导出的代码

public void exportModelExcel(HttpServletResponse response, String language) {
    try {
        //导出的数据
        List<ScriptExcel> dataSys = new ArrayList<>();
        List<ScriptExcel> dataFlow = new ArrayList<>();
        List<ScriptExcel> dataTalk = new ArrayList<>();
        List<ScriptExcel> dataRule = new ArrayList<>();

        String fileName = URLEncoder.encode("Script_" + DateUtils.getDateFormat("yyyyMMddHHmmss"), "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "System").head(ScriptExcel.class).build();
        WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "Flow").head(ScriptExcel.class).build();//导出的类型不同，修改ScriptExcel.class
        WriteSheet writeSheet3 = EasyExcel.writerSheet(2, "Small Talk").head(ScriptExcel.class).build();
        WriteSheet writeSheet4 = EasyExcel.writerSheet(3, "Rule").head(ScriptExcel.class).build();

        excelWriter.write(dataSys, writeSheet1);
        excelWriter.write(dataFlow, writeSheet2);
        excelWriter.write(dataTalk, writeSheet3);
        excelWriter.write(dataRule, writeSheet4);
        excelWriter.finish();

    } catch (IOException e) {
        e.getStackTrace();
        throw new EditorException(ErrorConstant.EXPORT_EXCEL_EXCEPTION, e);
    }
}

5、excel导入

1）添加导入的监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

public class ExcelListener extends AnalysisEventListener {
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<>();
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
        doSomething(o);//根据自己业务做处理
    }

    private void doSomething(Object object) {
        //1、入库调用接口
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // datas.clear();//解析结束销毁不用的资源
    }
}

2）读取导入数据

public void importExcel(MultipartFile multipartFile) {
    List<Object> excelData = new LinkedList<>();//将读取到的数据统一放到该List中
    try {
        for (int i = 0; i < 4; i++) {//读取4个sheet
            ExcelListener listener = new ExcelListener();//**注意：每次读sheet要新new一个监听，否则会重复读取之前读过的sheet的数据
            EasyExcel.read(multipartFile.getInputStream(), ScriptExcel.class, listener).sheet(i).doRead();
            excelData.addAll(listener.getDatas());
        }
    } catch (Exception e) {
        logger.error(CLASS_NAME + "importExcel():::" + e.getMessage());
        throw new EditorException(ErrorConstant.IMPORT_EXCEL_TRANS_DATA_EXCEPTION);
    }
}