模 組ccl_au3xls
相關設定
1.client端要安裝autoit3請至 http://www.autoitscript.com/site/autoit/downloads/
2.client端要建立二個資料夾c:\tmp,c:\tiptop
3.若使用樣版,你的樣版名稱,要分大小寫(大小寫要一樣)
4.自訂路徑也要一樣分大小寫
5.2012/07/27 新增分頁及設定每頁有幾項明細
6.2012/08/09 新增類型4報表
7.模組下載 http://csc.ocean-pioneer.com/docum/ccl_au3xls.zip

安裝
下載完畢後解壓縮
有兩個檔案
ccl_au3xls.global,ccl_au3xls.4gl
將這兩個檔案放入客製程式的clib\4gl 資料夾下
編譯
r.c2 au3xls
新增到lib函式庫中
exe p_link
查詢鍊結檔案lib
明細中加入ccl_au3xls,離開後會自動編譯
#如果有錯,檢查其它明細是否有重複的檔案,我司的就有錯(真是x?)

使用簡例:
範例1:
#最前面加入
GLOBALS "../../../topcust/clib/4gl/ccl_au3xls.global"
#基本設定
  LET g_xls_type = '1'
  call ccl_au3xls_init()
  LET g_cur_row = 1
  LEt g_cur_col = 1
  FOREACH cxmr551_curs1 INTO ofa.*,ofb.*
     IF SQLCA.sqlcode != 0 THEN
        CALL cl_err('foreach:',SQLCA.sqlcode,1)
        EXIT FOREACH
     END IF
     call ccl_au3_write_rc(g_cur_row,1,ofa.ofa011,2) #以row,column方式寫入儲存格,格式是字串
     call ccl_au3_borders_rc(g_cur_row,1,0) #畫框線
    #call ccl_au3_borders_crc(0)            #簡化如下
     call ccl_au3_write_rc(g_cur_row,2,ofb.ofb04,2)
     call ccl_au3_borders_rc(g_cur_row,2,0)    
     let g_cur_row = g_cur_row + 1
    
  END FOREACH 
  call ccl_au3_autofit(1) #自動欄I寬
  call ccl_au3_align(1,1) #對齊,靠左
  call ccl_au3_autofit(2) #自動欄寬
  call ccl_au3_align(2,3)#對齊靠右
 
  call ccl_au3_close()


可 用的全域變數名稱
GLOBALS
    DEFINE g_temp STRING
    DEFINE g_temp_au3_path STRING   #  伺服器端 寫入autoit3 暫存檔的路徑
    DEFINE g_temp_au3_name STRING   #  伺服器端 寫入autoit3 暫存檔的名稱   
    DEFINE g_temp_xls_name STRING      #  本機 寫入xls暫存檔的名稱
    DEFINE g_template_path string      #  伺服器端 樣版路徑
    DEFINE g_template_name string      #  樣版名稱
    DEFINE g_xls_type  char(1)         # 使用的樣版方式,有4種,1是寫入一個空檔,2是寫入樣版,3寫入樣版單檔多頁式,4寫入多檔多頁
    DEFINE g_xls_title_rows integer         # 定義xls檔案的檔頭
    DEFINE g_xls_page_rows integer     # 定義每頁有幾行
    DEFINE g_xls_dup_rang   string     # 備份範圍, 目前沒有使用,使用自動計算,
    DEFINE g_xls_sheet_name STRING     #  sheet名稱

    DEFINE g_xls_field_name STRING     #  欄位的抬頭
    DEFINE g_temp_au3_file_ch base.Channel # 檔案的channel ,系統使用
    DEFINE g_cur_col   integer       #目前所在欄
    DEFINE g_cur_row   integer       #目前所在列 
    DEFINE g_cur_cell  char(4)       #目前所在儲存格
    DEFINE g_cur_page  integer       #目前所在的頁數   
END GLOBALS
#g_cur_col ,g_cur_row,g_cur_cell會在寫入的相關函式重新計算(不包含相對寫入函式)

