QAD 实现数据批量导入(WEB版) JSP中间层

JSP 使用了Kendoui JS库

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="UTF-8" %>
<%@ page import="java.util.*" %>
<%@ page import="common.*" %>
<%@ page import="com.jspsmart.upload.*" %>
<%@ page import="com.progress.open4gl.javaproxy.*" %>
<%@ page import="com.progress.open4gl.*" %>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<META NAME="AUTHOR" CONTENT="TZ"/>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=10"/>
<%!
String domain = "";
String uid = "";
String xxcimtype = "";
String wbtitle = "";
String mytheme = "blueopal";
String xlstplate = "";
String dateformat = "";
String kdjsrc = "";
String ulang = "";
String myjs4 = "";
String otmsg = "";
String kdcolumns = "";
String htmltb = "";
boolean preview = false;
boolean choicesync = false;
boolean showdateformat = false;

String isupdate = "no";
String isasync = "no";
String colist = "";
String coltype = "";

GetCfg cfg = new GetCfg();
String appsvname = cfg.appsvname;
String appsvpropath = cfg.appsvpropath;
String savepath = cfg.savepath;
String appsvurl = cfg.appsvurl + "/" + cfg.appsvname;
String tpltpath = cfg.tpltpath;


%>
<%
StringBuilder jsterz = new StringBuilder();
if (request.getMethod().equalsIgnoreCase("POST")) 
{
    try{
        SmartUpload su = new SmartUpload();
        su.initialize(pageContext);
        su.setAllowedFilesList("xls,xlsx");
        su.upload();
        su.save(savepath,SmartUpload.SAVE_PHYSICAL);  //以绝对路径来测试
        domain = su.getRequest().getParameter("domain");
        uid = su.getRequest().getParameter("uid");
        xxcimtype = su.getRequest().getParameter("xxcimtype");
        coltype = su.getRequest().getParameter("coltype");
        isupdate = su.getRequest().getParameter("isupdate");
        isasync = su.getRequest().getParameter("isasync");
        com.jspsmart.upload.File file = su.getFiles().getFile(0);
        String filepath = savepath + "/" + file.getFileName();        //.getFilePathName();
        execAppsv(filepath);
        java.io.File dfile = new java.io.File(filepath);
        if (dfile.exists())
        {
            dfile.delete();    //删除上传的文件
        }
    }
    catch(Exception e)
    {
        otmsg = e.getMessage();
    }
}
else  //get
{
    domain = request.getParameter("domain");
    uid = request.getParameter("uid");
    xxcimtype = request.getParameter("xxcimtype");
    execAppsv("");
    if (xlstplate != null && colist != null && !xlstplate.isEmpty() && !colist.isEmpty())
    {
        String tpltfpath = tpltpath + "/" + xlstplate;
        java.io.File tfile = new java.io.File(tpltfpath);
        if (!tfile.exists())    //如果模板尚不存在
        {
            Npexcel p = new Npexcel();
            p.createxls(colist,tpltfpath,xlstplate.substring(0,xlstplate.lastIndexOf(".")));
        }
    }
}

jsterz.append("<script src=\"" + kdjsrc + "/udfs/scripts/jquery.min.js\"></script>\n");
jsterz.append("<script src=\"" + kdjsrc + "/udfs/scripts/kendo.web.min.js\"></script>\n");
jsterz.append("<script src=\"" + kdjsrc + "/udfs/scripts/jszip.min.js\"></script>\n");
jsterz.append("<script src=\"" + kdjsrc + "/udfs/layer/layer.js\"></script>\n");
if (ulang.equalsIgnoreCase("ch"))
{
    jsterz.append("<script src=\"" + kdjsrc + "/udfs/scripts/kendo.terz.ch.js\"></script>\n");
    jsterz.append("<script src=\"" + kdjsrc + "/udfs/scripts/kendo.culture.zh-CN.min.js\"></script>\n");
    jsterz.append("<script src=\"" + kdjsrc + "/udfs/scripts/kendo.messages.zh-CN.js\"></script>\n");
    }
else 
{
    jsterz.append("<script src=\"" + kdjsrc + "/udfs/scripts/kendo.terz.us.js\"></script>\n");
}
%>
<TITLE><%= wbtitle%></TITLE>
<link href="/udfs/css/main.css" rel="stylesheet" />
<link href="/udfs/css/kendo.common.min.css" rel="stylesheet" />
<link href="/udfs/css/kendo.<%= mytheme%>.min.css" rel="stylesheet" />
<link href="/udfs/css/kendo-offline.css" rel="stylesheet" />
<%= jsterz.toString()%>
<style type="text/css">
 #bg{ display: none; position: absolute; top: 0%; left: 0%; width: 100%; height: 100%; background-color: black; z-index:1001; -moz-opacity: 0.2; opacity:.2; filter: alpha(opacity=70);}
 .loading{display: none; position: absolute; top: 50%; left: 50%; z-index:1002; }
