download to mutiple worksheets

************************************************************************
* Program     : ZRFI140         REL : 4.6c    Author : Jetty jiang   *
* Created     : 16-05-2011        APP : All     Type   : REPORT      *
* Title       : Regent – Trial Balance                               *
* Reference   : ZRFI140                                              *
*----------------------------------------------------------------------*
*------Update list-----------------------------------------------------*
*     16 May 2011     Jetty       the first version
*" M1  2012.03.27     Bruce       Change The Field  Program logic And Selection mode
*  M2  2012.07.26     Daniel       Enh VAS report Trial Balance
*   the latest update: 2012-07-27 18:30
*--------------------------------------------------------------------- *
REPORT  ZRFI140.
INCLUDE OLE2INCL.

tables: itcpo,nast,ekpo,bkpf,bseg,skat,t001f,ZCEN_VAS_MAP.

data: begin of itab occurs 0,
      bukrs like bkpf-bukrs, "m2 add
      alt_acct like ZCEN_VAS_MAP-ALT_ACCT ,
"M1 modify
*      txt20 like SKAT-TXT20,
      txt20 like ZCEN_VAS_MAP-ALT_ACCT_NAME,
"M1 end
      HSLVT like GLT0-HSLVT,
      HSL01 like GLT0-HSL01,
      HSL02 like GLT0-HSL02,
      HSL03 like GLT0-HSL03,
      HSL04 like GLT0-HSL04,
      HSL05 like GLT0-HSL05,
      HSL06 like GLT0-HSL06,
      HSL07 like GLT0-HSL07,
      HSL08 like GLT0-HSL08,
      HSL09 like GLT0-HSL09,
      HSL10 like GLT0-HSL10,
      HSL11 like GLT0-HSL11,
      HSL12 like GLT0-HSL12,
      DSLVT like GLT0-HSLVT,
      DSL01 like GLT0-HSL01,
      DSL02 like GLT0-HSL02,
      DSL03 like GLT0-HSL03,
      DSL04 like GLT0-HSL04,
      DSL05 like GLT0-HSL05,
      DSL06 like GLT0-HSL06,
      DSL07 like GLT0-HSL07,
      DSL08 like GLT0-HSL08,
      DSL09 like GLT0-HSL09,
      DSL10 like GLT0-HSL10,
      DSL11 like GLT0-HSL11,
      DSL12 like GLT0-HSL12,
      CSLVT like GLT0-HSLVT,
      CSL01 like GLT0-HSL01,
      CSL02 like GLT0-HSL02,
      CSL03 like GLT0-HSL03,
      CSL04 like GLT0-HSL04,
      CSL05 like GLT0-HSL05,
      CSL06 like GLT0-HSL06,
      CSL07 like GLT0-HSL07,
      CSL08 like GLT0-HSL08,
      CSL09 like GLT0-HSL09,
      CSL10 like GLT0-HSL10,
      CSL11 like GLT0-HSL11,
      CSL12 like GLT0-HSL12,
      b_open like GLT0-HSLVT,
      b_incud like GLT0-HSLVT,
      b_incuc like GLT0-HSLVT,
      b_result like GLT0-HSLVT,
      F_open(1) type c,
      F_incu(1) type c,
      F_result(1) type c,
      end of itab.
data:begin of it_cen occurs 0,
      alt_acct like ZCEN_VAS_MAP-ALT_ACCT,
      cen_acct like ZCEN_VAS_MAP-CEN_ACCT,
*M1 add
      alt_acct_name like ZCEN_VAS_MAP-ALT_ACCT_NAME,
*M1 end
     end of it_cen.
data:begin of it_glt0 occurs 0,
      bukrs like glt0-bukrs, "M2 add
      racct like GLT0-RACCT,
      hslvt like GLT0-HSLVT,
      hsl01 like GLT0-HSL02,
      hsl02 like GLT0-HSL03,
      hsl03 like GLT0-HSL04,
      hsl04 like GLT0-HSL05,
      hsl05 like GLT0-HSL06,
      hsl06 like GLT0-HSL07,
      hsl07 like GLT0-HSL08,
      hsl08 like GLT0-HSL09,
      hsl09 like GLT0-HSL10,
      hsl10 like GLT0-HSL11,
      hsl11 like GLT0-HSL11,
      HSL12 like GLT0-HSL12,
      HSL13 like GLT0-HSL13,
      HSL14 like GLT0-HSL14,
      HSL15 like GLT0-HSL15,
      HSL16 like GLT0-HSL16,
      DRCRK like GLT0-DRCRK,
    end of it_glt0.
