ABAP2XLSX EXCEL图片签章功能,保存PDF

利用开源项目ABAP2XLSX,做一个签章(放置图片),并保存PDF。

签章功能可以有很多种方法,常见的是OLE调用vba macro。优点:OLE好理解   缺点:OLE速度慢,而且图片需要下载本地电脑,不能后台,重要的是VBA调试难。

SAP将图片下载本地,调用macro,放置签章。然后删除本地文件。

Sub init()
ActiveSheet.Pictures.Insert("C:\财务章.bmp").Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 120#
Selection.ShapeRange.Width = 120#
Selection.ShapeRange.Top = Cells(23, 6).Top
Selection.ShapeRange.Left = Cells(12, 5).Left + 60
Kill ("C:\财务章.bmp")
End Sub

 

Sub PDF()

' 另存PDF,删除EXCEL

spatch = Excel.ThisWorkbook.Path
sname = "D:\PRINTPDF.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sname, OpenAfterPublish:=True

Kill ("C:\test.xlsx")
End Sub

 

上述方法使用简单,完整代码我就不写了。   但是麻烦,每次从SAP服务器下载,填充,删除。数据量大了就会卡,如果开发不细心,后台进程没杀完,EXCEL就会卡住。很不方便。

 

 

 

下面介绍ABAP2XLSX做电子签章

参照ZDEMO_EXCEL15和ZDEMO_EXCEL16,可以大概了解如何使用ABAP2XLSX填充数据和图片。

ABAP2XLSX  EXCEL图片签章功能,保存PDF

  METHOD write_download_excel.

    DATA:lt_mime_data TYPE TABLE OF w3mime,
         ls_key       TYPE wwwdatatab.
    DATA:lv_string  TYPE string,
         lv_xstring TYPE xstring.
*--------------------------------------------------------------------*
*   "ABAP2XLSX
    DATA: lo_excel_writer TYPE REF TO zif_excel_writer,
          lo_worksheet    TYPE REF TO zcl_excel_worksheet,
          lo_drawing      TYPE REF TO zcl_excel_drawing,
          lo_reader       TYPE REF TO zif_excel_reader.
    DATA: ex               TYPE REF TO zcx_excel,
          msg              TYPE string,
          lv_file          TYPE xstring,
          lv_bytecount     TYPE i,
          lt_file_tab      TYPE solix_tab,
          output_path_xlsx TYPE string,
          l_dummy_rc       TYPE i.

*   "write list
*    DATA: highest_column   TYPE zexcel_cell_column,
*          highest_row      TYPE int4,
*          column           TYPE zexcel_cell_column VALUE 1,
*          col_str          TYPE zexcel_cell_column_alpha,
*          row              TYPE int4               VALUE 1,
*          value            TYPE zexcel_cell_value.

*--------------------------------------------------------------------*
*   "picture
    "Load samle image
    DATA: lt_bin     TYPE solix_tab,
          lv_len     TYPE i,
          lv_content TYPE xstring.


*--------------------------------------------------------------------*
*      "Upload EXCEL
*--------------------------------------------------------------------*

*--------------------------------------------------------------------*
*SMW0上传EXCEL

    IF is_path-xlsx_objid IS NOT INITIAL.
      output_path_xlsx = 'C:\SAP\下载文档.xlsx'.

      ls_key-relid ='MI'.
      ls_key-objid = is_path-xlsx_objid.  " 'ZOT005'."模板ID
      CALL FUNCTION 'WWWDATA_IMPORT'
        EXPORTING
          key               = ls_key
        TABLES
          mime              = lt_mime_data
        EXCEPTIONS
          wrong_object_type = 1
          import_error      = 2
          OTHERS            = 3.

      SELECT SINGLE value
          FROM wwwparams
          WHERE relid EQ @ls_key-relid
          AND objid EQ @ls_key-objid
          AND name EQ 'filesize'
          INTO @DATA(lv_para_value).
      lv_len = lv_para_value.
    ENDIF.

*--------------------------------------------------------------------*
*本地上传EXCEL

    IF is_path-local_path IS NOT INITIAL.
      output_path_xlsx = is_path-local_path.

      CALL METHOD cl_gui_frontend_services=>gui_upload
        EXPORTING
          filename   = is_path-local_path
          filetype   = 'BIN'
        IMPORTING
          filelength = lv_len
        CHANGING
          data_tab   = lt_mime_data.
    ENDIF.

*--------------------------------------------------------------------*
*   EXCEL-> XSTRING
*--------------------------------------------------------------------*

    CHECK lt_mime_data IS NOT INITIAL.
    CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
      EXPORTING
        input_length = lv_len
*       FIRST_LINE   = 0
*       LAST_LINE    = 0
      IMPORTING
        buffer       = lv_xstring
      TABLES
        binary_tab   = lt_mime_data
      EXCEPTIONS
        failed       = 1
        OTHERS       = 2.

*--------------------------------------------------------------------*
*   "Write excel
*--------------------------------------------------------------------*
    TRY.
        CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.
        excel = lo_reader->load( i_excel2007   = lv_xstring ).
        lo_worksheet = excel->get_active_worksheet( ).
        lo_worksheet->set_title( 'Sheet1' ).
