OAOR+ABAP2XLSX 将数据写入指定格式的EXCEL中

*&---------------------------------------------------------------------*
*& Report ZTEST007
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ztest007.

TABLES :acdoca,kna1.

TYPES: BEGIN OF ty_data,
         rldnr     TYPE acdoca-rldnr,
         rbukrs    TYPE acdoca-rbukrs,
         belnr     TYPE acdoca-belnr,
         gjahr     TYPE acdoca-gjahr,
         poper     TYPE acdoca-poper,
         buzei     TYPE acdoca-buzei,
         usnam     TYPE acdoca-usnam,
         cpudt     TYPE bkpf-cpudt,
         stblg     TYPE bkpf-stblg,
         bktxt     TYPE bkpf-bktxt,
         blart     TYPE acdoca-blart,
         kidno     TYPE bseg-kidno,
         wsl       TYPE acdoca-wsl,
         rwcur     TYPE acdoca-rwcur,
         docln     TYPE acdoca-docln,
         bschl     TYPE acdoca-bschl,
         budat     TYPE acdoca-budat,
         racct     TYPE acdoca-racct,
         txt20     TYPE skat-txt20,
         hsl       TYPE acdoca-hsl,
         rhcur     TYPE acdoca-rhcur,
         matnr     TYPE bseg-matnr,
         maktx     TYPE makt-maktx,
         menge     TYPE bseg-menge,
         drcrk     TYPE acdoca-drcrk,
         meins     TYPE bseg-meins,
         kunnr     TYPE kna1-kunnr,
         name1     TYPE kna1-name1,
         land1     TYPE kna1-land1,
         vbund     TYPE kna1-vbund,
         gkont     TYPE bseg-gkont,
         shkzg     TYPE bseg-shkzg,
         prctr     TYPE bseg-prctr,
         ktext     TYPE cepct-ktext,

         zglyf     TYPE char10, "关联与否
         zgb       TYPE char10, "国别
         zdj       TYPE acdoca-hsl, "单价
         zjshj     TYPE acdoca-hsl, "价税合计
         kbetr     TYPE konp-kbetr, "TP价
         zhjje     TYPE acdoca-hsl, "合计金额
         zdjcy     TYPE konp-kbetr, "与SAP跑的单价差异
         zjecy     TYPE acdoca-hsl, "与SAP跑的金额差异

         zzpp      TYPE zfit110-zzpp, "规格
         zcpmc     TYPE zfit110-zcpmc, "品名
         kbetr_kp  TYPE zfit110-kbetr, "税率
         zje       TYPE acdoca-hsl, "金额
         mwsbp     TYPE vbap-mwsbp, "税额
         zjshj_kp  TYPE zfit110-kbetr, "价税合计

         awref     TYPE acdoca-awref,
         zfoms     TYPE vbak-zfoms,
         zmesj     TYPE vbak-zmesj,

         sel       TYPE char1,
         wsl_ybf   TYPE acdoca-wsl,
         rwcur_ybf TYPE acdoca-rwcur,
         vernr     TYPE zfit110_bukrs-vernr,
         xzcs      TYPE i,

       END OF ty_data.

DATA gt_data TYPE TABLE OF ty_data.
DATA lt_data TYPE TABLE OF ty_data.
DATA gs_data TYPE ty_data.

TYPES: BEGIN OF ty_a005,
         vkorg TYPE a005-vkorg,
         kunnr TYPE a005-kunnr,
         matnr TYPE  a005-matnr,
         datab TYPE   a005-datab,
         datbi TYPE   a005-datbi,
         kbetr TYPE   konp-kbetr,
         konwa TYPE   konp-konwa,
         kpein TYPE   konp-kpein,
         kmein TYPE   konp-kmein,
       END OF ty_a005.

DATA lt_a005 TYPE TABLE OF ty_a005 WITH NON-UNIQUE SORTED KEY a1 COMPONENTS vkorg kunnr matnr.

DATA lv_kunnr TYPE kna1-kunnr.