data:begin of it_skat occurs 0,
      txt20 like SKAT-TXT20,
      saknr like SKAT-SAKNR,
     end of it_skat.
data: g_form like t001f-fornr value 'ZFFI028'.    " SAPscript form name
data: ZLOGO(30) TYPE C.
data: lv_zero(10) type c value '0000000000',
      lv_i type i,
      lv_j type i,
      v_open_D type GLT0-HSLVT,
      v_open_C type GLT0-HSLVT,
      v_incu_D type GLT0-HSLVT,
      v_incu_C type GLT0-HSLVT,
      v_result_D type GLT0-HSLVT,
      v_result_C type GLT0-HSLVT.


*M2 add

*TYPES: ty_line(1500) TYPE c.
*DATA: gt_TB_raw  TYPE TABLE OF ty_line, " Contains records for first sheet
*      gt_154_raw  TYPE TABLE OF ty_line, " Contains records for second sheet
*      gt_632_raw  TYPE TABLE OF ty_line, "
*      wa_tab type ty_line.

data wa_count type i.
data type_c(60) type c.
data: begin of gt_TB_raw  occurs 0 ,
         field01 like type_c,
         field02 like type_c,
         field03 like type_c,
         field04 like type_c,
         field05 like type_c,
         field06 like type_c,
         field07 like type_c,
         field08 like type_c,
         field09 like type_c,
         field10 like type_c,
         field11 like type_c,
         field12 like type_c,
         field13 like type_c,
         field14 like type_c,
         field15 like type_c,
         field16 like type_c,
         field17 like type_c,
   end of gt_TB_raw.
data gt_154_raw  like gt_tb_raw occurs 0 with header line.
data gt_632_raw like gt_tb_raw occurs 0 with header line.

************************************************************************
* OLE objects Declarations
************************************************************************
DATA: w_excel TYPE ole2_object,
      w_workbook TYPE ole2_object,
      w_worksheet TYPE ole2_object,
      w_columns  TYPE ole2_object,
      w_column_ent TYPE ole2_object,
      w_cell TYPE ole2_object,
      w_int TYPE ole2_object,
      w_range TYPE ole2_object,


      wa_cell1 type ole2_object,
      wa_cell2 type ole2_object.



************************************************************************
* Data declarations
************************************************************************
DATA: w_deli(1) TYPE c, "Delimiter
      w_hex TYPE x,
      w_rc TYPE i.
************************************************************************
* Field Symbols
************************************************************************
FIELD-SYMBOLS: <fs> .

************************************************************************
* Constants
************************************************************************
CONSTANTS wl_c09(2) TYPE n VALUE 09.

************************************************************************



*M2 end


selection-screen begin of block 1 with frame title text-001.
"M1  delete default
*    select-options: s_bukrs for bkpf-bukrs default '5130' no intervals no-extension obligatory.
    select-options: s_bukrs for bkpf-bukrs no intervals no-extension obligatory.
"M1 end
    select-options: s_monat for bkpf-monat obligatory .
    select-options: s_gjahr for bkpf-gjahr no intervals no-extension obligatory.
    select-options: s_acct for zcen_vas_map-alt_acct no-display. "m2 add
    select-options: s_budat for bkpf-budat no-display. "m2 add
selection-screen end of block 1.
*M2 add
selection-screen  begin of block b1 with frame title text-t01.
selection-screen: begin of line.
parameters c_excel as checkbox .
selection-screen: comment 3(18) text-001.
*    parameter: p_file like rlgrap-filename .
selection-screen end of line.
selection-screen end of block b1.
*m2 end
at selection-screen.
   if s_monat-high is initial.
     s_monat-high = s_monat-low.
   endif.
START-OF-SELECTION.

perform get_export_parm . "M2 add
perform get_154_632. "m2 add
perform get_data.
*M2 add
if c_excel = 'X'.
  perform download_to_excel.
else.
*M2 end.
  perform write_data.
endif.
INCLUDE ZRFI140A. "M2
*&---------------------------------------------------------------------*
*&      Form  get_data
*&---------------------------------------------------------------------*
FORM get_data.
    clear:itab[].

  select alt_acct
         cen_acct
*M1 add
         alt_acct_name
*M1 end
    into table it_cen from ZCEN_VAS_MAP
    where bukrs in s_bukrs and alt_coa = 'ZVN' .


  select  bukrs "m2 add
          racct
          HSLVT
          HSL01
          HSL02
          HSL03
          HSL04
          HSL05
          HSL06
          HSL07
          HSL08
          HSL09
          HSL10
          HSL11
          HSL12
          HSL13
          HSL14
          HSL15
          HSL16
          DRCRK
    into table it_glt0 from GLT0
    where rldnr = '00' and bukrs in s_bukrs and ryear in s_gjahr.


    loop at it_cen.
      loop at it_glt0.
      shift it_glt0-racct left deleting leading '0'.
        if  it_glt0-racct = it_cen-cen_acct.
            itab-alt_acct = it_cen-alt_acct.
*M1 modify
*            itab-txt20    = space.
            itab-txt20    = it_cen-alt_acct_name.
*M1 end
            if it_glt0-drcrk = 'S'.
            itab-bukrs = it_glt0-bukrs. "m2 add
            itab-hslvt    = space.
            itab-hsl01    = space.
            itab-hsl02    = space.
            itab-hsl03    = space.
            itab-hsl04    = space.
            itab-hsl05    = space.
            itab-hsl06    = space.
            itab-hsl07    = space.
            itab-hsl08    = space.
            itab-hsl09    = space.
            itab-hsl10    = space.
            itab-hsl11    = space.
            itab-hsl12    = space.
            itab-dslvt    = it_glt0-hslvt * 100.
            itab-dsl01    = it_glt0-hsl01 * 100.
            itab-dsl02    = it_glt0-hsl02 * 100.
            itab-dsl03    = it_glt0-hsl03 * 100.
            itab-dsl04    = it_glt0-hsl04 * 100.
            itab-dsl05    = it_glt0-hsl05 * 100.
            itab-dsl06    = it_glt0-hsl06 * 100.
            itab-dsl07    = it_glt0-hsl07 * 100.
            itab-dsl08    = it_glt0-hsl08 * 100.
            itab-dsl09    = it_glt0-hsl09 * 100.
            itab-dsl10    = it_glt0-hsl10 * 100.
            itab-dsl11    = it_glt0-hsl11 * 100.
            itab-dsl12    = ( it_glt0-hsl12 + it_glt0-hsl13 + it_glt0-hsl14 + it_glt0-hsl15 + it_glt0-hsl16 ) * 100.
            itab-cslvt    = space.
            itab-csl01    = space.
            itab-csl02    = space.
            itab-csl03    = space.
            itab-csl04    = space.
            itab-csl05    = space.
            itab-csl06    = space.
            itab-csl07    = space.
            itab-csl08    = space.
            itab-csl09    = space.
            itab-csl10    = space.
            itab-csl11    = space.
            itab-csl12    = space.
            else.
            itab-bukrs = it_glt0-bukrs. "m2 add
            itab-hslvt    = space.
            itab-hsl01    = space.
            itab-hsl02    = space.
            itab-hsl03    = space.
            itab-hsl04    = space.
            itab-hsl05    = space.
            itab-hsl06    = space.
            itab-hsl07    = space.
            itab-hsl08    = space.
            itab-hsl09    = space.
            itab-hsl10    = space.
            itab-hsl11    = space.
            itab-hsl12    = space.
            itab-dslvt    = space.
            itab-dsl01    = space.
            itab-dsl02    = space.
            itab-dsl03    = space.
            itab-dsl04    = space.
            itab-dsl05    = space.
            itab-dsl06    = space.
            itab-dsl07    = space.
            itab-dsl08    = space.
            itab-dsl09    = space.
            itab-dsl10    = space.
            itab-dsl11    = space.
            itab-dsl12    = space.
            itab-cslvt    = it_glt0-hslvt * 100.
            itab-csl01    = it_glt0-hsl01 * 100.
            itab-csl02    = it_glt0-hsl02 * 100.
            itab-csl03    = it_glt0-hsl03 * 100.
            itab-csl04    = it_glt0-hsl04 * 100.
            itab-csl05    = it_glt0-hsl05 * 100.
            itab-csl06    = it_glt0-hsl06 * 100.
            itab-csl07    = it_glt0-hsl07 * 100.
            itab-csl08    = it_glt0-hsl08 * 100.
            itab-csl09    = it_glt0-hsl09 * 100.
            itab-csl10    = it_glt0-hsl10 * 100.
            itab-csl11    = it_glt0-hsl11 * 100.
            itab-csl12    = ( it_glt0-hsl12 + it_glt0-hsl13 + it_glt0-hsl14 + it_glt0-hsl15 + it_glt0-hsl16 ) * 100.
            endif.
            itab-b_open   = '0.00'.
            itab-b_incud  = '0.00'.
            itab-b_incuc  = '0.00'.
            itab-b_result = '0.00'.
            itab-F_open   = space.
            itab-F_incu   = space.
            itab-F_result = space.
            collect itab.
          endif.
      endloop.
    endloop.

