#axmi171-02 start
DEFINE xlapp           INTEGER  # Excel 應用程式 Handle
DEFINE xlwb            INTEGER  # Excel workbook Handle
DEFINE result          STRING   # 錯誤代碼
#axmi171-02 end

IF cl_chk_act_auth() THEN
    display cl_download_file("/u1/topprod/topcust/doc/axmi171_yearPlan.xlt", "C:/tiptop/axmi171_yearPlan.xlt")
    CALL openExcel()
    CALL SetProperty( "N1", 0, 0, "value", g_opc.ta_opcud01)
    call ui.interface.frontcall("WinCOM","CallMethod",[xlwb,'Sheets(1).setYearPlanQty'],[result])
    CALL freeExcelMemory()
END IF

#axmi171-02 start
FUNCTION SetProperty( p_location, p_x, p_y, p_type, p_value)
      DEFINE p_location STRING,
             p_x        INTEGER,
             p_y        INTEGER,
             p_type     STRING,
             p_value    STRING,
             l_error_count INT
      #先判定用Range還是用Cells
      IF p_location is null THEN
            LET p_location = 'Activesheet.Cells( ',p_x,', ',p_y,')'
      ELSE
            LET p_location = 'Activesheet.Range("',p_location,'")'
      END IF

      CASE p_type
            WHEN "value"
                  LET p_location = p_location , ".Value"
            WHEN "size"
                  LET p_location = p_location , ".Font.Size"
            WHEN "bold"
                  LET p_location = p_location , ".Font.Bold"
            WHEN "name"
                  LET p_location = p_location , ".Font.Name"
            WHEN "border-left" #1:細實  2:細虛  4:點虛  9:雙細實線) 
                  #1:左 2:右 3:頂 4:底 5:斜 6:斜/
                  LET p_location = p_location , ".Borders(1).LineStyle"
            WHEN "border-right"
                  LET p_location = p_location , ".Borders(2).LineStyle"
            WHEN "border-top"
                  LET p_location = p_location , ".Borders(3).LineStyle"
            WHEN "border-bottom"
                  LET p_location = p_location , ".Borders(4).LineStyle"
            WHEN "Horizontal" #設定水平
                  CASE p_value
                        WHEN "center"
                              LET p_value = "3"
                        WHEN "right"
                              LET p_value = "4"
                        OTHERWISE
                              LET p_value = "2"
                  END CASE
                  LET p_location = p_location , ".HorizontalAlignment"
            WHEN "Vertical"   #設定垂直 4可自動換列
                  CASE p_value
                        WHEN "top"
                              LET p_value = "1"
                        WHEN "bottom"
                              LET p_value = "3"
                        WHEN "auto"
                              LET p_value = "4"
                        OTHERWISE
                              LET p_value = "2"
                  END CASE
                  LET p_location = p_location , ".VerticalAlignment"
      END CASE

      WHILE TRUE
          CALL ui.interface.frontCall("WinCOM", "SetProperty", [xlwb, p_location, p_value], [result])
          IF result=0 THEN
              EXIT WHILE
          else
              sleep 0.1
              LET l_error_count = l_error_count + 1
              IF l_error_count > 100 THEN
                  EXIT WHILE
              END IF
          END IF
      END WHILE
      #CALL checkExcelError( result, "Excel SetProperty Error!")
END FUNCTION

FUNCTION checkExcelError( p_result, p_errMsg)
      DEFINE p_result   INTEGER,
             p_errMsg   STRING
      IF p_result <> "-1" THEN RETURN END IF
      display p_errMsg
END FUNCTION

FUNCTION openExcel()
      CALL ui.Interface.frontCall("WinCOM", "CreateInstance", ["Excel.Application"], [xlapp])
      #CALL checkExcelError( xlapp, "Open Excel Error!")
      #CALL checkExcelError(xlapp,"Open Excel",1)

      #新的excel
      #CALL ui.interface.frontcall("WinCOM","CallMethod",[xlapp,"WorkBooks.Add"],[xlwb])
      #CALL checkExcelError( xlwb, "WorkBooks Add Error!")
      #CALL checkExcelError(xlwb,"Create Excel",1)

       CALL ui.interface.frontcall("WinCOM","CallMethod",[xlapp,"WorkBooks.Open","C:\\tiptop\\axmi171_yearPlan.xlt"],[xlwb])


      #CALL ui.interface.frontCall("WinCOM", "GetProperty", [xlapp, 'Worksheets.Count'], [l_body_value])
      #CALL checkExcelError(xlwb,"Count Sheet",2)
      #display xlapp
      #display l_body_value

      CALL ui.interface.frontcall("WinCOM","SetProperty",[xlapp,"Visible",true],[result])
      #CALL checkExcelError( result, "Excel Visible Error!")
      #CALL ui.interface.frontcall("WinCOM","SetProperty",[xlapp,"Worksheets(2).Visible",false],[result])
      #CALL ui.interface.frontcall("WinCOM","SetProperty",[xlapp,"Worksheets(1).Name", "JOSH"],[result])

      #顯示格線否
      #call ui.interface.frontcall("WinCOM","SetProperty",[xlapp,"ActiveWindow.DisplayGridlines",false],[result])


END FUNCTION

FUNCTION freeExcelMemory()
      CALL ui.interface.frontcall("WinCOM","SetProperty",[xlapp,"Visible",true],[result])
      CALL ui.Interface.frontCall("WinCOM","ReleaseInstance", [xlwb], [result] )
      CALL ui.Interface.frontCall("WinCOM","ReleaseInstance", [xlapp], [result] )
END FUNCTION

#axmi171-02 end

#精簡範例
#########################################################
CALL ui.Interface.frontCall("WinCOM", "CreateInstance", ["Word.Application"], [xlapp])
CALL ui.interface.frontcall("WinCOM","CallMethod",[xlapp,"Documents.Open","C:\\tiptop\\macro01.doc"],[xlwb])
#CALL ui.Interface.frontCall("WINCOM","CallMethod",[xlapp,"Documents.Add"],[xlwb])

CALL ui.interface.frontcall("WinCOM","SetProperty",[xlapp,"Visible",false],[result])

#call宏
CALL ui.interface.frontcall("WinCOM","CallMethod",[xlwb,'test','arg123','arg456'],[result])

#存檔並關閉
#CALL ui.Interface.frontCall("WinCOM","CallMethod",[xlwb, 'Save'],[result])
#CALL ui.Interface.frontCall("WinCOM","CallMethod",[xlwb, 'close'],[result])
#不存檔不提示，避免卡彈
CALL ui.interface.frontCall('WinCOM','CallMethod',[xlwb,'close',false],[result])

#關閉程式
CALL ui.interface.frontCall('WinCOM','CallMethod',[xlApp,'Quit'],[result])

CALL ui.Interface.frontCall("WinCOM","ReleaseInstance", [xlwb], [result])
CALL ui.Interface.frontCall("WinCOM","ReleaseInstance", [xlapp],[result])