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层即可

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