Spring下的excel上传识别到数据库

1.在pom.xml中添加jar包

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

<dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
</dependency>

<dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.3</version>
</dependency>

2.在aplicationContext.xml中定义文件解析器

<!-- 定义文件上传解析器 --> 
<bean id="multipartResolver"  class="org.springframework.web.multipart.commons.CommonsMultipartResolver">   
    <property name="defaultEncoding" value="utf-8"></property>
</bean>

3.前端页面

<form name="fileupload" enctype="multipart/form-data" action="teacher/upload" method="post">
            <p style="font-size:16px;">请选择正确的excel文件上传</p>
            <input type="file" name="excel">
            <input type="submit" value="上传">            
             <p style="color:red;">支持的excel格式为：xls、xlsx！</p>
</form>

4.controller部分

    @RequestMapping("/upload")
    public String up(@RequestParam(value = "excel", required = false) MultipartFile files, HttpServletRequest request) {
        if(!files.isEmpty()) {
         String path = request.getSession().getServletContext().getRealPath("/upload");
         String originalFilename = files.getOriginalFilename();    
         originalFilename = UUID.randomUUID().toString()+originalFilename;             
          File targetFile = new File(path,originalFilename );      //在对应区域生成文件    
          if (!targetFile.getParentFile().exists()) {
            targetFile.getParentFile().mkdirs();
          }else if (!targetFile.exists()) {
            targetFile.mkdirs();
          }          
          try {
                files.transferTo(targetFile);  
              } catch (Exception e) {
                e.printStackTrace();
              }               
        
         FileUploadServiceImpl fileUp = new FileUploadServiceImpl();
          String rootpath = path + File.separator + originalFilename;
          System.out.println("目标文件名称："+ rootpath); 
          List<String[]> excellist = fileUp.readExcel(rootpath);
          
          int len = excellist.size();  //行数
          System.out.println("集合的长度为："+len);
          for (int i = 0; i < len; i++) {
            String[] fields = excellist.get(i);            
            String title = fields[0];        
            String optiona = fields[1];         
            String optionb = fields[2];          
            String optionc = fields[3];        
            String optiond = fields[4];
            String s= fields[5];
            s=s.substring(0, 1);
            Integer point = Integer.valueOf(s);                    
            String types = fields[6];
            String answer = fields[7];
            if(types.equals("SINGLE")||types.equals("JUDGE")) {
                answer= answer.substring(0, 1);
            }

            Question question = new Question();
            question.setType(QuestionType.valueOf(types));
            question.setAnswer(answer);
            question.setOptionA(optiona);
            question.setOptionB(optionb);
            question.setOptionC(optionc);
            question.setOptionD(optiond);
            question.setPoint(point);
            question.setTitle(title);
            question.setTeacher((Teacher) request.getSession().getAttribute("teacher"));
            questionService.saveOrUpdate(question);
          }  
        
        }       
        return "teacher/index";        
    }    

5.service部分

public interface IFileUploadService {
     public List<String[]> readExcel(String path);
}

@Service
public class FileUploadServiceImpl implements IFileUploadService {

  @Override
  public List<String[]> readExcel(String path) { 
      SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); 
      List<String[]> list = null;
      //System.out.println("hao1");
      try { 
        //同时支持Excel 2003、2007 
        File excelFile = new File(path); //创建文件对象 
        FileInputStream is = new FileInputStream(excelFile); //文件流 
        Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的 
       // System.out.println("hao2");
        int sheetCount = workbook.getNumberOfSheets(); //Sheet的数量
        System.out.println(sheetCount);
       //存储数据容器 
        list = new ArrayList<String[]>();
        //遍历每个Sheet 
        for (int s = 0; s < sheetCount; s++) { 
          Sheet sheet = workbook.getSheetAt(s); 
          int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
          System.out.println(rowCount);
          //遍历每一行 ，从第二行开始遍历
          for (int r = 0; r < rowCount; r++) { 
             //第一行跳过 
            if(r == 0) continue;  
            Row row = sheet.getRow(r); 
            int cellCount = row.getPhysicalNumberOfCells(); //获取总列数 
            System.out.println(cellCount);
           //用来存储每行数据的容器 
            String[] model = new String[cellCount-1];
            //遍历每一列 ，从第二列开始遍历
            for (int c = 0; c < cellCount; c++) { 
              if(c == 0) continue;//第一列ID为标志列，不解析
              Cell cell = row.getCell(c); 
              int cellType = cell.getCellType();
     
              String cellValue = null; 
              switch(cellType) { 
                case Cell.CELL_TYPE_STRING: //文本 
                  cellValue = cell.getStringCellValue(); 
                //  model[c-1] = cellValue;
                  break; 
                case Cell.CELL_TYPE_NUMERIC: //数字、日期 
                  if(DateUtil.isCellDateFormatted(cell)) { 
                    cellValue = fmt.format(cell.getDateCellValue()); //日期型 
              
                  } 
                  else {                    
                    cellValue = String.valueOf(cell.getNumericCellValue()); //数字                                
                  } 
                  break; 
                case Cell.CELL_TYPE_BOOLEAN: //布尔型 
                  cellValue = String.valueOf(cell.getBooleanCellValue()); 
                  break; 
                case Cell.CELL_TYPE_BLANK: //空白 
                  cellValue = cell.getStringCellValue(); 
                  break; 
                case Cell.CELL_TYPE_ERROR: //错误 
                  cellValue = "错误"; 
                  break; 
                case Cell.CELL_TYPE_FORMULA: //公式 
                  cellValue = "错误"; 
                  break; 
                default: 
                  cellValue = "错误"; 
                  
              } 
              System.out.print(cellValue + "  "); 
              
              model[c-1] = cellValue;   //每行进行存储
            } 
            //model放入list容器中 
            list.add(model);     //按行存储
           
          } 
        } 
        is.close();     
      } 
      catch (Exception e) { 
        e.printStackTrace(); 
      }
      
      return list;  
    }
}