"M1 delete: the field had change
*   select txt20 saknr into table it_skat from SKAT where ktopl = 'ZVN'.
"M1 end

  data: lv_tabix type i.
        v_open_D = 0.
        v_open_C = 0.
        v_incu_D = 0.
        v_incu_C = 0.
        v_result_D = 0.
        v_result_C = 0.
  loop at itab.
    lv_tabix = sy-tabix.
"M1 delect: the field had change
*    loop at it_skat.
*     shift it_skat-saknr left deleting leading '0'.
*     if
*         it_skat-saknr = itab-alt_acct.
*         itab-txt20 = it_skat-txt20.
*        modify itab index lv_tabix .
*      endif.
*    endloop.
"M1 end

    itab-hslvt = itab-dslvt + itab-cslvt.
    itab-hsl01 = itab-dsl01 + itab-csl01.
    itab-hsl02 = itab-dsl02 + itab-csl02.
    itab-hsl03 = itab-dsl03 + itab-csl03.
    itab-hsl04 = itab-dsl04 + itab-csl04.
    itab-hsl05 = itab-dsl05 + itab-csl05.
    itab-hsl06 = itab-dsl06 + itab-csl06.
    itab-hsl07 = itab-dsl07 + itab-csl07.
    itab-hsl08 = itab-dsl08 + itab-csl08.
    itab-hsl09 = itab-dsl09 + itab-csl09.
    itab-hsl10 = itab-dsl10 + itab-csl10.
    itab-hsl11 = itab-dsl11 + itab-csl11.
    itab-hsl12 = itab-dsl12 + itab-csl12.
    modify itab index lv_tabix .

    data:counter(2) type N ,
         v_counter(2) type N,
         counter1(2) type N,
         v_str(8) type c value 'itab-hsl',
         v_strd(8) type c value 'itab-dsl',
         v_strc(8) type c value 'itab-csl',
         v_newstr(10) type c.
         field-symbols: <OB>.

    counter = s_monat-low - 1.
    v_counter = 1.
    itab-b_open = itab-hslvt.
    do counter times.
        concatenate v_str v_counter into v_newstr.
        assign (v_newstr) to <OB>.
        itab-b_open = itab-b_open + <OB>.
        v_counter = v_counter + 1.
    enddo.
    modify itab index lv_tabix .

    counter1 = s_monat-high - s_monat-low + 1.
    v_counter = s_monat-low.
    itab-b_incud = 0.
    itab-b_incuc = 0.
    do counter1 times.
       concatenate v_strd v_counter into v_newstr.
        assign (v_newstr) to <OB>.
        itab-b_incud = itab-b_incud + <OB>.
        clear:<OB>.
       concatenate v_strc v_counter into v_newstr.
        assign (v_newstr) to <OB>.
        itab-b_incuc = itab-b_incuc + <OB>.
        v_counter = v_counter + 1.
    enddo.
    modify itab index lv_tabix .

    itab-b_result = itab-b_open + itab-b_incud - ABS( itab-b_incuc ).
    modify itab index lv_tabix .

    if itab-b_open >= 0.
      itab-F_open = 'X'.
      v_open_D = v_open_D + itab-b_open.
    else.
      itab-b_open = ABS( itab-b_open ).
      v_open_C = v_open_C + itab-b_open.
    endif.
    modify itab index lv_tabix .

    v_incu_D = v_incu_D + itab-b_incud.
    itab-b_incuc = ABS( itab-b_incuc ).
    v_incu_C = v_incu_C + itab-b_incuc.
    modify itab index lv_tabix .
    if itab-b_result >= 0.
      itab-F_result = 'X'.
      v_result_D = v_result_D + itab-b_result.
    else.
      itab-b_result = ABS( itab-b_result ).
      v_result_C = v_result_C + itab-b_result.
    endif.
    modify itab index lv_tabix .
  endloop.

  read table itab.
    if sy-subrc ne 0.
      message e058(zpp01) with 'No Record selected'.
      exit.
    endif.
  sort itab ASCENDING by alt_acct.

  select single zlogo into zlogo from zlogo
                          where bukrs in s_bukrs
                            and ztype = 'F'.
