spring+struts2+mybatis中poi导入excel数据

1、html

<form id="questionTypesManage" method="post" enctype="multipart/form-data">
<table cellpadding="0" cellspacing="0" class="queryTable"
style="height:38px;border: 0; margin: 0; padding: 0;">
<tr>
<td class="queryTdRight" style="width:22%;">&nbsp;&nbsp;交易年度：
<input id="dealYear" name="dealYear" style="width: 140px"/>
</td>
<td class="queryTdRight" style="width:40%;">
请选择文件导入：
<input class="easyui-filebox" style="width:75%" name="uploadExcel" id="uploadExcel" data-options="onChange:function(){},prompt:'选择文件',accept:'application/vnd.ms-excel application/x-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'"/>
</td>
<td align="left" style="padding-left:5px;">
<a href="javascript:uploadExcel()" class="easyui-linkbutton" data-options="iconCls:'icon-upload'">上&nbsp;传</a>
<a href="javascript:matching()" class="easyui-linkbutton" data-options="iconCls:'icon-goto'">匹&nbsp;配</a>
<a href="javascript:saveUploadExcel()" class="easyui-linkbutton" data-options="iconCls:'icon-download'">导&nbsp;入</a>
<a href="javascript:deleteData()" class="easyui-linkbutton" data-options="iconCls:'icon-cancel'">删&nbsp;除</a>
<a href="javascript:initTable(0)" class="easyui-linkbutton" data-options="iconCls:'icon-search'">查&nbsp;询</a>
</td>
</tr>
</table>
</form>

2、js

/**
 * 上传按钮
 * 
 */
function uploadExcel(){    
    //得到上传文件的全路径  
     var fileName= $('#uploadExcel').filebox('getText'); 
     if(fileName==""){     
        $.messager.alert('提示','请选择上传文件！','info');   
        return;
    }
    $.messager.progress({title:'提示',msg:'正在校验，请稍候...'});
     $.ajax({
        url: basePath+"/demandForecast/selectTempData.action",
        data: "",
        type: "post",
        cache: false,
        dataType: "json",
        success: function (data) {
            $.messager.progress("close");
            var d=data.resultData;
            if(d.length>0){
                $.messager.confirm('提示', '数据已经存在，是否要覆盖？', function(r){
                    if (r){
                        importExcel(fileName);
                    }
                });
            }else{
                importExcel(fileName);
            }
        },
        error: function (error) {
            
        },
        complete: function () { 
            
        }
    });
}

/**
 * 导入文件上传
 */
function importExcel(fileName){
     var dealYear= $('#dealYear').combobox('getValue');  
   
      if(dealYear !=""){   
         //进行基本校验  
          //debugger;
         if(fileName==""){     
            $.messager.alert('提示','请选择上传文件！','info');   
         }else{  
            //对文件格式进行校验  
            var f_content = fileName;  
            var fileext=f_content.substring(f_content.lastIndexOf("."),f_content.length);
            fileext=fileext.toLowerCase();
            //fileName=encodeURI(fileName);
            if (fileext=='.xls' || fileext=='.xlsx'){  
                /*异步提交*/
                $.messager.progress({title:'提示',msg:'正在上传文件，请稍候...'});
                $("#questionTypesManage").ajaxSubmit({
                    url:basePath+"/demandForecast/importToTemp.action?dealYear="+encodeURI(encodeURI(dealYear)),
                    dataType:"text/html",  
                    type:'post',
                    clearForm:false, 
                    complete:function(data){
                        $.messager.progress("close");
                        data=""+data.responseText;
                        var sd=data.split("\"");
                        $.messager.alert('提示',sd[1],'info', function() {
                            initTable();
                        });
                    }
                });
            }else{  
                $.messager.alert('提示','请选择xls/xlsx格式文件！','info');   
                $('#uploadExcel').filebox('setValue','');   
            }  
         }    
  }else{    
      $.messager.alert('提示','请选择日期！','info');   
  }
}

3、struts xml

<action name="importToTemp" class="com.icss.action.demandForecast.DemandForecastAction" method="importToTemp">
            <result name="importResult" type="json">
                <param name="contentType">
                    text/html
                </param>
                <param name="root">mark</param>
            </result>
        </action>

4、action

public String importToTemp(){
        try { 
            demandForecastService.saveMonthSalePlanImportTemp(new FileInputStream(uploadExcel),dealYear);
            mark="上传成功"; 
            operationLogService.saveLog(ConstantConfig.DEMAND_FORECAST_IMPORT_CODE, ConstantConfig.DEMAND_FORECAST_IMPORT_NAME, "[电脑][上传][成功]");
        } catch (Exception e) { 
            e.printStackTrace(); 
            mark="上传失败,模板数据不规范"; 
            try {
                operationLogService.saveLog(ConstantConfig.DEMAND_FORECAST_IMPORT_CODE, ConstantConfig.DEMAND_FORECAST_IMPORT_NAME, "[电脑][上传][失败]["+e.getMessage()+"]");
            } catch (Exception e1) {
                e1.printStackTrace();
                
            }
        } 
        
        return "importResult";
    }

5、service