</style>
</HEAD>
<BODY>
<div id="bg"></div>
<div class="loading"><img src="/udfs/css/Default/loading-image.gif"/></div>
<br/>
<div align="center">
<script>
function chkfile(e) {
if($('input[name="filename"]').val()==null || $('input[name="filename"]').val()==''){
    alert(kwarnxls);
    $("#bg,.loading").hide();
    return false;
  }
}
</script>
  <form id="form1" onSubmit="return chkfile();" enctype="MULTIPART/FORM-DATA" action="xxwebcimlc.jsp" method="POST">
  <table>
        <tr>
        <td>
        <label id="lbdomain" for="domain">Domain</label>
        <input id="domain" name="domain" style="width: 60px" maxlength="6" value="<%= domain%>" readonly="readonly"/>
        &nbsp;&nbsp;&nbsp;&nbsp;
        <label id="lbuse" for="loaduse">Use for</label>
        <input id="loaduse" name="loaduse" value="<%= wbtitle%>" style="width: 240px" maxlength="36" readonly="readonly"/>
        <input type="hidden" id="uid" name="uid" value="<%= uid%>"/>
        <input type="hidden" id="xxcimtype" name="xxcimtype" value="<%= xxcimtype%>"/>
        <input type="hidden" id="coltype" name="coltype" value="<%= coltype%>"/>
        <input type="hidden" id="isupdate" name="isupdate" value="no"/>
        <input type="hidden" id="isasync" name="isasync" value="no"/>
        </td>
        <td align="left">
        &nbsp;&nbsp;&nbsp;&nbsp;
        <a id="templt" href="/udfs/docs/<%= xlstplate%>">Template</a>
        &nbsp;&nbsp;&nbsp;&nbsp;
        <button id="btnload" type="SUBMIT">Upload</button> 
        &nbsp;&nbsp;&nbsp;&nbsp;
        <label id="lbckupdate" for="ckupdate">Update</label>
        <input id="ckupdate" type="checkbox" />
        &nbsp;&nbsp;&nbsp;&nbsp;
        <label id="lbckasync" for="ckasync">Async</label>
        <input id="ckasync" type="checkbox" />
        &nbsp;&nbsp;&nbsp;&nbsp;        
        <label id="lbdateformat" for="dateformat">Date-format</label>
        <input id="dateformat" name="dateformat" style="width: 80px" maxlength="10" value="<%= dateformat%>" readonly="readonly"/>
      </td>
      </tr>
      <tr>
      <td>
      <input type="file" name="filename" id="filename" accept=".xls,.xlsx"/>
      </td>
      </tr>
      </table>
      </form>
</div>
<script>
$(document).ready(function () {  
  function onSelect(e) {
    $("#grid tbody").html("");
  };
  function Load(e) {
    $("#bg,.loading").show();
  };
  $("#filename").kendoUpload({
    multiple: false,
    validation: {
      allowedExtensions: [".xls",".xlsx"],
      maxFileSize: 2100000
    },
    select: onSelect
  });
  $("#loaduse").kendoButton({
  });
  $("#templt").kendoButton({
  });
  $("#domain").kendoMaskedTextBox({
  });
  $("#dateformat").kendoMaskedTextBox({
  });
  $("#btnload").kendoButton({
    click: Load
  });
});
</script>
<div> 
</div>
<br/>
<div id="webloadw">
<table id="grid">
<tbody>
<%= htmltb%>
</tbody>
</table>
<script>
$(document).ready(function() {
<%
if (ulang.equalsIgnoreCase("ch"))
{    
    out.println("kendo.culture(\"zh-CN\");\n");
}
if (!preview)
{    
    out.println("$(\"#lbckupdate\").hide();\n" +
    "$(\"#ckupdate\").hide();\n");
}
if (!choicesync) 
{
    out.println("$(\"#lbckasync\").hide();\n" +
    "$(\"#ckasync\").hide();\n");
}
if (!showdateformat) 
{
    out.println("$(\"#lbdateformat\").hide();\n" +
    "$(\"#dateformat\").hide();\n");
}
%>
  $("#lbdomain").html(kdomain + ":");
  $("#lbuse").html(kusefor + ":");
  $("#templt").html(ktemplate);
  $("#btnload").html(kupload);
  $("#lbckupdate").html(kckupdate);
  $("#lbckasync").html(kckasync);
  $("#lbdateformat").html(kdateformat + ":");
  
  $("#grid").kendoGrid({
    toolbar: [{ name: "excel", text: kexprst }],
      excel: {
      allPages: true,
      fileName: "ResultExport.xlsx",
      filterable: false
    },
    height: 635,
    filterable: false,
    resizable: true, 
    sortable: true,
    columns: [
    <%= kdcolumns%>
    ],
    pageable: true,
    pageable: {
      pageSizes: true
    }
  });
  
  $("#ckupdate").click(function () {
    if ($("#ckupdate").is(':checked') == true)
    { $("#isupdate").val("yes"); }
    else
    { $("#isupdate").val("no"); }
    });

  $("#ckasync").click(function () {
    if ($("#ckasync").is(':checked') == true)
    { $("#isasync").val("yes"); }
    else
    { $("#isasync").val("no"); }
    });

});
</script>
</div>
<%
//otmsg = cfg.appsvurl + "/" + cfg.appsvname + "/" + cfg.savepath;
if (!otmsg.equals(""))
out.println(
"<script>\n" +
"layer.open({\n" +
"title: 'Message',\n" +
"content: '" + otmsg +"',\n" +
"icon: 0,\n" +
"btn: 'OK'\n" +
"});\n" +
"</script>");

