WEB下的excel批量导入功能

新手学习中，记录一下excel导出功能实现的整个流程。使用框架ssm。

control层 importExcel+parseDataItem：

@RequestMapping("importExcel.do")
  public String importExcel(@RequestParam MultipartFile uploadfile,
          HttpServletRequest request, HttpServletResponse response) {
      String fileName = uploadfile.getOriginalFilename();
      if (StringUtils.isEmpty(fileName)) {
          System.out.println("未上传文件名为空");
      }
      String tempPath = request.getSession().getServletContext().getRealPath("/")
              + "upload";
      File dir = new File(tempPath);
      if (!tempPath.endsWith(File.separator)) {
          tempPath = tempPath + File.separator;
      }
      if (!dir.exists()) {
          dir.mkdirs();
      }
      System.out.println(tempPath);
      //制作路径
      String newFile = tempPath + fileName;
      File file = new File(newFile);
      try {
          FileCopyUtils.copy(uploadfile.getBytes(), file);
          //导入excel中的内容
          this.<span style="color:#ff0000;">parseDataItem</span>(newFile);
          System.out.println("成功导入");
      } catch (IOException e) {
          e.printStackTrace();
      } finally {
          try {
              FileUtils.forceDelete(file);
          } catch (IOException e) {
              e.printStackTrace();
              return "uploadfail";
          }
      }
      return "uploadsuccess";
  }

  public final boolean <span style="color:#ff0000;">parseDataItem</span>(String file){

     return ysdwAnimalService.<span style="color:#ff0000;">importExcel</span>(file);
  }

ysdwAnimalService层 import 调用底层通用方法readExcel：

