easyexcel-获取文件资源和导入导出excel

1、获取本地资源文件，根据模板填充数据导出

public void exportExcel(HttpServletResponse httpResponse, @RequestBody AssayReportDayRecordQuery query) {AssayReportDayRecordDTO dto = this.queryByDate(query);ExcelWriter excelWriter = null;ExcelUtil.configResponse(httpResponse);// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替// {} 代表普通变量 {.} 代表是list的变量 {前缀.} 前缀可以区分不同的listInputStream inputStream = null;File reportFile = null;try {inputStream = new ClassPathResource("classpath:template/" + query.getTemplateCode() + ".xls").getInputStream();reportFile = new File(query.getTemplateCode() + ".xls");FileUtils.copyInputStreamToFile(inputStream, reportFile);excelWriter = EasyExcel.write(httpResponse.getOutputStream()).withTemplate(reportFile.getPath()).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();// 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).direction(WriteDirectionEnum.VERTICAL).build();//填充列表数据if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(dto.getDataList())) {excelWriter.fill(dto.getDataList(), fillConfig, writeSheet);}} catch (Exception e) {log.error("导出异常：{}", e.getMessage());} finally {if (Objects.nonNull(reportFile) && reportFile.exists()) {boolean delete = reportFile.delete();log.info("文件删除" + (delete ? "成功" : "失败"));}// 千万别忘记close 会帮忙关闭流if (excelWriter != null) {excelWriter.finish();try {inputStream.close();} catch (IOException e) {log.error("文件流异常", e);}}}}

2、根据实体类导出，实体类做表头

public void generateDeviceExcel(Page<DeviceEntityVO> page, HttpServletResponse response) {List<DeviceEntityExcel> excels = new ArrayList<>();for (DeviceEntityVO vo : page) {DeviceEntityExcel excel = new DeviceEntityExcel();excel.setDeviceName(vo.getDeviceName());excel.setDeviceId(vo.getAssetsCode());excel.setCatalogue(vo.getCatalogue());excel.setCatalogueLeaf(vo.getCatalogueLeaf());excel.setModel(vo.getModel());excel.setManufactorName(vo.getManufactorName());excel.setUnit(vo.getUnit());excel.setDeviceGrade(vo.getDeviceGrade());excel.setDeviceStatusName(DeviceEntityStatus.getMessage(vo.getDeviceStatus()));excel.setCreateTime(vo.getCreateTime());excels.add(excel);}ExcelUtil.generateExcel(response, DeviceEntityExcel.class, excels);}

@Data
public class DeviceEntityExcel {@ApiModelProperty(value="设备资产名称", position=2)@ExcelProperty("设备资产名称")private String deviceName;/*** 设备资产编号*/@ApiModelProperty(value="设备资产编号", position=4)@ExcelProperty("设备资产编号")private String deviceId;@ApiModelProperty("设备分类")@ExcelProperty("设备分类")private String catalogue;@ApiModelProperty("细分种类")@ExcelProperty("细分种类")private String catalogueLeaf;/*** 设备型号*/@ApiModelProperty(value="设备型号", position=2)@ExcelProperty("设备型号")private String model;@ApiModelProperty("生产厂商")@ExcelProperty("生产厂商")private String manufactorName;@ApiModelProperty("单位")@ExcelProperty("单位")private String unit;/*** 等级*/@ApiModelProperty(value="等级", position=4)@ExcelProperty("等级")private String deviceGrade;@ApiModelProperty("状态")@ExcelProperty("状态")private String deviceStatusName;@ApiModelProperty("创建时间")@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")@JSONField(format = "yyyy-MM-dd HH:mm:ss")@ExcelProperty("创建时间")private Date createTime;
}

@Slf4j
public class ExcelUtil {public static void configResponse(HttpServletResponse response) {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf8");response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".xlsx");response.setHeader("Pragma", "public");response.setHeader("Cache-Control", "no-store");response.addHeader("Cache-Control", "max-age=0");}/*** 生成excel** @param response* @param head* @param data*/public static void generateExcel(HttpServletResponse response, Class head, List data) {ExcelUtil.generateExcel(response, head, data, "报表");}public static void generateExcel(HttpServletResponse response, Class head, List data, String sheetName) {ExcelUtil.configResponse(response);try {Map<Integer, String[]> map = DownloadTemplateUtil.downloadTemplate(head);EasyExcelFactory.write(response.getOutputStream(), head).autoCloseStream(Boolean.TRUE).registerWriteHandler(new ExportCellWriteHandler(map)).sheet(sheetName).doWrite(data);} catch (IOException e) {
//            e.printStackTrace();log.error("generateExcel{}",e.getMessage());}}/*** 生成excel（公共简易excel导出）** @param response* @param head* @param data*/public static void generateCommonExcel(HttpServletResponse response, Class head, List data, String sheetName) {ExcelUtil.configResponse(response);try {EasyExcelFactory.write(response.getOutputStream()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).head(head).autoCloseStream(Boolean.TRUE).sheet(sheetName).doWrite(data);} catch (IOException e) {log.error("ExcelUtil->generateCommonExcel", e);}}/*** 生成excel带下拉框** @param response* @param head* @param data*/public static void generateExcelWithSelect(HttpServletResponse response, Class head, List data, String[] select) {ExcelUtil.configResponse(response);try {EasyExcelFactory.write(response.getOutputStream(), head).registerWriteHandler(new SheetWriteHandler() {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//不需要处理}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {if (select.length > 0) {//通过sheet处理下拉信息Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();CellRangeAddressList rangeList = new CellRangeAddressList();CellRangeAddress addr = new CellRangeAddress(1, data.size(), 2, 2);rangeList.addCellRangeAddress(addr);DataValidationConstraint constraint = helper.createExplicitListConstraint(select);DataValidation validation = helper.createValidation(constraint, rangeList);sheet.addValidationData(validation);}}}).autoCloseStream(Boolean.TRUE).sheet("报表").doWrite(data);} catch (IOException e) {log.error("ExcelUtil -> generateExcelWithSelect()，异常：{}", e.getMessage());}}}

3、导入excel
监听器

public class DataListener extends AnalysisEventListener {public boolean really;public boolean repeatFlag;protected ExcelService service;List<Object> list = new ArrayList();@Overridepublic void invoke(Object o, AnalysisContext analysisContext) {list.add(o);}private void saveData() {try {this.repeatFlag = service.saveData(list,really);}catch (Exception e){throw new ValidateException("导入数据失败，请检查数据");}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {saveData();}public DataListener(ExcelService excelService,boolean really){this.service =  excelService;this.really = really;}
}

public interface ExcelService {boolean saveData(List list,boolean really);
}

DataListener listener = new DataListener(checkPointService,excelInfo.getReally());EasyExcelFactory.read(excelInfo.getFile().getInputStream(), CheckPointExcelVO.class,listener).sheet().doRead();

saveData自定义业务代码，将excel数据保存到数据库或做其他处理