maven项目导入excel表到数据库，插入mysql

导表是不能完全copy的，每个表的结构，和需要导入mysql的需求不一样，需要理解了，按自己的需求导表：

首先添加jar包：

<dependency>
           <groupId>org.apache.poi</groupId>
           <artifactId>poi-ooxml</artifactId>
           <version>3.9</version>
           </dependency>
           <dependency>
           <groupId>commons-fileupload</groupId>
           <artifactId>commons-fileupload</artifactId>
           <version>1.3.1</version>
           </dependency>
           <dependency>
           <groupId>commons-io</groupId>
           <artifactId>commons-io</artifactId>
           <version>2.4</version>
           </dependency>

html页面和js：

<h3>导入excel表格</h3>
<form action="uploadFile" method="post" enctype="multipart/form-data" onsubmit="return check();">
<input type="file" name="file" id="uploadEventFile">
</form>
<button type="button" class="btn btn-success btn-sm" onclick="user.uploadBtn()" >上传</button>

</body>
<script type="text/javascript">

   function uploadBtn(){

       //模拟上传excel
       $("#uploadEventBtn").unbind("click").bind("click",function(){
           $("#uploadEventFile").click();
       });

       $("#uploadEventFile").bind("change",function(){
           $("#uploadEventPath").attr("value",$("#uploadEventFile").val());
       });


   //点击上传按钮

};

</script>

controller里的代码:

/**接收上传的文件*/
   @RequestMapping(value="uploadFile",method=RequestMethod.POST)
   @ResponseBody
   public boolean upload(@RequestParam(value="file",required = false)MultipartFile file,HttpServletRequest request, HttpServletResponse response){
       boolean a = false;
   String fileName = file.getOriginalFilename();
   try {
   a = schoolTaskService.batchImport(fileName, file);
   } catch (Exception e) {
   e.printStackTrace();
   }
   return a;
   }

service接口:

public interface SchoolTaskService {
   /**
   * 读取excel中的数据,生成list
   */

boolean batchImport(String fileName, MultipartFile file) throws Exception;

}

serviceImp代码:

@Service
public class SchoolTaskServiceImp implements SchoolTaskService{

@Autowired
public SchoolTaskDao schoolTaskDao;

       @Transactional(readOnly = false,rollbackFor = Exception.class)
   @Override
   public boolean batchImport(String fileName, MultipartFile file) throws Exception {
           boolean notNull = false;

   if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
   throw new MyException("上传文件格式不正确");
   }
   boolean isExcel2003 = true;
   if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
   isExcel2003 = false;
   }
   InputStream is = file.getInputStream();
   Workbook wb = null;//创建工作簿
   if (isExcel2003) {
   wb = new HSSFWorkbook(is);
   } else {
   wb = new XSSFWorkbook(is);
   }
   Sheet sheet = wb.getSheetAt(0);//创建表
   if(sheet!=null){
   notNull = true;
   }

   int totalRows=sheet.getLastRowNum();//得到表的最后行数（总行数）
   // System.out.println("rowNum="+totalRows);
   List<SchoolTask> userList = new ArrayList<SchoolTask>();
   String[] strs = new String[8];
   Row rowtitle=sheet.getRow(0);//得到表下标为0的第一行
   System.out.println(rowtitle.getLastCellNum());//得到表的总列数
   for(int y=1;y<rowtitle.getLastCellNum();y++) {
       strs[y-1]=getValue(rowtitle.getCell(y));//把标题放进数组
   }


   String subjectName=fileName.substring(0,fileName.lastIndexOf("."));
   for (int r = 1; r <= totalRows; r++) {
   Row row = sheet.getRow(r);
   if (row == null){
   continue;
   }
   String schoolName = getValue(row.getCell(0));//得到下标为0的第一列的所有值
//   System.out.println("rowCell="+row.getLastCellNum());
   for(int i=1;i<row.getLastCellNum();i++) {
       SchoolTask info = new SchoolTask();
       info.setSubjectName(subjectName);
       info.setSchoolName(schoolName);
       info.setQuestionName(strs[i-1]);
       info.setTask(Integer.parseInt(getValue(row.getCell(i))));
       userList.add(info);
   }

   }

   for (SchoolTask userResord : userList) {
   String school = userResord.getSchoolName();
   String subject = userResord.getSubjectName();
   schoolTaskDao.deleteBySS(school,subject);
   }
   for (SchoolTask userResord : userList) {
   schoolTaskDao.save(userResord);
   }
   return notNull;
   }


       private String getValue(Cell cell) {
           String value = "";
           switch (cell.getCellType()) {
       case Cell.CELL_TYPE_NUMERIC: // 数字
       //如果为时间格式的内容
       if (HSSFDateUtil.isCellDateFormatted(cell)) {
       //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
       SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
       value=sdf.format(HSSFDateUtil.getJavaDate(cell.
       getNumericCellValue())).toString();
       break;
       } else {
       value = new DecimalFormat("0").format(cell.getNumericCellValue());
       }
       break;
       case Cell.CELL_TYPE_STRING: // 字符串
       value = cell.getStringCellValue();
       break;
       case Cell.CELL_TYPE_BOOLEAN: // Boolean
       value = cell.getBooleanCellValue() + "";
       break;
       case Cell.CELL_TYPE_FORMULA: // 公式
       value = cell.getCellFormula() + "";
       break;
       case Cell.CELL_TYPE_BLANK: // 空值
       value = "";
       break;
       case Cell.CELL_TYPE_ERROR: // 故障
       value = "非法字符";
       break;
       default:
       value = "未知类型";
       break;
       }



       return value;