DATA lv_index TYPE sy-index.

DATA lt_zfit110_bukrs TYPE TABLE OF zfit110_bukrs.

DATA lv_budat_high LIKE sy-datum.


SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.

  PARAMETERS :p_rldnr TYPE acdoca-rldnr DEFAULT '0L' .

  SELECT-OPTIONS: s_bukrs FOR acdoca-rbukrs,
                  s_budat FOR acdoca-budat,
                  s_racct FOR acdoca-racct,
                  s_kunnr FOR acdoca-kunnr,
                  s_zglyf FOR kna1-sortl.

SELECTION-SCREEN END OF BLOCK b1.

START-OF-SELECTION.

  DATA:lv_zzpp TYPE ausp-atinn.
  DATA rt_currkey TYPE RANGE OF tcurx-currkey.
  DATA:lv_factor TYPE isoc_factor.
  DATA rt_prctr TYPE RANGE OF acdoca-prctr.
  DATA rs_prctr LIKE LINE OF rt_prctr.


  CLEAR:rs_prctr,rt_prctr[].
  rs_prctr+0(3) = 'IEQ'.
  rs_prctr-low = 'ALGN'.
  APPEND rs_prctr TO rt_prctr.



  SELECT a~rldnr,a~rbukrs,a~usnam,a~gjahr,a~poper,a~belnr,a~blart,a~wsl,a~rwcur,
         a~docln,a~bschl,a~budat,a~racct,a~hsl,a~rhcur,a~kunnr,d~kunnr AS gkont,a~prctr,a~drcrk,
         b~cpudt,b~stblg,b~bktxt,c~kidno,c~matnr,c~menge,c~meins,c~shkzg,a~awref
    FROM acdoca AS a
    INNER JOIN bkpf AS b
    ON  b~bukrs = a~rbukrs
    AND  b~belnr = a~belnr
    AND  b~gjahr = a~gjahr
    INNER JOIN bseg AS c
    ON  c~bukrs = a~rbukrs
    AND  c~belnr = a~belnr
    AND  c~gjahr = a~gjahr
    AND  c~buzei = a~buzei
    LEFT JOIN kna1 AS d
    ON  d~kunnr = a~gkont
    WHERE a~rldnr = @p_rldnr
      AND a~rbukrs IN @s_bukrs
      AND a~budat IN @s_budat
      AND a~racct IN @s_racct
      AND a~kunnr IN @s_kunnr
      AND a~prctr IN @rt_prctr
    INTO CORRESPONDING FIELDS OF TABLE @gt_data
   .

  DELETE gt_data WHERE kunnr IS INITIAL AND gkont IS INITIAL.

  IF gt_data[] IS NOT INITIAL.
    SELECT saknr,txt20,txt50 FROM skat WHERE spras = @sy-langu AND ktopl = 'AA01' INTO TABLE @DATA(lt_skat).    
SELECT matnr,maktx FROM makt WHERE spras = @sy-langu INTO TABLE @DATA(lt_makt).    
SELECT prctr,ktext FROM cepct WHERE spras = @sy-langu AND datbi >= @sy-datum INTO TABLE @DATA(lt_cepct).    

SELECT kunnr,name1,land1 FROM kna1      
FOR ALL ENTRIES IN @gt_data      
WHERE kunnr = @gt_data-kunnr OR kunnr = @gt_data-gkont      
INTO TABLE @DATA(lt_kna1).    

SORT lt_skat BY saknr.    
SORT lt_makt BY matnr.    
SORT lt_kna1 BY kunnr.    
SORT lt_cepct BY prctr.    

DATA(lt_data_dj) = gt_data[].    
SORT lt_data_dj BY rbukrs kunnr matnr budat.    
DELETE ADJACENT DUPLICATES FROM lt_data_dj COMPARING rbukrs kunnr matnr budat.    

