基于spring mvc的Excel导入保存数据库

---

参考了博主一路不停的博文SpringMvc+POI 处理Excel的导入操作

依赖包：

 compile 'commons-fileupload:commons-fileupload:1.3.1' compile group: 'org.apache.poi', name: 'poi', version: '3.13' //兼容2007+ 版本的excel （.xlsx）需要用到 compile group: 'org.apache.poi', name: 'poi-ooxml', version: '3.13'

配置：
spring-mvc.cfg.xml
重点：

<bean id="multipartResolver"       class="org.springframework.web.multipart.commons.CommonsMultipartResolver">        <property name="maxUploadSize" value="104857600"/>        <!-- <property name="maxInMemorySize" value="4096"/> -->    </bean>

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:aop="http://www.springframework.org/schema/aop"    xmlns:tx="http://www.springframework.org/schema/tx"     xmlns:context="http://www.springframework.org/schema/context"    xmlns:mvc="http://www.springframework.org/schema/mvc"    xmlns:p="http://www.springframework.org/schema/p"    xsi:schemaLocation="            http://www.springframework.org/schema/mvc             http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd            http://www.springframework.org/schema/beans            http://www.springframework.org/schema/beans/spring-beans-3.2.xsd            http://www.springframework.org/schema/aop            http://www.springframework.org/schema/aop/spring-aop-3.2.xsd            http://www.springframework.org/schema/tx            http://www.springframework.org/schema/tx/spring-tx-3.2.xsd            http://www.springframework.org/schema/context            http://www.springframework.org/schema/context/spring-context-3.2.xsd">    <context:component-scan base-package="com.zhwy,com.parkinglot"></context:component-scan>    <bean class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping"/>    <bean        class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">        <property name="messageConverters">            <list>                <ref bean="jsonHttpMessageConverter" />            </list>        </property>        <property name="webBindingInitializer">              <bean class="org.springframework.web.bind.support.ConfigurableWebBindingInitializer">                  <property name="conversionService">                      <bean class="org.springframework.format.support.FormattingConversionServiceFactoryBean"></bean>                  </property>              </bean>        </property>    </bean>     <bean id="jsonHttpMessageConverter"        class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter">        <property name="supportedMediaTypes">            <list>                <value>application/json;charset=UTF-8</value>            </list>        </property>    </bean>    <mvc:default-servlet-handler/>    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">        <property name="cache" value="false"/>        <property name="prefix" value="/"/>        <property name="suffix" value=".jsp"/>    </bean>    <bean id="multipartResolver"       class="org.springframework.web.multipart.commons.CommonsMultipartResolver">        <property name="maxUploadSize" value="104857600"/>        <!-- <property name="maxInMemorySize" value="4096"/> -->    </bean>    <mvc:interceptors>        <mvc:interceptor>            <mvc:mapping path="/**"/>            <bean class="com.platform.security.MyInterceptor"></bean>        </mvc:interceptor>    </mvc:interceptors>    <!-- 开发环境配置文件 -->      <beans profile="dev">          <context:property-placeholder location="classpath:application-dev.properties"/>      </beans>    <!-- 测试环境配置文件 -->      <beans profile="test">          <context:property-placeholder location="classpath:application-test.properties"/>      </beans>      <!-- 生产环境配置文件 -->      <beans profile="prod">          <context:property-placeholder location="classpath:application-prod.properties"/>      </beans></beans>

jsp页面