*        excel = app->zif_excel_reader~load( i_excel2007    = lv_xstring
**                                   i_use_alternate_zip    = VALUE #( )
**                                   iv_zcl_excel_classname = VALUE #( )
*                                        ).

*--------------------------------------------------------------------*
* Cell Value

        LOOP AT it_value INTO DATA(ls_value).
          lo_worksheet->set_cell( ip_column = ls_value-col
                                  ip_row = CONV #( ls_value-row )
                                  ip_value = ls_value-value ).
        ENDLOOP.

*--------------------------------------------------------------------*
* Picture
*
*        lo_drawing = excel->add_new_drawing( ).
*        lo_drawing->set_position( ip_from_row = 3
*                                  ip_from_col = '1' ).
*        ls_key-relid = 'MI'.
*        ls_key-objid = 'SAPLOGO.GIF'.
*        lo_drawing->set_media_www( ip_key = ls_key
*                                   ip_width = 166
*                                   ip_height = 75 ).
*        " assign drawing to the worksheet
*        lo_worksheet->add_drawing( lo_drawing ).


        LOOP AT it_pic INTO DATA(ls_pic).
          IF ls_pic-wid IS INITIAL.
            ls_pic-wid = 150.
          ENDIF.
          IF ls_pic-hei IS INITIAL.
            ls_pic-hei = 150.
          ENDIF.

          IF ls_pic-objid IS NOT INITIAL.
            " create global drawing, set position and media from web repository
            lo_drawing = excel->add_new_drawing( ).
            lo_drawing->set_position( ip_from_row = CONV #( ls_pic-row )
                                      ip_from_col = CONV #( ls_pic-col ) ).
            ls_key-relid = 'MI'.
            ls_key-objid = ls_pic-objid. "'SAPLOGO.GIF'.
            lo_drawing->set_media_www( ip_key = ls_key
                                       ip_width = CONV #( ls_pic-wid )
                                       ip_height = CONV #( ls_pic-hei ) ).

            " assign drawing to the worksheet
            lo_worksheet->add_drawing( lo_drawing ).
          ENDIF.

          IF ls_pic-xstring IS NOT INITIAL.
            " another drawing from a XSTRING read from a file
            lo_drawing = excel->add_new_drawing( ).
            lo_drawing->set_position( ip_from_row = CONV #( ls_pic-row )
                                      ip_from_col = CONV #( ls_pic-col ) ).
            lo_drawing->set_media( ip_media  = ls_pic-xstring
                                   ip_media_type = zcl_excel_drawing=>c_media_type_bmp
                                   ip_width = CONV #( ls_pic-wid )
                                   ip_height = CONV #( ls_pic-hei ) ).

            " assign drawing to the worksheet
            lo_worksheet->add_drawing( lo_drawing ).
          ENDIF.
        ENDLOOP.

*--------------------------------------------------------------------*
*Write LIST-PROCESSING
*
*        lo_worksheet = excel->get_active_worksheet( ).
*        highest_column = lo_worksheet->get_highest_column( ).
*        highest_row    = lo_worksheet->get_highest_row( ).
*
*        WHILE row <= highest_row.
*          WHILE column <= highest_column.
*            col_str = zcl_excel_common=>convert_column2alpha( column ).
*            lo_worksheet->get_cell(
*              EXPORTING
*                ip_column = col_str
*                ip_row    = row
*              IMPORTING
*                ep_value = value
*            ).
*            WRITE: value.
*            column = column + 1.
*          ENDWHILE.
*          WRITE: /.
*          column = 1.
*          row = row + 1.
*        ENDWHILE.

*--------------------------------------------------------------------*
*       "  xstring -> binary
*--------------------------------------------------------------------*
        CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
        lv_file = lo_excel_writer->write_file( excel ).

        " Convert to binary
        CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
          EXPORTING
            buffer        = lv_file
          IMPORTING
            output_length = lv_bytecount
          TABLES
            binary_tab    = lt_file_tab.

*    " This method is only available on AS ABAP > 6.40
*    lt_file_tab = cl_bcs_convert=>xstring_to_solix( iv_xstring  = lv_file ).
*    lv_bytecount = xstrlen( lv_file ).


*--------------------------------------------------------------------*
*       " Save the file
*--------------------------------------------------------------------*

        cl_gui_frontend_services=>gui_download( EXPORTING
                                                          bin_filesize = lv_bytecount
                                                          filename     = output_path_xlsx
                                                          filetype     = 'BIN'
                                                 CHANGING data_tab     = lt_file_tab
                                                 EXCEPTIONS
                                                          file_write_error          = 1
                                                          no_batch                  = 2
                                                          gui_refuse_filetransfer   = 3
                                                          invalid_type              = 4
                                                          no_authority              = 5
                                                          unknown_error             = 6
                                                          header_not_allowed        = 7
                                                          separator_not_allowed     = 8
                                                          filesize_not_allowed      = 9
                                                          header_too_long           = 10
                                                          dp_error_create           = 11
                                                          dp_error_send             = 12
                                                          dp_error_write            = 13
                                                          unknown_dp_error          = 14
                                                          access_denied             = 15
                                                          dp_out_of_memory          = 16
                                                          disk_full                 = 17
                                                          dp_timeout                = 18
                                                          file_not_found            = 19
                                                          dataprovider_exception    = 20
                                                          control_flush_error       = 21
                                                          not_supported_by_gui      = 22
                                                          error_no_gui              = 23
                                                          OTHERS                    = 24 ).
        IF sy-subrc NE 0.
          MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
        ENDIF.


      CATCH zcx_excel INTO ex.    " Exceptions for ABAP2XLSX
        msg = ex->get_text( ).   WRITE: / msg.
        MESSAGE msg TYPE 'E'.
    ENDTRY.


*--------------------------------------------------------------------*
*       " Convert to pdf and Delete xlsx file
*--------------------------------------------------------------------*
    "OLE attenstion: kill windows progress
    DATA: application TYPE ole2_object,
          workbook    TYPE ole2_object,
          sheet       TYPE ole2_object.
    IF iv_pdf IS NOT INITIAL.
      CREATE OBJECT application 'EXCEL.APPLICATION'.
      CALL METHOD OF application 'WORKBOOKS' = workbook.
      CALL METHOD OF workbook 'OPEN'
        EXPORTING
          #1 = output_path_xlsx.
      CALL METHOD OF application 'WORKSHEETS' = sheet
       EXPORTING
       #1 = 1.    "Sheet1
      CALL METHOD OF sheet 'ACTIVATE'.


      output_path_pdf = output_path_xlsx.
      REPLACE '.xlsx' WITH '.pdf' INTO output_path_pdf.
      REPLACE '.XLSX' WITH '.pdf' INTO output_path_pdf.
      CALL METHOD OF sheet 'ExportAsFixedFormat'
        EXPORTING
          #1 = 0
          #2 = output_path_pdf.


      CALL METHOD OF workbook 'CLOSE'.
      CALL METHOD OF application 'QUIT'.
      FREE OBJECT: application,workbook,sheet.

      CALL METHOD cl_gui_frontend_services=>file_exist
        EXPORTING
          file                 = output_path_pdf
        RECEIVING
          result               = DATA(l_res)
        EXCEPTIONS
          cntl_error           = 1
          error_no_gui         = 2
          wrong_parameter      = 3
          not_supported_by_gui = 4
          OTHERS               = 5.
      IF sy-subrc <> 0 OR l_res = space.
        MESSAGE |PDF另存失败| TYPE 'E'.
      ELSE.
        MESSAGE |PDF另存为{ output_path_pdf }| TYPE 'S'.
      ENDIF.


      CALL METHOD cl_gui_frontend_services=>file_delete
        EXPORTING
          filename             = output_path_xlsx
        CHANGING
          rc                   = l_dummy_rc
        EXCEPTIONS
          file_delete_failed   = 1
          cntl_error           = 2
          error_no_gui         = 3
          file_not_found       = 4
          access_denied        = 5
          unknown_error        = 6
          not_supported_by_gui = 7
          wrong_parameter      = 8
          OTHERS               = 9.
      IF sy-subrc <> 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
    ENDIF.
  ENDMETHOD.

支持 SMW0,MIME,本地EXCEL,填充数据和图片。代码有个问题,就是最后一步保存PDF,我还是使用的OLE。

奈何技术有限,解决不了abap转换EXCEL2PDF,只好用OLE来解决了,如果哪位有更好的办法,欢迎留言指出。

 

 写一个简单的上传程序,用来上传签章图片。

ABAP2XLSX  EXCEL图片签章功能,保存PDF

    CALL FUNCTION 'F4_FILENAME'
      EXPORTING
        program_name  = sy-cprog
        dynpro_number = sy-dynnr
      IMPORTING
        file_name     = mv_path.
    CHECK mv_path IS NOT INITIAL.

    CALL METHOD cl_gui_frontend_services=>gui_upload
      EXPORTING
        filename   = CONV #( mv_path )
        filetype   = 'BIN'
      IMPORTING
        filelength = lv_len
      CHANGING
        data_tab   = lt_bin.

    CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
      EXPORTING
        input_length = lv_len
      IMPORTING
        buffer       = lv_content
      TABLES
        binary_tab   = lt_bin
      EXCEPTIONS
        failed       = 1
        OTHERS       = 2.

    MODIFY ztexcel_pic FROM @( VALUE ztexcel_pic( BASE CORRESPONDING #( ms_out ) xstring = lv_content ) ).
    MESSAGE '保存成功' TYPE 'S'.

 

 

调用封装好的类,就可以一步到位填充大批量数据、填充多个图片到一个EXCEL并保存PDF

  CALL METHOD zcl_excel_pdf=>write_download_excel
    EXPORTING
     iv_pdf   = 'X'
     is_path  = VALUE #( local_path = CONV #( p_dest ) )
     it_value = it_value
     it_pic   = VALUE #( ( col = 'D' row = '29' wid = '150' hei = '150' xstring = ls_ztexcel_pic-xstring ) ).

 

 

 

 *-----------------------------------------------------------------------分隔线-------------------------------------------------------------------------------------------------------------*

 因为博客不能上传文件,就把saplink  签章.nugg文本贴上来,有需要的可以自行下载

<?xml version="1.0" encoding="utf-8"?>
<nugget name="ABAP2XLSX_PDF">
 <CLAS CLSNAME="ZCL_EXCEL_PDF" VERSION="1" LANGU="1" DESCRIPT="EXCEL转PDF" CATEGORY="00" EXPOSURE="2" STATE="1" RELEASE="0" CLSFINAL="X" CLSCCINCL="X" FIXPT="X" UNICODE="X" CLSBCCAT="00" WITH_UNIT_TESTS="X" DURATION_TYPE="0 " RISK_LEVEL="0 " ZSAPLINK_PLUGIN_MAJOR_VERSION="0 " ZSAPLINK_PLUGIN_MINOR_VERSION="1 " ZSAPLINK_PLUGIN_BUILD_VERSION="0 " ZSAPLINK_PLUGIN_INFO1="ZSAPLINK_CLASS is part of the main ZSAPLINK project --&gt; This plugin found there instead of ZSAPLINK_PLUGINS projects" ZSAPLINK_PLUGIN_INFO2="SAPLINK homepage: https://www.assembla.com/spaces/saplink/wiki" ZSAPLINK_PLUGIN_INFO3="Download from https://www.assembla.com/code/saplink/subversion/nodes" ZSAPLINK_PLUGIN_INFO4="and navigate to:  trunk -&gt; core -&gt; ZSAPLINK -&gt; CLAS -&gt; ZSAPLINK_CLASS.slnk">
  <localImplementation>*&quot;* use this source file for the definition and implementation of
*&quot;* local helper classes, interface definitions and type
*&quot;* declarations</localImplementation>
  <localTypes>*&quot;* use this source file for any type of declarations (class
*&quot;* definitions, interfaces or type declarations) you need for
*&quot;* components in the private section</localTypes>
  <localMacros>*&quot;* use this source file for any macro definitions you need
*&quot;* in the implementation part of the class</localMacros>
  <localTestClasses>*&quot;* use this source file for your ABAP unit test classes</localTestClasses>
  <attribute CLSNAME="ZCL_EXCEL_PDF" CMPNAME="EXCEL" VERSION="1" LANGU="1" EXPOSURE="0" STATE="1" EDITORDER="1 " ATTDECLTYP="1" ATTEXPVIRT="0" TYPTYPE="3" TYPE="ZCL_EXCEL" SRCROW1="0 " SRCCOLUMN1="0 " SRCROW2="0 " SRCCOLUMN2="0 " TYPESRC_LENG="0 "/>
  <attribute CLSNAME="ZCL_EXCEL_PDF" CMPNAME="OUTPUT_PATH_PDF" VERSION="1" LANGU="1" EXPOSURE="2" STATE="1" EDITORDER="2 " ATTDECLTYP="1" ATTEXPVIRT="0" TYPTYPE="1" TYPE="STRING" SRCROW1="0 " SRCCOLUMN1="0 " SRCROW2="0 " SRCCOLUMN2="0 " TYPESRC_LENG="0 "/>
  <method CLSNAME="ZCL_EXCEL_PDF" CMPNAME="WRITE_DOWNLOAD_EXCEL" VERSION="1" LANGU="1" EXPOSURE="2" STATE="1" EDITORDER="1 " DISPID="0 " MTDTYPE="0" MTDDECLTYP="1" BCMTDCAT="00" BCMTDSYN="0">
   <parameter CLSNAME="ZCL_EXCEL_PDF" CMPNAME="WRITE_DOWNLOAD_EXCEL" SCONAME="IS_PATH" VERSION="1" LANGU="1" DESCRIPT="路径" CMPTYPE="1" MTDTYPE="0" EDITORDER="1 " DISPID="0 " PARDECLTYP="0" PARPASSTYP="0" TYPTYPE="1" TYPE="ZSEXCEL_PATH"/>
   <parameter CLSNAME="ZCL_EXCEL_PDF" CMPNAME="WRITE_DOWNLOAD_EXCEL" SCONAME="IT_VALUE" VERSION="1" LANGU="1" DESCRIPT="单元格赋值" CMPTYPE="1" MTDTYPE="0" EDITORDER="2 " DISPID="0 " PARDECLTYP="0" PARPASSTYP="0" TYPTYPE="1" TYPE="ZTT_EXCEL_VALUE" PAROPTIONL="X"/>
   <parameter CLSNAME="ZCL_EXCEL_PDF" CMPNAME="WRITE_DOWNLOAD_EXCEL" SCONAME="IT_PIC" VERSION="1" LANGU="1" DESCRIPT="图片" CMPTYPE="1" MTDTYPE="0" EDITORDER="3 " DISPID="0 " PARDECLTYP="0" PARPASSTYP="0" TYPTYPE="1" TYPE="ZTT_EXCEL_PIC" PAROPTIONL="X"/>
   <parameter CLSNAME="ZCL_EXCEL_PDF" CMPNAME="WRITE_DOWNLOAD_EXCEL" SCONAME="IV_PDF" VERSION="1" LANGU="1" DESCRIPT="是否转pdf" CMPTYPE="1" MTDTYPE="0" EDITORDER="4 " DISPID="0 " PARDECLTYP="0" PARPASSTYP="0" TYPTYPE="1" TYPE="CHAR01" PAROPTIONL="X"/>
   <source>METHOD write_download_excel.

    DATA:lt_mime_data    TYPE TABLE OF w3mime,
         ls_key          TYPE wwwdatatab.
    DATA:lv_string       TYPE string,
         lv_xstring      TYPE xstring.
*--------------------------------------------------------------------*
*   &quot;ABAP2XLSX
    DATA: lo_excel_writer  TYPE REF TO zif_excel_writer,
          lo_worksheet     TYPE REF TO zcl_excel_worksheet,
          lo_drawing       TYPE REF TO zcl_excel_drawing,
          lo_reader        TYPE REF TO zif_excel_reader.
    DATA: ex               TYPE REF TO zcx_excel,
          msg              TYPE string,
          lv_file          TYPE xstring,
          lv_bytecount     TYPE i,
          lt_file_tab      TYPE solix_tab,
          output_path_xlsx TYPE string,
          l_dummy_rc       TYPE i.

*   &quot;write list
*    DATA: highest_column   TYPE zexcel_cell_column,
*          highest_row      TYPE int4,
*          column           TYPE zexcel_cell_column VALUE 1,
*          col_str          TYPE zexcel_cell_column_alpha,
*          row              TYPE int4               VALUE 1,
*          value            TYPE zexcel_cell_value.

*--------------------------------------------------------------------*
*   &quot;picture
    &quot;Load samle image
    DATA: lt_bin     TYPE solix_tab,
          lv_len     TYPE i,
          lv_content TYPE xstring.


*--------------------------------------------------------------------*
*      &quot;Upload EXCEL
*--------------------------------------------------------------------*

*--------------------------------------------------------------------*
*SMW0上传EXCEL

    IF is_path-xlsx_objid IS NOT INITIAL.
      output_path_xlsx = &apos;C:\SAP\下载文档.xlsx&apos;.

      ls_key-relid =&apos;MI&apos;.
      ls_key-objid = is_path-xlsx_objid.  &quot; &apos;ZOT005&apos;.&quot;模板ID
      CALL FUNCTION &apos;WWWDATA_IMPORT&apos;
        EXPORTING
          key               = ls_key
        TABLES
          mime              = lt_mime_data
        EXCEPTIONS
          wrong_object_type = 1
          import_error      = 2
          OTHERS            = 3.

      SELECT SINGLE value
          FROM wwwparams
          WHERE relid EQ @ls_key-relid
          AND objid EQ @ls_key-objid
          AND name EQ &apos;filesize&apos;
          INTO @DATA(lv_para_value).
      lv_len = lv_para_value.
    ENDIF.

*--------------------------------------------------------------------*
*本地上传EXCEL

    IF is_path-local_path IS NOT INITIAL.
      output_path_xlsx = is_path-local_path.

      CALL METHOD cl_gui_frontend_services=&gt;gui_upload
        EXPORTING
          filename   = is_path-local_path
          filetype   = &apos;BIN&apos;
        IMPORTING
          filelength = lv_len
        CHANGING
          data_tab   = lt_mime_data.
    ENDIF.

*--------------------------------------------------------------------*
*   EXCEL-&gt; XSTRING
*--------------------------------------------------------------------*

    CHECK lt_mime_data IS NOT INITIAL.
    CALL FUNCTION &apos;SCMS_BINARY_TO_XSTRING&apos;
      EXPORTING
        input_length = lv_len
*       FIRST_LINE   = 0
*       LAST_LINE    = 0
      IMPORTING
        buffer       = lv_xstring
      TABLES
        binary_tab   = lt_mime_data
      EXCEPTIONS
        failed       = 1
        OTHERS       = 2.


*--------------------------------------------------------------------*
*   &quot;Write excel
*--------------------------------------------------------------------*
    TRY.
        CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.
        excel = lo_reader-&gt;load( i_excel2007   = lv_xstring ).
        lo_worksheet = excel-&gt;get_active_worksheet( ).
        lo_worksheet-&gt;set_title( &apos;Sheet1&apos; ).
*        excel = app-&gt;zif_excel_reader~load( i_excel2007    = lv_xstring
**                                   i_use_alternate_zip    = VALUE #( )
**                                   iv_zcl_excel_classname = VALUE #( )
*                                        ).

*--------------------------------------------------------------------*
* Cell Value

        LOOP AT it_value INTO DATA(ls_value).
          lo_worksheet-&gt;set_cell( ip_column = ls_value-col
                                  ip_row = CONV #( ls_value-row )
                                  ip_value = ls_value-value ).
        ENDLOOP.

*--------------------------------------------------------------------*
* Picture
*
*        lo_drawing = excel-&gt;add_new_drawing( ).
*        lo_drawing-&gt;set_position( ip_from_row = 3
*                                  ip_from_col = &apos;1&apos; ).
*        ls_key-relid = &apos;MI&apos;.
*        ls_key-objid = &apos;SAPLOGO.GIF&apos;.
*        lo_drawing-&gt;set_media_www( ip_key = ls_key
*                                   ip_width = 166
*                                   ip_height = 75 ).
*        &quot; assign drawing to the worksheet
*        lo_worksheet-&gt;add_drawing( lo_drawing ).



        LOOP AT it_pic INTO DATA(ls_pic).
          IF ls_pic-wid IS INITIAL.
            ls_pic-wid = 150.
          ENDIF.
          IF ls_pic-hei IS INITIAL.
            ls_pic-hei = 150.
          ENDIF.

          IF ls_pic-objid IS NOT INITIAL.
            &quot; create global drawing, set position and media from web repository
            lo_drawing = excel-&gt;add_new_drawing( ).
            lo_drawing-&gt;set_position( ip_from_row = CONV #( ls_pic-row )
                                      ip_from_col = CONV #( ls_pic-col ) ).
            ls_key-relid = &apos;MI&apos;.
            ls_key-objid = ls_pic-objid. &quot;&apos;SAPLOGO.GIF&apos;.
            lo_drawing-&gt;set_media_www( ip_key = ls_key
                                       ip_width = CONV #( ls_pic-wid )
                                       ip_height = CONV #( ls_pic-hei ) ).

            &quot; assign drawing to the worksheet
            lo_worksheet-&gt;add_drawing( lo_drawing ).
          ENDIF.

          IF ls_pic-xstring IS NOT INITIAL.
            &quot; another drawing from a XSTRING read from a file
            lo_drawing = excel-&gt;add_new_drawing( ).
            lo_drawing-&gt;set_position( ip_from_row = CONV #( ls_pic-row )
                                      ip_from_col = CONV #( ls_pic-col ) ).
            lo_drawing-&gt;set_media( ip_media  = ls_pic-xstring
                                   ip_media_type = zcl_excel_drawing=&gt;c_media_type_bmp
                                   ip_width = CONV #( ls_pic-wid )
                                   ip_height = CONV #( ls_pic-hei ) ).

            &quot; assign drawing to the worksheet
            lo_worksheet-&gt;add_drawing( lo_drawing ).
          ENDIF.
        ENDLOOP.

*--------------------------------------------------------------------*
*Write LIST-PROCESSING
*
*        lo_worksheet = excel-&gt;get_active_worksheet( ).
*        highest_column = lo_worksheet-&gt;get_highest_column( ).
*        highest_row    = lo_worksheet-&gt;get_highest_row( ).
*
*        WHILE row &lt;= highest_row.
*          WHILE column &lt;= highest_column.
*            col_str = zcl_excel_common=&gt;convert_column2alpha( column ).
*            lo_worksheet-&gt;get_cell(
*              EXPORTING
*                ip_column = col_str
*                ip_row    = row
*              IMPORTING
*                ep_value = value
*            ).
*            WRITE: value.
*            column = column + 1.
*          ENDWHILE.
*          WRITE: /.
*          column = 1.
*          row = row + 1.
*        ENDWHILE.

*--------------------------------------------------------------------*
*       &quot;  xstring -&gt; binary
*--------------------------------------------------------------------*
        CREATE OBJECT lo_excel_writer TYPE zcl_excel_writer_2007.
        lv_file = lo_excel_writer-&gt;write_file( excel ).

        &quot; Convert to binary
        CALL FUNCTION &apos;SCMS_XSTRING_TO_BINARY&apos;
          EXPORTING
            buffer        = lv_file
          IMPORTING
            output_length = lv_bytecount
          TABLES
            binary_tab    = lt_file_tab.

*    &quot; This method is only available on AS ABAP &gt; 6.40
*    lt_file_tab = cl_bcs_convert=&gt;xstring_to_solix( iv_xstring  = lv_file ).
*    lv_bytecount = xstrlen( lv_file ).


*--------------------------------------------------------------------*
*       &quot; Save the file
*--------------------------------------------------------------------*

        cl_gui_frontend_services=&gt;gui_download( EXPORTING
                                                          bin_filesize = lv_bytecount
                                                          filename     = output_path_xlsx
                                                          filetype     = &apos;BIN&apos;
                                                 CHANGING data_tab     = lt_file_tab
                                                 EXCEPTIONS
                                                          file_write_error          = 1
                                                          no_batch                  = 2
                                                          gui_refuse_filetransfer   = 3
                                                          invalid_type              = 4
                                                          no_authority              = 5
                                                          unknown_error             = 6
                                                          header_not_allowed        = 7
                                                          separator_not_allowed     = 8
                                                          filesize_not_allowed      = 9
                                                          header_too_long           = 10
                                                          dp_error_create           = 11
                                                          dp_error_send             = 12
                                                          dp_error_write            = 13
                                                          unknown_dp_error          = 14
                                                          access_denied             = 15
                                                          dp_out_of_memory          = 16
                                                          disk_full                 = 17
                                                          dp_timeout                = 18
                                                          file_not_found            = 19
                                                          dataprovider_exception    = 20
                                                          control_flush_error       = 21
                                                          not_supported_by_gui      = 22
                                                          error_no_gui              = 23
                                                          OTHERS                    = 24 ).
        IF sy-subrc NE 0.
          MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
                WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
        ENDIF.


      CATCH zcx_excel INTO ex.    &quot; Exceptions for ABAP2XLSX
        msg = ex-&gt;get_text( ).   WRITE: / msg.
        MESSAGE msg TYPE &apos;E&apos;.
    ENDTRY.


*--------------------------------------------------------------------*
*       &quot; Convert to pdf and Delete xlsx file
*--------------------------------------------------------------------*
    &quot;OLE attenstion: kill windows progress
    DATA: application TYPE ole2_object,
          workbook    TYPE ole2_object,
          sheet       TYPE ole2_object.
    IF iv_pdf IS NOT INITIAL.
      CREATE OBJECT application &apos;EXCEL.APPLICATION&apos;.
      CALL METHOD OF application &apos;WORKBOOKS&apos; = workbook.
      CALL METHOD OF workbook &apos;OPEN&apos;
        EXPORTING
          #1 = output_path_xlsx.
      CALL METHOD OF application &apos;WORKSHEETS&apos; = sheet
       EXPORTING
       #1 = 1.    &quot;Sheet1
      CALL METHOD OF sheet &apos;ACTIVATE&apos;.


      output_path_pdf = output_path_xlsx.
      REPLACE &apos;.xlsx&apos; WITH &apos;.pdf&apos; INTO output_path_pdf.
      REPLACE &apos;.XLSX&apos; WITH &apos;.pdf&apos; INTO output_path_pdf.
      CALL METHOD OF sheet &apos;ExportAsFixedFormat&apos;
        EXPORTING
          #1 = 0
          #2 = output_path_pdf.


      CALL METHOD OF workbook &apos;CLOSE&apos;.
      CALL METHOD OF application &apos;QUIT&apos;.
      FREE OBJECT: application,workbook,sheet.

      CALL METHOD cl_gui_frontend_services=&gt;file_exist
        EXPORTING
          file                 = output_path_pdf
        RECEIVING
          result               = DATA(l_res)
        EXCEPTIONS
          cntl_error           = 1
          error_no_gui         = 2
          wrong_parameter      = 3
          not_supported_by_gui = 4
          OTHERS               = 5.
      IF sy-subrc &lt;&gt; 0 OR l_res = space.
        MESSAGE |PDF另存失败| TYPE &apos;E&apos;.
      ELSE.
        MESSAGE |PDF另存为{ output_path_pdf }| TYPE &apos;S&apos;.
      ENDIF.


      CALL METHOD cl_gui_frontend_services=&gt;file_delete
        EXPORTING
          filename             = output_path_xlsx
        CHANGING
          rc                   = l_dummy_rc
        EXCEPTIONS
          file_delete_failed   = 1
          cntl_error           = 2
          error_no_gui         = 3
          file_not_found       = 4
          access_denied        = 5
          unknown_error        = 6
          not_supported_by_gui = 7
          wrong_parameter      = 8
          OTHERS               = 9.
      IF sy-subrc &lt;&gt; 0.
        MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
      ENDIF.
    ENDIF.
  ENDMETHOD.</source>
  </method>
 </CLAS>
 <TABL TABNAME="ZSEXCEL_PATH" DDLANGUAGE="1" TABCLASS="INTTAB" DATMIN="0000000000" DATMAX="0000000000" DATAVG="0000000000" DDTEXT="路径" AUTHCLASS="00" PROZPUFF="000" EXCLASS="0">
  <dd09l AS4VERS="0000" SCHFELDANZ="000"/>
  <dd03p TABNAME="ZSEXCEL_PATH" FIELDNAME="XLSX_OBJID" DDLANGUAGE="1" POSITION="0001" ROLLNAME="W3OBJID" ADMINFIELD="0" INTTYPE="C" INTLEN="000080" DOMNAME="SYCHAR40" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="10" SCRLEN2="10" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000040" OUTPUTLEN="000040" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="SAP WWW 网关对象名" SCRTEXT_S="对象名称" SCRTEXT_M="对象名称" DOMNAME3L="SYCHAR40" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_PATH" FIELDNAME="LOCAL_PATH" DDLANGUAGE="1" POSITION="0002" ADMINFIELD="0" INTTYPE="g" INTLEN="000008" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DATATYPE="STRG" LENG="000000" OUTPUTLEN="000000" DECIMALS="000000" MASK="  STRG" MASKLEN="0000" DEPTH="00" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
 </TABL>
 <TABL TABNAME="ZSEXCEL_VALUE" DDLANGUAGE="1" TABCLASS="INTTAB" DATMIN="0000000000" DATMAX="0000000000" DATAVG="0000000000" DDTEXT="structure" AUTHCLASS="00" PROZPUFF="000" EXCLASS="0">
  <dd09l AS4VERS="0000" SCHFELDANZ="000"/>
  <dd03p TABNAME="ZSEXCEL_VALUE" FIELDNAME="ROW" DDLANGUAGE="1" POSITION="0001" ROLLNAME="CHAR4" ADMINFIELD="0" INTTYPE="C" INTLEN="000008" DOMNAME="CHAR4" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000004" OUTPUTLEN="000004" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="未定义范围(可用于补丁级别)" DOMNAME3L="CHAR4" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_VALUE" FIELDNAME="COL" DDLANGUAGE="1" POSITION="0002" ROLLNAME="CHAR4" ADMINFIELD="0" INTTYPE="C" INTLEN="000008" DOMNAME="CHAR4" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000004" OUTPUTLEN="000004" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="未定义范围(可用于补丁级别)" DOMNAME3L="CHAR4" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_VALUE" FIELDNAME="VALUE" DDLANGUAGE="1" POSITION="0003" ROLLNAME="CHAR50" ADMINFIELD="0" INTTYPE="C" INTLEN="000100" DOMNAME="TEXT50" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DTELMASTER="E" DATATYPE="CHAR" LENG="000050" OUTPUTLEN="000050" DECIMALS="000000" LOWERCASE="X" MASK="  CHARE" MASKLEN="0000" DDTEXT="注释" REPTEXT="c" SCRTEXT_S="c" SCRTEXT_M="c" SCRTEXT_L="c" DOMNAME3L="TEXT50" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
 </TABL>
 <TABL TABNAME="ZSEXCEL_PIC" DDLANGUAGE="1" TABCLASS="INTTAB" DATMIN="0000000000" DATMAX="0000000000" DATAVG="0000000000" DDTEXT="structure" AUTHCLASS="00" PROZPUFF="000" EXCLASS="0">
  <dd09l AS4VERS="0000" SCHFELDANZ="000"/>
  <dd03p TABNAME="ZSEXCEL_PIC" FIELDNAME="ROW" DDLANGUAGE="1" POSITION="0001" ROLLNAME="CHAR4" ADMINFIELD="0" INTTYPE="C" INTLEN="000008" DOMNAME="CHAR4" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000004" OUTPUTLEN="000004" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="未定义范围(可用于补丁级别)" DOMNAME3L="CHAR4" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_PIC" FIELDNAME="COL" DDLANGUAGE="1" POSITION="0002" ROLLNAME="CHAR4" ADMINFIELD="0" INTTYPE="C" INTLEN="000008" DOMNAME="CHAR4" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000004" OUTPUTLEN="000004" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="未定义范围(可用于补丁级别)" DOMNAME3L="CHAR4" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_PIC" FIELDNAME="WID" DDLANGUAGE="1" POSITION="0003" ROLLNAME="CHAR4" ADMINFIELD="0" INTTYPE="C" INTLEN="000008" DOMNAME="CHAR4" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000004" OUTPUTLEN="000004" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="未定义范围(可用于补丁级别)" DOMNAME3L="CHAR4" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_PIC" FIELDNAME="HEI" DDLANGUAGE="1" POSITION="0004" ROLLNAME="CHAR4" ADMINFIELD="0" INTTYPE="C" INTLEN="000008" DOMNAME="CHAR4" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="00" SCRLEN2="00" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000004" OUTPUTLEN="000004" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="未定义范围(可用于补丁级别)" DOMNAME3L="CHAR4" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_PIC" FIELDNAME="OBJID" DDLANGUAGE="1" POSITION="0005" ROLLNAME="W3OBJID" ADMINFIELD="0" INTTYPE="C" INTLEN="000080" DOMNAME="SYCHAR40" ROUTPUTLEN="000000" HEADLEN="00" SCRLEN1="10" SCRLEN2="10" SCRLEN3="00" DTELMASTER="D" DATATYPE="CHAR" LENG="000040" OUTPUTLEN="000040" DECIMALS="000000" MASK="  CHARE" MASKLEN="0000" DDTEXT="SAP WWW 网关对象名" SCRTEXT_S="对象名称" SCRTEXT_M="对象名称" DOMNAME3L="SYCHAR40" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
  <dd03p TABNAME="ZSEXCEL_PIC" FIELDNAME="XSTRING" DDLANGUAGE="1" POSITION="0006" ROLLNAME="DPR_TV_EDMS_CONTENT" ADMINFIELD="0" INTTYPE="y" INTLEN="000008" ROUTPUTLEN="000000" HEADLEN="32" SCRLEN1="10" SCRLEN2="15" SCRLEN3="20" DTELMASTER="E" DATATYPE="RSTR" LENG="000000" OUTPUTLEN="000000" DECIMALS="000000" MASK="  RSTRE" MASKLEN="0000" DDTEXT="文件内容" REPTEXT="内容" SCRTEXT_S="内容" SCRTEXT_M="内容" SCRTEXT_L="内容" DEPTH="00" COMPTYPE="E" EXCLASS="0" DBPOSITION="0000" OUTPUTSTYLE="00" STRORLOCPOS="00"/>
 </TABL>
 <TTYP TYPENAME="ZTT_EXCEL_VALUE" DDLANGUAGE="1" ROWTYPE="ZSEXCEL_VALUE" ROWKIND="S" DATATYPE="STRU" LENG="000000" DECIMALS="000000" ACCESSMODE="T" KEYDEF="D" KEYKIND="N" KEYFDCOUNT="0000" DDTEXT="tt_value" TYPELEN="000116" CTLENG="000000" CTDECIMALS="000000" OCCURS="00000"/>
 <TTYP TYPENAME="ZTT_EXCEL_PIC" DDLANGUAGE="1" ROWTYPE="ZSEXCEL_PIC" ROWKIND="S" DATATYPE="STRU" LENG="000000" DECIMALS="000000" ACCESSMODE="T" KEYDEF="D" KEYKIND="N" KEYFDCOUNT="0000" DDTEXT="图片" TYPELEN="000120" CTLENG="000000" CTDECIMALS="000000" OCCURS="00000"/>
</nugget>

 

上一篇:dubbo接口调用方式1_telnet调用


下一篇:npm源切换:从别的源切换回npm源