"价格查询    
SELECT a005~vkorg,                  a005
~kunnr,                  a005
~matnr,                  a005
~datab,                  a005
~datbi,                  konp
~kbetr,                  konp
~konwa,                  konp
~kpein,                  konp
~kmein      
FROM a005 INNER JOIN konp      
ON a005~knumh EQ konp~knumh      INNER
JOIN @lt_data_dj AS b      
ON a005~vkorg = b~rbukrs      
AND a005~kunnr = b~kunnr      
AND a005~matnr = b~matnr      
AND ( a005~datab <= b~budat AND a005~datbi >= b~budat )      
WHERE  a005~kappl = 'V'        
AND a005~kschl = 'ZA60'
*        AND a005~vkorg = @gt_data-rbukrs
*        AND a005~vtweg = '20'
*        AND a005~kunnr = @gt_data-kunnr
*        AND a005~matnr = @gt_data-matnr
*        AND ( a005~datab <= @gt_data-budat AND a005~datbi >= @gt_data-budat )        
AND konp~loevm_ko = ''      
INTO TABLE @lt_a005.    

SORT lt_a005 BY vkorg kunnr matnr datab datbi.    
DELETE ADJACENT DUPLICATES FROM lt_a005 COMPARING vkorg kunnr matnr datab datbi.    

CALL FUNCTION 'CONVERSION_EXIT_ATINN_INPUT'      
EXPORTING        
input  = 'ZZPP'      
IMPORTING        
output = lv_zzpp.    

SELECT CAST( a~objek AS CHAR( 18 ) ) AS objek,atinn,atwrt      
FROM ausp AS a      
WHERE klart = '001'        
AND atinn = @lv_zzpp    
INTO TABLE @DATA(lt_ausp).    

SORT lt_ausp BY objek.    

SELECT a~vbeln,a~aubel,b~zfoms,c~bstkd,b~zmesj      
FROM vbrp AS a      INNER
JOIN vbak AS b      
ON b~vbeln = a~aubel      INNER
JOIN vbkd AS c      
ON c~vbeln = b~vbeln      
FOR ALL ENTRIES IN @gt_data      
WHERE a~vbeln = @gt_data-awref      
INTO TABLE @DATA(lt_vbrp).    

SORT lt_vbrp BY vbeln.    

SELECT 'I' AS sign ,'EQ' AS optin,currkey AS low      
FROM tcurx      
INTO TABLE @rt_currkey.    

LOOP AT gt_data INTO gs_data.      

IF gs_data-rwcur IN rt_currkey AND rt_currkey[] IS NOT INITIAL.        
CLEAR lv_factor.        
CALL FUNCTION 'CURRENCY_CONVERTING_FACTOR'          
EXPORTING            
currency          = gs_data-rwcur          
IMPORTING            factor            
= lv_factor          
EXCEPTIONS            too_many_decimals
= 1            
OTHERS            = 2.        gs_data
-wsl = gs_data-wsl * lv_factor.        gs_data
-hsl = gs_data-hsl * lv_factor.      
ENDIF.      

IF gs_data-shkzg = 'H'.        gs_data
-menge = gs_data-menge * -1.      
ENDIF.      

READ TABLE lt_skat INTO DATA(ls_skat) WITH KEY saknr = gs_data-racct BINARY SEARCH.      
IF sy-subrc = 0.        gs_data
-txt20 = ls_skat-txt20.      
ENDIF.      

READ TABLE lt_makt INTO DATA(ls_makt) WITH KEY matnr = gs_data-matnr BINARY SEARCH.      
IF sy-subrc = 0.        gs_data
-maktx = ls_makt-maktx.      
ENDIF.      

IF gs_data-kunnr IS INITIAL AND gs_data-gkont IS NOT INITIAL.        gs_data
-kunnr = gs_data-gkont.      
ENDIF.      

IF gs_data-kunnr IS NOT INITIAL.        
READ TABLE lt_kna1 INTO DATA(ls_kna1) WITH KEY kunnr = gs_data-kunnr BINARY SEARCH.        
IF sy-subrc = 0.          gs_data
-name1 = ls_kna1-name1.          gs_data
-land1 = ls_kna1-land1.        
ENDIF.      
ENDIF.      

