OLE导出EXCEL 问题处理

需求：

2、资产负债表、利润表导出优化，由于项目公司门店较多，需要增加批量导出功能。按纳税主体维度导出execl文件，输入了几个纳税主体，就生成几个execl文件。

实现：

用程序ZFIR0014XLS  submit  ZFIR0014 ，每次传入一个纳税主体，实现一次导出多个EXCEL

  SORT lt_nszt BY znsztbm.
  DELETE ADJACENT DUPLICATES FROM lt_nszt COMPARING znsztbm.
  LOOP AT lt_nszt.
    CLEAR:rt_nszt[].
    rt_nszt = VALUE #( ( sign = 'I' option = 'EQ'   low = lt_nszt-znsztbm high = '' ) ).
    SUBMIT zfir0014 WITH p_bukrs IN p_bukrs
                    WITH p_tpaye IN rt_nszt
                    WITH p_gjahr IN p_gjahr
                    WITH p_monat = p_monat AND RETURN.
*                    WITH p_rg1   = ''
*                    WITH p_rg2   = 'X'
*                    WITH repfile = repfile
  ENDLOOP.

但是测试过程遇到好几个EXCEL 的奇怪错误，Excel 的错误不熟悉，不知道怎么找原因，需要研究

问题：1. 双击打开EXCEL文件 ，闪一下就没反应了，文档没有打开

2.打开 EXCEL文件，第一次能看到数据， 关闭，再一次代开，EXCEL文件中sheet2的 数据没有了

3.报错EXCEL 被我自己的用户打开了，已经锁定

 

原因:  有两个Excel进程没有关闭， 进程被占用，所以打开任何一个excel文件都会显示  自己正在编辑。

要用到以下语法解决：

 

EXCEL 导出的函数源码  

 函数：Z_SAVE_INITABLE_TO_EXCEL

FUNCTION Z_SAVE_INITABLE_TO_EXCEL.
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     VALUE(IV_TYPES) TYPE  CHAR1
*"     VALUE(IV_VISIBLE) TYPE  I DEFAULT 1
*"     REFERENCE(IV_FILENAME)
*"  TABLES
*"      IT_TABLE STRUCTURE  ZFAGL_EXCEL
*"  EXCEPTIONS
*"      NO_RECORD
*"----------------------------------------------------------------------


  INCLUDE  ole2incl.
  DATA: excel TYPE  ole2_object.   "Excel Object
  DATA: books TYPE ole2_object.    "List of workbooks
  DATA: range TYPE ole2_object.
  DATA: book TYPE ole2_object.     "workbook
  DATA: worksheets TYPE ole2_object.     "workbook
  DATA: sheets TYPE ole2_object.     "workbook
  DATA: cells TYPE ole2_object.     "workbook
  DATA: s1(10) TYPE c,
        s2(10) TYPE c,
        msg(100) TYPE c.
  DEFINE insert_excel_row.
    call method of
    sheets
    'ROWS' = range
    exporting
      #1     = &1.
    call method of range 'INSERT'
    .
  END-OF-DEFINITION.
  DEFINE write_excel_cells.
    call method of
    excel
    'Cells' = cells
    exporting
      #1      = &1
      #2      = &2.
    set property of cells 'VALUE' = &3.
  END-OF-DEFINITION.


  s1 = lines( it_table[] ) .
  IF s1 = 0.
    MESSAGE '没有满足条件的记录！' TYPE 'I' .
    RAISE no_record.
  ENDIF.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
  EXPORTING
    percentage = 0
    text       = '正在处理Excel文件......'
  EXCEPTIONS
    OTHERS     = 1.
*  ********打开EXCEL表************
  CREATE OBJECT excel 'Excel.Application'.
  SET PROPERTY OF excel 'VISIBLE' = 0.
  CALL METHOD OF
  excel
  'WORKBOOKS' = books.
  CALL METHOD OF
  books
  'OPEN' = book
  EXPORTING
    #1     = iv_filename.  "本地文件地址和文件名
*  选择Sheets 2
  CALL METHOD OF
  excel
  'Sheets' = sheets
  EXPORTING
    #1       = 2.
  CALL METHOD OF
  sheets
  'Select'.
