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