彩票网站开发系统,公司图标大全,php网站下载,昆明网页重做1.实现目标
excel进行批量数据导入时#xff0c;将批量数据转化成指定的实体集合用于数据操作#xff0c;实现思路#xff1a;使用注解将属性与表格中的标题进行同名绑定来赋值。
2.代码实现
2.1 目录截图如下 2.2 代码实现
package poi.constants;/*** description: 用…1.实现目标
excel进行批量数据导入时将批量数据转化成指定的实体集合用于数据操作实现思路使用注解将属性与表格中的标题进行同名绑定来赋值。
2.代码实现
2.1 目录截图如下 2.2 代码实现
package poi.constants;/*** description: 用来定义一些通用的常量数据* author: zengwenbo* date: 2024/3/10 13:08*/
public class Constant {public final static String POINT .;public final static String SPACE ;
}
package poi.exception;/*** description: 用于解析excel报错提供的异常* author: zengwenbo* date: 2024/3/10 12:47*/
public class ExcelException extends RuntimeException {public ExcelException() {super();}public ExcelException(String message) {super(message);}public ExcelException(String message, Throwable cause) {super(message, cause);}
}
package poi.annotation;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.util.StringUtils;import java.lang.annotation.*;
import java.util.List;import static poi.constants.Constant.SPACE;/*** description: 用于绑定excel和实体字段属性的注解* author: zengwenbo* date: 2024/3/10 12:51*/
Documented
Retention(RetentionPolicy.RUNTIME)
Target(ElementType.FIELD)
public interface ExcelDescription {String desc(); // excel描述的标题的内容DataType type() default DataType.String; // 指定当前title数据的数据类型enum DataType {String {Overridepublic Object evaluateDataByType(Cell dataCell, ListListjava.lang.String validateList) {String cellValue dataCell.getStringCellValue();if (StringUtils.hasLength(cellValue) StringUtils.hasLength(cellValue.trim())) {// 判断当前值是否在序列中在的话用SPACE进行划分取前面的值不在的话原值返回return validateList.stream().filter(item - item.contains(cellValue)).map(item - cellValue.split(SPACE)[0]).findFirst().orElse(cellValue);}return cellValue;}}, Date {Overridepublic Object evaluateDataByType(Cell dataCell, ListListjava.lang.String validateList) {double cellValue dataCell.getNumericCellValue();return cellValue ! 0 ? DateUtil.getJavaDate(cellValue) : null;}}, BigDecimal {Overridepublic Object evaluateDataByType(Cell dataCell, ListListjava.lang.String validateList) {return java.math.BigDecimal.valueOf(dataCell.getNumericCellValue());}};/*** 根据数据类型来获取excel的数据值** param dataCell excel单元格对象* param validateList excel当前的序列数据有效性* return*/public abstract Object evaluateDataByType(Cell dataCell, ListListString validateList);}
}
package poi.utils;import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import poi.annotation.ExcelDescription;
import poi.exception.ExcelException;import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.nio.file.OpenOption;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;import static poi.constants.Constant.POINT;/*** description: 提供解析excel的工具类* author: zengwenbo* date: 2024/3/10 12:42*/
public class ExcelUtil {private final static String EXCEL_XLS xls;private final static String EXCEL_XLSX xlsx;/*** 将excel中table里面的数据转换成对应的实体** param clazz 需要转化实体的类对象* param titleRowNum table表格的标题行* param sheetIndex sheet的下标* return 返回转换后对象的list集合*/public static T ListT transTableToEntity(MultipartFile file, ClassT clazz, int titleRowNum, int sheetIndex) {// 1.获取文件的后缀String filename file.getOriginalFilename();String suffix filename.substring(filename.lastIndexOf(POINT) 1);// 2.根据获取的后缀名获取操作excel的对象Workbook workbook;try (InputStream inputStream file.getInputStream()) {switch (suffix) {case EXCEL_XLS:workbook new HSSFWorkbook(inputStream);break;case EXCEL_XLSX:workbook new XSSFWorkbook(inputStream);break;default:throw new ExcelException(后缀名不符);}} catch (IOException e) {throw new ExcelException(文件解析失败, e);}// 3.获取要操作的sheetSheet sheet workbook.getSheetAt(sheetIndex);// 4.通过表格标题获取操作的开始列和结束列Row titleRow sheet.getRow(titleRowNum);short firstCellNum titleRow.getFirstCellNum();short lastCellNum titleRow.getLastCellNum();// 5.获取表格中序列的数据有效性ListListString validateList new ArrayList();if (null ! sheet.getDataValidations()) {sheet.getDataValidations().forEach(item - {// 筛选出有效性数据时序列的进行添加if (null ! item.getValidationConstraint().getExplicitListValues()) {validateList.add(Arrays.asList(item.getValidationConstraint().getExplicitListValues()));}});}// 6.遍历数据进行解析ArrayListT list new ArrayList();Field[] fields clazz.getDeclaredFields();for (int i titleRowNum 1; i sheet.getLastRowNum(); i) {Row row sheet.getRow(i);try {// 获取实例对每个绑定的属性进行赋值T t clazz.newInstance();for (int j firstCellNum; j lastCellNum; j) {Cell titleCell titleRow.getCell(j);if (null titleCell) {throw new ExcelException(标题缺失请检查导入模板是否正常);}String title titleCell.getStringCellValue();if (!StringUtils.hasLength(title)) {throw new ExcelException(标题内容为空请检查导入模板是否正常);}Optional.ofNullable(row.getCell(j)).ifPresent(value - evaluateField(t, fields, value, title, validateList));}list.add(t);} catch (InstantiationException e) {throw new ExcelException(创建实例异常该类缺失无参构造方法);} catch (IllegalAccessException e) {throw new ExcelException(创建实例异常权限不足);}}return list;}/*** 通过单元格的值给对象的属性进行赋值** param t 对象实体* param fields 对象对应的属性数组* param dataCell 单元格对象* param title 单元格对象对应的title* param validateList 数据有效性列表*/private static T void evaluateField(T t, Field[] fields, Cell dataCell,String title, ListListString validateList) {for (Field field : fields) {// 处理属性上有ExcelDescription注解的数据进行赋值if (field.isAnnotationPresent(ExcelDescription.class)) {ExcelDescription annotation field.getAnnotation(ExcelDescription.class);// 获取注解的描述String desc annotation.desc();// 获取注解的数据类型ExcelDescription.DataType type annotation.type();// 如果title和描述desc一致则将cell里面的值赋值给该属性if (title.equals(desc)) {// 获取value的值Object value type.evaluateDataByType(dataCell, validateList);field.setAccessible(true);try {field.set(t, value);} catch (IllegalAccessException e) {throw new ExcelException(对象属性赋值权限异常);}}}}}
}3.测试数据
测试实体
package poi.bean;import lombok.Data;
import poi.annotation.ExcelDescription;import java.math.BigDecimal;
import java.util.Date;/*** description:* author: zengwenbo* date: 2024/3/10 14:07*/
Data
public class Person {ExcelDescription(desc 名称)private String name;ExcelDescription(desc 年龄, type ExcelDescription.DataType.BigDecimal)private BigDecimal age;ExcelDescription(desc 生日, type ExcelDescription.DataType.Date)private Date birth;ExcelDescription(desc 国籍)private String country;
}
测试的excel文件数据截图 对国籍数据进行了有效性填充 测试代码将excel文件放在resource目录下
package com.example.demo;import com.example.demo.redis.User;import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
import poi.bean.Person;
import poi.utils.ExcelUtil;import java.io.File;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.nio.file.Files;
import java.util.List;RunWith(SpringRunner.class)
SpringBootTest
class DemoApplicationTests {Autowiredprivate ResourceLoader resourceLoader;Testvoid TestExcel() throws Exception {Resource resource resourceLoader.getResource(classpath:test.xls );String fileName resource.getFilename();byte[] fileBytes Files.readAllBytes(resource.getFile().toPath());MultipartFile multipartFile new MockMultipartFile(fileName, fileName, text/plain, fileBytes);ListPerson list ExcelUtil.transTableToEntity(multipartFile, Person.class, 0, 0);}}
最终结果