@Transactional
    public boolean importExcel(String file) {
        final int rowStart = 2;
        final int cellStrart = 0;
        List<YsdwAnimal> list = new LinkedList<YsdwAnimal>();
        YsdwAnimal ysdwAnimal = null;
        try {
            List<Object> dataList = ReadExcleUtils.<span style="color:#ff0000;">readExcel</span>(file, new YsdwAnimalExcel(), rowStart, cellStrart);
            for (Object object : dataList) {
               YsdwAnimalExcel excel = (YsdwAnimalExcel) object;
                ysdwAnimal = new YsdwAnimal();
                String uuid = CommonUtils.getUuid();
                ysdwAnimal.setId(uuid);
                ysdwAnimal.setAnimalname(excel.getAnimalname());
                ysdwAnimal.setAnimalnumber(excel.getAnimalnumber());
                ysdwAnimal.setAnimaltypes(excel.getAnimaltypes());
                ysdwAnimal.setVegetationtypes(excel.getVegetationtypes());
                ysdwAnimal.setJingdu(excel.getJingdu());
                ysdwAnimal.setWeidu(excel.getWeidu());
                ysdwAnimal.setPodu(excel.getPodu());
                ysdwAnimal.setPowei(excel.getPowei());
                ysdwAnimal.setPoxiang(excel.getPoxiang());
                ysdwAnimal.setZuobiao(excel.getZuobiao());
                ysdwAnimal.setHaiba(excel.getHaiba());
                ysdwAnimal.setTianqi(excel.getTianqi());
                ysdwAnimal.setJilusj(excel.getJilusj());
                ysdwAnimal.setQita(excel.getQita());
                ysdwAnimal.setDid(excel.getDid());
                ysdwAnimal.setBaohdj(excel.getBaohdj());
                list.add(ysdwAnimal);
            }
            //存库
            for (YsdwAnimal YsdwAnimal : list) {
                YsdwAnimalMapper.saveOrUpdate(YsdwAnimal);
                System.out.println("存库");
            }
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;

    }

底层通用方法readExcel

/**
     * 读取EXCLE2007的方法
     * @param filePath 文件名
     * @param obj 对象名
     * @param rowStart 起点行（从0开始）
     * @param cellStrart 起点列（从0开始）
     * @return 对象集合
     * @throws IOException
     */
    @SuppressWarnings("rawtypes")
    public static List<Object> readExcel(final String filePath, final Object obj, final int rowStart, final int cellStrart) throws IOException {
        List<Object> aList = null;
        final String extension = filePath.substring(filePath.lastIndexOf(".")+1, filePath.length());//截取文件扩展名
        final Class c = obj.getClass();//获得类对象
        <span style="color:#cc0000;">final Field[] filds = c.getDeclaredFields();//获得该类中所有的属性</span>
        final File excelFile = new File(filePath);
        final InputStream is = new FileInputStream(excelFile);// 获取文件输入流
        if ("xlsx".equals(extension)) {//解析2007
            aList = ReadExcleUtils.<span style="color:#ff0000;">analyExcle2007</span>(c, is, filds, rowStart, cellStrart);
        } else if ("xls".equals(extension)) {
            aList = ReadExcleUtils.<span style="color:#ff0000;">analyExcle2003</span>(c, is, filds, rowStart, cellStrart);
        } else {
            System.out.println("上传文件不正确");
        }
        return aList;
    }

    @SuppressWarnings("rawtypes")
    private static List<Object> analyExcle2007(final Class c, final InputStream is, final Field[] filds, final int rowStart, final int cellStrart) throws IOException {
        Object objs = null;
        Map<String,Object> mapAtt = null;
        final List<Object> aList = new ArrayList<Object>();
        final XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2007文件对象
        final XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0
        final XSSFFormulaEvaluator evaluator=new XSSFFormulaEvaluator(workbook2007);
        for (int i = rowStart; i <= sheet.getLastRowNum(); i++) {
            String cellStr = null;// 单元格，最终按字符串处理
            final XSSFRow row = sheet.getRow(i);// 获取行对象
            if (row == null) {// 如果为空，不处理
                continue;
            }else {
                mapAtt = new HashMap<String,Object>();
                for (int j = 0; j < filds.length; j++) {
                    final XSSFCell cell = row.getCell(j+cellStrart);
                    //判断单元格的数据类型
                    if (cell != null) {
                        //对时间的特殊处理
                        int dataFormat = cell.getCellStyle().getDataFormat();
                        if (dataFormat == 14 || dataFormat == 176 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
                            cellStr = ReadExcleUtils.getDateValue2007(cell);
                        } else{
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC://数值
                                BigDecimal db = new BigDecimal(cell.getNumericCellValue());
                                if (db.toString().indexOf(".") != -1) {
                                    java.text.DecimalFormat dfomat = new java.text.DecimalFormat("0.000000");
                                    cellStr = dfomat.format(db);
                                }else {
                                    cellStr = db.toPlainString();
                                }
//                              cellStr = db.toPlainString();
//                              cellStr = String.valueOf(cell.getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                cellStr = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                cellStr = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA://公式
                                cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空值
                                cellStr =  "";
                                break;
                            default:
                                cellStr = cell.getStringCellValue();
                                break;
                            }
                        }
                    } else {
                        cellStr = null;
                    }
                    //讲单元格中的数据放入集合中
                    mapAtt.put(filds[j].getName(), cellStr);
                }
            }
            try {
                objs = c.newInstance();
                ReadExcleUtils.invokeMethod(c, objs, mapAtt);
                aList.add(objs);
            } catch (InstantiationException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return aList;
    }

    @SuppressWarnings("rawtypes")
    private static List<Object> analyExcle2003(final Class c, final InputStream is, final Field[] filds, final int rowStart, final int cellStrart) throws IOException {
        Object objs = null;
        Map<String,Object> mapAtt = null;
        final List<Object> aList = new ArrayList<Object>();
        final HSSFWorkbook workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象
        final HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0
        final HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook2003);
        for (int i = rowStart; i <= sheet.getLastRowNum(); i++) {
            final HSSFRow row = sheet.getRow(i);// 获取行对象
            if (row == null) {// 如果为空，不处理
                continue;
            }else {
                mapAtt = new HashMap<String,Object>();
                for (int j = 0; j < filds.length; j++) {
                    String cellStr = null;// 单元格，最终按字符串处理
                    final HSSFCell cell = row.getCell(j+cellStrart);
                    //判断单元格的数据类型
                    if (cell != null) {
                        //对时间的特殊处理
                        int dataFormat = cell.getCellStyle().getDataFormat();
//                      if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181 || dataFormat == 182) {
                        if (dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58) {
                            cellStr = ReadExcleUtils.getDateValue2003(cell);
                        } else{
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC://数值
                                BigDecimal db = new BigDecimal(cell.getNumericCellValue());
                                if (db.toString().indexOf(".") != -1) {
                                    java.text.DecimalFormat dfomat = new java.text.DecimalFormat("0.000000");
                                    cellStr = dfomat.format(db);
                                }else {
                                    cellStr = db.toPlainString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                cellStr = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                cellStr = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA://公式
                                cellStr = String.valueOf(evaluator.evaluate(cell).getNumberValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK://空值
                                cellStr =  "";
                                break;
                            default:
                                cellStr = cell.getStringCellValue();
                                break;
                            }
                        }
                    } else {
                        cellStr = null;
                    }
                    //讲单元格中的数据放入集合中
                    mapAtt.put(filds[j].getName(), cellStr);
                }
            }
            try {
                objs = c.newInstance();
                ReadExcleUtils.invokeMethod(c, objs, mapAtt);
                aList.add(objs);
            } catch (InstantiationException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return aList;
    }

注意！！在底层方法种完成对excel表格字段顺序的控制，具体控制语句为：final Field[] filds = c.getDeclaredFields();//获得该类中所有的属性

这就是从底层开始导入excel的完整过程