Apachepoi实现form表单上传Excel

<!-- 第一步：pom.xml配置相关jar包Excel上传下载 -->
        <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.5</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

//第二步：工具类ImportExcelUtil.java


        package com.boomhope.excelUtil;

        import java.io.IOException;
        import java.io.InputStream;
        import java.text.DecimalFormat;
        import java.text.SimpleDateFormat;
        import java.util.ArrayList;
        import java.util.List;

        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.xssf.usermodel.XSSFWorkbook;


        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  
                int s=work.getNumberOfSheets();  
                for (int i = 0; i < work.getNumberOfSheets(); i++) {  
                    sheet = work.getSheetAt(i);  
                    if(sheet==null){continue;}  
                    int s2=sheet.getLastRowNum();  
                    //遍历当前sheet中的所有行  
                    for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); 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);  
                            li.add(this.getCellValue(cell));  
                        }  
                        list.add(li);  
                    }  
                }  
                work.close();  
                return list;  
            }  
               /** 
             *action中对应方法解析Excel，持久化到数据库
             * 描述：根据文件后缀，自适应上传文件的版本  
             * @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.00");  //格式化数字  

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

<!--第三步：Jsp页面设计一个表单用于提交excel到后台action-->
<form class="form-inline clearfix " method="POST" enctype="multipart/form-data" id="form1" action="<%=basePath%>/uploadExcel/upload">  
              <div class="row">  
                <div class="form-group col-xs-12">  
                  <label class="fl">上传文件: </label>  
                  <input type="file" class="form-control" id="upfile" name="upfile">  
                <input type="submit" value="导入"/>  
                </div>  
           </div>  
    </form>

<!--    第四步：applicationContext配置，否则action接收参数时有类型转换异常抛出  -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  
<property name="maxUploadSize" value="104857600"/>  
<property name="maxInMemorySize" value="4096"/>  
</bean>

/** 
 * 描述：通过传统方式form表单提交方式导入excel文件 
 * @param request 
 * @throws Exception 
 */  
@RequestMapping("uploadExcel/upload")
public @ResponseBody Object upload(HttpServletRequest request,HttpServletResponse response)  throws Exception {  
    MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
    System.out.println("通过传统方式form表单提交方式导入excel文件！");  

    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();  
    //因为要导入的excel比较特殊，不是常规的一行类别，其他行都是数据，所以我需要根据具体模板来读取excel  
    for (int i = 0; i < listob.size(); i+=11) {  
//集体的解析每行的数据，持久化到数据库；
    }  
    return "result";  
}