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