<%@ page contentType="text/html;charset=UTF-8"%><%@include file="/zhwy/common/plugins.jsp"%><style>body{    background-color:#fff;}td{    font-size:14px;    text-align: left;}</style><body class="easyui-layout">    <div style="padding: 15px;">        <form id="fm" method="post" enctype="multipart/form-data" action="/api/fee/meterRecord/saveImport.ac">            <input type="hidden" name="feePlanId" value="${feePlan.id}">            <input type="hidden" name="projectId" value="${feePlan.projectId}">            <input type="hidden" name="meterType" value="${feePlan.feeType}">            <table>                <tr>                    <td style="text-align: right">要求:</td>                      <td style="color:red;">                            【${project.fullName}-${feePlan.yearofmonth}-                            <c:choose>                                <c:when test='${feePlan.feeType == "1"}'>                                    水表                                </c:when>                                <c:when test='${feePlan.feeType == "2"}'>                                     电表                                </c:when>                                <c:otherwise>                                      燃气表                                </c:otherwise>                            </c:choose>】抄表数据                    </td>                          </tr>                <tr ><td style="padding: 10px"></td></tr>                     <tr>                    <td style="text-align: right">Excel:</td>                    <td>                        <input class="easyui-filebox" id="myFile" name="myFile"  data-options="width:300, buttonText:'选择'" />                    </td>                                   </tr>            </table>        </form>        <div style="padding: 10px"></div>        <div style="text-align: center">           <a href="#" class="easyui-linkbutton" iconCls="icon-save" onclick="save()">保存</a>            <a href="#" class="easyui-linkbutton" iconCls="icon-cancel" onclick="closewindow()">取消</a>         </div>    </div>    <%@include file="/zhwy/common/bottom.jsp"%></body><script type="text/javascript">//导入function save(){    var file = $('#myFile').filebox('getValue');    var fileType = file.substring(file.lastIndexOf('.'), file.length);    if(file == ''){        $.messager.alert('警告','请导入Excel文件!','warning');        return false;    }    if(fileType != '.xls' && fileType != '.xlsx'){        $.messager.alert('警告','请导入Excel文件!','warning');        return false;    }     $.messager.progress({            title: '提示',            msg: '正在处理，请稍候……',            text: ''       });      $('#fm').form('submit', {        success : function(result) {          $.messager.progress('close');          result = JSON.parse(result);            if (result.code == 200) {                $.messager.show({                    title : '结果',                    msg : "保存成功"                });                parent.window.search();                  closewindow();            } else {                $.messager.show({                    title : '错误',                    msg : result.desc                });            }        },        error: function(XMLHttpRequest, textStatus, errorThrown) {          $.messager.progress('close');            $.messager.show({                title : "错误",                msg : "响应失败"            });        }    });}</script>

controller

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;/**     * 保存导入数据     * @return     */    @RequestMapping(value="/saveImport")    @ResponseBody    public Object saveImport(@RequestParam("myFile")MultipartFile myFile, String feePlanId, String projectId, String meterType, HttpSession session, Model model){        ReturnValue returnValue = null;        InputStream in =null;          List<MeterRecordVo> meterRecordVoList = null;               try {                       if(myFile.isEmpty()){                  throw new Exception("文件不存在！");              }              in = myFile.getInputStream();              meterRecordVoList = getBankListByExcel(in,myFile.getOriginalFilename());              for(MeterRecordVo vo : meterRecordVoList){                vo.setFeePlanId(feePlanId);                vo.setProjectId(projectId);                vo.setMeterType(meterType);                //获取房子信息                HouseVo houseVo = projectService.findHouse(vo.getProjectId(), vo.getHouseNum());                if(houseVo != null){                    vo.setBuildingId(houseVo.getBuildingId());                    vo.setFloorId(houseVo.getFloorId());                }                //收费用户                FeeUserVo feeUserVo = feeService.findFeeUser(vo.getProjectId(), vo.getHouseNum());                if(feeUserVo != null && ValidateHelper.isNotEmptyString(feeUserVo.getUserId())){                    UserVo userVo = userService.getUserById(feeUserVo.getUserId());                    if(userVo != null && userVo.getCustomer() != null){                        vo.setProprietorName(userVo.getCustomer().getRealName());                    }                }                //获取上一期读数                Double preNum = feeService.findPreNumOfMeterRecord(vo.getMeterType(), vo.getProjectId(), vo.getHouseNum());                vo.setPreNum(preNum);                //本期用量                vo.setUseAmount(vo.getPreNum() == null? vo.getCurrNum():vo.getCurrNum() - vo.getPreNum());                vo.setFromClient(FeeConstants.MeterRecord.FROM_WEB);                feeService.saveMeterRecord(vo);            }            returnValue = ReturnValue.success();        }catch (Exception e) {            returnValue = new ReturnValue(e.getMessage(),e.getMessage());        }finally{            if (in != null) {                try {                    in.close();                } catch (IOException e) {                    returnValue = new ReturnValue(e.getMessage(),e.getMessage());                }            }        }        return returnValue;    }    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<MeterRecordVo> getBankListByExcel(InputStream in,String fileName) throws Exception{          List<MeterRecordVo> list = null;          //创建Excel工作薄          Workbook work = this.getWorkbook(in,fileName);          if(null == work){              throw new Exception("创建Excel工作薄为空！");          }          Sheet sheet = null;          Row row = null;          list = new ArrayList<MeterRecordVo>();          //遍历Excel中所有的sheet          for (int i = 0; i < work.getNumberOfSheets(); i++) {              sheet = work.getSheetAt(i);              if(sheet==null){continue;}              //遍历当前sheet中的所有行              for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) {                  row = sheet.getRow(j);                  if(row==null||row.getFirstCellNum()==j){continue;}                  MeterRecordVo meterRecordVo = new MeterRecordVo();                meterRecordVo.setHouseNum(getCellValue(row.getCell(3)).toString());                meterRecordVo.setCurrNum(Double.valueOf(getCellValue(row.getCell(4)).toString()));                list.add(meterRecordVo);            }          }          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;          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 = df2.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;      }