ENDFORM.                    " get_data
*&---------------------------------------------------------------------*
*&      Form  write_data
*&---------------------------------------------------------------------*
FORM write_data.
    perform open_form.
    perform start_form.
    perform write_content.
    perform end_form.
    perform close_form.
ENDFORM.

*&---------------------------------------------------------------------*
*&      Form  open_form
*&---------------------------------------------------------------------*
FORM open_form.
  call function 'OPEN_FORM'
      exporting
          device = 'PRINTER'
          language = sy-langu
*          language = '1'
          options = itcpo
          dialog = 'X'.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  start_form
*&---------------------------------------------------------------------*
FORM start_form.
  call function 'START_FORM'
      exporting
          form = g_form
*          form = tnapr-fonam
          language = sy-langu.
*          language = '1'.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  start_form
*&---------------------------------------------------------------------*
FORM write_content.
  data: lv_counter type i,
        lv_rowless type i,
        lv_pagecount type i value 20,
        lv_chk type i,
        lv_result type p decimals 2.
  lv_counter = 0.
  call function 'WRITE_FORM'
      exporting
          element = 'DETAIL_BOX'.
  call function 'WRITE_FORM'
      exporting
          element = 'DETAIL_HEADER'.

  loop at itab.
     lv_counter = lv_counter + 1.
      if lv_counter > lv_pagecount.
          lv_result = ( lv_counter - 1 ) / lv_pagecount.
          lv_chk = ( lv_counter - 1 ) / lv_pagecount.
          if lv_result = lv_chk.
              call function 'WRITE_FORM'
                  exporting
                      element = 'DETAIL_HEADER'.
              call function 'WRITE_FORM'
                  exporting
                      element = 'DETAIL_BOX'.
          endif.
      endif.

      call function 'WRITE_FORM'
          exporting
              element = 'DETAIL_MAIN'
              window  = 'MAIN'
              exceptions
                element       = 1
                function      = 2
                type          = 3
                unopened      = 4
                unstarted     = 5
                window        = 6
                others        = 7.
  endloop.

  lv_rowless = 19 - ( lv_counter mod 20 ).
  do lv_rowless times.
  call function 'WRITE_FORM'
      exporting
        element = 'DETAIL_EMPTY'.
  enddo.

  call function 'WRITE_FORM'
      exporting
          element = 'DETAIL_TOTAL'.
  call function 'WRITE_FORM'
      exporting
          element = 'DATE'
          window  = 'DATE'.
  call function 'WRITE_FORM'
      exporting
          element = 'FOOT'
          window  = 'FOOT'.

ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  end_form
*&---------------------------------------------------------------------*
FORM end_form.
  call function 'END_FORM'.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  close_form
*&---------------------------------------------------------------------*
FORM close_form.
   call function 'CLOSE_FORM'.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  GET_EXPORT_PARM
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GET_EXPORT_PARM .
refresh: s_acct , s_budat.
data l_date type sy-datum.

concatenate s_gjahr-low s_monat-high  '01' into l_date.
CALL FUNCTION 'LAST_DAY_OF_MONTHS'
  EXPORTING
      DAY_IN            = l_date
  IMPORTING
      LAST_DAY_OF_MONTH = s_budat-high.

concatenate s_gjahr-low s_monat-low '01' into s_budat-low .
s_budat-option = 'BT'.
s_budat-sign = 'I'.
append s_budat.

