Java解析Excel文件並把數據存入數據庫

前段時間做一個小項目，為了同時存儲多條數據，其中有一個功能是解析Excel並把其中的數據存入對應數據庫中。花了兩天時間，不過一天多是因為用了"upload"關鍵字作為URL從而導致總報同一個錯，最後在同學的幫助下順利解決，下面我把自己用"POI"解析的方法總結出來供大家參考（我用的是SpingMVC和hibernate框架）。

1.web.xml中的配置文件

web.xml中的配置文件就按照這種方式寫，只需要把"application.xml"換成你的配置文件名即可

<!--文件上傳對應的配置文件-->
  <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>

2.application.xml的配置文件（固定寫發）

在這個配置文件中你還可以規定上傳文件的格式以及大小等多種屬性限制

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

3.文件上傳的前端HTML

注意：

1.enctype="multipart/form-data" 必須寫，封裝表單

2.method="post"，提交方式必須為"post"提交

3.action="${text}/uploadfile"， "uploadfile"切記不要寫成"upload"，否則你找到世界末日也不會找到哪裡有問題（本人因為這個折騰了一天多時間）。

<form name="fileupload" enctype="multipart/form-data" action="${text}/uploadfile" method="post">
<p >請選擇正確的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"  name="file" onchange="txt.value=this.value">
<br/><input type="button" onclick="checkSuffix();" value="提交上傳" >
 <p >支持的excel格式為：xls、xlsx、xlsb、xlsm、xlst！</p>
</form>

4.驗證上傳文件的格式

//用於驗證文件擴展名的正則表達式
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("文件名不合法"); 
 }
}

5.dao層的接口和實現類

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;
@Repository
public 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);
  }

}

6.service層的接口和實現類

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;
@Service
public 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);
  } 
  }

7.controller層實現

//文件上傳方法
  @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";
  }

以上這7個部分就是我實現解析excel文件並存入數據庫的全部代碼。希望對大家的學習有所幫助，也希望大家多多支持。