前端利用 js-xlsx 实现 Excel 文件导入导出功能

前端代码

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <title>自定义派单 - Powered By </title>
  <link href="${base}/template/admin/js/eayyui132/themes/icon.css" rel="stylesheet" type="text/css" />
  <link href="${base}/template/admin/js/eayyui132/themes/default/easyui.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="${base}/template/admin/js/eayyui132/jquery-1.8.0.min.js"></script>
  <script type="text/javascript" src="${base}/template/admin/js/eayyui132/jquery.easyui.min.js"></script>
  <script type="text/javascript" src="${base}/template/admin/js/eayyui132/easyui-lang-zh_CN.js"></script>
  <script type="text/javascript" src="${base}/template/admin/js/ajaxfileupload.js"></script>
        <script type="text/javascript" src="${base}/template/admin/js/report.js"></script>
  


  <script type="text/javascript" src="${base}/template/admin/js/datagridSelectCascade.js"></script>

  <script type="text/javascript" src="${base}/template/common/datePicker/WdatePicker.js"></script>
  <script type="text/javascript" src="${base}/template/admin/js/jsXlsx/xlsx.full.min.js"></script>

</head>
<body style="width:100%;height:100%">
<div style="width:100%;position:absolute;bottom:30px;top:0;left:0">
<table id="clientGrid" class="easyui-datagrid" style="padding:5px;" toolbar="#toolbar" fit="true" rownumbers="true" fitColumns="true" singleSelect="true" >
  <thead>
  <tr>
    <th sortable="true" data-options=" halign:'center',field:'city_ID',formatter:isNull">本地网标识</th>
    <th sortable="true" data-options=" halign:'center',field:'hd_ID',formatter:isNull">活动ID</th>
    <th sortable="true" data-options=" halign:'center',field:'hd_NAME',formatter:isNull">活动名称</th>
    <th sortable="true" data-options=" halign:'center',field:'hd_ST_DATE',formatter:isNull">活动开始时间</th>
    <th sortable="true" data-options=" halign:'center',field:'hd_ED_DATE',formatter:isNull">活动结束时间</th>
    <th sortable="true" data-options=" halign:'center',field:'connectno',formatter:isNull">接入号</th>
    <th sortable="true" data-options=" halign:'center',field:'client_MARK',formatter:isNull">客户标识</th>
    <th sortable="true" data-options=" halign:'center',field:'regional_PIC',formatter:isNull">片区负责人工号</th>
    <th sortable="true" data-options=" halign:'center',field:'main_COMMEND_NAME',formatter:isNull">主推荐名称</th>
    <th sortable="true" data-options=" halign:'center',field:'main_COMMEND_INTRO',formatter:isNull">主推推荐简介</th>
    <th sortable="true" data-options=" halign:'center',field:'sale_INFO',formatter:isNull">营销建议</th>
    <th sortable="true" data-options=" halign:'center',field:'main_COMMEND_TYPE',formatter:isNull">主推推荐类型</th>
    <th sortable="true" data-options=" halign:'center',field:'sc_COMMEND_NAME',formatter:isNull">次推推荐名称</th>
    <th sortable="true" data-options=" halign:'center',field:'sc_COMMEND_INTRO',formatter:isNull">次推推荐简介</th>
    <th sortable="true" data-options=" halign:'center',field:'sc_COMMEND_TYPE',formatter:isNull">次推推荐类型</th>
    <th sortable="true" data-options=" halign:'center',field:'ds_COMMEND_NAME',formatter:isNull">顺销推荐名称</th>
    <th sortable="true" data-options=" halign:'center',field:'ds_COMMEND_INTRO',formatter:isNull">顺销推荐简介</th>
    <th sortable="true" data-options=" halign:'center',field:'ds_COMMEND_TYPE',formatter:isNull">顺销推荐类型</th>

  </tr>
  </thead>
</table>
        </div>

    <div id="pp" class="easyui-pagination" style="border:1px solid #ccc;position:fixed;bottom:0;right:0;left:0;background-color:#fff" data-options="  
            total: 0,  
            pageList: [50,100,200],  
                onSelectPage: function(pageNumber, pageSize){ // 页面切换动作  
                    getDataByPageRows(pageNumber,pageSize);     
            }">
        </div>

