Java解析Excel文件并把數(shù)據(jù)存入數(shù)據(jù)庫

更新時(shí)間：2017年05月11日 14:47:42 作者：蘇書——小米

本篇文章主要介紹了Java解析Excel文件并把數(shù)據(jù)存入數(shù)據(jù)庫，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

前段時(shí)間做一個(gè)小項(xiàng)目，為了同時(shí)存儲多條數(shù)據(jù)，其中有一個(gè)功能是解析Excel并把其中的數(shù)據(jù)存入對應(yīng)數(shù)據(jù)庫中。花了兩天時(shí)間，不過一天多是因?yàn)橛昧?upload"關(guān)鍵字作為URL從而導(dǎo)致總報(bào)同一個(gè)錯(cuò)，最后在同學(xué)的幫助下順利解決，下面我把自己用"POI"解析的方法總結(jié)出來供大家參考（我用的是SpingMVC和hibernate框架）。

1.web.xml中的配置文件

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

<!--文件上傳對應(yīng)的配置文件-->
  <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的配置文件（固定寫發(fā)）

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

<!-- 定義文件上傳解析器 --> 
  <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"，否則你找到世界末日也不會找到哪里有問題（本人因?yàn)檫@個(gè)折騰了一天多時(shí)間）。

<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>

4.驗(yàn)證上傳文件的格式

//用于驗(yàn)證文件擴(kuò)展名的正則表達(dá)式
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層的接口和實(shí)現(xiàn)類

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層的接口和實(shí)現(xiàn)類

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 { 
        //同時(shí)支持Excel 2003、2007 
        File excelFile = new File(path); //創(chuàng)建文件對象 
        FileInputStream is = new FileInputStream(excelFile); //文件流 
        Workbook workbook = WorkbookFactory.create(is); //這種方式 Excel 2003/2007/2010 都是可以處理的 
        int sheetCount = workbook.getNumberOfSheets(); //Sheet的數(shù)量
       //存儲數(shù)據(jù)容器 
        list = new ArrayList<String[]>();
        //遍歷每個(gè)Sheet 
        for (int s = 0; s < sheetCount; s++) { 
          Sheet sheet = workbook.getSheetAt(s); 
          int rowCount = sheet.getPhysicalNumberOfRows(); //獲取總行數(shù)      
          //遍歷每一行 
          for (int r = 0; r < rowCount; r++) { 
            Row row = sheet.getRow(r); 
            int cellCount = row.getPhysicalNumberOfCells(); //獲取總列數(shù) 
           //用來存儲每行數(shù)據(jù)的容器 
            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為標(biāo)志列，不解析
              
              String cellValue = null; 
              switch(cellType) { 
                case Cell.CELL_TYPE_STRING: //文本 
                  cellValue = cell.getStringCellValue(); 
                  //model[c-1] = cellValue;
                  break; 
                case Cell.CELL_TYPE_NUMERIC: //數(shù)字、日期 
                  if(DateUtil.isCellDateFormatted(cell)) { 
                    cellValue = fmt.format(cell.getDateCellValue()); //日期型 
                    //model[c-1] = cellValue;
                  } 
                  else { 
                    
                    cellValue = String.valueOf(cell.getNumericCellValue()); //數(shù)字 
                    //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: //錯(cuò)誤 
                  cellValue = "錯(cuò)誤"; 
                  break; 
                case Cell.CELL_TYPE_FORMULA: //公式 
                  cellValue = "錯(cuò)誤"; 
                  break; 
                default: 
                  cellValue = "錯(cuò)誤"; 
                  
              } 
              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層實(shí)現(xiàn)

//文件上傳方法
  @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("目標(biāo)文件名稱："+originalFilename+",目標(biāo)文件類型："+type);
    File targetFile = new File(path,originalFilename );
    if (!targetFile.getParentFile().exists()) {
      targetFile.getParentFile().mkdirs();
    }else if (!targetFile.exists()) {
      targetFile.mkdirs();
    }
    // 獲得上傳文件的文件擴(kuò)展名
    String subname = originalFilename.substring(originalFilename.lastIndexOf(".")+1);
    System.out.println("文件的擴(kuò)展名："+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轉(zhuǎn)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個(gè)部分就是我實(shí)現(xiàn)解析excel文件并存入數(shù)據(jù)庫的全部代碼。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

您可能感興趣的文章: