Excel数据处理

1、任务需求

2、核心代码

<div class="sd_main_item sd_fms">    <div class="tbl_nav sd_fms_nav" style="padding-left:15px;">        <a href="#fam_file_upload" class="btn-fam-ipmort"><div class="btn btn-default">导入</div></a>    </div></div>                        <div class="col-md-12">                            <input type="checkbox" cref="on_line"></input> 列统一设置                            <hr/>                            <div class="pane_on_line">                            </div>                        </div>                        <div class="col-md-12" style="text-align:left;padding-left:100px;margin-top:20px;">                            <span class="btn btn-success btn_save" rel="107">保存</span>                        </div>

<div id="fam_file_upload" style="display:none;">    <div class="file-upload-title">        导入 <span class="file_upload_title" style="color:#0c6">进线和踩线</span> 的数据    </div>    <div id="fam_1_file_upload" class="file-upload">        <div class="file-upload-click dz-default dz-message">           <i class=" icon-upload"></i> 点击这里上传数据        </div>    </div>    <div class="file-upload-close">        <button class="btn btn-info btn_file_upload_close">Close</button>    </div>    <div class="file-upload-note">          <span class="label label-danger">NOTE:</span>           该功能目前仅支持 Chrome, Firefox & Internet Explorer 10 浏览器.    </div></div>

  <script>        var baseparam = {            schId: null,             yearIn:null        };        function getSchool(){            baseparam.schId = $('.sch_name').attr('schid');        }        function init_top_events(){            //初始化yearIn选择栏            function select_yearIn_event(){                var ny = moment().year();                for( var i = 6; i > 0; i--){                    $('.yearIn').append('<option value="'+(ny-i)+'">'+(ny-i)+'</option>');                }                for( var i = 0; i < 6; i++){                    if( (ny+i) == ny ){                        $('.yearIn').append('<option value="'+(ny+i)+'" selected>'+(ny+i)+'</option>');                        baseparam.yearIn = ((ny+i)+"").substring(2,4);                    }else                        $('.yearIn').append('<option value="'+(ny+i)+'">'+(ny+i)+'</option>');                }                $('.yearIn').on( 'change', function(e){                    $('.as_result input[name="yearIn"]').val( $('.yearIn option:selected').val().substring(2,4) );                    baseparam.yearIn = $('.yearIn option:selected').val().substring(2,4);                    GET.changeBCO();                });            }            select_yearIn_event();        }           var $= jQuery.noConflict();    var stusUploader = null;    function init_upload(){     getSchool();         init_top_events();             var schId = baseparam.schId;         var yearIn = baseparam.yearIn;            CUBE.uploadOption.maxFiles = 1;        CUBE.uploadOption.url = '../conf/upload/noline'+'/'+schId+'/'+yearIn;        stusUploader = new Dropzone("#fam_1_file_upload", CUBE.uploadOption);    }    function handler_upload(){            function beforeShow(){                CUBE.alarmTipClose();                //关闭nav                // $('.system_school_import').show().hide();                CUBE.lockMainWindow( $( window.parent.document.body));            }            function afterClose(){                // $('.school_set_url_error').html('');//清理提示信息                // $('.school_set_contact_error').html('');//清理提示信息                CUBE.unLockMainWindow( $( window.parent.document.body));            }            $('.sd_fms_nav').delegate(".btn-fam-ipmort", "click", function(){                CUBE.fancyDialogOption.beforeShow = beforeShow;                CUBE.fancyDialogOption.afterClose = afterClose;                //呈现上传窗口                $('.btn-fam-ipmort').fancybox( CUBE.fancyDialogOption);                stusUploader.removeAllFiles(true);            })            $('.sd_fms_nav').delegate(".btn-fam-phone-ipmort", "click", function(){                CUBE.fancyDialogOption.beforeShow = beforeShow;                CUBE.fancyDialogOption.afterClose = afterClose;                //呈现上传窗口                $('.btn-fam-phone-ipmort').fancybox( CUBE.fancyDialogOption);                ztUploader.removeAllFiles(true);            })            $(".file-upload-close").delegate(".btn_file_upload_close", "click", function(){                $.fancybox.close();            })        }        $(document).ready(function(){            initView.init();            handler_upload();            init_upload();        });    </script>

 <script id="pane_on_line" type="text/template">        <div class="on_line_tbl" id="<%=sortAlias%>" style="">            <table width="100%" key="<%=sortAlias%>" role="on_line">                <tbody>                     <td style="min-width:80px;background-color:white;height:30px;font-size:20px;">                            <%=sortAlias%>                     </td>                    <%_.each( data, function(d){%>                    <tr key="<%=d.xkAlias%>" xkid="<%=d.xkId>100?d.xkId-100:d.xkId%>" count="<%=d.count%>">                        <td style="min-width:80px;background-color:#ddd;">                            <%=d.xkName%>                            <span class="btn_ol_add"><i class="icon-plus-sign"></i></span>                        </td>                        <%var i = 0;%>                        <%_.each( d.data, function(dd){%>                            <td>                                <span class="btn_ol_delete"><i class="icon-remove-sign"></i></span>                                <input class="form-control notempty" placeholder=",,,," value="<%=dd%>" type="text" name="line_<%=i%>" style="width:180px;margin-left: 30px;" />                            </td>                            <%i++;%>                        <%})%>                    </tr>                    <%})%>                </tbody>            </table>        </div>    </script>

js

 function renderOn_line(){        if( bco_school.on_line == undefined) return false;        var keys = _.keys(bco_school.on_line);        var _datas = [];        _.each( keys, function(key){            if( key.indexOf('_lk') != -1 || key.indexOf('_wk') != -1 || key.indexOf('_qk') != -1){                var d = _.result( bco_school.on_line, key );                var _keys = _.keys(d);                var _data = [], r = {};                _.each( _keys, function(_key){                    var _d = _.result( d, _key);                    var _data_ = [];                    _.each(_d, function(_d_){                        var v = _d_.line_name+','+_d_.line_type+','+_d_.line_std+','+_d_.line_up+','+_d_.line_down;                        _data_.push(v);                    });                    var alias = _key.split('_')[1], xkName = utils.getXKName(alias), xkId = utils.getXKId(alias), rd={};                    _.extend( rd, {'data': _data_});                    _.extend( rd, {'xkName': xkName});                    _.extend( rd, {'xkId': (xkId<=10 && xkId>0)?xkId+100: xkId});                    _.extend( rd, {'xkAlias': _key});                    _.extend( rd, {'count': _data_.length});                    _data.push(rd);                });                _data = _.sortBy(_data, 'xkId');                var sortId = utils.getSortId( key.split('_')[1] );                _.extend( r, {'data': _data});                if(key == 'ol_wk'){                _.extend( r, {'sortAlias': '文科'});                }else if(key == 'ol_lk'){                _.extend( r, {'sortAlias': '理科'});                }else if(key == 'ol_qk'){                _.extend( r, {'sortAlias': '全科'});                }                               _.extend( r, {'sortId': sortId} );                if( sortId != -1 )                    _datas.push(r);            }        });        if( _datas.length == 0){            $('.pane_on_line').append('未查询到全科配置信息');            return false;        }        CUBE.renderList(            _datas,            $('.pane_on_line'),            $('#pane_on_line'),            $('.pane_on_line'),            true,            function(){                btn_event_delete();                btn_event_add();            }        );  

后台逻辑代码

@RequestMapping(value = "/upload/noline/{schId}/{yearIn}", method = RequestMethod.POST)public @ResponseBody JSONObject uploadonLine(@PathVariable("schId") Long schId,@PathVariable("yearIn")Integer yearIn,HttpServletRequest  request, HttpServletResponse response )throws ServletException, IOException {JSONObject jo = null;response.setContentType("application/json");response.setCharacterEncoding("utf-8");JSONObject param = new JSONObject();String sch = schId.toString();String year = yearIn.toString();param.put("schId", schId);param.put("yearIn", yearIn);try {ReadExcel readExcel = new ReadExcelImpl();ExcelCheckCallback<JSONObject> callback = new OnLineCheckCallback();List<JSONObject> list  = readExcel.readExcel(request, callback, param );if(list != null && !list.isEmpty())jo = list.get(0);JSONArray arr = jo.getJSONArray("Sheet1");JSONObject obj1 = new JSONObject();JSONObject obj2 = new JSONObject();JSONObject obj3 = new JSONObject();JSONObject obj4 = new JSONObject();int j = 0;int k = 1;int x = 1;for(int i = 1; i < arr.size(); i++){ArrayList  arr1 = (ArrayList)arr.get(i);String v1 = arr1.get(0).toString();String v2 = arr1.get(1).toString();String v3 = arr1.get(2).toString();String v4 = arr1.get(3).toString();String v5 = arr1.get(4).toString();String v6 = arr1.get(5).toString();String v7 = arr1.get(6).toString();if(v1 != "" && v2 != "" && v3 != "" && i == 1){obj1 = new JSONObject();obj2 = new JSONObject();obj3 = new JSONObject();obj4 = new JSONObject();obj1.put("line_"+j, v3+","+v4+","+v5+","+v6+","+v7);obj2.put(v2, obj1);obj3.put(v1, obj2);j++;obj4.put("on_line", obj3);}else if(v1 == "" && v2 == "" ){obj1.put("line_"+j, v3+","+v4+","+v5+","+v6+","+v7);j++;v2 = ((ArrayList)arr.get(i-k)).get(1).toString();k++;obj2.put(v2, obj1);v1 = ((ArrayList)arr.get(i-x)).get(0).toString();x++;obj3.put(v1, obj2);obj4.put("on_line", obj3);}else if(v1 == "" && v2 != ""){j = 0;obj1 = new JSONObject();obj1.put("line_"+j, v3+","+v4+","+v5+","+v6+","+v7);j++;k = 1;obj2.put(v2, obj1);v1 = ((ArrayList)arr.get(i-x)).get(0).toString();x++;obj3.put(v1, obj2);obj4.put("on_line", obj3);}else if(v1 != "" && v2 != "" && v3 != "" && i != 1){obj1 = new JSONObject();obj2 = new JSONObject();j=0;k=1;x=1;obj1.put("line_"+j, v3+","+v4+","+v5+","+v6+","+v7);j++;obj2.put(v2, obj1);obj3.put(v1, obj2);obj4.put("on_line", obj3);}}obj4.put("yearIn", year);obj4.put("schId", sch);System.out.println("fyq: "+obj4);ISchAnlyConfSettingBO ope = new SchAnlyConfSettingBOImpl();ope.setOn_LineConf( obj4);} catch (FileUploadException e) {e.printStackTrace();response.setStatus( 500);return getErrorResult( e.getMessage() );} catch (ExcelCheckException e) {e.printStackTrace();response.setStatus( 500);return getErrorResult( e.getExceptionInfo() );} catch (Exception e) {e.printStackTrace();response.setStatus( 500);return getErrorResult( "文件内容无法识别，请检查是否为符合要求的文件" );}return getSuccessResult(null);}

/** *     * @Title: setOn_LineConf    * @Description: 进线、踩线配置    * @param cjo    * @return    * @throws Exception    参数    * @return JSONObject    * @throws */public JSONObject setOn_LineConf( JSONObject  cjo ) throws Exception{System.out.println(cjo);String url = DTC_BCO_URL_BASE + PropertiesConfigUtils.getString("url.bco.107");Long schId = cjo.getLong("schId");Integer yearIn = cjo.getInteger("yearIn");url = url + schId+"/"+yearIn+"/";//PostJSONObject pjo =cjo.getJSONObject("on_line");Set<String> keys = pjo.keySet();JSONObject _rjo = new JSONObject();//脱第一层for( String key : keys ){JSONObject child = pjo.getJSONObject(key);Set<String> ckeys = child.keySet();JSONObject _cjo = new JSONObject();//脱第二层for( String ckey : ckeys ){JSONObject shild = child.getJSONObject(ckey);Set<String> skeys = shild.keySet();JSONArray sjarr = new JSONArray();//处理 {"line_0":"前10%,1,0.1,-1,-1","line_1":"前30%,1,0.3,-1,-1","line_2":"前60%,1,0.6,0.05,0.1"}这个对象for( String skey: skeys){String str = shild.getString(skey);if( str == null || str.isEmpty() ) continue;if( str.indexOf(",") == -1) continue;JSONObject sjo = new JSONObject();sjo.put( "line_name", str.split("\\,")[0]);sjo.put( "line_type", str.split("\\,")[1]==null?-1: new Integer( str.split("\\,")[1]) );sjo.put( "line_std", str.split("\\,")[2]==null?-1: new Double( str.split("\\,")[2]) );sjo.put( "line_up", str.split("\\,")[3]==null?-1:new Double( str.split("\\,")[3]) );sjo.put( "line_down", str.split("\\,")[4]==null?-1:new Double( str.split("\\,")[4]) );sjarr.add( sjo);}_cjo.put(ckey, sjarr);}_rjo.put(key, _cjo);}HttpClientUtil instance = HttpClientUtil.getInstance("UTF-8", Constants.HTTPCLIENT_TIMEOUT);String result = instance.postResponseBodyAsString(url, _rjo.toJSONString()  );System.out.println("_rjo.toJSONString()"+ _rjo.toJSONString());JSONObject rjo = new JSONObject();rjo = JSON.parseObject( result);String r = "";if(rjo.getIntValue("code") != 0 ){if(r.length() > 0 ) r =  r+", "+rjo.getString("data") ;else r =  rjo.getString("data") ;}if( r.length() == 0)return getSuccessResult(new JSONObject());elsereturn getErrorResult(r);}