<div id="toolbar">
  <div>
    <label class="textStyle ">工单创建时间段:</label>
    <input id="staTime" style="width:110px " class="easyui-text " name="staTime"  onclick="WdatePicker({dateFmt: 'yyyy-MM-dd'}) " " />
    <label class="textStyle">--</label>
    <input id="overTime" style="width:110px" class="easyui-text" name="overTime"  onclick="WdatePicker({dateFmt:'yyyy-MM-dd'})" />

    <a href="#" onclick="searchEnd();" class="easyui-linkbutton" plain="true" icon="icon-search">查询</a>
    <a href="#" id="export" onclick="clientExport()" class="easyui-linkbutton" plain="true" icon="icon-print">导出</a>
    <a href="#" onclick="exportExcel(jsono)" class="easyui-linkbutton" iconCls="icon-selectAll" plain="true">模板下载</a>
    <a href="" download="自定义派单.xlsx" id="hf"></a>
    <a href="#" onclick="importDataModel()" class="easyui-linkbutton" iconCls="icon-add" plain="true">批量导入</a>

  </div>
</div>


<!-- 导入数据模块 -->
<div id="importDataTable" class="easyui-window" title="导入信息" style="width:980px;height:510px; padding:2px;" data-options="modal:true, collapsible:false, minimizable:false,maximizable:true,closed:true,onMaximize:function(){MaximizeImport()},onRestore:function(){MaximizeImport()}">

  <div style="width:100%;height:100%;" data-options="fit:true">
    <div id="tb3" style="padding:4px 10px;">
      <input type="hidden" id="inputID" />
      <a href="#" onclick="chooseFile()" class="easyui-linkbutton" type="file" icon="icon-add">选择文件导入</a>
        <form action="custom_dispatch_order!checkExport.action" method='post' onsubmit="return exportCheckResult();" style="position:relative;margin-top:-28px;margin-left:120px;width:70px">
                <input type="text" value="[]" id="exportData" name="exportData" style="visibility:hidden;position:fixed" />
                <input type="submit" value="导出检验结果" class="easyui-linkbutton" plain="true" icon="icon-print" style="width:95px;height:23px;" id ="exportCheckBtn"/>
            </form>
    </div>
    <table id="showImportinfo" class="easyui-datagrid" data-options="toolbar:'#tb3',fit:true,fitColumns:false,scrollbarSize:10,pagination:true" rownumbers="true">
      <thead>
      <tr>
        <th data-options="align:'center',field:'HD_NAME',width:140">活动名称</th>
        <th data-options="align:'center',field:'HD_ST_DATE',width:140 ,formatter:crtTimeFtt" >活动开始时间</th>
        <th data-options="align:'center',field:'HD_ED_DATE',width:140 ,formatter:crtTimeFtt">活动结束时间</th>
        <th data-options="align:'center',field:'CONNECTNO',width:140">接入号</th>
        <th data-options="align:'center',field:'MAIN_COMMEND_NAME',width:110">主推荐名称</th>
        <th data-options="align:'center',field:'MAIN_COMMEND_INTRO',width:110">主推推荐简介</th>
        <th data-options="align:'center',field:'SALE_INFO',width:110">营销建议</th>
        <th data-options="align:'center',field:'MAIN_COMMEND_TYPE',width:110">主推推荐类型</th>
        <th data-options="align:'center',field:'SC_COMMEND_NAME',width:100">次推推荐名称</th>
        <th data-options="align:'center',field:'SC_COMMEND_INTRO',width:100">次推推荐简介</th>
        <th data-options="align:'center',field:'SC_COMMEND_TYPE',width:120">次推推荐类型</th>
        <th data-options="align:'center',field:'DS_COMMEND_NAME',width:100">顺销推荐名称</th>
        <th data-options="align:'center',field:'DS_COMMEND_INTRO',width:120">顺销推荐简介</th>
        <th data-options="align:'center',field:'DS_COMMEND_TYPE',width:120">顺销推荐类型</th>
        <th data-options="align:'center',field:'checkResult',width:110,formatter:chenkInfo">校验结果</th>
        <th data-options="align:'center',field:'something'">备注</th>
      </tr>
      </thead>
    </table>
  </div>
</div>
</div>
</div>

