java导入导出excel 用easypoi 太简单了，简单的让我感动的热泪盈眶，因为你让提高了效率让我早点下班
N多年用的poi导入导出实在写太多的代码，挺麻烦，现在有了这个 easypoi 这个小可爱，事情就变的如此简单了。
pom.xml导入包
<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.2.0</version> </dependency>
配置实体类
package com.giikin.ads.fb.adasset.entity.dto; import cn.afterturn.easypoi.excel.annotation.Excel; import cn.afterturn.easypoi.excel.annotation.ExcelTarget; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import org.checkerframework.checker.i18nformatter.qual.I18nFormat; import org.springframework.util.StringUtils; import javax.validation.constraints.Min; import javax.validation.constraints.NotBlank; import javax.validation.constraints.NotNull; import java.time.LocalDateTime; import java.util.ArrayList; import java.util.Collections; import java.util.List; import java.util.Objects; @Data @NoArgsConstructor @AllArgsConstructor @ExcelTarget(value = "BatchDto") public class FbBatchDto { @Excel(name = "平台*" , orderNum = "0" , width = 20.0) private String platform; @Excel(name = "开户批次号*" , orderNum = "1" ,width = 20.0) private String batchNum; @Excel(name = "申请时间*" , orderNum = "2" , format = "yyyy/MM/dd" , width = 20.0) private LocalDateTime applyTime; @Excel(name = "开户优化组长ID*" ,orderNum = "3" , width = 20.0) private Integer optLeaderId;//优化组长 @Excel(name = "开户人ID*" ,orderNum = "4" , width = 20.0) private Integer creatorId;//创建人 }
excel导入代码 excel直接导入成了java对象 放到了list里面
MultipartFile file //上传过来的文件对象
ImportParams params = new ImportParams(); params.setTitleRows(0); params.setHeadRows(1); params.setSheetNum(i); List<FbBatchDto> batchs = ExcelImportUtil.importExcel(file.getInputStream(), FbBatchDto.class, params);
excel导出代码
ExportParams params = new ExportParams(); params.setSheetName("无运营"); Workbook workbook = ExcelExportUtil.exportExcel(params, PageComparisonResult.class, Collections.emptySet()); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); byte[] bytes; try { workbook.write(outputStream); bytes = outputStream.toByteArray(); } catch (IOException e) { bytes = new byte[2]; }
另外赠送一下相关工具类
package com.giikin.ads.fb.util; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import org.apache.poi.hssf.usermodel.HSSFWorkbookFactory; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.util.StringUtils; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.io.IOException; import java.io.InputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; public class EasyPoiUtil { /** * 导出Excel,包括文件名以及表名，是否创建表头 * @param list 导出的实体类 * @param title 表头名称 * @param sheetName sheet表名 * @param pojoClass 映射的实体类 * @param fileName 文件名成 * @param isCreateHeader 是否创建表头 * @param response 响应对象 */ public static void exportExcel(List<?> list , String title , String sheetName , Class<?> pojoClass , String fileName , boolean isCreateHeader , HttpServletResponse response){ ExportParams params = new ExportParams(title , sheetName); params.setCreateHeadRows(isCreateHeader); defaultExport(list ,pojoClass , fileName , response , params); } /** * 导出Excel，默认创建表头 * @param list * @param title * @param sheetName * @param pojoClass * @param fileName * @param response */ public static void exportExcel(List<?> list , String title ,String sheetName , Class<?> pojoClass , String fileName , HttpServletResponse response){ defaultExport(list , pojoClass , fileName , response , new ExportParams(title , sheetName)); } /** * map多sheet形式导出 * @param list * @param fileName * @param response */ public static void exportExcel(List<Map<String , Object>> list , String fileName , HttpServletResponse response){ defaultExport(list , fileName , response); } /** * 常规默认导出方式 * @param list * @param pojoClass * @param fileName * @param response * @param params */ private static void defaultExport(List<?> list , Class<?> pojoClass , String fileName , HttpServletResponse response , ExportParams params){ Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, list); if (workbook != null)downLoadExcel(fileName , response , workbook); } /** * 多sheet默认导出方式 * @param list * @param fileName * @param response */ private static void defaultExport(List<Map<String , Object>> list , String fileName , HttpServletResponse response){ Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF); if (workbook != null)downLoadExcel(fileName , response , workbook); } private static void downLoadExcel(String fileName , HttpServletResponse response , Workbook workbook){ try { response.setCharacterEncoding("UTF-8"); response.setHeader("content-Type" , "application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); workbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } } /** * 通过文件路径形式导入 * @param filePath * @param titleRows * @param headerRows * @param pojoClass * @param <T> * @return */ public static <T> List<T> importExcel(String filePath , Integer titleRows , Integer headerRows , Class<T> pojoClass){ if (!StringUtils.hasText(filePath))return null; ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params); } catch (NoSuchElementException e) { throw new RuntimeException("模板不能为空"); } catch (Exception e){ e.printStackTrace(); throw new RuntimeException(e.getMessage()); } return list; } /** * 通过流的形式导入 * @param file * @param titleRows * @param headerRows * @param pojoClass * @param <T> * @return */ public static <T> List<T> importExcel(MultipartFile file , Integer titleRows , Integer headerRows , Class<T> pojoClass){ if (file == null)return null; ImportParams params = new ImportParams(); params.setTitleRows(titleRows); params.setHeadRows(headerRows); List<T> list = null; try { list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params); } catch (Exception e) { throw new RuntimeException("文件导入出错"); } return list; } /** * 得到workbook对象 * @param file * @return * @throws IOException */ public static Workbook getWorkBook(MultipartFile file) throws IOException { InputStream inputStream = file.getInputStream(); return HSSFWorkbookFactory.create(inputStream); } }
