SpringMvc+POI处理excel表数据导入

一.概念介绍

ApachePOI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能

二.功能相关代码

1.环境说明：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">
  <!-- 默认编码 -->
  <property name="defaultEncoding" value="utf-8" />
  <!-- 文件大小最大值 -->
  <property name="maxUploadSize" value="10485760000" />
  <!-- 内存中的最大值 -->
  <property name="maxInMemorySize" value="40960" />
 </bean>

3.相关工具类及代码编写

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流中的数据，组装成List<List<Object>>对象
  * @param in,fileName
  * @return
  * @throws IOException
  */
 public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
  List<List<Object>> list = null;

  //创建Excel工作薄
  Workbook work = this.getWorkbook(in,fileName);
  if(null == work){
   throw new Exception("创建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;}

   //遍历当前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;
 }

 /**
  * 描述：根据文件后缀，自适应上传文件的版本
  * @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;
 }

 /**
  * 描述：对表格中数值进行格式化
  * @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"); //格式化数字

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

 /**
  * 获取合并单元格的内容
  * @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;
 }
}

请求控制器（处理页面excel导入请求）

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 { 

 /**
  * 描述：通过传统方式form表单提交方式导入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(); 

  //该处可调用service相应方法进行数据保存到数据库中，现只对数据输出
  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("打印信息-->机构:"+vo.getCode()+" 名称："+vo.getName()+" 时间："+vo.getDate()+" 资产："+vo.getMoney());
  }
  return "result";
 } 

前端代码

前端运用了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对象，保存Excel数据对应的对象

package com.poiexcel.vo; 

//将Excel每一行数值转换为对象
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.页面展示

2.后台信息打印

四.总结

该例子只在控制台对导入的数据进行了输出，并没有进行持久化。如果要持久化，只需在注释的位置调用service层即可

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。