{"id":7945,"date":"2020-11-10T13:55:36","date_gmt":"2020-11-10T05:55:36","guid":{"rendered":"http:\/\/www.hefeiyu.com\/?p=7945"},"modified":"2020-11-10T13:57:03","modified_gmt":"2020-11-10T05:57:03","slug":"t100-excel%e5%af%bc%e5%85%a5-%e5%85%b3%e9%94%ae%e4%bb%a3%e7%a0%81","status":"publish","type":"post","link":"https:\/\/www.hefeiyu.com\/?p=7945","title":{"rendered":"T100 EXCEL\u5bfc\u5165 \u5173\u952e\u4ee3\u7801"},"content":{"rendered":"<pre class=\"pure-highlightjs\"><code class=\"null\">PUBLIC FUNCTION cxmt500_01_excel_imp(p_excelname)\r\nDEFINE p_excelname      LIKE type_t.chr1000  #excel\u6863\u540d\r\nDEFINE r_success        LIKE type_t.num5\r\nDEFINE L_success        LIKE type_t.num5\r\nDEFINE l_excelname      STRING               #excel\u6863\u540d\r\nDEFINE l_count          LIKE type_t.num10\r\nDEFINE li_i             LIKE type_t.num10\r\nDEFINE li_j             LIKE type_t.num10\r\nDEFINE xlapp,iRes,iRow  LIKE type_t.num5\r\nDEFINE l_sql            STRING\r\nDEFINE l_xmdc            RECORD\r\n       seq LIKE type_t.num5,\r\n   l_xmda004 LIKE type_t.chr10,\r\n   l_xmdc027 LIKE type_t.chr500,\r\n   l_xmdc027_desc LIKE type_t.chr500,\r\n   l_xmdc027_desc_1 LIKE type_t.chr500,    \r\n   imaa001 LIKE imaa_t.imaa001,\r\n   l_xmdc007 LIKE type_t.num20_6,\r\n   l_xmdc012 LIKE type_t.dat,\r\n   l_oofg001 LIKE type_t.chr10,\r\n   imaa003 LIKE imaa_t.imaa003,\r\n   imaa009 LIKE imaa_t.imaa009,\r\n   l_xmdc050 LIKE type_t.chr500\r\nEND RECORD  \r\nDEFINE l_cnt      LIKE type_t.num5\r\n\r\n\r\nLET l_sql=\"INSERT INTO cxmt500_01_tmp VALUES\r\n           (?,?,?,?,?,\r\n            ?,?,?,?,?,\r\n            ?,?)\"\r\nPREPARE cxmt500_01_ins_p FROM l_sql            \r\n\r\n\r\n\r\n\r\nWHENEVER ERROR CONTINUE\r\n   LET r_success = TRUE\r\n\r\n\r\n\r\n\r\n   LET l_count = LENGTH(p_excelname CLIPPED)\r\n   LET l_excelname=p_excelname CLIPPED\r\n   ###\u521b\u5efaEXCEL\u5b9e\u4f8b\r\n   CALL ui.interface.frontCall('WinCOM','CreateInstance',\r\n                               ['Excel.Application'],[xlApp])\r\n   IF xlApp &lt;&gt; -1 THEN####\u6ca1\u6709\u8fd9\u4e2a\u6587\u4ef6\r\n      CALL ui.interface.frontCall('WinCOM','CallMethod',####\u65b0\u5efaexcel\u6587\u4ef6\r\n                                  [xlApp,'WorkBooks.Open',l_excelname],[iRes])\r\n      IF iRes &lt;&gt; -1 THEN####excel\u8868\u683c\u4e3a\u7a7a\r\n         CALL ui.interface.frontCall('WinCOM','GetProperty',\r\n              [xlApp,'ActiveSheet.UsedRange.Rows.Count'],[iRow])\r\n          IF iRow &gt; 1 THEN######\u83b7\u53d6excel\u6570\u636e\r\n            FOR li_i = 2 TO iRow\r\n                 INITIALIZE l_xmdc.* TO NULL\r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',1).Value'],[l_xmdc.seq])                              \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',2).Value'],[l_xmdc.l_xmda004])        \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',3).Value'],[l_xmdc.l_xmdc027])        \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',4).Value'],[l_xmdc.l_xmdc027_desc])  \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',5).Value'],[l_xmdc.l_xmdc027_desc_1])    \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',6).Value'],[l_xmdc.imaa001])        \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',7).Value'],[l_xmdc.l_xmdc007])      \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',8).Value'],[l_xmdc.l_xmdc012])            \r\n                 CALL ui.interface.frontCall('WinCOM','GetProperty',[xlApp,'ActiveSheet.Cells('||li_i||',9).Value'],[l_xmdc.l_xmdc050])    \r\n      \r\n\r\n\r\n                  #\u680f\u4f4d\u7ba1\u63a7\r\n                   #1\u3001\u5e8f\u53f7\u680f\u4f4d\u4e0d\u5141\u8bb8\u91cd\u590d\uff1b\r\n                   IF cl_null(l_xmdc.seq) THEN\r\n                   #\u68c0\u67e5\u5ba2\u6237\u4ee3\u53f7\u662f\u5426\u5b58\u5728\r\n                      INITIALIZE g_errparam TO NULL\r\n                      LET g_errparam.code = 'aoo-00146'\r\n                      LET g_errparam.extend = \" \u9879\u6b21\uff1a\",l_xmdc.seq,\"\u4e0d\u80fd\u4e3a\u7a7a\"\r\n                      LET g_errparam.popup = TRUE\r\n                      CALL cl_err()                            \r\n                      LET r_success = FALSE                        \r\n                   END IF                                          \r\n                   SELECT COUNT(*) INTO l_cnt\r\n                     FROM cxmt500_01_tmp\r\n                    WHERE seq=l_xmdc.seq\r\n                   IF l_cnt&gt;0 THEN\r\n                     INITIALIZE g_errparam TO NULL\r\n                     LET g_errparam.code = 'aoo-00146'\r\n                     LET g_errparam.extend = \"\u5ba2\u6237\u7f16\u53f7\uff1a\",l_xmdc.l_xmda004,\" \u9879\u6b21\uff1a\",l_xmdc.seq,\"\u91cd\u590d\"\r\n                     LET g_errparam.popup = TRUE\r\n                     CALL cl_err()\r\n                     LET r_success = FALSE          \r\n#                     EXIT FOR\r\n                   END IF\r\n                   IF NOT cl_null(l_xmdc.l_xmda004) THEN\r\n                   #\u68c0\u67e5\u5ba2\u6237\u4ee3\u53f7\u662f\u5426\u5b58\u5728\r\n                      INITIALIZE g_chkparam.* TO NULL\r\n                      \r\n                      #\u8a2d\u5b9ag_chkparam.*\u7684\u53c3\u6578\r\n                      LET g_chkparam.arg1 = l_xmdc.l_xmda004              \r\n                      IF NOT  cl_chk_exist(\"v_pmaa001_10\") THEN\r\n                         #\u6aa2\u67e5\u6210\u529f\u6642\u5f8c\u7e8c\u8655\u7406\r\n                         INITIALIZE g_errparam TO NULL\r\n                         LET g_errparam.code = 'aoo-00146'\r\n                         LET g_errparam.extend = \" \u9879\u6b21\uff1a\",l_xmdc.seq,\"\"\r\n                         LET g_errparam.popup = TRUE\r\n                         CALL cl_err()                            \r\n                         LET r_success = FALSE          \r\n#                        EXIT FOR\r\n                      END IF                \r\n                   END IF  \r\n\r\n\r\n                   IF  cl_null(l_xmdc.l_xmdc007) THEN\r\n                     LET l_xmdc.l_xmdc007=0                    \r\n                   END IF\r\n                   IF  cl_null(l_xmdc.l_xmdc012) THEN\r\n                     LET l_xmdc.l_xmdc012=g_today                  \r\n                   END IF                    \r\n                   EXECUTE cxmt500_01_ins_p USING l_xmdc.*\r\n            END FOR\r\n         END IF                  \r\n\r\n\r\n      ELSE\r\n         INITIALIZE g_errparam TO NULL\r\n         LET g_errparam.code = 'aoo-00146'\r\n         LET g_errparam.extend = 'NO FILE'\r\n         LET g_errparam.popup = TRUE\r\n         CALL cl_err()\r\n\r\n\r\n         LET r_success = FALSE\r\n      END IF\r\n   ELSE\r\n      INITIALIZE g_errparam TO NULL\r\n      LET g_errparam.code = 'aoo-00146'\r\n      LET g_errparam.extend = 'NO EXCEL'\r\n      LET g_errparam.popup = TRUE\r\n      CALL cl_err()\r\n\r\n\r\n      LET r_success = FALSE\r\n   END IF\r\n\r\n\r\n   CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'Quit'],[iRes])\r\n   CALL ui.interface.frontCall('WinCOM','ReleaseInstance',[xlApp],[iRes])\r\n\r\n\r\n   RETURN r_success\r\nEND FUNCTION<\/code><\/pre>\n<p>&nbsp;<\/p>\n<p>\u8f6c\u8f7d\u8bf7\u6ce8\u660e\uff1a<a href=\"https:\/\/www.hefeiyu.com\">\u8d6b\u975e\u57df<\/a> &raquo; <a href=\"https:\/\/www.hefeiyu.com\/?p=7945\">T100 EXCEL\u5bfc\u5165 \u5173\u952e\u4ee3\u7801<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>PUBLIC FUNCTION cxmt500_01_excel_imp(p_excelname) DEFINE p_excelname LIKE type_t.chr1000 #excel\u6863\u540d DEFINE r_success LIKE type_ [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[490],"tags":[67,467],"class_list":["post-7945","post","type-post","status-publish","format-standard","hentry","category-490","tag-excel","tag-467"],"_links":{"self":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/7945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7945"}],"version-history":[{"count":1,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/7945\/revisions"}],"predecessor-version":[{"id":7946,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=\/wp\/v2\/posts\/7945\/revisions\/7946"}],"wp:attachment":[{"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hefeiyu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}