Easyexcel导入导出

Easyexcel导⼊导出
1、参考
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);
}
}。