READ TABLE lt_cepct INTO DATA(ls_cepct) WITH KEY prctr = gs_data-prctr BINARY SEARCH.      
IF sy-subrc = 0.        gs_data
-ktext = ls_cepct-ktext.      
ENDIF.      

READ TABLE lt_vbrp INTO DATA(ls_vbrp) WITH KEY vbeln = gs_data-awref BINARY SEARCH.      
IF sy-subrc = 0.        gs_data
-zfoms = ls_vbrp-zfoms.        
IF gs_data-zfoms IS INITIAL.          gs_data
-zfoms = ls_vbrp-bstkd.        
ENDIF.        gs_data
-zmesj = ls_vbrp-zmesj.      
ENDIF.      

CASE gs_data-prctr.        
WHEN 'ALGN'.          
READ TABLE lt_ausp INTO DATA(ls_ausp) WITH KEY objek = gs_data-matnr BINARY SEARCH.          
IF sy-subrc = 0.            gs_data
-zzpp = ls_ausp-atwrt.            
TRANSLATE gs_data-zzpp TO UPPER CASE.          
ENDIF.        
WHEN 'DESG'.          gs_data
-zzpp = 'DESG'.        
WHEN 'MOLI'.          
IF gs_data-matnr = '000000000016000153' .            gs_data
-zzpp = 'MOLI'.          
ELSEIF gs_data-matnr = '000000000016000156' .            gs_data
-zzpp = 'MOLI-SOFT'.          
ELSEIF gs_data-matnr = '000000000016000154' .            gs_data
-zzpp = 'MOLI-M'.          
ELSEIF gs_data-matnr = '000000000016000155' .            gs_data
-zzpp = 'MOLI-S'.          
ENDIF.      
ENDCASE.      lv_kunnr

= |{ gs_data-kunnr ALPHA = OUT }|.      
IF strlen( lv_kunnr ) <= 4.        gs_data
-zglyf = '关联方'.        
IF gs_data-menge <> 0.          gs_data
-zdj =  gs_data-wsl / gs_data-menge.        
ENDIF.      
ELSE.        gs_data
-zglyf = '第三方'.      
ENDIF.      

IF gs_data-land1 = 'CN'.        gs_data
-zgb = '国内'.      
ELSE.        gs_data
-zgb = '海外'.      
ENDIF.      gs_data

-zjshj = gs_data-menge * gs_data-zdj.
*      LOOP AT lt_a005 INTO DATA(ls_a005) WHERE vkorg = gs_data-rbukrs
*                                            AND kunnr = gs_data-kunnr
*                                            AND matnr = gs_data-matnr
*                                            AND datab <= gs_data-budat
*                                            AND datbi >= gs_data-budat.
*        gs_data-kbetr = ls_a005-kbetr.
*        EXIT.
*      ENDLOOP.      

LOOP AT lt_a005 INTO DATA(ls_a005) USING KEY a1 WHERE vkorg = gs_data-rbukrs                                                        
AND kunnr = gs_data-kunnr                                                        
AND matnr = gs_data-matnr.        
IF ls_a005-datab <= gs_data-budat AND ls_a005-datbi >= gs_data-budat.          gs_data
-kbetr = ls_a005-kbetr.          
EXIT.        
ENDIF.      
ENDLOOP.

*      CLEAR lv_index.
*      READ TABLE lt_a005 TRANSPORTING NO FIELDS WITH KEY vkorg = gs_data-rbukrs
*                                                     kunnr = gs_data-kunnr
*                                                     matnr = gs_data-matnr BINARY SEARCH.
*      IF sy-subrc = 0.
*        lv_index = sy-tabix.
*        LOOP AT lt_a005 INTO DATA(ls_a005) FROM lv_index.
*          IF ls_a005-vkorg = gs_data-rbukrs
*            AND ls_a005-kunnr = gs_data-kunnr
*            AND ls_a005-matnr = gs_data-matnr
*            AND ls_a005-datab <= gs_data-budat
*            AND ls_a005-datbi >= gs_data-budat.
*
*            gs_data-kbetr = ls_a005-kbetr.
*            EXIT.
*          ENDIF.
*          IF ls_a005-vkorg <> gs_data-rbukrs
*            OR ls_a005-kunnr <> gs_data-kunnr
*            OR ls_a005-matnr <> gs_data-matnr.
*            EXIT.
*          ENDIF.
*        ENDLOOP.
*      ENDIF.      gs_data


