1000字范文 > Java使用poi做加自定义注解实现对象与Excel相互转换

Java使用poi做加自定义注解实现对象与Excel相互转换

时间：2018-12-05 03:24:51

引入依赖

maven

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency>

Gradle

implementation group: 'org.apache.poi', name: 'poi', version: '3.17'

代码展示

1、自定义注解类

@Retention(value = RetentionPolicy.RUNTIME)@Target(value = ElementType.FIELD)public @interface Excel {String name();//列的名字int width() default 6000;//列的宽度int index() default -1;//决定生成的顺序boolean isMust() default true; // 是否为必须值,默认是必须的}

2、Java的Excel对象，只展现了field，get与set方法就忽略了

public class GoodsExcelModel {@Excel(name = "ID_禁止改动", index = 0, width = 0)private Long picId;//picId@Excel(name = "产品ID_禁止改动", index = 1, width = 0)private Long productId;@Excel(name = "型号", index = 3)private String productName;//产品型号@Excel(name = "系列", index = 2)private String seriesName;//系列名字@Excel(name = "库存", index = 5)private Long quantity;@Excel(name = "属性值", index = 4)private String propValue;@Excel(name = "价格", index = 6)private Double price;@Excel(name = "商品编码", index = 7, isMust = false)private String outerId;@Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Long dbId; // 数据库自增长idprivate Date createTime; // 记录创建时间}

3、Excel表格与对象转换的工具类，使用时指定泛型参数和泛型的class即可

public class ExcelUtil {private static final String GET = "get";private static final String SET = "set";private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);/*** 将对象转换成Excel** @param objList 需要转换的对象* @return 返回是poi中的对象*/public static HSSFWorkbook toExcel(List objList) {if (CollectionUtils.isEmpty(objList)) throw new NullPointerException("无效的数据");Class aClass = objList.get(0).getClass();Field[] fields = aClass.getDeclaredFields();HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet();for (int i = 0; i < objList.size(); i++) {HSSFRow row = sheet.createRow(i + 1);//要从第二行开始写HSSFRow topRow = null;if (i == 0) topRow = sheet.createRow(0);for (Field field : fields) {Excel excel = field.getAnnotation(Excel.class);//得到字段是否使用了Excel注解if (excel == null) continue;HSSFCell cell = row.createCell(excel.index());//设置当前值放到第几列String startName = field.getName().substring(0, 1);String endName = field.getName().substring(1, field.getName().length());String methodName = new StringBuffer(GET).append(startName.toUpperCase()).append(endName).toString();try {Method method = aClass.getMethod(methodName);//根据方法名获取方法,用于调用Object invoke = method.invoke(objList.get(i));if (invoke == null) continue;cell.setCellValue(invoke.toString());} catch (NoSuchMethodException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}if (topRow == null) continue;HSSFCell topRowCell = topRow.createCell(excel.index());topRowCell.setCellValue(excel.name());sheet.setColumnWidth(excel.index(), excel.width());}}return workbook;}/*** 将Excel文件转换为指定对象** @param file 传入的Excel* @param c 需要被指定的class* @return* @throws IOException* @throws IllegalAccessException* @throws InstantiationException*/public static <T> List<T> excelFileToObject(MultipartFile file, Class<T> c) throws IOException, IllegalAccessException, InstantiationException {//key为反射得到的下标,value为对于的set方法Map<Integer, String> methodMap = new HashMap<>();//保存第一列的值与对应的下标,用于验证用户是否删除了该列,key为下标,value为名字Map<Integer, String> startRowNameMap = new HashMap<>();//用来记录当前参数是否为必须的Map<Integer, Boolean> fieldIsMustMap = new HashMap<>();//得到所有的字段Field[] fields = c.getDeclaredFields();for (Field field : fields) {Excel excel = field.getAnnotation(Excel.class);if (excel == null) continue;String startName = field.getName().substring(0, 1);String endName = field.getName().substring(1, field.getName().length());String methodName = new StringBuffer(SET).append(startName.toUpperCase()).append(endName).toString();methodMap.put(excel.index(), methodName);startRowNameMap.put(excel.index(), excel.name());fieldIsMustMap.put(excel.index(), excel.isMust());}String fileName = file.getOriginalFilename();Workbook wb = fileName.endsWith(".xlsx") ? new XSSFWorkbook(file.getInputStream()) : new HSSFWorkbook(file.getInputStream());Sheet sheet = wb.getSheetAt(0);Row sheetRow = sheet.getRow(0);for (Cell cell : sheetRow) {Integer columnIndex = cell.getColumnIndex();if (cell.getCellTypeEnum() != CellType.STRING) throw new ExcelException("excel校验失败, 请勿删除文件中第一行数据 !!!");String value = cell.getStringCellValue();String name = startRowNameMap.get(columnIndex);if (name == null) throw new ExcelException("excel校验失败,请勿移动文件中任何列的顺序!!!");if (!name.equals(value)) throw new ExcelException("excel校验失败,【" + name + "】列被删除,请勿删除文件中任何列 !!!");}sheet.removeRow(sheetRow);//第一行是不需要被反射赋值的List<T> models = new ArrayList<>();for (Row row : sheet) {if (row == null || !checkRow(row)) continue;T obj = c.newInstance();//创建新的实例化对象Class excelModelClass = obj.getClass();startRowNameMap.entrySet().forEach(x -> {Integer index = x.getKey();Cell cell = row.getCell(index);String methodName = methodMap.get(index);if (StringUtils.isEmpty(methodName)) return;List<Method> methods = Lists.newArrayList(excelModelClass.getMethods()).stream().filter(m -> m.getName().startsWith(SET)).collect(Collectors.toList());String rowName = startRowNameMap.get(index);//列的名字for (Method method : methods) {if (!method.getName().startsWith(methodName)) continue;//检测value属性String value = valueCheck(cell, rowName, fieldIsMustMap.get(index));//开始进行调用方法反射赋值methodInvokeHandler(obj, method, value);}});models.add(obj);}return models;}/*** 检测当前需要赋值的value** @param cell 当前循环行中的列对象* @param rowName 列的名字{@link Excel}中的name* @param isMust 是否为必须的* @return 值*/private static String valueCheck(Cell cell, String rowName, Boolean isMust) {//有时候删除单个数据会造成cell为空,也可能是value为空if (cell == null && isMust) {throw new ExcelException("excel校验失败,【" + rowName + "】中的数据禁止单个删除");}if (cell == null) return null;cell.setCellType(CellType.STRING);String value = cell.getStringCellValue();if ((value == null || value.trim().isEmpty()) && isMust) {throw new ExcelException("excel校验失败,【" + rowName + "】中的数据禁止单个删除");}return value;}/*** 反射赋值的处理的方法** @param obj循环创建的需要赋值的对象* @param method 当前对象期中一个set方法* @param value 要被赋值的内容*/private static void methodInvokeHandler(Object obj, Method method, String value) {Class<?> parameterType = method.getParameterTypes()[0];try {if (parameterType == null) {method.invoke(obj);return;}String name = parameterType.getName();if (name.equals(String.class.getName())) {method.invoke(obj, value);return;}if (name.equals(Long.class.getName())) {method.invoke(obj, Long.valueOf(value));return;}if (name.equals(Double.class.getName())) {method.invoke(obj, Double.valueOf(value));}} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();}}private static boolean checkRow(Row row) {try {if (row == null) return false;short firstCellNum = row.getFirstCellNum();short lastCellNum = row.getLastCellNum();if (firstCellNum < 0 && lastCellNum < 0) return false;if (firstCellNum != 0) {for (short i = firstCellNum; i < lastCellNum; i++) {Cell cell = row.getCell(i);String cellValue = cell.getStringCellValue();if (!StringUtils.isBlank(cellValue)) return true;}return false;}return true;} catch (Exception e) {return true;}}

4、导出Excel与导入Excel的示例代码