SpringMvc+POI处理excel表数据导入 SpringMvc+POI处理excel表数据导入

<!-- 添加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>

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

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

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

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

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