if (!myjs4.equals(""))
out.println(myjs4);
%>
<%!
//核心部分 调用APPSERVER 执行CIMLOAD
public void execAppsv(String pfilepath)
{
    try{
    Wkdatatb wktb = new Wkdatatb(pfilepath,coltype);
    int maxcol = wktb.getColnum();
    ParamArray parms = new ParamArray(11);
    ProResultSetMetaDataImpl metaData1;
    metaData1 = new ProResultSetMetaDataImpl(maxcol);    //for static table 定义列数 还要定义列名
    for (int i=1;i<=maxcol;i++)
    {
        metaData1.setFieldMetaData(i,"wkchr" + String.valueOf(i),0,Parameter.PRO_CHARACTER);  //统一读取为字符型
    }
    ProResultSetMetaDataImpl metaData2;
    metaData2 = new ProResultSetMetaDataImpl(16);    //for table handle 只要定义fldnumber
    parms.addCharacter(0,domain,ParamArrayMode.INPUT);
    parms.addCharacter(1,uid,ParamArrayMode.INPUT);
    parms.addCharacter(2,xxcimtype,ParamArrayMode.INPUT);
    parms.addCharacter(3,isupdate,ParamArrayMode.INPUT);
    parms.addCharacter(4,isasync,ParamArrayMode.INPUT);
    if (!pfilepath.equals(""))
        parms.addTable(5,wktb,ParamArrayMode.INPUT,metaData1);
    else
        parms.addTable(5,null,ParamArrayMode.INPUT,metaData1);
    parms.addTableHandle(6,null,ParamArrayMode.OUTPUT,metaData2);
    parms.addCharacter(7,null,ParamArrayMode.OUTPUT);
    parms.addLongchar(8,null,ParamArrayMode.OUTPUT);
    parms.addLongchar(9,null,ParamArrayMode.OUTPUT);
    parms.addLogical(10,true,ParamArrayMode.INPUT);    //true: 在QAD里需要检查session false: 不需要验证QAD的session
    OeAppsvHelper.execProc(appsvurl,appsvname,appsvpropath + "xxwebcimlcsrv.p",parms);
    ResultSet rs = (ResultSet)parms.getOutputParameter(6);
    otmsg = (String)parms.getOutputParameter(7);
    htmltb = (String)parms.getOutputParameter(8);
    kdcolumns = (String)parms.getOutputParameter(9);
    parms.clear();
    if (rs.next())  //返回的就只有一个rs 故不用循环
    {
        wbtitle = rs.getString("wbtitle");
        mytheme = rs.getString("mytheme");
        xlstplate = rs.getString("xlstplate");
        preview = rs.getBoolean("preview");
        choicesync = rs.getBoolean("choicesync"); 
        showdateformat = rs.getBoolean("showdateformat");
        dateformat = rs.getString("dateformat");
        kdjsrc = rs.getString("kdjsrc");
        ulang = rs.getString("ulang");
        myjs4 = rs.getString("myjs4");
        colist = rs.getString("colist"); 
        coltype = rs.getString("coltype");
        //可补充 对应appserver端
    }
    if (otmsg.equals(""))
    otmsg = wktb.getErr();
    }
    catch(Exception e){
        otmsg = e.getMessage();
    }
}
%>
</BODY>
</HTML>