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); } }