*  激活选择的sheets
*  CALL METHOD OF
*      SHEETS
*      'ACTIVATE'.
*  把内表数据写入到 sheet2 中。

  SORT it_table BY zzcode DESCENDING.
  LOOP AT it_table.
    s2 = sy-tabix.
    CONDENSE: s1,s2.
    CONCATENATE  '正在写入EXCEL:' s2 '/' s1  '......' INTO msg.
    CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      percentage = 2
      text       = msg
    EXCEPTIONS
      OTHERS     = 1.
    insert_excel_row sy-tabix.
    write_excel_cells:sy-tabix 1 it_table-zzcode,
    sy-tabix 2 it_table-zzitemcn,
    sy-tabix 3 it_table-ncbtr,
    sy-tabix 4 it_table-qmbtr,
    sy-tabix 5 it_table-butxt,
    sy-tabix 6 it_table-monat,
    sy-tabix 7 it_table-hsldt,
    sy-tabix 8 it_table-hsldt_total,
    sy-tabix 9 it_table-hsldt_before,
    sy-tabix 10 it_table-hsldt_year_total,
    sy-tabix 11 it_table-hsldt_before_year,
    sy-tabix 12 it_table-attrx1,
    sy-tabix 13 it_table-attrx2,
    sy-tabix 14 it_table-attrx3,
    sy-tabix 15 it_table-attr1,
    sy-tabix 16 it_table-attr2,
    sy-tabix 17 it_table-attr3,
    sy-tabix 18 it_table-attr4,
    sy-tabix 19 it_table-attr5,
    sy-tabix 20 it_table-attr6,
    sy-tabix 21 it_table-attr7,
    sy-tabix 22 it_table-attr8,
    sy-tabix 23 it_table-attr9,
    sy-tabix 24 it_table-attr10,
    sy-tabix 25 it_table-attr11,
    sy-tabix 26 it_table-attr12 .
  ENDLOOP.

*  选择Sheets 1
  SET PROPERTY OF excel 'VISIBLE' = iv_visible.
  CALL METHOD OF
  excel
  'Sheets' = sheets
  EXPORTING
    #1       = 1.
  CALL METHOD OF
  sheets
  'Select'.
*  激活选择的sheets
  CALL METHOD OF
  sheets
  'ACTIVATE'.
  SET PROPERTY OF book 'SAVED' = 0.
*  SET PROPERTY OF excel 'Visible' = 1.

  FREE OBJECT book.



ENDFUNCTION.

 修改后代码

  INCLUDE  ole2incl.
  DATA: excel  TYPE ole2_object.   "Excel Object
  DATA: books  TYPE ole2_object.   "List of workbooks
  DATA: sheets TYPE ole2_object.   "workbook
  DATA: book   TYPE ole2_object.   "workbook
  DATA: cells  TYPE ole2_object.   "workbook

  DATA: s1(10)   TYPE c,
        s2(10)   TYPE c,
        msg(100) TYPE c.

  DEFINE write_excel_cells.
    CALL METHOD OF
    excel
    'Cells' = cells
    EXPORTING
      #1      = &1
      #2      = &2.
    SET PROPERTY OF cells 'VALUE' = &3.
  END-OF-DEFINITION.



  s1 = lines( it_table[] ) .
  IF lines( it_table[] ) = 0.
    MESSAGE '没有满足条件的记录！' TYPE 'I' .
    RAISE no_record.
  ENDIF.

  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
    EXPORTING
      percentage = 0
      text       = '正在处理Excel文件......'
    EXCEPTIONS
      OTHERS     = 1.

  CREATE OBJECT excel 'Excel.Application'.
  SET PROPERTY OF excel 'VISIBLE' = 0.

  CALL METHOD OF excel 'WORKBOOKS' = books.

  CALL METHOD OF  books 'OPEN' = book
    EXPORTING
      #1         = iv_filename.  "本地文件地址和文件名

  CALL METHOD OF excel 'Sheets' = sheets
    EXPORTING
        #1       = 2.

*  SET PROPERTY OF sheets 'NAME' = '纳税主体'.

  CALL METHOD OF sheets 'Select'.


  SORT it_table BY zzcode .
  LOOP AT it_table .
    s2 = sy-tabix.
    CONDENSE: s1,s2.
    msg = '正在写入EXCEL:' && s2 &&  '/' && s1 &&  '......'.
    CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
      EXPORTING
        percentage = 2
        text       = msg
      EXCEPTIONS
        OTHERS     = 1.


    write_excel_cells:
          s2 1 it_table-zzcode,
          s2 2 it_table-zzitemcn,
          s2 3 it_table-ncbtr,
          s2 4 it_table-qmbtr,
          s2 5 it_table-butxt,
          s2 6 it_table-monat,
          s2 7 it_table-hsldt,
          s2 8 it_table-hsldt_total,
          s2 9 it_table-hsldt_before,
          s2 10 it_table-hsldt_year_total,
          s2 11 it_table-hsldt_before_year,
          s2 12 it_table-attrx1,
          s2 13 it_table-attrx2,
          s2 14 it_table-attrx3,
          s2 15 it_table-attr1,
          s2 16 it_table-attr2,
          s2 17 it_table-attr3,
          s2 18 it_table-attr4,
          s2 19 it_table-attr5,
          s2 20 it_table-attr6,
          s2 21 it_table-attr7,
          s2 22 it_table-attr8,
          s2 23 it_table-attr9,
          s2 24 it_table-attr10,
          s2 25 it_table-attr11,
          s2 26 it_table-attr12 .


  ENDLOOP.

  CALL METHOD OF book 'Save'.
  CALL METHOD OF book 'close'.
  CALL METHOD OF excel 'quit'.