Java解析Excel文件并把数据存入数据库

<!--文件上传对应的配置文件-->  <listener>     <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>   </listener>  <context-param>     <param-name>contextConfigLocation</param-name>     <param-value>classpath:application.xml</param-value>   </context-param>

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

<form name="fileupload" enctype="multipart/form-data" action="${text}/uploadfile" method="post"><p style="font-size:16px;">请选择正确的excel文件上传</p><input id="txt" class="input" type="text" disabled="disabled" value="文件域" name="txt"> <input class="liulan" type="button" onclick="file.click()" size="30" value="上传文件" onmousemove="file.style.pixelLeft=event.x-60;file.style.pixelTop=this.offsetTop;"><input id="file1" class="files" type="file" hidefocus="" size="1" style="height:26px;" name="file" onchange="txt.value=this.value"><br/><input type="button" onclick="checkSuffix();" value="提交上传" style="height:26px;width:100px"> <p style="color:red;">支持的excel格式为：xls、xlsx、xlsb、xlsm、xlst！</p></form>

//用于验证文件扩展名的正则表达式function checkSuffix(){ var name = document.getElementById("txt").value; var strRegex = "(.xls|.xlsx|.xlsb|.xlsm|.xlst)$";  var re=new RegExp(strRegex); if (re.test(name.toLowerCase())){  alert("上传成功");  document.fileupload.submit(); } else{  alert("文件名不合法");  }}

package com.gxxy.team1.yyd.dao;public interface IFileUploadDao {   public void save(Object o); }

package com.gxxy.team1.yyd.dao.impl;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Repository;import com.gxxy.team1.yyd.dao.IFileUploadDao;@Repositorypublic class FileUploadDaoImpl implements IFileUploadDao {  @Autowired  private SessionFactory sessionFactory;  private Session getSession() {    Session session = sessionFactory.getCurrentSession();    return session;  }  @Override  public void save(Object o) {        getSession().save(o);  }}

package com.gxxy.team1.yyd.service;import java.util.List;public interface IFileUploadService {  public List<String[]> readExcel(String path);  public void save(Object o);}

package com.gxxy.team1.yyd.service.impl;import java.io.File;import java.io.FileInputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import com.gxxy.team1.yyd.dao.IFileUploadDao;import com.gxxy.team1.yyd.service.IFileUploadService;@Servicepublic class FileUploadServiceImpl implements IFileUploadService {  @Autowired  private IFileUploadDao fileDao;  @Override  public List<String[]> readExcel(String path) {       SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");       List<String[]> list = null;      try {         //同时支持Excel 2003、2007         File excelFile = new File(path); //创建文件对象         FileInputStream is = new FileInputStream(excelFile); //文件流         Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的         int sheetCount = workbook.getNumberOfSheets(); //Sheet的数量       //存储数据容器         list = new ArrayList<String[]>();        //遍历每个Sheet         for (int s = 0; s < sheetCount; s++) {           Sheet sheet = workbook.getSheetAt(s);           int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数                //遍历每一行           for (int r = 0; r < rowCount; r++) {             Row row = sheet.getRow(r);             int cellCount = row.getPhysicalNumberOfCells(); //获取总列数            //用来存储每行数据的容器             String[] model = new String[cellCount-1];            //遍历每一列             for (int c = 0; c < cellCount; c++) {               Cell cell = row.getCell(c);               int cellType = cell.getCellType();                            if(c == 0) continue;//第一列ID为标志列，不解析                            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()); //日期型                     //model[c-1] = cellValue;                  }                   else {                                         cellValue = String.valueOf(cell.getNumericCellValue()); //数字                     //model[c-1] = cellValue;                                    }                   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);            System.out.println();           }         }         is.close();           }       catch (Exception e) {         e.printStackTrace();       }            return list;      }  @Override  public void save(Object o) {    fileDao.save(o);  }   }

//文件上传方法  @RequestMapping("/uploadfile")  public String upload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request, ModelMap model,Model mod) throws Exception {    String path = request.getSession().getServletContext().getRealPath("upload");    System.out.println("文件路径："+path);    String originalFilename = file.getOriginalFilename();    String type = file.getContentType();    //originalFilename = UUID.randomUUID().toString()+originalFilename;    System.out.println("目标文件名称："+originalFilename+",目标文件类型："+type);    File targetFile = new File(path,originalFilename );    if (!targetFile.getParentFile().exists()) {      targetFile.getParentFile().mkdirs();    }else if (!targetFile.exists()) {      targetFile.mkdirs();    }    // 获得上传文件的文件扩展名    String subname = originalFilename.substring(originalFilename.lastIndexOf(".")+1);    System.out.println("文件的扩展名："+subname);        try {      file.transferTo(targetFile);      } catch (Exception e) {      e.printStackTrace();    }    FileUploadServiceImpl fileUp = new FileUploadServiceImpl();    String rootpath = path + File.separator + originalFilename;    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);      SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");      String sampleNo = fields[0];        Double valueOf = Double.valueOf(fields[1]);      int sampleType = valueOf.intValue(); //double转int            String createTime = fields[2];      Date createTime1 = format.parse(createTime);        String name = fields[3];      String pId = fields[4];      String hospitalName = fields[5];      String cellPhone = fields[6];      Sample sample = new Sample(sampleNo, sampleType, createTime1, name, pId);      Patient patient = new Patient(hospitalName, cellPhone);      fileService.save(sample);      fileService.save(patient);        }      //model.addAttribute("fileUrl", request.getContextPath()+"/upload/"+originalFilename);        String username = (String) request.getSession().getAttribute("username");    List<List<Menu>> power = powerService.power(username);    mod.addAttribute("list", power);    return "redirect:/ yyd";  }