ABAP2XLSXEXCEL图⽚签章功能，保存PDF
利⽤开源项⽬ABAP2XLSX，做⼀个签章（放置图⽚），并保存PDF。
签章功能可以有很多种⽅法，常见的是OLE调⽤vba macro。优点：OLE好理解缺点：OLE速度慢，⽽且图⽚需要下载本地电脑，不能后台，重要的是VBA调试难。
SAP将图⽚下载本地，调⽤macro，放置签章。然后删除本地⽂件。 vba如下：
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
typec转dpspatch = 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填充数据和图⽚。
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 NE0.
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 <> 0OR 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来解决了，如果哪位有更好的办法，欢迎留⾔指出。
写⼀个简单的上传程序，⽤来上传签章图⽚。
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
<localImplementation>*"* use this source file for the definition and implementation of
*"* local helper classes, interface definitions and type
*"* declarations</localImplementation>
<localTypes>*"* use this source file for any type of declarations (class
*"* definitions, interfaces or type declarations) you need for
*"* components in the private section</localTypes>
<localMacros>*"* use this source file for any macro definitions you need
*"* in the implementation part of the class</localMacros>
<localTestClasses>*"* 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
<attribute CLSNAME="ZCL_EXCEL_PDF" CMPNAME="OUTPUT_PATH_PDF" VERSION="1" LANG
U="1" EXPOSURE="2" STATE="1" EDITORDER="2 " ATTDECLTYP="1" ATTEXPVIRT="0" TYPTYPE="1" TYPE="STRING"
<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"
<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 <parameter CLSNAME="ZCL_EXCEL_PDF" CMPNAME="WRITE_DOWNLOAD_EXCEL" SCONAME="IT_VALUE" VERSION="1" LANGU="1" DESCRIPT="单元格赋值" CMPTYPE="1" MTDTYPE="0" EDITORDER="2 " DISPID
<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 <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 "
<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.
*--------------------------------------------------------------------*
*  "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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。