SpringMvc+POI處理excel表數(shù)據(jù)導(dǎo)入

更新時(shí)間：2018年06月27日 15:54:24 作者：THE42

這篇文章主要為大家詳細(xì)介紹了SpringMvc+POI處理excel表數(shù)據(jù)導(dǎo)入，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

一.概念介紹

ApachePOI是Apache軟件基金會(huì)的開放源碼函式庫(kù)，POI提供API給Java程序?qū)icrosoft Office格式檔案讀和寫的功能

二.功能相關(guān)代碼

1.環(huán)境說(shuō)明：JDK1.7+tomcat7+spring

2.配置文件的配置

pom文件中添加POI所需依賴

<!-- 添加POI支持 -->
  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.13</version>
  </dependency>
  <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.13</version>
  </dependency>

spring-mvc.xml配置文件上傳

 <bean id="multipartResolver"
   class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
  <!-- 默認(rèn)編碼 -->
  <property name="defaultEncoding" value="utf-8" />
  <!-- 文件大小最大值 -->
  <property name="maxUploadSize" value="10485760000" />
  <!-- 內(nèi)存中的最大值 -->
  <property name="maxInMemorySize" value="40960" />
 </bean>

3.相關(guān)工具類及代碼編寫

Excel解析工具類（ImportExcelUtil.java）

package com.jointem.hrm.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
 * Created by jenking on 2017/9/8.
 */
public class ImportExcelUtil {

 private final static String excel2003L =".xls"; //2003- 版本的excel
 private final static String excel2007U =".xlsx"; //2007+ 版本的excel

 /**
  * 描述：獲取IO流中的數(shù)據(jù)，組裝成List<List<Object>>對(duì)象
  * @param in,fileName
  * @return
  * @throws IOException
  */
 public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
  List<List<Object>> list = null;

  //創(chuàng)建Excel工作薄
  Workbook work = this.getWorkbook(in,fileName);
  if(null == work){
   throw new Exception("創(chuàng)建Excel工作薄為空！");
  }
  Sheet sheet = null;
  Row row = null;
  Cell cell = null;

  list = new ArrayList<List<Object>>();
  //遍歷Excel中所有的sheet
  for (int i = 0; i < work.getNumberOfSheets(); i++) {
   sheet = work.getSheetAt(i);
   if(sheet==null){continue;}


   //遍歷當(dāng)前sheet中的所有行
   System.out.println(sheet.getLastRowNum());
   for (int j = sheet.getFirstRowNum(); j <=sheet.getLastRowNum()-11; j++)
   {
    row = sheet.getRow(j);
//    if(row==null||row.getFirstCellNum()==j)
//    {
//     continue;
//    }
    //遍歷所有的列

    List<Object> li = new ArrayList<Object>();
    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++)
    {
     cell = row.getCell(y);
     if(this.isMergedRegion(sheet,j,y))
     {
      li.add(this.getMergedRegionValue(sheet,j,y));
     }
     else
     {
      li.add(this.getCellValue(cell));
     }

    }
    list.add(li);
   }
  }
  work.close();
  return list;
 }

 /**
  * 描述：根據(jù)文件后綴，自適應(yīng)上傳文件的版本
  * @param inStr,fileName
  * @return
  * @throws Exception
  */
 public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
  Workbook wb = null;
  String fileType = fileName.substring(fileName.lastIndexOf("."));
  if(excel2003L.equals(fileType)){
   wb = new HSSFWorkbook(inStr); //2003-
  }else if(excel2007U.equals(fileType)){
   wb = new XSSFWorkbook(inStr); //2007+
  }else{
   throw new Exception("解析的文件格式有誤！");
  }
  return wb;
 }

 /**
  * 描述：對(duì)表格中數(shù)值進(jìn)行格式化
  * @param cell
  * @return
  */
 public Object getCellValue(Cell cell){
  Object value = null;
  DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
  SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
  DecimalFormat df2 = new DecimalFormat("0"); //格式化數(shù)字

  switch (cell.getCellType()) {
   case Cell.CELL_TYPE_STRING:
    value = cell.getRichStringCellValue().getString();
    break;
   case Cell.CELL_TYPE_NUMERIC:
    if("General".equals(cell.getCellStyle().getDataFormatString())){
     value = df.format(cell.getNumericCellValue());
    }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
     value = sdf.format(cell.getDateCellValue());
    }else{
     value = df2.format(cell.getNumericCellValue());
    }
    break;
   case Cell.CELL_TYPE_BOOLEAN:
    value = cell.getBooleanCellValue();
    break;
   case Cell.CELL_TYPE_BLANK:
    value = "";
    break;
   default:
    break;
  }
  return value;
 }


 /**
  * 獲取合并單元格的內(nèi)容
  * @param sheet
  * @param row
  * @param column
  * @return
  */
 public Object getMergedRegionValue(Sheet sheet, int row, int column)
 {
  int sheetMergeCount = sheet.getNumMergedRegions();
  for (int i = 0; i < sheetMergeCount; i++)
  {
   CellRangeAddress ca = sheet.getMergedRegion(i);
   int firstColumn = ca.getFirstColumn();
   int lastColumn = ca.getLastColumn();
   int firstRow = ca.getFirstRow();
   int lastRow = ca.getLastRow();
   if (row >= firstRow && row <= lastRow)
   {
    if (column >= firstColumn && column <= lastColumn)
    {
     Row fRow = sheet.getRow(firstRow);
     Cell fCell = fRow.getCell(firstColumn);
     return this.getCellValue(fCell);
    }
   }
  }
  return null;
 }

 /**
  * 判斷是否是合并單元格
  * @param sheet
  * @param row
  * @param column
  * @return
  */
 public boolean isMergedRegion(Sheet sheet,int row ,int column) {
  int sheetMergeCount = sheet.getNumMergedRegions();
  for (int i = 0; i < sheetMergeCount; i++) {
   CellRangeAddress range = sheet.getMergedRegion(i);
   int firstColumn = range.getFirstColumn();
   int lastColumn = range.getLastColumn();
   int firstRow = range.getFirstRow();
   int lastRow = range.getLastRow();
   if(row >= firstRow && row <= lastRow){
    if(column >= firstColumn && column <= lastColumn){
     return true;
    }
   }
  }
  return false;
 }
}