@Override
    public void saveMonthSalePlanImportTemp(FileInputStream is,
            String dealYear) throws Exception {
        
        //先清空临时表
        String condition="1=1";
        baseDao.delete("WP_DEMAND_FORECAST_IMPORT_TEMP", condition);
        
        Workbook wb = WorkbookFactory.create(is);  
        Sheet sheet = wb.getSheetAt(0);//获取工作簿

        DemandForecastImportTemp demandForecastImportTemp=null;
        
        for (int i = 0,rowNum = sheet.getLastRowNum(); i < rowNum; i++) {//迭代行
            demandForecastImportTemp = new DemandForecastImportTemp();
            Row row = sheet.getRow(i+1);//获取工作簿中的行（不取第一行）
            boolean isLasttextRow = false;//是否最后一行
            for (int j = 0,cellNum = row.getLastCellNum(); j < cellNum; j++) {//迭代列
                Cell cell = row.getCell(j);//取得列中的值
                if (cell == null) {
                    break;
                }
                String cellValue = dataType(cell);
                
                if (j == 0 && StringUtils.isEmpty(cellValue)) {
                    isLasttextRow = true;
                    break;
                }
                setData(demandForecastImportTemp, cellValue, j, dealYear);
            }
            if (isLasttextRow) {
                break;
            }
            //保存数据
            demandForecastImportTemp.setDealYear(dealYear);
            this.save(demandForecastImportTemp);
        }
    }
    
    private void save(DemandForecastImportTemp demandForecastImportTemp) {
        ArrayList<String> columns=new ArrayList<String>();
        ArrayList<Object> values=new ArrayList<Object>();
        columns.add("ORG_ID");
        values.add(demandForecastImportTemp.getOrgId());
        columns.add("ORG_NAME");
        values.add(demandForecastImportTemp.getOrgName());
        columns.add("IND_ID");
        values.add(demandForecastImportTemp.getIndId());
        columns.add("IND_NAME");
        values.add(demandForecastImportTemp.getIndName());
        columns.add("CIG_CODE");
        values.add(demandForecastImportTemp.getCigCode());
        columns.add("CIG_NAME");
        values.add(demandForecastImportTemp.getCigName());
        columns.add("CIG_CLASS");
        values.add(demandForecastImportTemp.getCigClass());
        columns.add("CIG_PRICE");
        values.add(demandForecastImportTemp.getCigPrice());
        columns.add("YEAR_DEMAND");
        values.add(demandForecastImportTemp.getYearDemand());
        columns.add("PRE_HALFYEAR_STORE");
        values.add(demandForecastImportTemp.getPreHalfyearStore());
        columns.add("HALFYEAR_DEMAND_FORECAST");
        values.add(demandForecastImportTemp.getHalfyearDemandForecast());
        columns.add("YEAR_END_STORE");
        values.add(demandForecastImportTemp.getYearEndStore());
        columns.add("NEXT_HALFYEAR_DEMAND");
        values.add(demandForecastImportTemp.getNextHalfyearDemand());
        columns.add("NEXT_HALFYEAR_AUDIT_DEMAND");
        values.add(demandForecastImportTemp.getNextHalfyearAuditDemand());
        columns.add("CHECK_STATUS");
        values.add(demandForecastImportTemp.getCheckStatus());
        columns.add("DEAL_YEAR");
        values.add(demandForecastImportTemp.getDealYear());
        
        baseDao.insert("WP_DEMAND_FORECAST_IMPORT_TEMP", columns, values);
    }

        //判断excel单元格内容的格式，并对其进行转换，以便插入数据库
        private final String dataType(Cell cell){
            switch (cell.getCellType()) {
                case 0:
                    return String.valueOf(cell.getNumericCellValue());
                case 1:
                    return cell.getStringCellValue();
                case 2:
                    return String.valueOf(cell.getDateCellValue());
                case 4:
                    return String.valueOf(cell.getBooleanCellValue());
                case 5:
                    return String.valueOf(cell.getErrorCellValue());
            }
            return "";
        }
        
        private final void setData(DemandForecastImportTemp demandForecastImportTemp,
                String cellValue,int j,String dealYear){
            if (j == 0) {
                demandForecastImportTemp.setOrgName(cellValue);
            } else if (j == 1) {
                demandForecastImportTemp.setCigName(cellValue);
            } else if (j == 2) {
                demandForecastImportTemp.setCigCode(cellValue);
            } else if (j == 3) {
                demandForecastImportTemp.setIndName(cellValue);
            } else if (j == 4) {
                double cigClass = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setCigClass(cigClass);
            } else if (j == 5) {
                double cigPrice = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setCigPrice(cigPrice);
            } else if (j == 6) {
                double yearDemand = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setYearDemand(yearDemand);
            } else if (j == 7) {
                double preHalfyearStore = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setPreHalfyearStore(preHalfyearStore);
            } else if (j == 8) {
                double halfyearDemandForecast = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setHalfyearDemandForecast(halfyearDemandForecast);
            } else if (j == 9) {
                double yearEndStore = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setYearEndStore(yearEndStore);
            } else if (j == 10) {
                double nextHalfyearDemand = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setNextHalfyearDemand(nextHalfyearDemand);
            }else if (j == 11) {
                double nextHalfyearAuditDemand = Double.valueOf(StringUtils.isEmpty(cellValue) ? "0" : cellValue);
                demandForecastImportTemp.setNextHalfyearAuditDemand(nextHalfyearAuditDemand);
            }
            demandForecastImportTemp.setDealYear(dealYear);
            demandForecastImportTemp.setCheckStatus(0);
        }