-zhjje = gs_data-menge * gs_data-kbetr.      gs_data
-zdjcy = gs_data-zdj - gs_data-kbetr.      gs_data
-zjecy = gs_data-zjshj - gs_data-zhjje.      


MODIFY gt_data FROM gs_data.    
ENDLOOP.  
ENDIF.  

IF s_zglyf[] IS NOT INITIAL.    
DELETE gt_data WHERE zglyf NOT IN s_zglyf.  
ENDIF.  lt_data[]


= gt_data[].  
DELETE lt_data WHERE zglyf <> '关联方'.  
DELETE lt_data WHERE kunnr+0(8) = '00000086'.  
CLEAR gt_data[].  

SELECT a~gjahr,a~poper,a~rbukrs,a~kunnr,a~name1,         
SUM( CASE WHEN a~matnr <> '000000000010009001' THEN a~menge ELSE 0 END ) AS menge,         
SUM( CASE WHEN a~matnr <> '000000000010009001' THEN a~wsl ELSE 0 END ) AS zje,         
SUM( CASE WHEN a~matnr = '000000000010009001' THEN a~wsl ELSE 0 END ) AS wsl_ybf,         
SUM( a~wsl ) AS zhjje    
FROM @lt_data AS a    
GROUP BY a~gjahr,a~poper,a~rbukrs,a~kunnr,a~name1    
INTO CORRESPONDING FIELDS OF TABLE @gt_data    
.  

SELECT * FROM zfit110_bukrs INTO TABLE @lt_zfit110_bukrs.  
IF gt_data[] IS NOT INITIAL.    
SELECT * FROM zfit110_log      
FOR ALL ENTRIES IN @gt_data      
WHERE gjahr = @gt_data-gjahr        
AND poper = @gt_data-poper        
AND bukrs = @gt_data-rbukrs        
AND kunnr = @gt_data-kunnr    
INTO TABLE @DATA(lt_zfit110_log).  
ENDIF.  

LOOP AT gt_data INTO gs_data.    

LOOP AT lt_data INTO DATA(ls_data) WHERE gjahr = gs_data-gjahr AND poper = gs_data-poper                                    
AND rbukrs = gs_data-rbukrs AND kunnr = gs_data-kunnr                                    
AND matnr <> '000000000010009001'.      gs_data
-rwcur = ls_data-rwcur.      
EXIT.    
ENDLOOP.    

READ TABLE lt_data INTO ls_data WITH KEY gjahr = gs_data-gjahr poper = gs_data-poper                                             rbukrs
= gs_data-rbukrs kunnr = gs_data-kunnr                                             matnr
= '000000000010009001'.    
IF sy-subrc = 0.      gs_data
-rwcur_ybf = ls_data-rwcur.    
ENDIF.    

READ TABLE lt_zfit110_bukrs INTO DATA(ls_zfit110_bukrs) WITH KEY bukrs = gs_data-rbukrs.    
IF sy-subrc = 0.      gs_data
-vernr = ls_zfit110_bukrs-vernr.    
ENDIF.    

READ TABLE lt_zfit110_log INTO DATA(ls_zfit110_log) WITH KEY gjahr = gs_data-gjahr poper = gs_data-poper                                                                 bukrs
= gs_data-rbukrs kunnr = gs_data-kunnr.    
IF sy-subrc = 0.      gs_data
-xzcs = ls_zfit110_log-xzcs.    
ENDIF.    gs_data

