当前位置：首页 > news >正文

使用easypoi读取Excel模板

news 来源：原创 2024/9/20 8:09:34

1、只读取一个脚本号=====Excel
2、读取多个脚本号的sheet…=====Excel

1、只读取sheet0(只读取一个脚本号的Excel)

前言：引入pom文件

  <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel-core</artifactId><version>3.3.2</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.2</version></dependency>

读取文件模板
读取文件实体

//注意Excel 引入的包
//注意Excel 引入的包
//注意Excel 引入的包
import cn.afterturn.easypoi.excel.annotation.Excel;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestDto {@Excel(name = "姓名")private String name;@Excel(name = "年龄")private int age;@Excel(name = "性别")private String sex;
}

读取文件

//controller层
public class ExcelImportController {@Autowiredprivate ExcelImportServiceImpl excelImportService;@PostMapping("/import")@ApiOperation(value = "批量导入")public void importExcel() {File file = new File("D:\\maven_workplace\\Aproject-GJ\\yh-calculation-server\\calculation\\src\\main\\resources\\test.xlsx");return excelImportService.importExcel(file);}
}
//service层
@Service
public class ExcelImportServiceImpl {@Autowiredprivate LossComputeService lossComputeService;public void importExcel(File file) {//文件进行校验
//        checkFile(file);//获取excel文件List<TestDto> excelImportCos = EasyExcelUtil.importExcel(file, TestDto.class);System.out.println("输出excel返回实体======>"+excelImportCos.toString());ArrayList<TestDto> testDtos = new ArrayList<>();for (TestDto testDto : excelImportCos) {TestDto dto = new TestDto();dto.setName(testDto.getName());dto.setAge(testDto.getAge());dto.setSex(testDto.getSex());testDtos.add(testDto);}System.out.println("输出封装到实体中的数据======>"+ testDtos.toString());return null;}
//EasyExcelUtil  工具类@Component
@Slf4j
public class EasyExcelUtil {public static <T> List<T> importExcel(File file, Class<T> pojoClass)  {ImportParams params = new ImportParams();params.setTitleRows(0);params.setHeadRows(1);params.setKeyIndex(1);return  importExcelExcep(file, pojoClass, params);}private static <T> List<T> importExcelExcep(File file, Class<T> pojoClass, ImportParams params) {List<T> list = null;try {//ExcelImportUtil  是包   cn.afterturn.easypoi.excel;list = ExcelImportUtil.importExcel(file, pojoClass, params);
//            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);} catch (Exception e) {log.error("导入失败",e);throw new RuntimeException("导入失败");}return list;}
}

结果：
在这里插入图片描述

2、读取多个脚本号的sheet…的Excel

前言：引入pom文件