<#--选择文件上传窗口-->
  <div id="uploadFileWin" class="easyui-window" title="选择文件" style="width:650px;height:260px" data-options="modal:true,minimizable:false,maximizable:false,closed: true">
    <div style="padding:10px;">
      <table class="inputTable">
        <tr>
          <th>
            上传文件:
          </th>
          <td>
            <input type="file" name="uploadFile" id="uploadFile" onchange="importf(this)"/>
          </td>
        </tr>
        <tr>
          <th>
            文件格式说明:
          </th>
          <td style="color:#999;">
            <label class="requireField" color="red"> <span  color="red"> 1、文件格式必须为Excel2007模板文件,请勿修改模板格式  </span></br>
              <span  color="red"> 2、如果文件为Excel2003格式(扩展名  .xls),请打开文件,然后另存为Excel2007(扩展名 .xlsx) </span></br>
              <span  color="red">3、文档前缀名称不能修改,只能修改日期 </span>
            </label>
          </td>
        </tr>
      </table>
    </div>
    <div class="buttonArea" align="center" style="margin-top:20px;">
      <input type="button" id="submitForm" onclick="submitList()" class="formButton" value="确  定" hidefocus />
    </div>
  </div>

</body>

<script type="text/javascript">
  var excelData;//excel数据
  var checkInfo;//校验结果
  var wb;//读取完成的数据
  var tableCheck;//界面检验结果
  var successfulData=[];//检验成功的数据
  var errorData=[];//检验失败的数据
  var rABS = false; //是否将文件读取为二进制字符串
  var defauleSelectCount=50;
  function MaximizeImport() {
    var pWid = $('#importDataTable').innerWidth(),
      phgt = $('#importDataTable').innerHeight();

    $('#showImportinfo, #showCheckDatainfo').datagrid({
      width: '100%',
      height: '100%'
    });
  }

  //打开上传文件的窗口
  function chooseFile() {
    $("#uploadFileWin").window("open");
  }


  function importf(obj) {//导入
    if (!obj.files) {
      return;
    }
    var f = obj.files[0];
    var reader = new FileReader();
    reader.onload = function (e) {
      var data = e.target.result;
      if (rABS) {
        wb = XLSX.read(btoa(fixdata(data)), {//手动转化
          type: 'base64'
        });
      } else {
        wb = XLSX.read(data, {
          type: 'binary'
        });
      }
      excelData = JSON.stringify(XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]));
      excelData = excelData.replace(/活动名称/g, "HD_NAME").replace(/活动开始时间/g, "HD_ST_DATE").replace(/活动结束时间/g, "HD_ED_DATE").replace(/接入号/g, "CONNECTNO").replace(/主推荐名称/g, "MAIN_COMMEND_NAME").replace(/主推推荐简介/g, "MAIN_COMMEND_INTRO").replace(/营销建议/g, "SALE_INFO").replace(/主推推荐类型/g, "MAIN_COMMEND_TYPE").replace(/次推推荐名称/g, "SC_COMMEND_NAME").replace(/次推推荐简介/g, "SC_COMMEND_INTRO").replace(/次推推荐类型/g, "SC_COMMEND_TYPE").replace(/顺销推荐名称/g, "DS_COMMEND_NAME").replace(/顺销推荐简介/g, "DS_COMMEND_INTRO").replace(/顺销推荐类型/g, "DS_COMMEND_TYPE")
      excelData = eval("(" + excelData + ")");
      console.log(excelData);

      successfulData=[];//检验成功的数据
      errorData=[];//检验失败的数据
    };
    if (rABS) {
      reader.readAsArrayBuffer(f);
    } else {
      reader.readAsBinaryString(f);
    }
  }

  //提交上传文件
  function submitList() {
    successfulData=[];
    errorData=[];//清空
    var cityId = window.location.pathname.substring(window.location.pathname.indexOf('Radar-') + 6, window.location.pathname.indexOf('Radar-') + 9) +"000000";
    var CONNECTNO = "";//接入号
    var HD_NAME = "";//活动名称
    for(var i=0;i<excelData.length;i++){
      CONNECTNO+=excelData[i].CONNECTNO +",";
      HD_NAME+=excelData[i].HD_NAME +",";
    }
    $.post('custom_dispatch_order!checkImportInfo.action', {
      city: cityId,
      connectNo:CONNECTNO.substring(0,CONNECTNO.length-1),
      hdName:HD_NAME.substring(0,HD_NAME.length-1)
    },function(data){
      checkInfo = eval("(" + data + ")");
      console.log(checkInfo);
      var result="";
      console.log(excelData);
      for(var i=0;i < excelData.length;i++){
       result="";
       excelData[i].checkResult='校验成功'
        excelData[i].HD_ST_DATE = crtTimeFtt(excelData[i].HD_ST_DATE);//时间格式化
        excelData[i].HD_ED_DATE = crtTimeFtt(excelData[i].HD_ED_DATE);
        if(excelData[i].HD_NAME == null || excelData[i].HD_NAME == ' '||excelData[i].HD_NAME == ''||excelData[i].HD_NAME == 'null' ||excelData[i].HD_NAME == undefined ){
          excelData[i].checkResult='校验失败';
          result += '活动名称为空、';
        }
        //活动开始时间不为空且是日期格式跳出if
        if(isNaN(Date.parse(excelData[i].HD_ST_DATE))){
          excelData[i].checkResult='校验失败';
          result += '活动开始时间不能为空并且格式需要正确、';
        }
        //活动结束时间不为空且是日期格式跳出if
        if(isNaN(Date.parse(excelData[i].HD_ED_DATE))){
          excelData[i].checkResult='校验失败';
          result += '活动结束时间不能为空并且格式需要正确、';
        }
        //接入号
        if(excelData[i].CONNECTNO ==undefined || excelData[i].CONNECTNO == null || excelData[i].CONNECTNO == ' ' ||excelData[i].CONNECTNO == '' ||excelData[i].CONNECTNO == 'null' ) {
          excelData[i].checkResult='校验失败';
          result += '接入号不能为空、';
        }
        if(checkInfo.data[i].connectNo=='false'){
          excelData[i].checkResult='校验失败';
          result += '请检查接入号输入是否正确、';
        }
        //主推荐名称
        if(excelData[i].MAIN_COMMEND_NAME==undefined ||excelData[i].MAIN_COMMEND_NAME == null || excelData[i].MAIN_COMMEND_NAME == ' '|| excelData[i].MAIN_COMMEND_NAME == ''|| excelData[i].MAIN_COMMEND_NAME == 'null' ) {
          excelData[i].checkResult='校验失败';
          result += '主推荐名称不能为空、';
        }
        //主推推荐简介
        if(excelData[i].MAIN_COMMEND_INTRO == undefined || excelData[i].MAIN_COMMEND_INTRO == null || excelData[i].MAIN_COMMEND_INTRO == ' '|| excelData[i].MAIN_COMMEND_INTRO == ''|| excelData[i].MAIN_COMMEND_INTRO == 'null' ) {
          excelData[i].checkResult='校验失败';
          result += '主推推荐简介不能为空、';
        }
        //判断该接入号是否属于该地市
        if(checkInfo.data[i].isCurrentCity=='false'){
          excelData[i].checkResult='校验失败';
          result += '改接入号不属于当前的地市、';
        }
        excelData[i].checkResult == '校验失败'?errorData.push(excelData[i]):successfulData.push(excelData[i]);
     

        excelData[i].something = result.substring(0,result.length-1);
      }

      $('#showImportinfo').datagrid('reload');
      $('#showImportinfo').datagrid('loadData', excelData);
      importData();//插入检验成功的数据
      $("#uploadFileWin").window("close");
    });

  }

  var jsono = [{ //导出模板数据
    "活动名称": "必填",
    "活动开始时间": "必填(格式为:2018-10-15 00:00:00)",
    "活动结束时间": "必填(格式为:2018-10-15 00:00:00)",
    "接入号": "必填",
    "主推荐名称": "必填",
    "主推推荐简介": "必填",
    "营销建议": "",
    "主推推荐类型": "",
    "次推推荐名称": "",
    "次推推荐简介": "",
    "次推推荐类型": "",
    "顺销推荐名称": "",
    "顺销推荐简介": "",
    "顺销推荐类型": ""
  }];
  var tmpDown; //导出的二进制对象
  function exportExcel(json, type) {
    var tmpdata = json[0];
    json.unshift({});
    var keyMap = []; //获取keys
    //keyMap =Object.keys(json[0]);
    for (var k in tmpdata) {
      keyMap.push(k);
      json[0][k] = k;
    }
    var tmpdata = [];//用来保存转换好的json
    json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
      v: v[k],
      position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
    }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
      v: v.v
    });
    var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
    var tmpWB = {
      SheetNames: ['mySheet'], //保存的表标题
      Sheets: {
        'mySheet': Object.assign({},
          tmpdata, //内容
          {
            '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
          })
      }
    };
    tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
      {bookType: (type == undefined ? 'xlsx':type),bookSST: false, type: 'binary'}//这里的数据是用来定义导出的格式类型
    ))], {
      type: ""
    }); //创建二进制对象写入转换好的字节流
    var href = URL.createObjectURL(tmpDown); //创建对象超链接
    document.getElementById("hf").href = href; //绑定a标签
    document.getElementById("hf").click(); //模拟点击实现下载
    setTimeout(function() { //延时释放
      URL.revokeObjectURL(tmpDown); //用URL.revokeObjectURL()来释放这个object URL
    }, 100);
  }

  function s2ab(s) { //字符串转字符流
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
  }
  // 将指定的自然数转换为26进制表示。映射关系:[0-25] -> [A-Z]。
  function getCharCol(n) {
    let temCol = '',
      s = '',
      m = 0
    while (n > 0) {
      m = n % 26 + 1
      s = String.fromCharCode(m + 64) + s
      n = (n - m) / 26
    }
    return s
  }



  //导出检验结果
  function exportCheckResult() {
     $.messager.alert("提示", "导出校验结果成功"); 
  }



  //判断时间格式
  String.prototype.isDate = function() {

    var r = this.match(/^(\d{1,4})(-|\/)(\d{1,2})\2(\d{1,2})$/);
    var r1 = this.match(/^(\d{1,4})(-|\/)(\d{1,1})\2(\d{1,2})$/);
    var r2 = this.match(/^(\d{1,4})(-|\/)(\d{1,2})\2(\d{1,1})$/);
    var r3 = this.match(/^(\d{1,4})(-|\/)(\d{1,1})\2(\d{1,1})$/);

    if(r == null || r1 != null || r2 != null || r3 != null) {
      return false;
    } else {
      var d = new Date(r[1], r[3] - 1, r[4]);
      return(d.getFullYear() == r[1] && (d.getMonth() + 1) == r[3] && d.getDate() == r[4]);
    }
  };

  //到期日期转换
  function formatRes(value, row, index) {
    var expireDate = row.expireDate;//到期日期
    var result;

    if(expireDate == '2099-01-01 00:00:00.0'){
      result = '';
    }
    return result;
  }



  //验证时分秒
  String.prototype.isTime = function()
  {
    var r = this.match(/^(\d{1,4})(-|\/)(\d{1,2})\2(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/);
    if(r==null)return false; var d = new Date(r[1], r[3]-1,r[4],r[5],r[6],r[7]);
    return (d.getFullYear()==r[1]&&(d.getMonth()+1)==r[3]&&d.getDate()==r[4]&&d.getHours()==r[5]&&d.getMinutes()==r[6]&&d.getSeconds()==r[7]);
  };

  //导出客户信息(条件导出)
  function clientExport() {

        location.href = "custom_dispatch_order!searchOrderInfo.action?strTime="+$("#staTime").attr("value")+"&endTime="+$("#overTime").attr("value")+"&staPosition=0&endPosition=0&type=1";



  }


  function importData(){
    console.log(successfulData);
                    Load();
    $.post('custom_dispatch_order!importInfo.action', {
      importData: JSON.stringify(successfulData)
    },function(data){
        dispalyLoad();
       $("#exportData").val(JSON.stringify(excelData))
       $.messager.alert("提示", "导入成功的有"+successfulData.length+"条数据,导入失败的有"+errorData.length+"条数据");
  
    });
  }










  //查询按钮
  function searchEnd() {
                Load();
  $.post('custom_dispatch_order!searchOrderInfo.action', {
      strTime: $("#staTime").attr("value"),
      endTime: $("#overTime").attr("value"),
      staPosition: 0,
      endPosition: defauleSelectCount,
      type:0
    },function(data){
    dispalyLoad();
     jsonObj = eval("(" + data + ")");
     console.log(jsonObj);
     $('#pp').pagination({
        total: jsonObj.count,
        pageNumber: 1,
        });
    $('#clientGrid').datagrid('reload');
    $('#clientGrid').datagrid('loadData', jsonObj.data);    
    });


  }
  
  function getDataByPageRows(pageNum, rowsLimit) {
             defauleSelectCount=rowsLimit;
                var infoMarketInfo = null; //表格的数据
            
                $("#clientGrid").datagrid("loadData", {
                    total: 0,
                    rows: []
                });
                Load();
                $.post("custom_dispatch_order!searchOrderInfo.action", {
                        staTime: $("#staTime").attr("value"),
                        endTime: $("#overTime").attr("value"),
                        staPosition: (pageNum-1) * rowsLimit,
                        endPosition: pageNum * rowsLimit,
                        type: 0
                    },
                    function(data) {
                        dispalyLoad();
                            jsonObj = eval("(" + data + ")");
                            console.log(jsonObj);
                            $('#clientGrid').datagrid('reload');
                            $('#clientGrid').datagrid('loadData', jsonObj.data);                

                    });

            }






  //导入模块弹窗
  function importDataModel() {
    //清空表格导入和校验的数据
    $('#showImportinfo').datagrid('loadData',{total:0,rows:[]});
    $('#showCheckDatainfo').datagrid('loadData',{total:0,rows:[]});
    //导入、校验、信息生成必须按顺序操作否则不能成功
    $('input[id="inputID"]').val(2);
    //导入前禁用校验、信息生成、导出校验结果以及修改样式
    $('#bufe').prop("disabled",true);
    $("#bufe").css({"color":"white","background":"#D3D3D3"});
    $("#importDataTable").window("open");
  }

  //日期时间格式化
  function formatDate(value) {
    if(isNaN(value) && !isNaN(Date.parse(value))) {
      var date = new Date(value);
      return date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + date.getDate();
    } else {
      return value;
    }
  }
  //创建时间格式化显示
function crtTimeFtt(value){
   var crtTime = new Date(value);
   var time = value!=undefined?dateFtt("yyyy-MM-dd hh:mm:ss",crtTime):value;
   return time;     
}
  
  function dateFtt(fmt,date)   
{   
  var o = {   
    "M+" : date.getMonth()+1,                 //月份   
    "d+" : date.getDate(),                    //日   
    "h+" : date.getHours(),                   //小时   
    "m+" : date.getMinutes(),                 //分   
    "s+" : date.getSeconds(),                 //秒   
    "q+" : Math.floor((date.getMonth()+3)/3), //季度   
    "S"  : date.getMilliseconds()             //毫秒   
  };   
  if(/(y+)/.test(fmt))   
    fmt=fmt.replace(RegExp.$1, (date.getFullYear()+"").substr(4 - RegExp.$1.length));   
  for(var k in o)   
    if(new RegExp("("+ k +")").test(fmt))   
  fmt = fmt.replace(RegExp.$1, (RegExp.$1.length==1) ? (o[k]) : (("00"+ o[k]).substr((""+ o[k]).length)));   
  return fmt;   
}

function chenkInfo(value){
if(value!='校验失败'){
  return "校验成功";
 }else{
 return value;
 }
 

}

function isNull(value){
if(value=='null' || value ==null || value==undefined){
return "";
}else{
return value;
}

}


</script>

</html>

后端代码

package net.radar.action.admin;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import net.radar.bean.ResponsePage;
import net.radar.entity.DispatchOrder;
import net.radar.entity.PermissionCascade;
import net.radar.service.AdminService;
import net.radar.service.CustomDispatchOrderService;
import net.radar.service.PermissionCascadeService;
import net.radar.util.DateOpUtil;
import net.radar.util.JsonUtil;
import net.radar.util.SpringUtil;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;
import net.sf.json.JSONArray;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.struts2.convention.annotation.ParentPackage;



@ParentPackage("admin")
public class CustomDispatchOrderAction extends BaseAdminAction {
    private static final long serialVersionUID = 1L;
    private String company;
    private String serviceCenter;
    private String area;
    private String city;
    private String keyid;
    private String grid;
    private String hdName;
    private String connectNo;
    private String strTime;
    private String endTime;
    private String type;
    private String staPosition;
    private String endPosition;
    private String exportData;

    private String importData;

    @Resource(name = "CustomDispatchOrderServiceImpl")
    private CustomDispatchOrderService customDispatchOrderService;
    
    @Resource(name = "adminServiceImpl")
    private AdminService adminService;


    public String list() {
        return LIST;
    }

    // 查询派单信息
    public String searchOrderInfo() {
        Map<String, Object> map = customDispatchOrderService.searchOrderInfo(strTime, endTime,type,staPosition,endPosition);
        if(Integer.parseInt(type)==1){
            String result = JsonUtil.toJson(map);
            ReportExportAction tt = new ReportExportAction();
            JSONObject jsStr = JSONObject.fromObject(result);
            String expertInfo = jsStr.get("data").toString();
            tt.customDispatchOrderExport(expertInfo);
        }
         return ajax(JsonUtil.toJson(map));
    }

    public String checkImportInfo() {
        Map<String, Object> map = new HashMap<String, Object>();
        String[] actNameArray = hdName.split(",");
        String[] connectNoArray = connectNo.split(",");

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        for (int i = 0; i < actNameArray.length; i++) {
            Map<String, Object> obj = new HashMap<String, Object>();
            // 判断活动名称是否为空则插入数据
             customDispatchOrderService.isCheckActName(actNameArray[i]);
            
            // 判断接入号是否存在
            if (connectNoArray[i] == null || "null".equalsIgnoreCase(connectNoArray[i])
                    || "undefined".equalsIgnoreCase(connectNoArray[i]) || "".equalsIgnoreCase(connectNoArray[i])) {
                obj.put("connectNo", "flase");
                obj.put("isCurrentCity", "flase");// 接入号为空,则改派单也不属于当前地市
            } else {
                String result = customDispatchOrderService.isCheckConnectNo(connectNoArray[i]);
                if (result == "true") {
                    // 判断该派单所属地市
                    String orderCity = customDispatchOrderService.getCity(connectNoArray[i]);
                    if (orderCity.equals(city)) {
                        obj.put("isCurrentCity", "true");
                    } else {
                        obj.put("isCurrentCity", "flase");
                    }
                }
                obj.put("isCurrentCity", "flase");// 接入号查询不存在flase
                obj.put("connectNo", result);
            }

            list.add(obj);
        }

        map.put("data", list);

        return ajax(JsonUtil.toJson(map));
    }
    
    // 插入派单信息
        public void importInfo() {
            JSONArray jsonArray = JSONArray.fromObject(importData);
            @SuppressWarnings({ "unchecked", "rawtypes" })
            List<Map<String, Object>> mapListJson = (List) jsonArray;
            for (int i = 0; i < mapListJson.size(); i++) {
                Map<String, Object> obj = mapListJson.get(i);
                DispatchOrder  dispatchOrder = (DispatchOrder) JSONObject.toBean(
                        (JSONObject) obj, new DispatchOrder().getClass());
                //根据接入号查询网格ID,再查询网格ID的所有父节点
                List<PermissionCascade> allDirectParentNode = customDispatchOrderService.getAllDirectParentNodeNew(dispatchOrder.getCONNECTNO());
                //根据活动名称查新活动ID
                String HD_ID = customDispatchOrderService.getHdId(dispatchOrder.getHD_NAME());
                dispatchOrder.setHD_ID(HD_ID);
                for (int j = 0; j < allDirectParentNode.size(); j++) {
                    PermissionCascade p = allDirectParentNode.get(j);
                    switch(p.getGrade()){
                    case 5: //网格
                        dispatchOrder.setGRID_ID(p.getId());
                        break;
                    case 4://片区
                        dispatchOrder.setAREA_ID(p.getId());
                        String name  = p.getName();
                       if(name.equals("未划分片区")){
                            dispatchOrder.setREGIONAL_PIC("");//片区负责人
                       }else{
                            dispatchOrder.setREGIONAL_PIC(p.getId());//片区负责人
                       }
                        break;
                    case 3://营服
                        dispatchOrder.setBRANCH_ID(p.getId());
                        break;
                    case 2://县份
                        dispatchOrder.setSUBST_ID(p.getId());
                        break;
                    case 1://地市
                        dispatchOrder.setCITY_ID(p.getId());
                        break;
                     default:
                         dispatchOrder.setPROV_ID("1");
                        break;
                    }
                }

                dispatchOrder.setKEYID(Long.toString(DateOpUtil.randomTimestampId()));//keyId
                dispatchOrder.setORDER_CODE("201"+Long.toString(DateOpUtil.randomTimestampId()));//keyId
                dispatchOrder.setORDER_TYPE("1");//工单类型为1
                dispatchOrder.setORDER_STATE("1");//工单状态为1
                dispatchOrder.setORDER_PROCESSING_STATE("1");//工单处理状态为1
                //根据接入号查询客户标识
                String clientMark = customDispatchOrderService.getClientMark(dispatchOrder.getCONNECTNO());
                dispatchOrder.setCLIENT_MARK(clientMark);//工单处理状态为1
                AdminService adminServiceImpl = (AdminService) SpringUtil
                        .getBean("adminServiceImpl");
                String loginUserId =  adminServiceImpl.getLoginAdmin().getId();
                dispatchOrder.setUSER_ID(loginUserId);
                //插入数据
                customDispatchOrderService.insertOrder(dispatchOrder);

            }
    

        
        }
        //导出检验结果
        public String checkExport() {
            ReportExportAction tt = new ReportExportAction();
            tt.checkResultExport(exportData);
            return ajax("导出成功");
            
        }


    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    public String getServiceCenter() {
        return serviceCenter;
    }

    public void setServiceCenter(String serviceCenter) {
        this.serviceCenter = serviceCenter;
    }

    public String getArea() {
        return area;
    }

    public void setArea(String area) {
        this.area = area;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getKeyid() {
        return keyid;
    }

    public void setKeyid(String keyid) {
        this.keyid = keyid;
    }

    public String getGrid() {
        return grid;
    }

    public void setGrid(String grid) {
        this.grid = grid;
    }

    
    public String getHdName() {
        return hdName;
    }

    public void setHdName(String hdName) {
        this.hdName = hdName;
    }

    public String getConnectNo() {
        return connectNo;
    }

    public void setConnectNo(String connectNo) {
        this.connectNo = connectNo;
    }

    public String getStrTime() {
        return strTime;
    }

    public void setStrTime(String strTime) {
        this.strTime = strTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }

    public String getImportData() {
        return importData;
    }

    public void setImportData(String importData) {
        this.importData = importData;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getStaPosition() {
        return staPosition;
    }

    public void setStaPosition(String staPosition) {
        this.staPosition = staPosition;
    }

    public String getEndPosition() {
        return endPosition;
    }

    public void setEndPosition(String endPosition) {
        this.endPosition = endPosition;
    }

    public String getExportData() {
        return exportData;
    }

    public void setExportData(String exportData) {
        this.exportData = exportData;
    }

    
}

导出功能(2)

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title></title>
</head>

<body>
    <button onclick="downloadExl(jsono)">导出</button>
    <script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script>
    <!--调用FileSaver saveAs函数可以实现文件下载-->
    <!--<script src="http://sheetjs.com/demos/Blob.js"></script>
    <script src="http://sheetjs.com/demos/FileSaver.js"></script>-->
    <script>
        //如果使用 FileSaver.js 就不要同时使用以下函数
        function saveAs(obj, fileName) {//当然可以自定义简单的下载文件实现方式 
            var tmpa = document.createElement("a");
            tmpa.download = fileName || "下载";
            tmpa.href = URL.createObjectURL(obj); //绑定a标签
            tmpa.click(); //模拟点击实现下载
            setTimeout(function () { //延时释放
                URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
            }, 100);
        }
        var jsono = [{ //测试数据
            "保质期临期预警(天)": "adventLifecycle",
            "商品标题": "title",
            "建议零售价": "defaultPrice",
            "高(cm)": "height",
            "商品描述": "Description",
            "保质期禁售(天)": "lockupLifecycle",
            "商品名称": "skuName",
            "商品简介": "brief",
            "宽(cm)": "width",
            "阿达": "asdz",
            "货号": "goodsNo",
            "商品条码": "skuNo",
            "商品品牌": "brand",
            "净容积(cm^3)": "netVolume",
            "是否保质期管理": "isShelfLifeMgmt",
            "是否串号管理": "isSNMgmt",
            "商品颜色": "color",
            "尺码": "size",
            "是否批次管理": "isBatchMgmt",
            "商品编号": "skuCode",
            "商品简称": "shortName",
            "毛重(g)": "grossWeight",
            "长(cm)": "length",
            "英文名称": "englishName",
            "净重(g)": "netWeight",
            "商品分类": "categoryId",
            "这里超过了": 1111.0,
            "保质期(天)": "expDate"
        }];
        const wopts = { bookType: 'xlsx', bookSST: false, type: 'binary' };//这里的数据是用来定义导出的格式类型
        // const wopts = { bookType: 'csv', bookSST: false, type: 'binary' };//ods格式
        // const wopts = { bookType: 'ods', bookSST: false, type: 'binary' };//ods格式
        // const wopts = { bookType: 'xlsb', bookSST: false, type: 'binary' };//xlsb格式
        // const wopts = { bookType: 'fods', bookSST: false, type: 'binary' };//fods格式
        // const wopts = { bookType: 'biff2', bookSST: false, type: 'binary' };//xls格式

        function downloadExl(data, type) {
            const wb = { SheetNames: ['Sheet1'], Sheets: {}, Props: {} };
            wb.Sheets['Sheet1'] = XLSX.utils.json_to_sheet(data);//通过json_to_sheet转成单页(Sheet)数据
            saveAs(new Blob([s2ab(XLSX.write(wb, wopts))], { type: "application/octet-stream" }), "这里是下载的文件名" + '.' + (wopts.bookType=="biff2"?"xls":wopts.bookType));
        }
        function s2ab(s) {
            if (typeof ArrayBuffer !== 'undefined') {
                var buf = new ArrayBuffer(s.length);
                var view = new Uint8Array(buf);
                for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
                return buf;
            } else {
                var buf = new Array(s.length);
                for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
                return buf;
            }
        }
    </script>
</body>
</html>

推荐阅读更多精彩内容