產生報表的方法:
一般來講,產生報表可以分成
1.不需樣版,直接匯出
2.寫入一個樣版,單檔單頁
3.寫入樣版,單檔多頁,根據條件會分頁,
4.寫入樣版,多檔多頁,根據條件寫入不同的檔案

#可用的函式如下
Functions

可用的函式 說明
ccl_au3xls_init() #初始化
ccl_au3_close( #結束並執行
ccl_au3_wln(l_str) #寫入一行
ccl_au3xls_wln(l_str) #寫入一行但是前面幫你加物件$oExcel.
ccl_au3_deltmp() #刪除暫存檔
ccl_au3_write_rc(l_row,l_col,l_str,l_format) 寫入儲存格,l_format  ,格式可以N,S
N為整數,N後面一位為小數點,例如:N3為小數點三位
S為字串,S後面一位為對齊,不給預設靠左,SR(靠右),SC(靠中)
 註:若不需要對齊,可以給空白則字串
ccl_au3_write_cell(l_a,l_str,l_format 寫入儲存格以A1格式
ccl_au3_borders_rc(l_row,l_col,l_format) 在某儲存格畫框線,l_format
#0全部,1左,2右,3頂,4底,5上下,6左右,7上下左,8上下右.9:左右上 10:左右下
ccl_au3_borders_crc(l_format) 目前所在儲存格畫框線
ccl_au3_borders_rrc(l_row,l_col,l_format) 在相對儲存格畫框線
ccl_au3_borders_cell(l_a,l_format) 以A1的格式畫框線
ccl_au3_borders_rcell(l_a,l_format) 以相對儲存格畫框線
ccl_au3_Borders_range(l_lt,l_rb) 區域畫框線
ccl_au3_format(l_row,l_col,l_format) 設定儲存格的函式,格式是
字串如果是日期"yyyy/mm/dd",
或數字是" #,##0.00,
和excel巨集中使用的格式相同
ccl_au3_autofit(l_col) 欄位設定自動調整大小

ccl_au3_align(l_col,l_format) 欄位設定對齊,l_format ,1左,2右,3置中,4分散對齊
ccl_au3_write_rrc(l_row,l_col,l_str,l_format) #針對類型3.4
 以相對位置來寫入,列會計算絕對位置,再寫入
ccl_au3_write_rcell(l_a,l_str,l_format) #針對類型3,4
同上,但是儲存格的格式A1,支援達6位數,等於999,999(九十九萬九千九百九十九)
ccl_au3_bold_rc(l_row,l_col)>  設定rc為粗體字
ccl_au3_bold_cell(l_a)  設定儲存格為粗體字
ccl_au3_bold_rrc(l_row,l_col)  設定相對目前rc為粗體,會幫你計算為絕對位置
ccl_au3_bold_rcell(l_a)  設定相對目前儲存格為粗體
ccl_au3_bold_crc()  設定目前所在儲存格為粗體
ccl_au3_mg_cell(l_a1,l_a2)    合併儲存格
ccl_au3_mg_rcell(l_a1,l_a2)
 相對位置合併儲存格
ccl_au3xls_initA1()
#針對類型4的初始值 
ccl_au3xls_initA2(p_str) #針對類型4的初始值
  將xls檔名設定在這裏,傳遞的參數p_str檔名
ccl_au3xls_closeA1() #針對類型4
   autoit3 儲存excel檔
ccl_au3xls_closeA2() #針對類型4
   儲存au3檔並執行
ccl_au3_new_page()      

#新增分頁針對類型3,4
必須事先定義變數:
g_xls_title_rows#定義xls檔案的檔頭(頁首)
g_xls_page_rows # 定義每頁有幾行
會根據上述變數,加入分頁,複製樣版
   
#自設功能,如果,沒有你要的 函式功能,你都可以使用ccl_au3_wln()函式來執行,但是你要自組字串合乎其外部巨集的語法,
只要記住寫入的物件變數是$oExcel
例如:
設定頁首
let l_str = '$oExcel.PageSetup.CenterHeader="','抬頭','"' 
call ccl_au3_wln(l_str)
#加到clib中ccl_au3xls.4gl模組中
FUNCTION ccl_au3_pg_head(l_str)
DEFINE  l_str string
Let l_str = '$oExcel.PageSetup.CenterHeader="',l_str clipped ,'"'
call ccl_au3_wln(l_str)
END FUNCTION

#如上我們就增加了一個函式 ccl_au3_pg_head()





相對位址及絕對位址
1.是絕對位址,針對檔頭部份,位址是固定的
2.是相對位址,針對檔身部份外,明細除了第一筆外位址是固定
當使用單頁套寫時不會有問題,但是多頁套寫時會有問題,因此如何解決這個問題呢?
目前有幾個變數和紙張,及目前所在欄,和列位址有關
    DEFINE g_xls_title_rows integer    #定義xls檔案的檔頭,該區間內所有都物件都不會變更
    DEFINE g_xls_page_rows integer     #定義每頁有幾行
    DEFINE g_cur_col   integer         #目前所在欄
    DEFINE g_cur_row   integer         #目前所在列 
    DEFINE g_cur_cell  char(4)         #目前所在儲存格
    DEFINE g_cur_page  integer         #目前所在的頁數   
 必須由上述的變數來產生1,2相對位址
如果你要寫入相對位址
我們將g_xls_title_rows 以X代之,g_xls_page_rows以y代之,g_cur_page 以 P取代
當你知道其位置是CR,當你要寫入是每一頁的CR,
C欄是不變,要變的是R,也就是欄不變,但列要計算,
例如:每一頁的A6,我們以變數R取代6
(R-g_xls_title_rows)+ g_xls_title_rows +(g_cur_page - 1)* (g_xls_page_rows - g_xls_title_rows)
實例: A6,抬頭4,頁長60,第3頁
(R-4) + 4+ (3-1)*(60-4)
R+112
6+112 = 118 ----------------------->結果是對的
簡化公式
(R-X) +X+(P-1) *(Y-X)
R+(P-1)*(Y-X)
g_xls_title_rows 抬頭(頁首) = 4 每 一頁的抬頭

g_xls_page_rows-g_xls_title_rows
明細(頁長) =56
第一頁
A6相對2

明細(頁長=56
第二頁
A6相對2


第三頁
A6相對2
4+56+56+2=112+6=118
建立一個函式幫你計算l_row
ccl_au3_write_rrc(l_row,l_col,l_str,l_format)
#我們針對固


範例2
#最前面加入

GLOBALS "../../../topcust/clib/4gl/ccl_au3xls.global"
#基本設定
  LET g_xls_type = '2'
  LET g_template_name = 'salh581.xls'
  LET g_template_path = '/home/samba/xls'

  call ccl_au3xls_init()
  LET g_cur_row = 1
  LEt g_cur_col = 1
  FOREACH cxmr551_curs1 INTO ofa.*,ofb.*
     IF SQLCA.sqlcode != 0 THEN
        CALL cl_err('foreach:',SQLCA.sqlcode,1)
        EXIT FOREACH
     END IF
     call ccl_au3_write_rc(g_cur_row,1,ofa.ofa011,2) #以row,column方式寫入儲存格,格式是字串
     call ccl_au3_borders_rc(g_cur_row,1,0) #畫框線
     call ccl_au3_write_rc(g_cur_row,2,ofb.ofb04,2)
     call ccl_au3_borders_rc(g_cur_row,2,0)    
     let g_cur_row = g_cur_row + 1
    
  END FOREACH 
  call ccl_au3_autofit(1) #自動欄I寬
  call ccl_au3_align(1,1) #對齊,靠左
  call ccl_au3_autofit(2) #自動欄寬
  call ccl_au3_align(2,3)#對齊靠右
 
  call ccl_au3_close()

範例:3
這是cxmr551為例
  LET g_xls_type = '3'               #
  LET g_template_name = 'salh581.xls'
  LET g_template_path = '/home/samba/xls'
  LET g_xls_title_rows = 4
  LET g_xls_page_rows = 48
  LET l_p_limit  =  15  #限制每頁15筆
  CALL ccl_au3xls_init()
  LET l_per_ofa01 = 'top'
  FOREACH cxmr551_curs1 INTO ofa.*,ofb.*
     IF SQLCA.sqlcode != 0 THEN
        CALL cl_err('foreach:',SQLCA.sqlcode,1)
        EXIT FOREACH
     END IF

    #檔頭#################
        IF l_per_ofa01 <> ofa.ofa01 THEN
        LET l_g_count = 1
        LET l_count_d = 0
        #業務聯絡人,業務聯絡人分機號碼
        SELECT occ29,occ292,occ261,occ271,occ47 INTO sr.occ29 ,sr.occ292,sr.occ261,sr.occ271,sr.occ47 FROM occ_file
        WHERE occ01 = ofa.ofa03
        #價格條件編號
        SELECT oah02 INTO sr.oah02 FROM oah_file   WHERE oah01 = ofa.ofa31
        #收款條件編號
        SELECT oag02 INTO sr.oag02 FROM oag_file    WHERE oag01 = ofa.ofa32
        #FCC No,規格,單位使用方式
        SELECT ima138,ima021,ima906 INTO sr.ima138,sr.ima021,sr.ima906 FROM ima_file   WHERE ima01 = ofb.ofb04
        #ofa72(是否列印FCC No.)
        IF ofa.ofa72='N' THEN LET sr.ima138 = "" END IF
        #送貨客戶簡稱11.05.17 by tc
        select occ02 into sr.occ02 FROM occ_file WHERE occ01=sr.oea04
        #運輸方式
        LET l_ged02=''
        select ged02 into l_ged02 FROM ged_file WHERE ged01=ofa.ofa43
        IF ofa.ofa0352 IS NULL THEN #帳款客戶全名
           LET l_ofa0352 = ofa.ofa0353 LET ofa.ofa0353 = ofa.ofa0354  
           LET ofa.ofa0354 = ofa.ofa0355 LET ofa.ofa0355 = NULL
        ELSE  
           LET l_ofa0352 = ofa.ofa0352   #MOD-850197
        END IF
        IF ofa.ofa0452 IS NULL THEN #送貨客戶全名
           LET l_ofa0452 = ofa.ofa0453 LET ofa.ofa0453 = ofa.ofa0454  
           LET ofa.ofa0454 = ofa.ofa0455 LET ofa.ofa0455 = NULL
        ELSE   #MOD-850197
           LET l_ofa0452 = ofa.ofa0452   #MOD-850197
        END IF    
       #2011/3/7 add by csc 取得麥頭
     
       IF cl_null(ofa.ofa44) THEN
          LET l_ocf101 = "" LET l_ocf102 = ""
          LET l_ocf103 = "" LET l_ocf104 = ""        
       ELSE
         SELECT ocf101,ocf102,ocf103,ocf104 INTO l_ocf101,l_ocf102,l_ocf103,l_ocf104
            FROM ocf_file WHERE ocf01 = ofa.ofa03 AND ocf02 = ofa.ofa44
         IF SQLCA.sqlcode = NOTFOUND THEN
            LET l_ocf101 = "" LET l_ocf102 = "" LET l_ocf103 = "" LET l_ocf104 = ""
         END IF      
       END IF
       #2011/3/8 add by csc 取得港口名
       DISPLAY "======================="
       IF NOT cl_null(ofa.ofa41) THEN
          SELECT oac02 INTO l_ofa41t FROM oac_file WHERE oac01 = ofa.ofa41
          IF SQLCA.sqlcode = NOTFOUND THEN
             LET l_ofa41t = ''
          END IF 
       END IF
       IF NOT cl_null(ofa.ofa42) THEN
          SELECT oac02 INTO l_ofa42t FROM oac_file WHERE oac01 = ofa.ofa42
          IF SQLCA.sqlcode = NOTFOUND THEN
             LET l_ofa42t = ''
          END IF
       END IF
       #2011/12/05 add by csc 取得銷售業務員l_oea14
       if not cl_null(ofa.ofa10) then
          select oea14 into l_oea14 from oea_file where oea01 = ofa.ofa16
          if not cl_null(l_oea14) then
             select gen02 into l_gen02 from gen_file where gen01 = l_oea14  
          else
              let l_gen02= ''
          end if 
       else
          let l_gen02 = ''
       end if         
       #####################
       #尾
       #計算總金額及數量
       LET l_count = 0
       LET l_sum = 0
       SELECT count(*),SUM(ofb14) INTO l_count,l_sum FROM ofb_file WHERE ofb01=ofa.ofa01
       CALL cl_say(l_sum,80) RETURNING l_str1,l_str2
       #單價、金額、小計小數位數
       SELECT azi03,azi04,azi05 INTO t_azi03,t_azi04,t_azi05
          FROM azi_file WHERE azi01 = ofa.ofa23
       IF cl_null(t_azi03) THEN LET t_azi03 = 0 END IF
       IF cl_null(t_azi04) THEN LET t_azi04 = 0 END IF
       IF cl_null(t_azi05) THEN LET t_azi05 = 0 END IF
       #end
  
      END IF
      #頭寫excel ,因為同一個單號明細太多時無法放在同一頁因此寫的動作要放在外面
      IF l_per_ofa01 <> ofa.ofa01 or (l_count_d mod l_p_limit = 0 and l_count_d <> 0) THEN
         IF l_per_ofa01 <> 'top' THEN
            CALL ccl_au3_new_page()
         END IF          
       CALL ccl_au3_write_rcell('G6',ofa.ofa01,'S')
       CALL ccl_au3_write_rcell('G7',ofa.ofa02,'S')
       call ccl_au3_write_rcell('G15',l_gen02,'S')
       CALL ccl_au3_write_rcell('A8',ofa.ofa0351,'S')
       CALL ccl_au3_write_rcell('A9',ofa.ofa0352,'S')
       CALL ccl_au3_write_rcell('A10',ofa.ofa0353,'S')
       CALL ccl_au3_write_rcell('A11',ofa.ofa0354,'S')
       CALL ccl_au3_write_rcell('A12',ofa.ofa0355,'S')
       CALL ccl_au3_write_rcell('D8',ofa.ofa0452,'S')
       CALL ccl_au3_write_rcell('D9',ofa.ofa0453,'S')
       CALL ccl_au3_write_rcell('D10',ofa.ofa0454,'S')
       CALL ccl_au3_write_rcell('D11',ofa.ofa0455,'S')                                   
       CALL ccl_au3_write_rcell('C18',sr.oag02,'S')
       CALL ccl_au3_write_rcell('E18',sr.oah02,'S')                     
       LET i= g_cur_row + 2   
      END IF 

      #身
      #客戶訂單單號
      SELECT oea10,oea04 INTO sr.oea10,sr.oea04 FROM oea_file WHERE oea01=ofb.ofb31
      IF cl_null(sr.oea10) THEN LET sr.oea10 = '' END IF    
      LET i= i+ 1
      let l_count_d = l_count_d + 1    #明細筆數計算
      CALL ccl_au3_write_rc(i,1,sr.oea10,'S')
      CALL ccl_au3_borders_crc(0)
      CALL ccl_au3_write_rc(i,2,'','S')
      CALL ccl_au3_borders_crc(5)
      CALL ccl_au3_write_rc(i,3,ofb.ofb04,'S')
      CALL ccl_au3_borders_crc(8)      
      CALL ccl_au3_write_rc(i,4,ofb.ofb12,'N')
      CALL ccl_au3_borders_crc(5)    
      CALL ccl_au3_write_rc(i,5,ofb.ofb05,'S')
      CALL ccl_au3_borders_crc(8)
      CALL ccl_au3_write_rc(i,6,ofb.ofb13,'N4')
      CALL ccl_au3_borders_crc(0)   
      CALL ccl_au3_write_rc(i,7,ofb.ofb14,'N2') 
      CALL ccl_au3_borders_crc(0)     
      LET l_per_ofa01 = ofa.ofa01    
      IF l_g_count = l_count THEN #群組最後一筆
         LET i = i + 1
         LET l_str = 'Total:' , ofa.ofa23 CLIPPED
         CALL ccl_au3_write_rc(i,6,l_str,'S')
         CALL ccl_au3_borders_crc(0)
         CALL ccl_au3_write_rc(i,7,l_sum,'N2')
         CALL ccl_au3_borders_crc(0)
         CALL ccl_au3_write_rcell('A35','SAY TOTAL:','S')
         #call ccl_au3_bold_rcell('A35')
         CALL ccl_au3_bold_crc()
         CALL ccl_au3_write_rcell('B35',l_str1,'S')
         #CALL ccl_au3_mg_rcell('B35','E35')
         CALL ccl_au3_write_rcell('A45','Received By:','S')
         CALL ccl_au3_borders_rcell('B45',4)
         CALL ccl_au3_borders_rcell('C45',4)
         CALL ccl_au3_borders_rcell('E45',4)
         CALL ccl_au3_borders_rcell('F45',4)
         CALL ccl_au3_borders_rcell('G45',4)
         CALL ccl_au3_write_rcell('E46','Authorized Signature','S')
      ELSE 
         LET l_g_count = l_g_count + 1  #下一筆 
      END IF   
   END FOREACH
CALL ccl_au3_close()


範例:4
這是cxmr551改寫為例
    LET g_xls_type = '4'
  LET g_template_name = 'salh581.xls'
  LET g_template_path = '/home/samba/xls'
  LET g_xls_title_rows = 4
  LET g_xls_page_rows = 48
  LET l_p_limit = 22  #每頁限制22筆明細
 # CALL ccl_au3xls_init()
  call ccl_au3xls_initA1()
  LET l_per_ofa01 = 'top'
  FOREACH cxmr551_curs1 INTO ofa.*,ofb.*
     IF SQLCA.sqlcode != 0 THEN
        CALL cl_err('foreach:',SQLCA.sqlcode,1)
        EXIT FOREACH
     END IF

    #檔頭#################
     #########################
     IF l_per_ofa01 <> ofa.ofa01 THEN 
        call ccl_au3xls_initA2(ofa.ofa01) #新xls開始
        let l_per_ofa01 = 'top'
        LET l_g_count = 1
        LET l_count_d = 0
        #業務聯絡人,業務聯絡人分機號碼
        SELECT occ29,occ292,occ261,occ271,occ47 INTO sr.occ29 ,sr.occ292,sr.occ261,sr.occ271,sr.occ47 FROM occ_file
        WHERE occ01 = ofa.ofa03
        #價格條件編號
        SELECT oah02 INTO sr.oah02 FROM oah_file   WHERE oah01 = ofa.ofa31
        DISPLAY ofa.ofa31
        #收款條件編號
        SELECT oag02 INTO sr.oag02 FROM oag_file    WHERE oag01 = ofa.ofa32
        #FCC No,規格,單位使用方式
        SELECT ima138,ima021,ima906 INTO sr.ima138,sr.ima021,sr.ima906 FROM ima_file   WHERE ima01 = ofb.ofb04
        #ofa72(是否列印FCC No.)
        IF ofa.ofa72='N' THEN LET sr.ima138 = "" END IF
        #送貨客戶簡稱11.05.17 by tc
        select occ02 into sr.occ02 FROM occ_file WHERE occ01=sr.oea04
        #運輸方式
        LET l_ged02=''
        select ged02 into l_ged02 FROM ged_file WHERE ged01=ofa.ofa43
        IF ofa.ofa0352 IS NULL THEN #帳款客戶全名
           LET l_ofa0352 = ofa.ofa0353 LET ofa.ofa0353 = ofa.ofa0354   #MOD-850197
           LET ofa.ofa0354 = ofa.ofa0355 LET ofa.ofa0355 = NULL
        ELSE   #MOD-850197
           LET l_ofa0352 = ofa.ofa0352   #MOD-850197
        END IF
        IF ofa.ofa0452 IS NULL THEN #送貨客戶全名
           LET l_ofa0452 = ofa.ofa0453 LET ofa.ofa0453 = ofa.ofa0454   #MOD-850197
           LET ofa.ofa0454 = ofa.ofa0455 LET ofa.ofa0455 = NULL
        ELSE   #MOD-850197
           LET l_ofa0452 = ofa.ofa0452   #MOD-850197
        END IF    
       #2011/3/7 add by csc 取得麥頭
     
       IF cl_null(ofa.ofa44) THEN
          LET l_ocf101 = "" LET l_ocf102 = ""
          LET l_ocf103 = "" LET l_ocf104 = ""        
       ELSE
         SELECT ocf101,ocf102,ocf103,ocf104 INTO l_ocf101,l_ocf102,l_ocf103,l_ocf104
            FROM ocf_file WHERE ocf01 = ofa.ofa03 AND ocf02 = ofa.ofa44
         IF SQLCA.sqlcode = NOTFOUND THEN
            LET l_ocf101 = "" LET l_ocf102 = "" LET l_ocf103 = "" LET l_ocf104 = ""
         END IF      
       END IF
       #2011/3/8 add by csc 取得港口名
       DISPLAY "======================="
       IF NOT cl_null(ofa.ofa41) THEN
          SELECT oac02 INTO l_ofa41t FROM oac_file WHERE oac01 = ofa.ofa41
          IF SQLCA.sqlcode = NOTFOUND THEN
             LET l_ofa41t = ''
          END IF 
       END IF
       IF NOT cl_null(ofa.ofa42) THEN
          SELECT oac02 INTO l_ofa42t FROM oac_file WHERE oac01 = ofa.ofa42
          IF SQLCA.sqlcode = NOTFOUND THEN
             LET l_ofa42t = ''
          END IF
       END IF
       #2011/12/05 add by csc 取得銷售業務員l_oea14
       if not cl_null(ofa.ofa10) then
          select oea14 into l_oea14 from oea_file where oea01 = ofa.ofa16
          if not cl_null(l_oea14) then
             select gen02 into l_gen02 from gen_file where gen01 = l_oea14  
          else
              let l_gen02= ''
          end if 
       else
          let l_gen02 = ''
       end if         
       #####################
       #檔尾
       #計算總金額及數量
       LET l_count = 0
       LET l_sum = 0
       SELECT count(*),SUM(ofb14) INTO l_count,l_sum FROM ofb_file WHERE ofb01=ofa.ofa01
       CALL cl_say(l_sum,80) RETURNING l_str1,l_str2
       let l_str1 = l_str1 ,' ',l_str2
       #單價、金額、小計小數位數
       SELECT azi03,azi04,azi05 INTO t_azi03,t_azi04,t_azi05
          FROM azi_file WHERE azi01 = ofa.ofa23
       IF cl_null(t_azi03) THEN LET t_azi03 = 0 END IF
       IF cl_null(t_azi04) THEN LET t_azi04 = 0 END IF
       IF cl_null(t_azi05) THEN LET t_azi05 = 0 END IF
       #end
     END IF
      #頭寫excel ,因為同一個單號明細太多時無法放在同一頁因此寫的動作要放在外面
      IF l_per_ofa01 <> ofa.ofa01 or (l_count_d mod l_p_limit = 0 and l_count_d <> 0) THEN
         IF l_per_ofa01 <> 'top' THEN
            CALL ccl_au3_new_page() #插入新頁
         END IF
       CALL ccl_au3_write_rcell('G6',ofa.ofa01,'S')
       CALL ccl_au3_write_rcell('G7',ofa.ofa02,'S')
       call ccl_au3_write_rcell('G15',l_gen02,'S')
       CALL ccl_au3_write_rcell('A8',ofa.ofa0351,'S')
       CALL ccl_au3_write_rcell('A9',ofa.ofa0352,'S')
       CALL ccl_au3_write_rcell('A10',ofa.ofa0353,'S')
       CALL ccl_au3_write_rcell('A11',ofa.ofa0354,'S')
       CALL ccl_au3_write_rcell('A12',ofa.ofa0355,'S')
       CALL ccl_au3_write_rcell('D8',ofa.ofa0452,'S')
       CALL ccl_au3_write_rcell('D9',ofa.ofa0453,'S')
       CALL ccl_au3_write_rcell('D10',ofa.ofa0454,'S')
       CALL ccl_au3_write_rcell('D11',ofa.ofa0455,'S')                                   
       CALL ccl_au3_write_rcell('C18',sr.oag02,'S')
       CALL ccl_au3_write_rcell('E18',sr.oah02,'S')                     
       LET i= g_cur_row + 2   
      END IF       
      #身寫excel
      #客戶訂單單號
      SELECT oea10,oea04 INTO sr.oea10,sr.oea04 FROM oea_file WHERE oea01=ofb.ofb31
      IF cl_null(sr.oea10) THEN LET sr.oea10 = '' END IF    
      LET i= i+ 1
      let l_count_d = l_count_d + 1    #明細筆數計算
      CALL ccl_au3_write_rc(i,1,sr.oea10,'S')
      CALL ccl_au3_borders_crc(0)
      CALL ccl_au3_write_rc(i,2,'','S')
      CALL ccl_au3_borders_crc(5)
      CALL ccl_au3_write_rc(i,3,ofb.ofb04,'S')
      CALL ccl_au3_borders_crc(8)      
      CALL ccl_au3_write_rc(i,4,ofb.ofb12,'N')
      CALL ccl_au3_borders_crc(5)    
      CALL ccl_au3_write_rc(i,5,ofb.ofb05,'S')
      CALL ccl_au3_borders_crc(8)
      CALL ccl_au3_write_rc(i,6,ofb.ofb13,'N4')
      CALL ccl_au3_borders_crc(0)   
      CALL ccl_au3_write_rc(i,7,ofb.ofb14,'N2') 
      CALL ccl_au3_borders_crc(0)     
      LET l_per_ofa01 = ofa.ofa01    
      IF l_g_count = l_count THEN #群組最後一筆
         LET i = i + 1
         LET l_str = 'Total:' , ofa.ofa23 CLIPPED
         CALL ccl_au3_write_rc(i,6,l_str,'S')
         CALL ccl_au3_borders_crc(0)
         CALL ccl_au3_write_rc(i,7,l_sum,'N2')
         CALL ccl_au3_borders_crc(0)
         CALL ccl_au3_write_rcell('A42','SAY TOTAL:','S')
         #call ccl_au3_bold_rcell('A35')
         CALL ccl_au3_bold_crc()
         call ccl_au3_mg_rcell('B42','G42') #2012/07/26 加入合併儲存格
         CALL ccl_au3_write_rcell('B42',l_str1,'S')
         #CALL ccl_au3_mg_rcell('B35','E35')
         CALL ccl_au3_write_rcell('A45','Received By:','S')
         CALL ccl_au3_borders_rcell('B45',4)
         CALL ccl_au3_borders_rcell('C45',4)
         CALL ccl_au3_borders_rcell('E45',4)
         CALL ccl_au3_borders_rcell('F45',4)
         CALL ccl_au3_borders_rcell('G45',4)
         CALL ccl_au3_write_rcell('E46','Authorized Signature','S')
         call ccl_au3_closeA1()           #結束該xls
      ELSE 
         LET l_g_count = l_g_count + 1  #下一筆 
      END IF   
   END FOREACH

#CALL ccl_au3_close()
CALL ccl_au3_closeA2()
  #結束並產生xls


範例:3  使用了一個技巧
LET l_per_ofa01 = 'top'
FOREACH
#頭尾
IF l_per_ofa01 <> ofa.ofa01 THEN

   #頭:相關
   ....
 
  #尾
   #計算總金額及數量

  

END IF
#寫 xls頭
      IF l_per_ofa01 <> ofa.ofa01 or (l_count_d mod l_p_limit = 0 and l_count_d <> 0) THEN
         IF l_per_ofa01 <> 'top' THEN
            CALL ccl_au3_new_page()
      END IF
#寫明細

#寫尾 

END FOREACH