-zje = gs_data-zje * -1.    gs_data
-wsl_ybf = gs_data-wsl_ybf * -1.    gs_data
-zhjje = gs_data-zhjje * -1.    gs_data
-menge = abs( gs_data-menge ).    
IF gs_data-menge <> 0.      gs_data
-zdj = gs_data-zje / gs_data-menge.    
ENDIF.    gs_data
-zdj = abs( gs_data-zdj ).    gs_data

-kunnr = |{ gs_data-kunnr ALPHA = OUT }|.    

MODIFY gt_data FROM gs_data.  
ENDLOOP.  
SORT gt_data BY gjahr poper rbukrs kunnr.  

CHECK gt_data[] IS NOT INITIAL.  


DATA lv_budat LIKE sy-datum.  
DATA lv_budat_d LIKE sy-datum.  

DATA lv_str TYPE string.  
DATA lv_str_menge TYPE string.  
DATA lv_str_zhjje TYPE string.  
DATA lv_str_zje TYPE string.  
DATA lv_str_wsl_ybf TYPE string.  

DATA lv_bank_account TYPE char100.  
DATA lv_bank_account1 TYPE char100.  


CLEAR gs_data.  
READ TABLE gt_data INTO gs_data INDEX 1.  gs_data
-kunnr = |{ gs_data-kunnr ALPHA = IN }|.  


READ TABLE lt_zfit110_bukrs INTO ls_zfit110_bukrs WITH KEY bukrs = gs_data-rbukrs.  

READ TABLE lt_zfit110_bukrs INTO DATA(ls_zfit110_kunnr) WITH KEY bukrs = gs_data-kunnr+6(4).  lv_budat

= s_budat-low.  
CALL FUNCTION 'LAST_DAY_OF_MONTHS'    
EXPORTING      day_in            
= lv_budat    
IMPORTING      last_day_of_month
= lv_budat    
EXCEPTIONS      day_in_no_date    
= 1      
OTHERS            = 2.  lv_budat_d
= lv_budat + 90.  lv_str

= |Aligners Supply from { ls_zfit110_bukrs-rebpxname } to { ls_zfit110_kunnr-rebpxname }, including:|.  lv_str_menge

= abs( gs_data-menge ).  
CONDENSE lv_str_menge.  
IF gs_data-menge < 0.    lv_str_menge
= '-' && lv_str_menge.  
ENDIF.  lv_str_zhjje
= abs( gs_data-zhjje ).  
CONDENSE lv_str_zhjje.  
IF gs_data-zhjje < 0.    lv_str_zhjje
= '-' && lv_str_zhjje.  
ENDIF.  lv_str_zje
= abs( gs_data-zje ).  
CONDENSE lv_str_zje.  
IF gs_data-zje < 0.    lv_str_zje
= '-' && lv_str_zje.  
ENDIF.  lv_str_wsl_ybf
= abs( gs_data-wsl_ybf ).  
CONDENSE lv_str_wsl_ybf.  
IF gs_data-wsl_ybf < 0.    lv_str_wsl_ybf
= '-' && lv_str_wsl_ybf.  
ENDIF.  

IF gs_data-rbukrs = '8601'.    lv_bank_account
= 'Bank Account-1 (USD):'.    lv_bank_account1
= 'Bank Account-2 (USD):'.  
ELSEIF gs_data-rbukrs = '8608'.    lv_bank_account
= 'Bank Account (USD):'.    lv_bank_account1
= 'Bank Account (CNY):'.  
ELSE.    lv_bank_account
= 'Bank Account:'.  
ENDIF.  

DATA:gt_values TYPE zbcty_excel_values.  
CLEAR : gt_values[].  gt_values

= VALUE #( BASE gt_values        col
= 1  ( row = 3 value = 'Company:' && ls_zfit110_bukrs-butxt )        col
= 2  ( row = 8 value = ls_zfit110_bukrs-butxt )        col
= 2  ( row = 9 value = ls_zfit110_bukrs-taxnumxl )        col
= 2  ( row = 10 value = ls_zfit110_bukrs-country )        col
= 2  ( row = 11 value = ls_zfit110_bukrs-street )        col
= 2  ( row = 15 value = ls_zfit110_bukrs-vernr )        col
= 2  ( row = 16 value = gs_data-rbukrs && gs_data-kunnr+6(4) && gs_data-gjahr && gs_data-poper+1(2) )        col