  <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel-core</artifactId><version>3.3.2</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.2</version></dependency>

读取文件模板
读取文件实体

//注意Excel 引入的包
//注意Excel 引入的包
//注意Excel 引入的包
import cn.afterturn.easypoi.excel.annotation.Excel;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestDto {@Excel(name = "姓名")private String name;@Excel(name = "年龄")private int age;@Excel(name = "性别")private String sex;
}
------------------------------------------------另一个实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Test2Dto {@Excel(name = "姓名")private String name;@Excel(name = "年龄")private int age;@Excel(name = "时间")private String time;
}

读取文件

//controller层
public class ExcelImportController {@Autowiredprivate ExcelImportServiceImpl excelImportService;@PostMapping("/import")@ApiOperation(value = "批量导入")public AjaxResult<LossCalculationLog> importExcel() {File file = new File("D:\\maven_workplace\\Aproject-GJ\\yh-calculation-server\\calculation\\src\\main\\resources\\test.xlsx");return excelImportService.importExcel(file);}}//service层
@Service
public class ExcelImportServiceImpl {@Autowiredprivate LossComputeService lossComputeService;public AjaxResult<LossCalculationLog> importExcel(File file) {//文件进行校验
//        checkFile(file);//获取所有字段名称String[] allIsExcelFields = EasyExcelUtil.getAllIsExcelFields(TestDto.class);System.out.println("获取的所有的字段名称"+allIsExcelFields.toString());//获取list字段sheet0List<TestDto> excelImportCos = EasyExcelUtil.importExcelBatch(file, 0, TestDto.class, allIsExcelFields);System.out.println("输出excel返回实体======>"+excelImportCos.toString());ArrayList<TestDto> testDtos = new ArrayList<>();for (TestDto testDto : excelImportCos) {TestDto dto = new TestDto();dto.setName(testDto.getName());dto.setAge(testDto.getAge());dto.setSex(testDto.getSex());testDtos.add(testDto);}System.out.println("输出sheet0 封装到实体中的数据======>"+ testDtos.toString());String[] allIsExcelFields1 = EasyExcelUtil.getAllIsExcelFields(Test2Dto.class);//获取list字段sheet0,List<Test2Dto> excelImportCos1 = EasyExcelUtil.importExcelBatch(file, 1, Test2Dto.class, allIsExcelFields1);System.out.println("输出excel返回实体======>"+excelImportCos1.toString());ArrayList<Test2Dto> testDtos1 = new ArrayList<>();for (Test2Dto testDto : excelImportCos1) {Test2Dto dto = new Test2Dto();dto.setName(testDto.getName());dto.setAge(testDto.getAge());dto.setTime(testDto.getTime());testDtos1.add(dto);}System.out.println("输出sheet1 封装到实体中的数据======>"+ testDtos1.toString());//封装请求体,并调用损失计算服务
return null;}
}// EasyExcelUtil 工具@Component
@Slf4j
public class EasyExcelUtil {public static <T> List<T> importExcel(File file, Class<T> pojoClass)  {ImportParams params = new ImportParams();params.setTitleRows(0);params.setHeadRows(1);params.setKeyIndex(1);return  importExcelExcep(file, pojoClass, params);}private static <T> List<T> importExcelExcep(File file, Class<T> pojoClass, ImportParams params) {List<T> list = null;try {list = ExcelImportUtil.importExcel(file, pojoClass, params);
//            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);} catch (Exception e) {log.error("导入失败",e);throw new RuntimeException("导入失败");}return list;}/*** EXCEL注解的字段* @param tClass* @return* @param <T>*/public static<T> String[] getAllIsExcelFields(Class<T> tClass) {try{ArrayList<String> strings = new ArrayList<>();Field[] declaredFields = tClass.getDeclaredFields();for (Field declaredField : declaredFields) {declaredField.setAccessible(true);if(declaredField.isAnnotationPresent(Excel.class)){Excel annotation = declaredField.getAnnotation(Excel.class);strings.add(annotation.name());}}String[] allIsExcelFields = strings.toArray(new String[strings.size()]);return allIsExcelFields;}catch (Exception e){log.error("获取所有标注了EXCEL注解的字段异常",e);}return null;}public static <T> List<T> importExcelBatch(File file, Integer sheetNun, Class<T> pojoClass, String[] templateFields) {ImportParams params = new ImportParams();params.setTitleRows(0);params.setHeadRows(1);//开始位置params.setStartSheetIndex(sheetNun);//数量params.setSheetNum(1);params.setImportFields(templateFields);return  importExcelExcep(file, pojoClass, params);}
}
//也可以进行文件校验-----写到service即可，或者抽取到一个工具类中private void checkFile(MultipartFile file) {// 检查文件是否为空if (file == null || file.isEmpty()) {//上传文件为空 --异常，可自行定义throw new BizException(BizError.UPLOAD_FILE_EMPTY_ERROR.getMsg());}// 检查文件类型（支持 xlsx、xlsb、xlsm、xls、xlst）String fileName = file.getOriginalFilename();if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xlsb") && !fileName.endsWith(".xlsm") && !fileName.endsWith(".xls") && !fileName.endsWith(".xlst")) {//文件格式异常-----抛出异常可自行定义throw new BizException(BizError.IMP_IMPORT_FILE_TYPE_ERROR.getMsg());}}