EasyExcel对于导入导出的操作十分简洁,记录一下多个sheet且内容不一致的导入导出。
- 导出下载
提示:其中部分代码操作Dao层可以删除,可以自己创建ExportUserExcel 对象进行测试,思路数据映射到excel中。
这里ExportUserExcel .class 映射的模板替换下面代码中
@AllArgsConstructor
@NoArgsConstructorUser
@Builder
@HeadRowHeight(value = 20)
public class ExportUserExcel {
@ExcelProperty(value = "姓名",index = 0)
@ColumnWidth(value = 10)
private String userName;
@ExcelProperty(value = "年龄",index = 1)
@ColumnWidth(value = 20)
private String age;
@ExcelProperty(value = "性别",index = 2)
@ColumnWidth(value = 20)
private String gender;
}
- 导出下载 ExportUserExcel.class 模板
public void modelExport(HttpServletResponse response, Long id) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
try {
String fileName = URLEncoder.encode("Physical", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//新建ExcelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
//获取sheet0对象
WriteSheet mainSheet = EasyExcel.writerSheet(0, "模型信息").head(ExportUserExcel.class).build();
//获取模型信息,向sheet0写入数据
DmmPhysicalModel dmmPhysicalModel = dmmPhysicalModelMapper.selectById(id);
List<DmmPhysicalModel> dmmPhysicalModels1 = Arrays.asList(dmmPhysicalModel);
excelWriter.write(dmmPhysicalModels1, mainSheet);
//获取sheet1对象
WriteSheet detailSheet = EasyExcel.writerSheet(1, "词条信息").head(ExportUserExcel.class).build();
HashMap entryHashMap = new HashMap<>();
entryHashMap.put("model_id", id);
//获取词条信息,向sheet1写入数据
List<DmmPhysicalEntry> list = dmmPhysicalEntryMapper.selectByMap(entryHashMap);
excelWriter.write(list, detailSheet);
//关闭流
excelWriter.finish();
} catch (IOException e) {
log.error("导出异常{}", e.getMessage());
}
}
- 导入excel 存入数据库,
这里很多博客的代码没有贴全,少了一个监视器读取excel的类!
package com.infinitus.dmm.service.impl;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* excel表格读取监视器
*/
public class ExcelListener extends AnalysisEventListener {
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);
doSomething(o);
}
private void doSomething(Object object) {
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
2.excel导入到数据库 多个sheet、
ModelImportVO.class这也是模板,自己可以定义,对应的值。
public void modelImport(MultipartFile serviceFile, ContrastModelEntryDTO contrastModelEntryDTO) throws IOException {
//输入流
InputStream inputStream = serviceFile.getInputStream();
//监视器
ExcelListener listener = new ExcelListener();
ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
// 第一个sheet读取类型
ReadSheet readSheet1 = EasyExcel.readSheet(0).head(ModelImportVO.class).build();
// 第二个sheet读取类型
ReadSheet readSheet2 = EasyExcel.readSheet(1).head(EntryImportVO.class).build();
// 开始读取第一个sheet
excelReader.read(readSheet1);
//excel sheet0 信息
List<Object> list = listener.getDatas();
// 清空之前的数据
listener.getDatas().clear();
// 开始读取第二个sheet
excelReader.read(readSheet2);
//excel sheet1 信息
List<Object> entry = listener.getDatas();
}