s_acct-option = 'EQ'.
s_acct-sign = 'I'.
s_acct-low = '154'.
append s_acct.
s_acct-option = 'EQ'.
s_acct-sign = 'I'.
s_acct-low = '632'.
append s_acct.

*export s_bukrs to memory id 's_bukrs'.
*export s_acct to memory id 's_acct'.
ENDFORM.                    " GET_EXPORT_PARM
*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_TO_EXCEL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM DOWNLOAD_TO_EXCEL .


perform get_154_632_raw.
perform get_TB_data.

perform download_excel_method1.
*perform download_excel_method2.

ENDFORM.                    " DOWNLOAD_TO_EXCEL
*&---------------------------------------------------------------------*
*&      Form  DOWNLOAD_EXCEL_METHOD1
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM DOWNLOAD_EXCEL_METHOD1 .
DATA:EXCEL TYPE OLE2_OBJECT,
     SHEET TYPE OLE2_OBJECT,
     CELL TYPE OLE2_OBJECT,
     WORKBOOK TYPE OLE2_OBJECT.

DATA:VAL1(60) TYPE C.
DATA: R1 TYPE I,
      R2 TYPE I.

field-symbols: <fs>.
data wa_out(14) type c value 'it_out-field??'.
data wa_col(2) type n.

data l_index type i.


*输入文件名参数
*PARAMETERS:FN1 LIKE RLGRAP-FILENAME MEMORY ID M01.
data FN1  LIKE RLGRAP-FILENAME.
fn1 = 'D:\TB_154_632 raw.xls'.
*启动excel
CREATE OBJECT w_EXCEL 'EXCEL.APPLICATION'.

IF SY-SUBRC NE 0 .
  WRITE: / 'it happens error when create EXCEL'.
  STOP.
ENDIF.



CALL METHOD OF w_EXCEL 'WORKBOOKS' = w_WORKBOOK.

SET PROPERTY OF w_excel 'DisplayAlerts' = 0 .

*使excel 可视
SET PROPERTY OF w_EXCEL 'VISIBLE' = 1.

*建立worksheet
SET PROPERTY OF w_EXCEL 'SHEETSINNEWWORKBOOK' = wa_count.          "如果是读取excel文件中的内容 则是直接打开工作簿第一页
CALL METHOD OF w_WORKBOOK 'ADD'.


if gt_tb_raw[] is not initial.
  perform fill_worksheet tables gt_tb_raw
                         using 1 'TB raw'.
  if gt_154_raw[] is not initial.
    perform fill_worksheet tables gt_154_raw
                           using 2 '154 raw'.
    if gt_632_raw[] is not initial.
      perform fill_worksheet tables gt_632_raw
                             using 3 '632 raw'.
    endif.
  else.
    if gt_632_raw[] is not initial.
      perform fill_worksheet tables gt_632_raw
                             using 2 '632 raw'.
    endif.
  endif.
else.
  if gt_154_raw[] is not initial.
    perform fill_worksheet tables gt_154_raw
                           using 1 '154 raw'.
    if gt_632_raw[] is not initial.
      perform fill_worksheet tables gt_632_raw
                             using 2 '632 raw'.
    endif.
  else.
    if gt_632_raw[] is not initial.
      perform fill_worksheet tables gt_632_raw
                             using 1 '632 raw'.
    endif.
  endif.
endif.


* Save the Excel file
GET PROPERTY OF w_excel 'ActiveWorkbook' = w_workbook.
CALL METHOD OF w_workbook 'SAVEAS'
  EXPORTING #1 = FN1.
CALL METHOD OF w_WORKBOOK 'CLOSE'.           "关闭工作区
CALL METHOD OF w_EXCEL 'QUIT'.               " 退出excel
FREE OBJECT: w_worksheet, w_excel ,w_WORKBOOK.
WRITE 'DONE'.                              "退出成功，输出done


