需求: 由于表格字段是动态的, 所以在做上传数据时需要先根据表的结构生成表格模板, 再根据模板填写内容然后上传数据;
1.下载表头模板
@RestController
@RequestMapping("/excel")
public class ExcelController {
@GetMapping("/exportExcelTemp")
public void exportExcelTemp(HttpServletResponse response) {
//表头
List<String> headList = new ArrayList<>();
headList.add("lie1");
headList.add("列2");
headList.add("列3");
String fileName = "表明";
//下拉框
List<EasyExcelSelectionVo> selectionVoList = new ArrayList<>();
List<String> selectionList = new ArrayList<>();
selectionList.add("选项1");
selectionList.add("选项2");
selectionList.add("选项3");
EasyExcelSelectionVo selectionVo = EasyExcelSelectionVo.builder()
//第一列
.index(0)
.list(selectionList)
.build();
List<String> selectionList2 = new ArrayList<>();
selectionList2.add("选项11");
selectionList2.add("选项22");
selectionList2.add("选项33");
EasyExcelSelectionVo selectionVo2 = EasyExcelSelectionVo.builder()
//第一列
.index(2)
.list(selectionList2)
.build();
selectionVoList.add(selectionVo);
selectionVoList.add(selectionVo2);
EasyExcelUtils.downloadHeadExcel(response,headList,fileName,selectionVoList);
}
}
复制代码2.上传数据
public class EasyExcelUtils {
/**
* 读取excel 数据
*
* @param file 被上传的文件
*/
public static void readExcel(MultipartFile file,UploadDao uploadDao) {
try {
EasyExcel.read(file.getInputStream(), new MyUpLoadListener(uploadDao)).sheet().doRead();
} catch (IOException ex) {
ex.printStackTrace();
throw new RuntimeException(file.getOriginalFilename() + "解析失败,请检查!");
}
}
}
复制代码3.监听类
public class MyUpLoadListener extends AnalysisEventListener<Map<Integer, String>> {
/**
* 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<Map<String, String>> dataList = new ArrayList<>();
List<String> headList = new ArrayList<>();
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param uploadDAO
*/
public MyUpLoadListener(UploadDAO uploadDAO) {
this.uploadDAO = uploadDAO;
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
Set<Map.Entry<Integer, String>> entries = headMap.entrySet();
log.info("{} > ===================== TABLE HEAD READY ======================", DateUtil.now());
for (Map.Entry<Integer, String> entry : entries) {
headList.add(entry.getValue());
}
log.info("{} > ===================== TABLE HEAD END ======================", DateUtil.now());
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
Set<Map.Entry<Integer, String>> entries = data.entrySet();
int i = 0;
Map<String, String> headMappingData = new LinkedHashMap<>();
for (Map.Entry<Integer, String> entry : entries) {
String headName = this.headList.get(i);
headMappingData.put(headName, entry.getValue());
i++;
}
this.dataList.add(headMappingData);
if (this.dataList.size() >= BATCH_COUNT) {
saveData();
// 这里必须清理 list ， 否则会出现数据重复
this.dataList.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
}
/**
* 加上存储数据库
*/
private void saveData() {
uploadDAO.save(dataList);
}
}
复制代码EasyExcel工具类
public class EasyExcelUtils {
/**
*导出表头
*/
public static void downloadHeadExcel(HttpServletResponse resp,
List<String> headList,
String sheetName,
List<EasyExcelSelectionVo> selections) {
try {
String fileName = sheetName + "(导入模板)";
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20");
EasyExcelUtils.responseDecoration(resp, fileName);
ExcelWriterBuilder builder = EasyExcel.write(resp.getOutputStream()).head(EasyExcelUtils.createHead(headList));
if (selections != null && !selections.isEmpty()) {
builder.registerWriteHandler(new MultiSelectionSheetWriteHandler(selections));
}
builder.sheet(sheetName).doWrite(new ArrayList<>());
} catch (Exception e) {
EasyExcelUtils.errorExport(resp, e);
}
}
/**
* 导出表数据
*/
public static void downloadDataExcel(HttpServletResponse resp, List<String> headList, String sheetName, List<List<String>> dataList) {
try {
String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\+", "%20");
EasyExcelUtils.responseDecoration(resp, fileName);
EasyExcel.write(resp.getOutputStream())
.head(EasyExcelUtils.createHead(headList))
.sheet(sheetName)
.doWrite(dataList);
} catch (Exception e) {
EasyExcelUtils.errorExport(resp, e);
}
}
/**
* 根据模板导出表数据
*/
public static <T> void exportByTemp(List<T> data, String tempFileName, Map<String, String> map, HttpServletResponse response) throws IOException {
InputStream templateFileName = ExcelUtil.class.getResourceAsStream("/excel" + File.separator + tempFileName);
EasyExcelUtils.responseDecoration(response, tempFileName);
//读取Excel
ExcelWriter excelWriter = EasyExcel
.write(response.getOutputStream())
.withTemplate(templateFileName)
.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
//customerDealerInfo 是我查询并需导出的数据，并且里面的字段和excel需要导出的字段对应
// 直接写入Excel数据
excelWriter.fill(data, writeSheet);
if (map != null && !map.isEmpty()) {
excelWriter.fill(map, writeSheet);
}
// 别忘记关闭流
excelWriter.finish();
}
/**
* 读取excel 数据
*
* @param file 被上传的文件
*/
public static void readExcel(MultipartFile file, UploadDao uploadDao) {
try {
EasyExcel.read(file.getInputStream(), new MyUpLoadListener(uploadDao)).sheet().doRead();
} catch (IOException ex) {
ex.printStackTrace();
throw new RuntimeException(file.getOriginalFilename() + "解析失败,请检查!");
}
}
/**
* 包装表头
*
* @param heads
* @return
*/
private static List<List<String>> createHead(List<String> heads) {
List<List<String>> head = new ArrayList<>(heads.size());
List<String> son;
for (String name : heads) {
son = new ArrayList<>();
son.add(name);
head.add(son);
}
return head;
}
private static void responseDecoration(HttpServletResponse resp, String fileName) {
// 如果后续报错，请针对该代码做放开处理
resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
resp.setCharacterEncoding("utf-8");
resp.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// resp.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
}
/**
* 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
*/
private static void errorExport(HttpServletResponse resp, Exception e) {
// 重置response
resp.reset();
resp.setContentType("application/json");
resp.setCharacterEncoding("utf-8");
//自定义异常类
throw new RuntimeException("下载文件失败," + e.getMessage());
}
}
复制代码
近期评论