java中简单的几分钟让你上手的导入、导出、下载模板

效果图如下：

代码如下：

下载模板

    //下载模板的Controller
    @ResponseBody
    @RequestMapping("/downloadTableTemps.do")
    public void downloadTableTemps(
            HttpServletRequest request,
            HttpServletResponse response)throws Exception{
        response.setContentType("text/html;charset=utf-8");   
        request.setCharacterEncoding("utf-8");
        File file = new File(SystemConfig.getProperty("UPLOAD_PATH")+"/gd/modles.xlsx");
        response.setContentType("application/x-msdownload;");   
        response.setHeader("Content-disposition", "attachment; filename=" + 
                new String("工单上传模板.xlsx".getBytes("utf-8"),"ISO8859-1"));  
        response.setHeader("Content-Length", String.valueOf(file.length()));   
        FileUtil.downloadFile(SystemConfig.getProperty("UPLOAD_PATH")+"/gd/modles.xlsx", response.getOutputStream()); 

    }

引用到一个帮助类

@Component
public class SystemConfig {

    private static Properties props ;
    private static Properties jdbc_props ;

    public SystemConfig(){

        try {
            Resource resource = new ClassPathResource("/properties/resource.properties");//
            props = PropertiesLoaderUtils.loadProperties(resource);
            Resource jdbc_resource = new ClassPathResource("/jdbc.properties");//
            jdbc_props = PropertiesLoaderUtils.loadProperties(jdbc_resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    public static String getProperty(String key){

        return props == null ? null :  props.getProperty(key);

    }

    public static String getJdbcProperty(String key){

        return jdbc_props == null ? null :  jdbc_props.getProperty(key);

    }

    public static String getProperty(String key,String defaultValue){

         return props == null ? null : props.getProperty(key, defaultValue);

    }


    public static Properties getProperties(){
        return props;
    }
}

批量导入

1、这里面包括验证了模板字段不能为空，区县跟环节要匹配上才能成功，其次这里面若导入的数据在派单时间内接单人未办结此工单时，系统又再次派单，则每多派一次，“催单”字段+1，派单失败的工单应在派单结束时系统给出提示并“本次共导入100条工单，其中**条导入失败，点击下载”。重复派单按照订单编码+环节+区县+角色， 唯一性按照订单编码+环节判断。
派单失败的情况：
1、 同一批导入工单有出现重复时；2、导入的工单，后台无此环节；
3、导入工单时，所属县分不存在后台

//批量导入Controller
    @ResponseBody
    @RequestMapping("/importdatas.do")
    public Map<String,Object> importdatas(
            @RequestParam(value="file",required=true) MultipartFile file,
            HttpServletRequest request,
            HttpServletResponse response)throws Exception{  
        Map<String,Object> map = new HashMap<String, Object>();
        String msg="";

        InputStream is=null; 
        int state=0;
        List<GdOrder> errorSeatList=new ArrayList<>();//创建一个存放导入失败的数据的集合

        try {
            if (file!=null){        
            if(this.checkFile(file)) {
                 is = file.getInputStream();
                 XSSFWorkbook wb = new XSSFWorkbook(is);
                 XSSFSheet sheet = wb.getSheetAt(0);//0表示第一个工作薄
                 int rowNum = sheet.getLastRowNum();// 文件一共多少行
                 // 正文内容应该从第二行开始,第一行为表头的标题
                 XSSFRow row=null;

                 List<GdUrge> gdUrgeList=new ArrayList<>();//创建一个存放催单数据的集合
                 List<GdOrder> GdtList=new ArrayList<>();//创建一个存放工单数据的集合          
                 List<GdOrder> removeGdtList=new ArrayList<>();//创建一个重复
                 List<GdOrder> gdFlagList=new ArrayList<>();//创建一个存放未处理用来判断催单的工单数据的集合
                  gdFlagList=gdOrderService.queryFlag(); 
                  List<GdOrder> overFlagList=new ArrayList<>();//创建一个存放未处理用来判断催单的工单数据的集合 
                  overFlagList=gdOrderService.queryGdFlag();
                 for (int i=1;i<=rowNum;i++){
                     boolean flag=true;
                     boolean flag1=false;
                     boolean flag2=false;
                     String msg1="";
                     String msg2="";
                     String msg3="";
                     String msg4="";
                   //先定义要获得属性
                     String myid ="";//区县
                     String area_id ="";//区县
                     String order_bm ="";//编码
                     String step_id ="";//环节
                     String mobile ="";//号码
                     //String pmin="";//时限
                     String step_role="";//时限              
                     String step_ids="";
                     String area_ids=""; 
                     String overFlagid="";
                     row=sheet.getRow(i);//开始获取第一行数据
                     if (row == null){//如果行数据为空格或者null就跳过
                         continue;
                     }

                     XSSFCell cell0 = row.getCell(0);
                     if(cell0!=null){
                         cell0.setCellType(Cell.CELL_TYPE_STRING);
                         area_id=cell0.toString().trim();
                     }


                     XSSFCell cell1 = row.getCell(1);
                     if(cell1!=null){
                         cell1.setCellType(Cell.CELL_TYPE_STRING);
                         order_bm=cell1.toString().trim();
                     }


                     XSSFCell cell2 = row.getCell(2);
                     if(cell2!=null){
                         cell2.setCellType(Cell.CELL_TYPE_STRING);
                         step_id=cell2.toString().trim();
                     }



                     XSSFCell cell3 = row.getCell(3);
                     if(cell3!=null){
                         cell3.setCellType(Cell.CELL_TYPE_STRING);
                         mobile=cell3.toString().trim();
                     }


                     if(area_id==null || area_id.equals("")) {
                         msg4="字段不能为空！";
                         flag=false; 
                     }

                     if(order_bm==null || order_bm.equals("")) {
                         msg4="字段不能为空！";
                         flag=false; 
                     }

                     if(step_id==null || step_id.equals("")) {
                         msg4="字段不能为空！";
                         flag=false; 
                     }

                     if(mobile==null || mobile.equals("")) {
                         msg4="字段不能为空！";
                         flag=false; 
                     }

                     if(!StringUtil.isEmpty(area_id)) {
                         Department departments=departmentService.querypname(area_id);//得到机构表
                         if(departments==null) {
                             msg1="没有找到对应的县区！";
                             flag=false; 
                         }else {
                             area_ids=departments.getId();
                         }  
                     }

                     if(!StringUtil.isEmpty(step_id)) {
                     GdConfig gdConfig=
                             gdOrderExtService.queryConfigByStepName(step_id);//得到环节表
                      if(gdConfig==null) {
                         msg2="没有找到对应的环节！";
                          flag=false;
                      }else {               
                         step_ids=gdConfig.getId();
                         step_role=gdConfig.getRole_id();
                         //pmin=gdConfig.getAstrict_time();
                      } 
                     }
                     for (GdOrder gdOrder : GdtList) {//根据环节+订单编码判断同一张单是否有重复数据
                         if (order_bm.equals(gdOrder.getOrder_bm())&&step_ids.equals(gdOrder.getStep_id())){
                             msg3="数据异常,重复数据！";
                             flag=false;
                             removeGdtList.add(gdOrder);                        
                             gdOrder.setArea_id(area_id);
                             gdOrder.setStep_id(step_id);
                             gdOrder.setMsg("数据异常,重复数据！");
                             errorSeatList.add(gdOrder);                                                       
                         }
                    }      

                     if(flag) {//数据能匹配上的话
                         //判断是否催单
                         for (GdOrder gdOrder : gdFlagList) {
                             if (order_bm.equals(gdOrder.getOrder_bm())&&step_ids.equals(gdOrder.getStep_id())){
                                 flag1=true; 
                                 myid=gdOrder.getId();
                             }
                      } 

                         //判断是否重复派单
                         for (GdOrder gdOrder : overFlagList) {
                             if (area_ids.equals(gdOrder.getArea_id())&&order_bm.equals(gdOrder.getOrder_bm())
                                     &&step_ids.equals(gdOrder.getStep_id())&&step_role.equals(gdOrder.getStep_role())
                                     ){
                                 overFlagid=gdOrder.getId();
                                 GdOrder gdOrders=null;
                                   if(flag) {//如果是重复派单又是催单的话
                                     gdOrders=gdOrderService.queryOneWork(overFlagid);//得到催单的那个相同对象
                                     gdOrders.setIs_repeat(gdOrders.getIs_repeat()+1);;//次数+1
                                     gdOrderService.uporderFlag(gdOrders);//修改这一条数据 
                                   }else {
                                     gdOrder=new GdOrder();
                                     gdOrder.setId(UUIDUtil.getUUID());
                                     gdOrder.setOrder_time(DateUtil.getCurrentTime());
                                     gdOrder.setArea_id(area_ids);
                                     gdOrder.setOrder_bm(order_bm);
                                     gdOrder.setStep_id(step_ids);
                                     gdOrder.setMobile(mobile);
                                     gdOrder.setStep_role(step_role);
                                     //gdOrder.setPmin(Integer.parseInt(pmin));
                                     gdOrderService.importTasks(gdOrder);
                                     gdOrders=gdOrderService.queryOneWork(overFlagid);//得到催单的那个相同对象
                                     gdOrders.setIs_repeat(gdOrders.getIs_repeat()+1);;//次数+1
                                     gdOrderService.uporderFlag(gdOrders);//修改这一条数据 
                                   }
                             }
                    } 
                     }


                     GdOrder gdOrder=null;
                     if (!flag1) {//如果不是催单就new工单实例并加入list集合
                         gdOrder=new GdOrder();
                         gdOrder.setId(UUIDUtil.getUUID());
                         gdOrder.setOrder_time(DateUtil.getCurrentTime());
                         gdOrder.setArea_id(area_ids);
                         gdOrder.setOrder_bm(order_bm);
                         gdOrder.setStep_id(step_ids);
                         gdOrder.setMobile(mobile);
                         gdOrder.setStep_role(step_role);
                        // gdOrder.setPmin(Integer.parseInt(pmin));                        
                         if (!flag) {//如果错误就加入待到导出集合                 
                             gdOrder.setStep_id(step_id);
                             gdOrder.setArea_id(area_id);
                             gdOrder.setMsg(msg1+msg2+msg3+msg4);
                            errorSeatList.add(gdOrder);
                         } else {//数据无错误，加入待存数据库集合
                             GdtList.add(gdOrder);
                         }  

                     }else {//如果是重复催单就用订单编码和未处理的判断条件差出此工单
                         gdOrder=gdOrderService.queryOneWork(myid);//得到催单的那个相同对象
                         gdOrder.setUrge_count(gdOrder.getUrge_count()+1);//次数+1
                         gdOrderService.uporderFlag(gdOrder);//修改这一条数据
                         GdUrge gdUrge=new GdUrge();//new一张催单记录表然后加入数据
                         gdUrge.setId(UUIDUtil.getUUID());
                         gdUrge.setGd_id(myid);
                         gdUrge.setUarea_id(area_ids);
                         gdUrge.setUstep_id(step_ids);
                         gdUrge.setUrge_time(DateUtil.getCurrentTime());
                         gdUrgeList.add(gdUrge);//加入催单表待存数据库集合 
                     }


                 } 
                 //移除不要的数据
                 GdtList.removeAll(removeGdtList);
                 //添加工单表
                 for (GdOrder gdOrders : GdtList) {
                         state=gdOrderService.importTasks(gdOrders);
                         msg="保存成功！";
                    } 
                 //添加催单记录
                 for (GdUrge gdUrge : gdUrgeList) {
                     gdOrderService.savaUrge(gdUrge);                    
                }
                 //错误的信息集合
                 List<Map<String,String>> errorMaplist=new ArrayList<>();    
                 for (GdOrder gdOrder : errorSeatList) {
                    Map<String,String> errorMap=new LinkedHashMap<>();
                    errorMap.put("area_id", gdOrder.getArea_id());
                    errorMap.put("order_bm", gdOrder.getOrder_bm());
                    errorMap.put("step_id", gdOrder.getStep_id());
                    errorMap.put("mobile", gdOrder.getMobile());
                    errorMap.put("msg", gdOrder.getMsg());
                    errorMaplist.add(errorMap);
                } 

                 map.put("result",state);//返回的结果
                 map.put("UrgeCount",gdUrgeList.size());//重复催单的条数
                 map.put("sCount",GdtList.size());//成功的条数
                 map.put("eCounts",errorSeatList.size());//错误的条数
                 map.put("errorSeatList",errorMaplist);//错误集合的消息

            }else{
                    msg = "上传的文件格式出错!";
                }           
            }else{
                msg = "上传的文件格式出错!";
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }finally {      
            if(is!=null){
                is.close();//关闭文件流
            }
        }       
          map.put("code",msg);          
        return map;
    }

    /**
     * 文件格式验证
     */
    public boolean checkFile(MultipartFile file) {
        boolean flag = false;
        try {
            String fileName = file.getOriginalFilename();
            String picType = fileName.substring(fileName.lastIndexOf("."), fileName.length());
            if (picType.equals(".xlsx")||picType.equals(".xls")) {
                flag=true;
            }
        } catch (Exception e) {
            flag = false;
            e.printStackTrace();
        }
        return flag;
    }

批量导出

@Service层

    @Override
    public Map<String, Object> Statisticsdetail(HttpServletRequest request) throws Exception {
        Map<String, Object> data= new HashMap<String, Object>();
        Map<String, Object> param = new HashMap<>();
        param.put("area_id", request.getParameter("area_id"));
        param.put("type", request.getParameter("type"));
        param.put("startSend_date", request.getParameter("startSend_date"));
        param.put("endSend_date", request.getParameter("endSend_date"));
        List<GdOrder> gdOrders = 
                this.queryPageList("com.yuhui.dao.workOrder.GdOrderDao.Statisticsdetail", param, request);  
        int count = gdOrderDao.countStatisticsdetail(param);
        data.put("rows", gdOrders);
        data.put("total", count);
        List<Map<String, Object>> gdDetail= gdOrderDao.Statisticsdetail(param);
        data.put("gdDetail", gdDetail);
        return data;

    }

1、第一种方法（利用前端技术实现）
Controller层

    //导出
        @ResponseBody
        @RequestMapping("/export.do")
        public Map<String,Object> export(HttpServletRequest request, HttpServletResponse response)throws Exception{ 
            Map<String, Object> data =gdOrderService.Statisticsdetail(request); 
            List<Map<String,Object>>  list=departmentService.queryAllCity();
            List<Map<String,Object>> temp=(List<Map<String,Object>>) data.get("gdDetail");
            for(int i=0;i<temp.size();i++) {
                for(int j=0;j<list.size();j++) {
                    if(temp.get(i).get("AREA_ID").equals(list.get(j).get("DEPT_ID").toString())) {
                        temp.get(i).put("AREA_ID",list.get(j).get("DEPT_NAME").toString());
                        break;
                    }
                }
            }


            //信息集合
             List<Map<String,Object>> Maplist=new ArrayList<>();     
               for(int i=0;i<temp.size();i++) {
                Map<String,Object> Map=new LinkedHashMap<>();
                Map.put("area_id",temp.get(i).get("AREA_ID"));
                Map.put("order_bm", temp.get(i).get("ORDER_BM"));
                Map.put("step_id", temp.get(i).get("STEP_NAME"));
                Map.put("mobile", temp.get(i).get("MOBILE"));
                Map.put("order_time", temp.get(i).get("ORDER_TIME"));
                Map.put("pmin", temp.get(i).get("ASTRICT_TIME"));
                Map.put("urge_count", temp.get(i).get("URGE_COUNT"));
                Maplist.add(Map);
        }
            data.put("gdDetail", temp);
            data.put("Maplist", Maplist);
            logService.saveLog("工单", "报表统计", "报表统计详情导出",
                    HttpUtil.getRemoteHost(request), getUser(request),
                    HttpUtil.ADD);
                return data;
        }   

2、第二种方法（利用后台实现）
Controller层

//批量导出页面
        @RequestMapping("/upladList.do")
        public String upladList(HttpServletRequest request,
                HttpServletResponse response)  throws Exception {
            return "workoder/uploadWork";
        }   

        @RequestMapping("/dows.do")
        public void  dows(HttpServletRequest request, HttpServletResponse response)throws Exception{

            Map<String, Object> data =gdOrderService.Statisticsdetail(request);

        int count=0;
        //创建工作薄
        XSSFWorkbook hssfWordbook=new XSSFWorkbook();
        XSSFSheet hssfSheet=hssfWordbook.createSheet("订单管理");//建表
        XSSFRow row=hssfSheet.createRow(0);//建行
        XSSFCell cell=null;//建列

        List<Map<String,Object>>  cityName=departmentService.queryAllCity();
        List<Map<String,Object>> seat=(List<Map<String,Object>>) data.get("gdDetail");
        for(int i=0;i<seat.size();i++) {
            for(int j=0;j<cityName.size();j++) {
                if(seat.get(i).get("AREA_ID").equals(cityName.get(j).get("DEPT_ID").toString())) {
                    seat.get(i).put("AREA_ID",cityName.get(j).get("DEPT_NAME").toString());
                    break;
                }
            }
        }
        List<Object> Maplist=new ArrayList<>();
        for(int i=0;i<seat.size();i++) {
            List<Object>  list=new ArrayList<>();
            list.add(seat.get(i).get("AREA_ID"));
            list.add(seat.get(i).get("ORDER_BM"));
            list.add(seat.get(i).get("STEP_NAME"));
            list.add(seat.get(i).get("MOBILE"));
            list.add(seat.get(i).get("ORDER_TIME"));
            list.add(seat.get(i).get("ASTRICT_TIME"));
            list.add(seat.get(i).get("URGE_COUNT"));
            Maplist.add(list);
        }
        String u[]=new String[]{"县区","编码","环节","号码","派单时间","时限","催单"};
        for(int i=0;i<u.length;i++){
            cell=row.createCell(i);
            cell.setCellValue(u[i]);
            }
        if (Maplist!=null){
            //根据数据量创建行数
            for (int i=0;i<Maplist.size();i++){
                List<Object> datalist=new ArrayList<>();
                row=hssfSheet.createRow(++count);
                datalist=(List<Object>) Maplist.get(i);
                //遍历表头，创建多列
                String value=null;
                for (int j=0;j<u.length;j++){
                    cell=row.createCell(j);
                    value=datalist.get(j)==null ? "":datalist.get(j).toString();
                    cell.setCellValue(value);
                }
            }
            //自适应宽度
            for (int i = 0; i < Maplist.size(); i++) {
                hssfSheet.autoSizeColumn(i);
            }
        }

            response.reset(); //清除buffer缓存
            // 指定下载的文件名，浏览器都会使用本地编码，即GBK，浏览器收到这个文件名后，用ISO-8859-1来解码，然后用GBK来显示
            // 所以我们用GBK解码，ISO-8859-1来编码，在浏览器那边会反过来执行。
            response.setHeader("Content-Disposition", "attachment;filename=" + new String("导出表格.xlsx".getBytes("GBK"),"ISO-8859-1"));
            //如果使用该ContentType则浏览器自动下载到默认下载地址
            //response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //如果使用这两行代码则浏览器会弹出另存为的弹窗
            response.setHeader("Content-Type","application/force-download");
            response.setContentType("application/x-download;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            OutputStream output = null;
            BufferedOutputStream bufferedOutPut = null;
            output = response.getOutputStream();
            bufferedOutPut = new BufferedOutputStream(output);
            // 执行 flush 操作， 将缓存区内的信息更新到文件上
            bufferedOutPut.flush();
            // 将最新 的 Excel 文件写入到文件输出流中，更新文件信息
            hssfWordbook.write(bufferedOutPut);

            if(output!=null){
                output.close(); 
            }
            if(bufferedOutPut!=null){
                // 关闭输出流对象
                bufferedOutPut.close();
              }

    }

前端jsp页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <%@ include file="/WEB-INF/jsp/common.jspf" %>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="renderer" content="webkit">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="description" content="">
        <meta name="author" content="">
        <link rel="shortcut icon" href="${pageContext.request.contextPath}/assets/favicon.ico" />
        <script type="text/javascript" src="${ctx }/assets/js/pinyin.js"></script>
        <title>工单管理</title>
       <style type="text/css">
           .div-c{display:inline-block;border:1px;width:85px;} 
           .div-d{display:inline-block;border:1px;width:85px;} 
           .mytablestyle{
            margin-bottom: 0 ;
            border-bottom: 1px solid #dddddd;
            border-collapse: collapse ;
            border-radius: 1px;
           }
       </style>
    </head>
    <body> 
            <div class="ibox_cs">
                  <form class="form_horizontal_cs" name="form"> 

                     <div class="form-group"> 
                        <label for="" class="control_label_cs">订单编码:</label>
                          <input type="text" class="form_control_cs "  name="order_bm" />
                      </div>

                      <div class="form-group sendertm">
                        <label for="" class="control_label_cs">派单时间:</label>
                        <div class="date_picker_wrap" style="width: 200px;">
                            <input class="form-control ui_date_time" id="startSend_date"
                                name="order_time" type="text" value="" readonly
                                placeholder="开始时间"> <span class="date_remove iconfont">&#xe688;</span>
                            <span class="date_icon iconfont">&#xe8a6;</span>
                        </div>
                        <div class="date_picker_wrap" style="width: 200px;">
                            <input class="form-control ui_date_time" id="endSend_date"
                                name="endorder_time" type="text" value="" readonly
                                placeholder="结束时间"> <span class="date_remove iconfont">&#xe688;</span>
                            <span class="date_icon iconfont">&#xe8a6;</span>
                        </div>
                     </div> 
                     <div class="form-group backtm" hidden>
                        <label for="" class="control_label_cs">归档时间:</label>
                        <div class="date_picker_wrap" style="width: 200px;">
                            <input class="form-control ui_date_time" id="startSend_date"
                                name="back_time" type="text" value="" readonly
                                placeholder="开始时间"> <span class="date_remove iconfont">&#xe688;</span>
                            <span class="date_icon iconfont">&#xe8a6;</span>
                        </div>
                        <div class="date_picker_wrap" style="width: 200px;">
                            <input class="form-control ui_date_time" id="endSend_date"
                                name="endback_time" type="text" value="" readonly
                                placeholder="结束时间"> <span class="date_remove iconfont">&#xe688;</span>
                            <span class="date_icon iconfont">&#xe8a6;</span>
                        </div>
                     </div> 
                     <div class="form-group"> 
                         <button type="button" class="btn_main_cs " id="search">
                          <i class="iconfont">&#xe60a;</i> 查询</button> 
                      </div> 
                     <div class="form-group mybutton"> 
                     <privilege:PrivilegeByID privilegeID="1001008001008001001000" >
                    <button id="btn_add" type="button" class="btn_main_cs">
                        <span class="glyphicon glyphicon-plus" aria-hidden="true"></span>新增
                    </button>
                    </privilege:PrivilegeByID>
                    <privilege:PrivilegeByID privilegeID="100100800100800100200" >
                    <button id="btn_download" type="button" class="btn_main_cs">
                           <span class="iconfont">&#xe604;</span>下载模板
                    </button>
                    </privilege:PrivilegeByID>
                    <privilege:PrivilegeByID privilegeID="1001008001008001003000" > 
                     <button id="btn_upload" type="button" class="btn_main_cs">
                                <i  class="iconfont">&#xe605;</i>批量导入
                    </button>
                     </privilege:PrivilegeByID> 

                     <div class="form-group"> 
                         <button type="button" class="btn_main_cs " id="exportData">
                          <i class="iconfont"></i>批量导出方法一</button> 
                      </div> 
                   <div class="form-group"> 
                         <button type="button" class="btn_main_cs " id="exportData2">
                          <i class="iconfont"></i>批量导出方法二</button>  
                      </div> 
                     </div>                   
                    </form> 

                </div>


    <div class="ibox_cs query_result_wrap"> 

     <div class="bootstrap-table">
        <div class="fixed-table-container">
            <div class="fixed-table-header">
                <table id="table_result3" class="table table-hover table-striped">
                    <thead id="thead_"></thead>
                    <tbody id="tbody_"></tbody>
                 </table>
            </div>
        </div>
     </div>


        <div class="ibox_cs tab_wrap style2">
            <div class="tab_menu_wrap">
                <ul class="tab_menu">
                    <li class="tab_menu_ctn" status="0,1">在途单</li>
                    <li class="tab_menu_ctn" status="2">已归档</li>
                </ul>
            </div>
        </div>          
                <table id="table_result">
                </table>

               <input type="hidden" id="url" name="url" value="null" />
        </div>  



</body>

    <script type="text/javascript">
        $(function() {
            $('ul.tab_menu').on('click',function(){
                initTable();
                var status = $('li.active').eq(0).attr('status');
                if(status=='2'){
                    $('div.sendertm').hide();    
                    $('div.backtm').show();
                    $('div.mybutton').hide(); 
                }else{
                    $('div.sendertm').show();  
                    $('div.backtm').hide();
                    $('div.mybutton').show();
                }
            });
               //根据窗口调整表格高度
            $(window).resize(function() {
                $('#table_result').bootstrapTable('resetView', {
                    height: $.tableHeight()
                });
                $('#table_result2').bootstrapTable('resetView', {
                    height: $.tableHeight()
                });               
            });

            $("#search").click(function(){
                initTable();
            });

            //下载模板
            $("#btn_download").click(function(){
                downloadTempl();
            });

             $("#btn_upload").click(function(){
                var url = $("#url").val();
                 if(url!="null"){
                     layer.msg("这是文件，无法上传文件！"); 
                     return;
                 }
                     $.post('${pageContext.request.contextPath}/workOrder/upladList.do', {id:''}, function(htmlInner){
                         $.windowOperObtn('批量导入',htmlInner,'60%','auto');
                    }); 
             });

            //新增
            $("#btn_add").click(function(){
                add();
            });
            //批量导出方法一
         $("#exportData").click(function(){
                exportDatas();
            });

            //批量导出方法二
            $("#exportData2").click(function(){
                exportDatas2();
            });
            //时间控件
            $(".ui_date_time").datetimepicker({
                language:'zh-CN',
                format: 'yyyy-mm-dd hh:ii:ss',
                todayBtn: true,
                autoclose: true,
                /*maxView: "month",*/
                forceParse: true //当选择器关闭的时候，是否强制解析输入框中的值。
            });
              initTable();
            //添加环节列表
            queryStepNames(); 
            queryDeptNames();
            initTable2();
        });
         //批量导出方法一
        function exportDatas(){
            var cityid='${cityid}';
            var type='${type}';
            var startSend_date='${startSend_date}';
            var endSend_date='${endSend_date}';
            console.info(cityid);
            console.info(type);
            $.ajax({
                    url :"export.do",
                    type : "POST",
                    async: false,
                    dataType:"json",
                    data:{area_id:cityid,type:type,startSend_date:startSend_date,endSend_date:endSend_date},
                    success :function(data){
                        console.info(data.rows);
                        console.info(data.Maplist);
                        if(data.Maplist=='')
                            return;
                           var title=["县区","编码","环节","号码","派单时间","时限","催单"];//表头
                            JSONToExcelConvertor(data.Maplist, "工单详情",title);   
                    }
                })   

        }
        //批量导出方法二
        function exportDatas2(){
            var cityid='${cityid}';
            var type='${type}';
            var startSend_date='${startSend_date}';
            var endSend_date='${endSend_date}';
            window.location.href ="dows.do?area_id="+cityid+"&type="+type+"&startSend_date="+startSend_date+"&endSend_date="+endSend_date;
        }

        function queryStepNames(){
         $.ajax({
                url :"${pageContext.request.contextPath}/gdExt/queryStepNames.do",
                type : "POST",
                async: false,
                dataType:"json",
                data:{},
                success :function(data){
                    if(data.resCde=='0000'){
                        var rows = data.rows;
                        $.each(rows,function(i,item){
                            $('#step_ids').append('<option value="'+item.ID+'">'+item.STEP_NAME+'</option>');

                        });
                    }
                }
            })
       }


        function queryDeptNames(){
             $.ajax({
                    url :"showdepartment.do",
                    type : "POST",
                    async: false,
                    dataType:"json",
                    data:{},
                    success :function(data){
                        if(data.resCde=='0000'){
                            var rows = data.rows;
                            $.each(rows,function(i,item){
                                $('#area_id').append('<option value="'+item.DEPT_ID+'">'+item.DEPT_NAME+'</option>');

                            });
                        }
                    }
                });
          }   


        function initTable() {
          //先销毁表格  
         $('#table_result').bootstrapTable('destroy');     
          var url = 'getGdList.do';
          var  columns = [
              {
                  field: '',
                  title: '序号',
                  align: 'center',
                    formatter : function(value, row, index){                        
                        return index+1;
                    }
              },
              {
                   field: 'AREA_ID',
                   title: '区县',
                   align: 'center',  

               },{ 
                   field: 'ORDER_BM',
                   title: '订单编码', 
                   align: 'center', 
                   formatter : function(value, row, index){ 
                       var html;
                       if(row.GD_FLAG==2){
                          html="<a style='color: #32CD99' onclick=showDetail('"+row.ID+"')>"+value+"</a>";  
                      }else{
                          html=value; 
                      }
                      return html ;
                   }
               },{ 
                   field: 'STEP_NAME',
                   title: '环节',
                   align: 'center',  
               },{ 
                   field: 'MOBILE',
                   title: '号码',
                   align: 'center'                  
               },{ 
                   field: 'ORDER_TIME',
                   title: '派单时间',
                   align: 'center'
               },{ 
                   field: 'ASTRICT_TIME',
                   title: '时限（小时）',
                   align: 'center', 

               },
               { 
                   field: 'URGE_COUNT',
                   title: '催单',
                   align: 'center', 
                   formatter : function(value, row, index){
                       if(value>0){
                           var  html="<a style='color: #32CD99' onclick=showTime('"+row.ID+"')>"+(value>0?'+':'')+value+"</a>";  
                       }else{
                           var  html=value;  
                       }
                      return html ;
                   }
               }
               ]; 

          var status = $('li.active').eq(0).attr('status');
            if(status =='2'){
                var obj = columns[5];
                obj.field = "BACK_TIME";
                obj.title = "归档时间";
            }
            var params = {};
            var formdt = 
                $('form[name="form"]').serializeArray();
            $.each(formdt,function(i,item){
                params[item.name] = item.value;
            });
            params.start=0;
            params.gd_flag=status;
            $.loadTable4Data('table_result',url,columns,params);
         }   

        function initTable2() {
            $.ajax({
                url : "showAllOrder.do",
                cache : false,
                type : "post",
                async : false,
                data : {},              
                success : function(data) {                  
                    var rows=data.rows;
                    var titles=["区县","在途","已归档"];
                    var tr="<tr><th ><div class='th-inner'>"+titles[0]+"</div></th>";
                    for(var i=0;i<rows.length;i++){
                        tr+="<th><div class='th-inner'>"+rows[i].AREA_ID+"</div></th>";
                    }
                    $("#thead_").html(tr+"</tr>");
                    $("#tbody_").html("");
                    for(var j=1;j<titles.length;j++){
                        var tr="<tr><td>"+titles[j]+"</td>";    
                        for(var i=0;i<rows.length;i++){
                            tr+=j==1?"<td>"+rows[i].ROAD+"</td>":"<td>"+rows[i].ISOVER+"</td>";
                        }
                        $("#tbody_").append(tr+"</tr>");
                    }
                }
            });


             }   


        function add(){
            $.post('${pageContext.request.contextPath}/workOrder/workAdd.do', {id:''}, function(htmlInner){
                 $.windowOperObtn('工单新增',htmlInner,'60%','60%');
            });
        }
        function showDetail(bm){
            $.post('${pageContext.request.contextPath}/workOrder/queryOneWorks.do', {bm:bm}, function(htmlInner){
                 $.windowOperObtn('工单详情',htmlInner,'60%','70%');
            });
        }

        function showTime(bm){
            $.post('${pageContext.request.contextPath}/workOrder/urgeDetails.do', {bm:bm}, function(htmlInner){
                 $.windowOperObtn('派单时间',htmlInner,'30%','40%');
            });
        }


        function downloadTempl(){
            var url = "downloadTableTemps.do";
            window.location.href = url;
        }

    </script>

</html>

批量导入jsp页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="multipart/form-data; charset=utf-8" />
<title>导入</title>
<script type="text/javascript">
    $(function() { 

        $("#upload").on("click", function() {
            var files = document.getElementById('multiFile').files;
             var totalsize = 0;
             if(files.length<1){
                 layer.msg("请选择文件！");
                 return ;
             }
             for(var i=0;i<files.length;i++){
                 totalsize = totalsize + files[i].size;
             }
             if(totalsize>524288000){
                 layer.msg("超过500M!无法上传！");
                 return ;
             }
             var formData = new FormData($('#form')[0]);  
             $.ajax({
                url:"importdatas.do",
                cache: false,
                type:"POST",
               xhr : function() { // custom xhr  
                   myXhr = $.ajaxSettings.xhr();  
                   if (myXhr.upload) { // check if upload property exists  
                       myXhr.upload.addEventListener('progress', function(e) {  
                           //console.log(e);  

                       }, false); // for handling the progress of the upload  
                   }  
                   return myXhr;  
               }, 
                data :formData,
                contentType : false,  
               processData : false,
                success:function(data){
                    if(data.result>0 && data.UrgeCount=="0" && data.eCounts=="0"){
                         layer.closeAll();
                        layer.msg('导入成功');
                        initTable();
                        initTable2();
                    }
                    else if(data.eCounts>0){
                        layer.closeAll();
                         if(data.errorSeatList == '')
                            return;
                            var sum=data.sCount+data.eCounts+data.UrgeCount;
                            layer.confirm('本次导入数据共'+sum+'条，'+'成功'+data.sCount+'条，'+'失败'+data.eCounts+'条，'+'催单'+data.UrgeCount+'条，'+'点击确定下载', {
                                 closeBtn: 0, //不显示关闭按钮
                                  btn: ['下载','确定'] //按钮
                                }, function(){                                   
                                     var title=["县区","编码","环节","号码","失败原因"];//表头
                                    JSONToExcelConvertor(data.errorSeatList, "工单失败模板",title); 
                                    layer.closeAll();
                                    initTable();
                                    initTable2();
                                }, function(){
                                    layer.closeAll();
                                    initTable();
                                    initTable2();
                                 });
                        }
                         else if(data.UrgeCount>0 && data.eCounts=="0"){
                                layer.closeAll();
                                layer.msg('催单了'+data.UrgeCount+'条！');
                                    initTable();
                                    initTable2();
                        }else if(ata.result=="0"){
                            layer.closeAll();
                            layer.msg('导入失败');
                        }
                         else{
                            layer.closeAll();
                            layer.msg(''+data.code);
                        }

                    $('input[name="file"]').val("");
                }
            });
        });

    });


    function JSONToExcelConvertor(JSONData, FileName, ShowLabel) {
        //先转化json
        var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;

        var excel = '<table>';

        //设置表头
        var row = "<tr>";
        for (var i = 0, l = ShowLabel.length; i < l; i++) {
            row += "<td>" + ShowLabel[i] + '</td>';
        }


        //换行
        excel += row + "</tr>";

        //设置数据
        for (var i = 0; i < arrData.length; i++) {
            var row = "<tr>";

            for (var index in arrData[i]) {
                var value = arrData[i][index] === "." ? "" : arrData[i][index];
                row += '<td>' + value + '</td>';
            }

            excel += row + "</tr>";
        }

        excel += "</table>";

        var excelFile = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
        excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
        excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';
        excelFile += '; charset=UTF-8">';
        excelFile += "<head>";
        excelFile += "<!--[if gte mso 9]>";
        excelFile += "<xml>";
        excelFile += "<x:ExcelWorkbook>";
        excelFile += "<x:ExcelWorksheets>";
        excelFile += "<x:ExcelWorksheet>";
        excelFile += "<x:Name>";
        excelFile += "{worksheet}";
        excelFile += "</x:Name>";
        excelFile += "<x:WorksheetOptions>";
        excelFile += "<x:DisplayGridlines/>";
        excelFile += "</x:WorksheetOptions>";
        excelFile += "</x:ExcelWorksheet>";
        excelFile += "</x:ExcelWorksheets>";
        excelFile += "</x:ExcelWorkbook>";
        excelFile += "</xml>";
        excelFile += "<![endif]-->";
        excelFile += "</head>";
        excelFile += "<body>";
        excelFile += excel;
        excelFile += "</body>";
        excelFile += "</html>";


        var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);

        var link = document.createElement("a");
        link.href = uri;

        link.style = "visibility:hidden";
        link.download = FileName + ".xls";

        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
</script>
</head>
<body>

        <form action="" method="post" class="form_list_cs" id="form" enctype="multipart/form-data">
       <div class="form_control">
            <label>请选择导入文件:</label>
            <input class="browseButton" name="file" multiple="true"  
            accept="mime_type" id="multiFile" type="file"  /> 
       </div>
         <li class="form_btn_box">
            <button type="button" class="btn_main_cs" id="upload">提交</button>
            <button type="button" class="btn_red_cs" onclick="layer.closeAll()">取消</button>
         </li>      


   </form>

</body>
</html>