poi通用导入excell工具类

最新推荐文章于 2024-04-15 20:57:20 发布

原创最新推荐文章于 2024-04-15 20:57:20 发布 · 301 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一个用于导入Excel数据到Java对象的实用工具。该工具能够处理多种数据类型，并能自动匹配Excel列与Java对象属性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在对应的pom文件添加对应的maven依赖

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>

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

/**
   * 导入Excel
   *
   * @param clazz
   * @param xls
   * @return
   * @throws Exception
   */
   @SuppressWarnings("rawtypes")
   public static List importExcel(Class<?> clazz, InputStream xls, String fileName) throws Exception {
       try {
           // 兼容2003和2007
           Workbook wb = getWorkbook(xls, fileName);
           // 取得Excel
           Sheet sheet = wb.getSheetAt(0);
           Field[] fields = clazz.getDeclaredFields();
           List<Field> fieldList = new ArrayList<Field>(fields.length);
           for (Field field : fields) {
               if (field.isAnnotationPresent(ModelProp.class)) {
                   ModelProp modelProp = field.getAnnotation(ModelProp.class);
                   if (modelProp.colIndex() != -1) {
                       fieldList.add(field);
                   }
               }
           }
           // 行循环
           List<ImportModel> modelList = new ArrayList<ImportModel>(sheet.getPhysicalNumberOfRows() * 2);
           for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
               // 数据模型
               ImportModel model = (ImportModel) clazz.newInstance();
               int nullCount = 0;
               Exception nullError = null;
               int colIndex = 0;
               for (Field field : fieldList) {
                   ModelProp modelProp = field.getAnnotation(ModelProp.class);
                   Cell cell = sheet.getRow(i).getCell(modelProp.colIndex());
                   try {
                       if (cell == null || cell.toString().length() == 0) {
                           /*nullCount++;
                           if (!modelProp.nullable()) {
                               nullError = new Exception(StringUtil.format(modelProp.name()+":存在为空的数据",
                                       new String[] { "" + (1 + i), modelProp.name(), modelProp.name() }));
                           }*/
                           continue;
                       } else if (field.getType().equals(Date.class)) {
                           if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(), new Date(parseDate(parseString(cell))));
                           } else {
                               BeanUtils.setProperty(model, field.getName(),
                                       new Date(cell.getDateCellValue().getTime()));
                           }
                       } else if (field.getType().equals(Timestamp.class)) {
                           if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(),
                                       new Timestamp(parseDate(parseString(cell))));
                           } else {
                               BeanUtils.setProperty(model, field.getName(),
                                       new Timestamp(cell.getDateCellValue().getTime()));
                           }
                       } else if (field.getType().equals(java.sql.Date.class)) {
                           if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(),
                                       new java.sql.Date(parseDate(parseString(cell))));
                           } else {
                               BeanUtils.setProperty(model, field.getName(),
                                       new java.sql.Date(cell.getDateCellValue().getTime()));
                           }
                       } else if (field.getType().equals(java.lang.Integer.class)) {
                           if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue());
                           } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell)));
                           }
                       } else if (field.getType().equals(java.math.BigDecimal.class)) {
                           if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(),
                                       new BigDecimal(cell.getNumericCellValue()));
                           } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell)));
                           }
                       } else {
                           if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(),
                                       new BigDecimal(parseString(cell)));
                           } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                               BeanUtils.setProperty(model, field.getName(), parseString(cell));
                           }
                       }
                       colIndex++;
                   } catch (Exception e) {
                       e.printStackTrace();
                       throw new Exception(StringUtil.format(errormsg, new String[] { "" + (1 + i), modelProp.name() })
                               + "," + e.getMessage());
                   }

               }
               if (nullCount == fieldList.size()) {
                   break;
               }
               if (nullError != null) {
                   throw nullError;
               }

               modelList.add(model);
           }
           return modelList;
       } finally {
           xls.close();
       }
   }