easyEXCEL导入导出的使用（easy excel 导入导出）

随笔记录通过easy excel实现导入导出

第一步：导入依赖

<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.0</version></dependency>第二步：创建导入导出工具类 public class ExcelUtil { /** * 导出数据为excel文件 * * @param filename 文件名称 * @param sheetName sheet名称 * @param dataResult 集合内的bean对象类型要与clazz参数一致 * @param clazz 集合内的bean对象类型要与clazz参数一致 * @param response HttpServlet响应对象 *///有实体对象的导出方式 public static void export(String filename,String sheetName, List<?> dataResult, Class<?> clazz, HttpServletResponse response) { response.setStatus(200); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); OutputStream outputStream = null; ExcelWriter excelWriter = null; try { if (StringUtil.isEmpty(filename)) { throw new RuntimeException("filename 不能为空"); } String fileName = filename.concat(".xls"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); outputStream = response.getOutputStream(); // 根据不同的策略生成不同的ExcelWriter对象 if (dataResult == null){ excelWriter = getTemplateExcelWriter(outputStream); } else { excelWriter = getExportExcelWriter(outputStream); } //获取表头个数 int last = clazz.getDeclaredFields().length; WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(clazz) .relativeHeadRowIndex(1) .registerWriteHandler(new MonthSheetWriteHandler(sheetName,last))//设置大标题名称及其单元格合并 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽 .build(); // 写出数据 excelWriter.write(dataResult,writeSheet); } catch (Exception e) { log.error("导出excel数据异常：", e); throw new RuntimeException(e); } finally { if (excelWriter != null) { excelWriter.finish(); } if (outputStream != null) { try { outputStream.flush(); outputStream.close(); } catch (IOException e) { log.error("导出数据关闭流异常", e); } } } }//没有实体对象的导出方式 public static void export(String filename,String sheetName,String headName, List<?> dataResult, List<List<String>> head, HttpServletResponse response) { response.setStatus(200); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); OutputStream outputStream = null; ExcelWriter excelWriter = null; try { if (StringUtil.isEmpty(filename)) { throw new RuntimeException("filename 不能为空"); } String fileName = filename.concat(".xls"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); outputStream = response.getOutputStream(); // 根据不同的策略生成不同的ExcelWriter对象 if (dataResult == null){ excelWriter = getTemplateExcelWriter(outputStream); } else { excelWriter = getExportExcelWriter(outputStream); } WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(head) .relativeHeadRowIndex(1) .registerWriteHandler(new MonthSheetWriteHandler(headName,head.size()))//设置大标题名称及其单元格合并 .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽 .build(); // 写出数据 excelWriter.write(dataResult,writeSheet); } catch (Exception e) { log.error("导出excel数据异常：", e); throw new RuntimeException(e); } finally { if (excelWriter != null) { excelWriter.finish(); } if (outputStream != null) { try { outputStream.flush(); outputStream.close(); } catch (IOException e) { log.error("导出数据关闭流异常", e); } } } } /** * 根据不同策略生成不同的ExcelWriter对象    
    
    
， 可根据实际情况修改 * @param outputStream 数据输出流 * @return 模板下载ExcelWriter对象 */ private static ExcelWriter getTemplateExcelWriter(OutputStream outputStream){ return EasyExcel.write(outputStream) //.registerWriteHandler(new CommentWriteHandler()) //增加批注策略 //.registerWriteHandler(new CustomSheetWriteHandler()) //增加下拉框策略 .registerWriteHandler(getStyleStrategy()) //字体居中策略 .build(); } /** * 根据不同策略生成不同的ExcelWriter对象  


     


     


  ， 可根据实际情况修改 * @param outputStream 数据输出流 * @return 数据导出ExcelWriter对象 */ private static ExcelWriter getExportExcelWriter(OutputStream outputStream){ return EasyExcel.write(outputStream) .registerWriteHandler(getStyleStrategy()) //字体居中策略 .build(); } /** * 设置表格内容居中显示策略 * @return */ private static HorizontalCellStyleStrategy getStyleStrategy(){ WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index); //设置头字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short)10); headWriteFont.setBold(true); headWriteCellStyle.setWriteFont(headWriteFont); headWriteFont.setFontName("宋体"); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 内容策略 WriteCellStyle writeCellStyle = new WriteCellStyle(); // 设置内容水平居中 writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //垂直居中,水平居中 writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); writeCellStyle.setBorderLeft(BorderStyle.THIN); writeCellStyle.setBorderTop(BorderStyle.THIN); writeCellStyle.setBorderRight(BorderStyle.THIN); writeCellStyle.setBorderBottom(BorderStyle.THIN); writeCellStyle.setWriteFont(headWriteFont); //设置 自动换行 //contentWriteCellStyle.setWrapped(true); return new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle); } /** * 根据Excel模板


 




 




 
，批量导入数据 * @param file 导入的Excel * @param clazz 解析的类型 * @return 解析完成的数据 */ public static List<?> importExcel(MultipartFile file, Class<?> clazz){ if (file == null || file.isEmpty()){ throw new RuntimeException("没有文件或者文件内容为空！"); } List<Object> dataList = null; BufferedInputStream ipt = null; try { InputStream is = file.getInputStream(); // 用缓冲流对数据流进行包装 ipt = new BufferedInputStream(is); // 数据解析监听器 ExcelListener listener = new ExcelListener(); // 读取数据 EasyExcel.read(ipt, clazz,listener).headRowNumber(2).sheet().doRead(); // 获取去读完成之后的数据 dataList = listener.getDatas(); } catch (Exception e){ log.error(String.valueOf(e)); throw new RuntimeException("数据导入失败！" + e); } return dataList; }}第三步：表格头部设置 public class MonthSheetWriteHandler implements SheetWriteHandler { private String titleName=""; private int last=0; public MonthSheetWriteHandler(String titleName,int last){ this.titleName=titleName; this.last=last; } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Workbook workbook = writeWorkbookHolder.getWorkbook(); Sheet sheet = workbook.getSheetAt(0); Row row1 = sheet.createRow(0); row1.setHeight((short) 800); Cell cell = row1.createCell(0); //设置标题 cell.setCellValue(titleName+"表"); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.CENTER); Font font = workbook.createFont(); font.setBold(true); font.setFontHeight((short) 400); font.setFontName("宋体"); cellStyle.setFont(font); cell.setCellStyle(cellStyle); sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, last)); }}第四步：创建导入的监听器 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();//解析结束销毁不用的资源 }}第五步：service层调用导入导出方法//导出/** fileName:文件名称 sheetName:sheet名称 exportList:数据源HouseExportVO.class:实体类对象



  




  




  ，与数据源对应response:HttpServlet响应对象 */ //ExcelUtil.export(fileName,sheetName,exportList, HouseExportVO.class, response); 有实体类对象的调用方式/** fileName:文件名称 sheetName:sheet名称 headName:头部标题名称 dataList：数据源组装 headList：动态头部列组装 response:HttpServlet响应对象 */ //ExcelUtil.export(fileName,sheetName,headName,dataList, headList, response);动态列调用方式//导入 //读取文件  


  


  


，获取数据/** multipartFile:导入的文件    
     
     
，由前端传入 HouseImportExcelDTO.class：接受数据所对应的实体对象 读取到的数据源：excelData *///List<?> excelData = ExcelUtil.importExcel(multipartFile, HouseImportExcelDTO.class);自此通用的easyExcel导入导出就完成了




   




   




   ，具体导出的表格样式自行设置！