02.02.04.tiptop：组件篇（4gl 查询：导出数据到指定excle模板上）

本页目录：

• 1、封装
• 2、调用案例

---

• 封装的程序cl_data_to_excel

• p_link 添加上

# 开一个上传代码cmd窗口
sftp BL066912@ip
cd /u1/toptest/tiptop/lib/4gl
put cl_data_to_excel.4gl

# 开一个编译代码的cmd窗口
ssh BL066912@ip -p 22
cd /u1/toptest/tiptop/lib/4gl
r.c2 cl_data_to_excel
r.l2 lib

exe2 p_link 

调用案例

• 1、程序p_link要连接cl_rex_create_xls

• 2、capq105.xlsx文件要放到42r里面，程序名称和文件名称要一样

• 3、4gl引用

# 引用
IMPORT os  
DATABASE ds
GLOBALS "../../config/top.global"
# 全局变量
DEFINE xlapp                  INTEGER,
       xlwb                   INTEGER,
       p_row     LIKE type_file.num5,
       p_col     LIKE type_file.num5
       
# @mathodName: MAIN
# @author:xxxx
# @date:Created in 2024-05-17
# @description:主程序
MAIN
	IF (NOT cl_user()) THEN
      EXIT PROGRAM
    END IF
    WHENEVER ERROR CALL cl_err_msg_log
    IF (NOT cl_setup("CAP")) THEN
      EXIT PROGRAM
    END IF
    CALL  cl_used(g_prog,g_time,1) RETURNING g_time  

    LET p_row = 5 LET p_col = 10  -- 給變量賦值
    OPEN WINDOW p130_w AT p_row,p_col WITH FORM "cap/42f/capq105"
      ATTRIBUTE (STYLE = g_win_style CLIPPED)

        CALL export_excle()

    CLOSE WINDOW q105_w
    CALL  cl_used(g_prog,g_time,2) RETURNING g_time 
END MAIN

# @mathodName: export_excle
# @author:xxxx
# @date:Created in 2024-04-18
# @description: 导出指定模板的excle
FUNCTION export_excle()
  
  DEFINE windows_file_name  STRING,
         l_cur_clipboard    STRING,
         ires               integer,
         i                  integer,
         la_cells DYNAMIC ARRAY OF STRING
  
--initialization of global variables
  LET xlapp = -1
  LET xlwb = -1
  
  CALL cl_open_excel_tempate(null) RETURNING xlapp, xlwb,windows_file_name
   IF cl_null(xlapp) THEN
      -- 打開EXCEL 應用失敗
      return
   END IF

   IF cl_null(xlwb) THEN
      -- 打開EXCEL 模板失敗
      return
   END if
   
    CALL ui.interface.frontCall('standard','cbget',[],[l_cur_clipboard])  ## 保存現有剪貼板  
   
   FOR i=1 TO 10
       call la_cells.clear()
       LET la_cells[1]=i
       
       CALL cl_Excel_WriteRow(xlApp, xlwb, 5+i ,1, la_cells) RETURNING ires  # 把資料寫到第6行起 
       IF cl_null(ires) THEN
         return
       END if

       CALL cl_err((i/10)*100,'log-012',0)  ## 进度
       
   END FOR

   CALL ui.interface.frontCall('standard','cbset',[],[l_cur_clipboard])   ## 恢復剪貼板
   CALL ui.interface.frontCall('WinCOM','CallMethod',[xlwb, 'save' ],[ires])  ## 保存EXCEL
   CALL cl_Excel_COMCleanup(xlwb) 
   CALL cl_Excel_COMCleanup(xlapp)
   
   CALL freeMemory()  
   CALL ui.interface.FrontCall('standard','shellexec',[windows_file_name],[iRes])
	RETURN

END FUNCTION

FUNCTION freeMemory()
 DEFINE res INTEGER
  IF xlwb != -1 THEN
    CALL ui.Interface.frontCall("WinCOM","ReleaseInstance", [xlwb], [res] )
  END IF
  IF xlapp != -1 THEN
    CALL ui.Interface.frontCall("WinCOM","ReleaseInstance", [xlapp], [res] )
  END IF
END FUNCTION