= 1  ( row = 21 value = gs_data-gjahr && '.' && gs_data-poper+1(2)  )        col
= 3  ( row = 21 value = lv_str  )        col
= 4  ( row = 21 value = gs_data-rwcur  )        col
= 4  ( row = 22 value = gs_data-rwcur  )        col
= 4  ( row = 23 value = gs_data-rwcur_ybf  )        col
= 5  ( row = 22 value = lv_str_menge  )        col
= 6  ( row = 22 value = abs( gs_data-zdj )  )        col
= 7  ( row = 21 value = lv_str_zhjje  )        col
= 7  ( row = 22 value = lv_str_zje  )        col
= 7  ( row = 23 value = lv_str_wsl_ybf  )        col

= 2  ( row = 37 value = ls_zfit110_bukrs-accname  )        col
= 2  ( row = 38 value = ls_zfit110_bukrs-taxnumxl  )        col
= 1  ( row = 40 value = lv_bank_account  )        col
= 2  ( row = 41 value = ls_zfit110_bukrs-payeebanka  )        col
= 2  ( row = 42 value = ls_zfit110_bukrs-bankn  )        col
= 2  ( row = 43 value = ls_zfit110_bukrs-swift  )        col
= 7  ( row = 8 value = ls_zfit110_kunnr-butxt )        col
= 7  ( row = 9 value = ls_zfit110_kunnr-taxnumxl )        col
= 7  ( row = 10 value = ls_zfit110_kunnr-country )        col
= 7  ( row = 11 value = ls_zfit110_kunnr-street )        col
= 7  ( row = 15 value = lv_budat )        col
= 7  ( row = 16 value = lv_budat_d )                     
).  

IF ls_zfit110_bukrs-bankn1 IS NOT INITIAL.    gt_values
= VALUE #( BASE gt_values          col
= 1  ( row = 45 value = lv_bank_account1  )          col
= 1  ( row = 46 value = 'Beneficiary Bank Name'  )          col
= 1  ( row = 47 value = 'Bank Number'  )          col
= 1  ( row = 48 value = 'Swift Code'  )          col
= 2  ( row = 46 value = ls_zfit110_bukrs-payeebanka1  )          col
= 2  ( row = 47 value = ls_zfit110_bukrs-bankn1  )          col
= 2  ( row = 48 value = ls_zfit110_bukrs-swift1  )                       
).  
ENDIF.  




DATA: lv_classname TYPE  sbdst_classname,        lv_classtype
TYPE  sbdst_classtype,        lv_objectkey
TYPE  sbdst_object_key,        lv_templname
TYPE  char50,        lv_sheetname
TYPE  char50.  lv_templname


= 'ZFIR110'.  lv_classname
= 'ZFI'.  lv_classtype
= 'OT'.  lv_objectkey
= 'ZFI'.  

DATA: lcl_bdsdoc_set  TYPE REF TO cl_bds_document_set.  

DATA: lt_signature  TYPE sbdst_signature WITH HEADER LINE,        lt_components
TYPE sbdst_components WITH HEADER LINE,        lt_doc_uris   
TYPE sbdst_uri WITH HEADER LINE,        lt_content    
TYPE sbdst_content WITH HEADER LINE,        lv_vailable   
TYPE i.  lt_signature

-prop_name = 'DESCRIPTION'.  lt_signature
-prop_value = lv_templname.  
APPEND lt_signature.  

CREATE OBJECT lcl_bdsdoc_set.  

CALL METHOD lcl_bdsdoc_set->get_with_table    
EXPORTING      classname  
= lv_classname      classtype  
= lv_classtype      object_key
= lv_objectkey    
CHANGING      content    
= lt_content[]      signature  
= lt_signature[].  