ENDFORM.                    " DOWNLOAD_EXCEL_METHOD1
*&---------------------------------------------------------------------*
*&      Form  GET_TB_DATA
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GET_TB_DATA .

  check itab[] is not initial.
  wa_count = wa_count + 1.
  clear:it_out, it_out[].
  it_out-field01 = 'Company code'.
  it_out-field02 = 'From Period'.
  it_out-field03 = 'To Period'.
  it_out-field04 = 'Fiscal Year'.
  it_out-field05 = 'Account Code'.
  it_out-field06 = 'Account Name' .
  it_out-field07 = 'Opening balance' .
  it_out-field08 = 'Movment Dr'.
  it_out-field09 = 'Movement Cr'.
  it_out-field10 = 'Closing balance'.
  append it_out. clear it_out.

  loop at itab.
    it_out-field01 = itab-bukrs .
    it_out-field02 = s_monat-low.
    it_out-field03 = s_monat-high.
    it_out-field04 = s_gjahr-low.
    it_out-field05 = itab-alt_acct.
    it_out-field06 = itab-txt20.
    it_out-field07 = itab-b_open.
    it_out-field08 = itab-b_incud.
    it_out-field09 = itab-b_incuc.
    it_out-field10 = itab-b_result.
    append it_out. clear it_out.
  endloop.

  gt_tb_raw[] = it_out[].
  refresh it_out.




ENDFORM.                    " GET_TB_DATA
*&---------------------------------------------------------------------*
*&      Form  GET_154_632_RAW
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM GET_154_632_RAW .

data l_index type i.

  loop at it_out.
    l_index = sy-tabix.
    if l_index > 1.
      case it_out-field03 .
        when '154'.
          condense: it_out-field07, it_out-field08, it_out-field09.
          append it_out to gt_154_raw.
        when '632'.
          condense: it_out-field07, it_out-field08, it_out-field09.
          append it_out to gt_632_raw.
      endcase.
    endif.
  endloop.

  if gt_154_raw[] is not initial.
    read table it_out index 1.
    move it_out to gt_154_raw.
    insert gt_154_raw index 1.
    wa_count = wa_count + 1.
  endif.
  if gt_632_raw[] is not initial.
    read table it_out index 1.
    move it_out to gt_632_raw.
    insert gt_632_raw index 1.
    wa_count = wa_count + 1.
  endif.


ENDFORM.                    " GET_154_632_RAW
*&---------------------------------------------------------------------*
*&      Form  FILL_WORKSHEET
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_GT_TB_RAW  text
*      -->P_1      text
*      -->P_1877   text
*----------------------------------------------------------------------*
FORM FILL_WORKSHEET  TABLES   p_tab
                     USING    p_sheet type i
                              p_name type string.
DATA:VAL1(60) TYPE C.
DATA: R1 TYPE I,
      R2 TYPE I.

field-symbols: <fs>.
data wa_out(14) type c value 'p_tab-field??'.
data wa_col(2) type n.

data l_index type i.


*--------------------------------------------------------------------------------
data wa_raw like gt_TB_raw.

TYPES: ty_line(1500) TYPE c.
DATA: lt_tab TYPE TABLE OF ty_line,
      wa_tab type ty_line.

*w_deli = cl_abap_char_utilities=>horizontal_tab.

 ASSIGN w_deli TO <fs> TYPE 'X'.
  w_hex = wl_c09.
  <fs> = w_hex.

loop at p_tab into wa_raw.
  CONCATENATE wa_raw-field01 wa_raw-field02 wa_raw-field03 wa_raw-field04 wa_raw-field05
              wa_raw-field06 wa_raw-field07 wa_raw-field08 wa_raw-field09 wa_raw-field10
              wa_raw-field11 wa_raw-field12 wa_raw-field13 wa_raw-field14 wa_raw-field15
              wa_raw-field16 wa_raw-field17
  INTO wa_tab SEPARATED BY w_deli.
  append wa_tab to lt_tab .
endloop.
*

*------------------------------------------------------------------------------------------------------------
"the performance of this method is high
*************** gets the current worksheet
data l_col type i.
describe table lt_tab lines l_col.

CALL METHOD OF w_excel 'WORKSHEETS' = w_worksheet
   EXPORTING
   #1 = p_sheet.

CALL METHOD OF w_worksheet 'Activate' .

GET PROPERTY OF w_excel 'activesheet' = w_worksheet.
*************** sets the name of the worksheet
SET PROPERTY OF w_worksheet 'Name' = p_name.