請(qǐng)求控制器（處理頁(yè)面excel導(dǎo)入請(qǐng)求）

package com.poiexcel.control; 

import java.io.InputStream; 
import java.io.PrintWriter; 
import java.util.List; 

import javax.servlet.http.HttpServletRequest; 
import javax.servlet.http.HttpServletResponse; 

import org.springframework.stereotype.Controller; 
import org.springframework.web.bind.annotation.RequestMapping; 
import org.springframework.web.bind.annotation.RequestMethod; 
import org.springframework.web.bind.annotation.ResponseBody; 
import org.springframework.web.multipart.MultipartFile; 
import org.springframework.web.multipart.MultipartHttpServletRequest; 

import com.poiexcel.util.ImportExcelUtil; 
import com.poiexcel.vo.InfoVo; 

@Controller 
@RequestMapping("/uploadExcel/*") 
public class UploadExcelControl { 

 /** 
  * 描述：通過(guò)傳統(tǒng)方式form表單提交方式導(dǎo)入excel文件 
  * @param request 
  * @throws Exception 
  */ 
 @RequestMapping(value="upload.do",method={RequestMethod.GET,RequestMethod.POST}) 
 public String uploadExcel(HttpServletRequest request) throws Exception { 
  MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;  
  InputStream in =null; 
  List<List<Object>> listob = null; 
  MultipartFile file = multipartRequest.getFile("upfile"); 
  if(file.isEmpty()){ 
   throw new Exception("文件不存在！"); 
  } 
  in = file.getInputStream(); 
  listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename()); 
  in.close(); 

  //該處可調(diào)用service相應(yīng)方法進(jìn)行數(shù)據(jù)保存到數(shù)據(jù)庫(kù)中，現(xiàn)只對(duì)數(shù)據(jù)輸出 
  for (int i = 0; i < listob.size(); i++) { 
   List<Object> lo = listob.get(i); 
   InfoVo vo = new InfoVo(); 
   vo.setCode(String.valueOf(lo.get(0))); 
   vo.setName(String.valueOf(lo.get(1))); 
   vo.setDate(String.valueOf(lo.get(2))); 
   vo.setMoney(String.valueOf(lo.get(3))); 

   System.out.println("打印信息-->機(jī)構(gòu):"+vo.getCode()+" 名稱："+vo.getName()+" 時(shí)間："+vo.getDate()+" 資產(chǎn)："+vo.getMoney()); 
  } 
  return "result"; 
 }

前端代碼

前端運(yùn)用了bootstrap的文件上傳組件fileinput,需要引入fileinput.css，fileinput.js，zh.js，bootstrap.css，bootstrap.js,jquery.min.js

<body>
<h4>考勤信息錄入</h4>
<form method="POST" enctype="multipart/form-data" id="form1" action="${pageContext.request.contextPath }/attendance/uploadExcel">
 <input id="file-zh" name="upfile" type="file" >

</form>


</body>
<script>
 $('#file-zh').fileinput({
  language: 'zh',
  uploadUrl: '${pageContext.request.contextPath }/attendance/uploadExcel',
  allowedFileExtensions : ['xls', 'xlsx']
 });

</script>

Vo對(duì)象，保存Excel數(shù)據(jù)對(duì)應(yīng)的對(duì)象

package com.poiexcel.vo; 


//將Excel每一行數(shù)值轉(zhuǎn)換為對(duì)象 
public class InfoVo { 

 private String code; 
 private String name; 
 private String date; 
 private String money; 

 public String getCode() { 
  return code; 
 } 
 public void setCode(String code) { 
  this.code = code; 
 } 
 public String getName() { 
  return name; 
 } 
 public void setName(String name) { 
  this.name = name; 
 } 
 public String getDate() { 
  return date; 
 } 
 public void setDate(String date) { 
  this.date = date; 
 } 
 public String getMoney() { 
  return money; 
 } 
 public void setMoney(String money) { 
  this.money = money; 
 } 
}

三.效果展示

1.頁(yè)面展示