IF sy-subrc <> 0.    
MESSAGE '读取OAOR文档内容失败' TYPE 'E'.    
RETURN.  
ENDIF.  

FREE lcl_bdsdoc_set.  

DATA pv_xstring TYPE xstring.  
DATA lv_length        TYPE i.  

" 将内容表转换为XSTRING（处理多条记录）  
IF lt_content[] IS NOT INITIAL.    
" 计算总长度    
CLEAR lv_length.    
LOOP AT lt_content INTO DATA(ls_content_line).      lv_length
= lv_length + xstrlen( ls_content_line-line ).    
ENDLOOP.    

" 使用标准函数将二进制表合并为XSTRING    
CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'      
EXPORTING        input_length
= lv_length      
IMPORTING        
buffer       = pv_xstring      
TABLES        binary_tab   
= lt_content      
EXCEPTIONS        failed       
= 1        
OTHERS       = 2.    

IF sy-subrc <> 0.      
MESSAGE '合并OAOR内容失败' TYPE 'E'.      
RETURN.    
ENDIF.  
ENDIF.  

DATA: lo_excel  TYPE REF TO zcl_excel,        lo_reader
TYPE REF TO zif_excel_reader,        lo_writer
TYPE REF TO zif_excel_writer,        lo_sheet  
TYPE REF TO zcl_excel_worksheet.  

DATA iv_sheetname TYPE char31.  
DATA iv_startrow TYPE i.  iv_sheetname


= 'Sheet1'.  

" ① 加载模板（保留所有样式、格式）  
CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.  lo_excel
= lo_reader->load( pv_xstring ).  

" ② 获取目标 Sheet  
" 方式1: 按名称获取  
TRY.      lo_sheet
= lo_excel->get_worksheet_by_name( iv_sheetname ).    
CATCH zcx_excel.      
" 方式2: 如果按名称找不到，获取第一个 Sheet      lo_sheet
= lo_excel->get_active_worksheet( ).  
ENDTRY.  


" ④ 写入数据  
DATA: lv_row TYPE zexcel_cell_row,        lv_col
TYPE zexcel_cell_column.  

DATA: lv_value      TYPE zexcel_cell_value,
*      lv_style      TYPE zcl_excel_style,        lv_guid_style
TYPE zexcel_cell_style,        lv_formula    
TYPE zexcel_cell_formula.  

LOOP AT gt_values INTO DATA(gs_values).    lv_row

= gs_values-row.    lv_col
= gs_values-col.    lo_sheet

->get_cell(      
EXPORTING        ip_column  
= lv_col        ip_row     
= lv_row      
IMPORTING        ep_value   
= lv_value
*    ep_style   = lv_style      " 样式 GUID (字符串)        ep_guid     
= lv_guid_style        ep_formula
= lv_formula

*    ep_type    = lv_type    
).lo_sheet

->set_cell( ip_column = lv_col ip_row = lv_row                    ip_value
= gs_values-value                    ip_style
= lv_guid_style                    IP_FORMULA
= lv_formula ).  

ENDLOOP.  






DATA cv_result TYPE xstring.  

" ⑥ 导出为 XSTRING  
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.  cv_result
= lo_writer->write_file( lo_excel ).


*----------------------------------------------------------------------*
* 9. 下载
*----------------------------------------------------------------------*  
DATA: lt_bin  TYPE solix_tab,        lv_size
TYPE i.  lv_size

= xstrlen( cv_result ).  

CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'    
EXPORTING      
buffer        = cv_result    
IMPORTING      output_length
= lv_size    
TABLES      binary_tab    
= lt_bin.  

CALL FUNCTION 'GUI_DOWNLOAD'    
EXPORTING      filename     
= 'C:\temp\ZFIR110_output.xlsx'      filetype     
= 'BIN'      bin_filesize
= lv_size    
TABLES      data_tab     
= lt_bin.  

MESSAGE 'Excel文件已生成' TYPE 'S'.