*********** pass data to sheet
CALL METHOD OF w_worksheet 'Cells' = wa_cell1
EXPORTING
#1 = 1 " starting row of selection
#2 = 1. " starting column of selection
CALL METHOD OF w_worksheet 'Cells' = wa_cell2
EXPORTING
#1 = l_col " ending row of selection
#2 = 17. " ending column of selection

CALL METHOD OF w_worksheet 'Range' = w_range
EXPORTING
#1 = wa_cell1
#2 = wa_cell2.



CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT
IMPORTING
DATA = lt_tab[]
CHANGING
RC = w_RC.

CALL METHOD OF w_RANGE 'Select'.
CALL METHOD OF w_WORKSHEET 'Paste'.
get property of w_worksheet 'ActiveSheet' = w_worksheet.
free object wa_cell1.
free object wa_cell2.

**************************************
*--------------------------------------------------------------------------------------------------------------------




*----------------------------------------------------------------------
*CALL METHOD OF w_excel 'WORKSHEETS' = w_worksheet
*   EXPORTING
*   #1 = p_sheet.
*
*CALL METHOD OF w_worksheet 'Activate' .
*GET PROPERTY OF w_excel 'activesheet' = w_worksheet.
*SET PROPERTY OF w_worksheet 'Name' = p_name.
*
*  CALL METHOD OF w_excel 'Range' = w_range
*    EXPORTING
*    #1 = 'A1'
*    #2 = 'Q1'.
*
*CALL METHOD OF w_range 'INTERIOR' = w_int.
*  SET PROPERTY OF w_int 'ColorIndex' = 6.
*  SET PROPERTY OF w_int 'Pattern' = 1.
*
** Initially unlock all the columns( by default all the columns are locked )
*  CALL METHOD OF w_excel 'Columns' = w_columns.
*  SET PROPERTY OF w_columns 'Locked' = 0.
*
** Locking and formatting first column
*  CALL METHOD OF w_excel 'Columns' = w_columns
*    EXPORTING
*    #1 = 1.
*
*  SET PROPERTY OF w_columns  'Locked' = 1.
*  SET PROPERTY OF w_columns  'NumberFormat' = '@'.
*
*
* "Export the contents in the internal table to the clipboard
*  CALL METHOD cl_gui_frontend_services=>clipboard_export
*    IMPORTING
*      data                 = lt_tab[]
*    CHANGING
*      rc                   = w_rc
*    EXCEPTIONS
*      cntl_error           = 1
*      error_no_gui         = 2
*      not_supported_by_gui = 3
*      OTHERS               = 4.
*
*
* "Paste the contents in the clipboard to the worksheet
*
*  CALL METHOD OF w_worksheet 'Paste'.
*
** Autofit the columns according to the contents
*  CALL METHOD OF w_excel 'Columns' = w_columns.
*  CALL METHOD OF w_columns 'AutoFit'.
*
*  FREE OBJECT: w_columns, w_range.
*  GET PROPERTY OF w_excel 'ActiveSheet' = w_worksheet.
*-------------------------------------------------------------------------------------





*fill cell----------------------------------------------------------
"this performance of method is low
*CALL METHOD OF w_excel 'WORKSHEETS' = w_worksheet
*   EXPORTING
*   #1 = p_sheet.
*
*CALL METHOD OF w_worksheet 'Activate' .
*SET PROPERTY OF w_worksheet 'Name' = p_name.
*
*get property of w_worksheet 'ActiveSheet' = w_worksheet.
*
*  CALL METHOD OF w_excel 'Range' = w_range
*    EXPORTING
*    #1 = 'A1'
*    #2 = 'Q1'.
*
*
*LOOP AT p_tab.
*  R1 = SY-tabix.
*  wa_col = 1.
*  DO 17 TIMES.
**指定单元格，并写入值
*    R2 = SY-INDEX.
*    wa_out+11(2) = wa_col.
*    assign (wa_out) to <fs>.
*    VAL1 = <FS>.
*    CALL METHOD OF w_EXCEL 'CELLS' = w_CELL  EXPORTING #1 = R1  #2 = R2.
*    SET PROPERTY OF w_CELL 'VALUE' = VAL1.
*    wa_col = wa_col + 1.
*  ENDDO.
*ENDLOOP.
*
*UNASSIGN <FS>.

*end fill cell-------------------------------------------------------------


ENDFORM.                    " FILL_WORKSHEET