【IT168 技术文章】
我们在sap上限初期经常会将EXCEL数据倒入到SAP，sap也为此封装了许多的函数，但是这些函数都有单元文本长度限制。具体介绍如下：
1、 'TEXT_CONVERT_XLS_TO_SAP'单元格文本不能超过256个字符
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = 'X'
i_tab_raw_data = lt_raw
i_filename = p_file
TABLES
i_tab_converted_data = it_data
EXCEPTIONS
conversion_failed = 1
OTHERS = 2
.EXPORTING
i_line_header = 'X'
i_tab_raw_data = lt_raw
i_filename = p_file
TABLES
i_tab_converted_data = it_data
EXCEPTIONS
conversion_failed = 1
OTHERS = 2
2、'ALSM_EXCEL_TO_INTERNAL_TABLE'单元格文本不能超过50个字符
data: gt_upload type alsmex_tabline occurs 0 with header line.
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = p_file
i_begin_col = '1'
i_begin_row = '1'
i_end_col = '9'
i_end_row = '50000'
tables
intern = gt_upload.
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = p_file
i_begin_col = '1'
i_begin_row = '1'
i_end_col = '9'
i_end_row = '50000'
tables
intern = gt_upload.
3、自编程序倒入EXCEL，单元格文本长度可以自定义，代码如下（实现的功能和'ALSM_EXCEL_TO_INTERNAL_TABLE类似）：
REPORT RSDEMO01 NO STANDARD PAGE HEADING.
* this report demonstrates how to send some ABAP data to an
* EXCEL sheet using OLE automation.
TYPE-POOLS: ole2.
**TYPE UPLOAD TABLE
TYPES: BEGIN OF ZLSMEX_TABLINE,
row TYPE KCD_EX_ROW_N,
COL TYPE KCD_EX_COL_N,
VALUE(500),
END OF ZLSMEX_TABLINE.
DATA gt_upload type TABLE OF ZLSMEX_TABLINE WITH HEADER LINE.
* value of excel-cell
TYPES: ty_d_itabvalue TYPE ZLSMEX_TABLINE-value,
* internal table containing the excel data
ty_t_itab TYPE ZLSMEX_TABLINE OCCURS 0,
* line type of sender table
BEGIN OF ty_s_senderline,
line(4096) TYPE c,
END OF ty_s_senderline,
* sender table
ty_t_sender TYPE ty_s_senderline OCCURS 0.
CONSTANTS: gc_esc VALUE '"'.
* handles for OLE objects
DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object
workbooks TYPE OLE2_OBJECT, " list of workbooks
workbook TYPE OLE2_OBJECT, " workbook
worksheet TYPE ole2_object,
H_CELL TYPE OLE2_OBJECT, " cell
H_F TYPE OLE2_OBJECT. " font
DATA H TYPE I.
*----------------------------------------------------------------------*
* Type Declaration
*----------------------------------------------------------------------*
TYPE-POOLS: slis, truxs.
PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY.
DEFINE m_message.
case sy-subrc.
when 0.
when 1.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
when others. raise upload_ole.
endcase.
END-OF-DEFINITION.
*&---------------------------------------------------------------------*
*& Form SEPARATED_TO_INTERN_CONVERT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM separated_to_intern_convert TABLES i_tab TYPE ty_t_sender
i_intern TYPE ty_t_itab
USING i_separator TYPE c.
DATA: l_sic_tabix LIKE sy-tabix,
l_sic_col TYPE kcd_ex_col.
DATA: l_fdpos LIKE sy-fdpos.
REFRESH i_intern.
LOOP AT i_tab.
l_sic_tabix = sy-tabix.
l_sic_col = 0.
WHILE i_tab CA i_separator.
l_fdpos = sy-fdpos.
l_sic_col = l_sic_col + 1.
PERFORM line_to_cell_separat TABLES i_intern
USING i_tab l_sic_tabix l_sic_col
i_separator l_fdpos.
ENDWHILE.
IF i_tab <> space.
CLEAR i_intern.
i_intern-row = l_sic_tabix.
i_intern-col = l_sic_col + 1.
i_intern-value = i_tab.
APPEND i_intern.
ENDIF.
ENDLOOP.
ENDFORM. " SEPARATED_TO_INTERN_CONVERT
*---------------------------------------------------------------------*
FORM line_to_cell_separat TABLES i_intern TYPE ty_t_itab
USING i_line
i_row LIKE sy-tabix
ch_cell_col TYPE kcd_ex_col
i_separator TYPE c
i_fdpos LIKE sy-fdpos.
DATA: l_string TYPE ty_s_senderline.
DATA l_sic_int TYPE i.
CLEAR i_intern.
l_sic_int = i_fdpos.
i_intern-row = i_row.
l_string = i_line.
i_intern-col = ch_cell_col.
* csv Dateien mit separator in Zelle: --> ;"abc;cd";
IF ( i_separator = ';' OR i_separator = ',' ) AND
l_string(1) = gc_esc.
PERFORM line_to_cell_esc_sep USING l_string
l_sic_int
i_separator
i_intern-value.
ELSE.
IF l_sic_int > 0.
i_intern-value = i_line(l_sic_int).
ENDIF.
ENDIF.
IF l_sic_int > 0.
APPEND i_intern.
ENDIF.
l_sic_int = l_sic_int + 1.
i_line = i_line+l_sic_int.
ENDFORM. "line_to_cell_separat
*---------------------------------------------------------------------*
FORM line_to_cell_esc_sep USING i_string
i_sic_int TYPE i
i_separator TYPE c
i_intern_value TYPE ty_d_itabvalue.
DATA: l_int TYPE i,
l_cell_end(2).
FIELD-SYMBOLS: <l_cell>.
l_cell_end = gc_esc.
l_cell_end+1 = i_separator .
IF i_string CS gc_esc.
i_string = i_string+1.
IF i_string CS l_cell_end.
l_int = sy-fdpos.
ASSIGN i_string(l_int) TO <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 2.
i_sic_int = l_int.
i_string = i_string+l_int.
ELSEIF i_string CS gc_esc.
* letzte Celle
l_int = sy-fdpos.
ASSIGN i_string(l_int) TO <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 1.
i_sic_int = l_int.
i_string = i_string+l_int.
l_int = strlen( i_string ).
IF l_int > 0 . MESSAGE x001(kx) . ENDIF.
ELSE.
MESSAGE x001(kx) . "was ist mit csv-Format
ENDIF.
ENDIF.
ENDFORM. "line_to_cell_esc_sep
*&---------------------------------------------------------------------*
*& Form read_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM zalsm_excel_to_internal_table
TABLES
INTERN LIKE gt_upload[]
USING
VALUE(FILENAME) LIKE RLGRAP-FILENAME
VALUE(I_BEGIN_COL) TYPE I
VALUE(I_BEGIN_ROW) TYPE I
VALUE(I_END_COL) TYPE I
VALUE(I_END_ROW) TYPE I.
DATA: excel_tab TYPE ty_t_sender.
DATA: ld_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object.
DATA: h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object.
DATA:
ld_rc TYPE i.
* Rückgabewert der Methode "clipboard_export "
* Makro für Fehlerbehandlung der Methods
* check parameters
IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
* Get TAB-sign for separation of fields
CLASS cl_ABAP_char_utilities DEFINITION LOAD.
ld_separator = cl_ABAP_char_utilities=>horizontal_tab.
* open file in Excel
IF application-header = space OR application-handle = -1.
CREATE OBJECT application 'Excel.Application'.
m_message.
ENDIF.
CALL METHOD OF application 'Workbooks' = workbook.
m_message.
CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.
m_message.
* set property of application 'Visible' = 1.
* m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
* mark whole spread sheet
CALL METHOD OF worksheet 'Cells' = h_cell
EXPORTING #1 = i_begin_row #2 = i_begin_col.
m_message.
CALL METHOD OF worksheet 'Cells' = h_cell1
EXPORTING #1 = i_end_row #2 = i_end_col.
m_message.
CALL METHOD OF worksheet 'RANGE' = range
EXPORTING #1 = h_cell #2 = h_cell1.
m_message.
CALL METHOD OF range 'SELECT'.
m_message.
* copy marked area (whole spread sheet) into Clippboard
CALL METHOD OF range 'COPY'.
m_message.
* read clipboard into ABAP
CALL METHOD cl_gui_frontend_services=>clipboard_import
IMPORTING
data = excel_tab
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
IF sy-subrc <> 0.
MESSAGE a037(alsmex).
ENDIF.
PERFORM separated_to_intern_convert TABLES excel_tab intern
USING ld_separator.
* clear clipboard
REFRESH excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
CALL METHOD OF application 'QUIT'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT h_cell. m_message.
FREE OBJECT h_cell1. m_message.
FREE OBJECT range. m_message.
FREE OBJECT worksheet. m_message.
FREE OBJECT workbook. m_message.
FREE OBJECT application. m_message.
* <<<<< End of change note 575877
ENDForm. "zalsm_excel_to_internal_table
*&---------------------------------------------------------------------*
*& Event START-OF-SELECTION
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
mask = ',Microsoft Excel Files,*.xls'
static = 'X'
CHANGING
file_name = p_file.
*&---------------------------------------------------------------------*
*& Event START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
PERFORM ZALSM_EXCEL_TO_INTERNAL_TABLE
TABLES
gt_upload
USING
p_file
1
1
9
5000.
LOOP AT GT_UPLOAD.
WRITE GT_UPLOAD-VALUE.
ENDLOOP.
* this report demonstrates how to send some ABAP data to an
* EXCEL sheet using OLE automation.
TYPE-POOLS: ole2.
**TYPE UPLOAD TABLE
TYPES: BEGIN OF ZLSMEX_TABLINE,
row TYPE KCD_EX_ROW_N,
COL TYPE KCD_EX_COL_N,
VALUE(500),
END OF ZLSMEX_TABLINE.
DATA gt_upload type TABLE OF ZLSMEX_TABLINE WITH HEADER LINE.
* value of excel-cell
TYPES: ty_d_itabvalue TYPE ZLSMEX_TABLINE-value,
* internal table containing the excel data
ty_t_itab TYPE ZLSMEX_TABLINE OCCURS 0,
* line type of sender table
BEGIN OF ty_s_senderline,
line(4096) TYPE c,
END OF ty_s_senderline,
* sender table
ty_t_sender TYPE ty_s_senderline OCCURS 0.
CONSTANTS: gc_esc VALUE '"'.
* handles for OLE objects
DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object
workbooks TYPE OLE2_OBJECT, " list of workbooks
workbook TYPE OLE2_OBJECT, " workbook
worksheet TYPE ole2_object,
H_CELL TYPE OLE2_OBJECT, " cell
H_F TYPE OLE2_OBJECT. " font
DATA H TYPE I.
*----------------------------------------------------------------------*
* Type Declaration
*----------------------------------------------------------------------*
TYPE-POOLS: slis, truxs.
PARAMETERS: p_file LIKE rlgrap-filename OBLIGATORY.
DEFINE m_message.
case sy-subrc.
when 0.
when 1.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
when others. raise upload_ole.
endcase.
END-OF-DEFINITION.
*&---------------------------------------------------------------------*
*& Form SEPARATED_TO_INTERN_CONVERT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM separated_to_intern_convert TABLES i_tab TYPE ty_t_sender
i_intern TYPE ty_t_itab
USING i_separator TYPE c.
DATA: l_sic_tabix LIKE sy-tabix,
l_sic_col TYPE kcd_ex_col.
DATA: l_fdpos LIKE sy-fdpos.
REFRESH i_intern.
LOOP AT i_tab.
l_sic_tabix = sy-tabix.
l_sic_col = 0.
WHILE i_tab CA i_separator.
l_fdpos = sy-fdpos.
l_sic_col = l_sic_col + 1.
PERFORM line_to_cell_separat TABLES i_intern
USING i_tab l_sic_tabix l_sic_col
i_separator l_fdpos.
ENDWHILE.
IF i_tab <> space.
CLEAR i_intern.
i_intern-row = l_sic_tabix.
i_intern-col = l_sic_col + 1.
i_intern-value = i_tab.
APPEND i_intern.
ENDIF.
ENDLOOP.
ENDFORM. " SEPARATED_TO_INTERN_CONVERT
*---------------------------------------------------------------------*
FORM line_to_cell_separat TABLES i_intern TYPE ty_t_itab
USING i_line
i_row LIKE sy-tabix
ch_cell_col TYPE kcd_ex_col
i_separator TYPE c
i_fdpos LIKE sy-fdpos.
DATA: l_string TYPE ty_s_senderline.
DATA l_sic_int TYPE i.
CLEAR i_intern.
l_sic_int = i_fdpos.
i_intern-row = i_row.
l_string = i_line.
i_intern-col = ch_cell_col.
* csv Dateien mit separator in Zelle: --> ;"abc;cd";
IF ( i_separator = ';' OR i_separator = ',' ) AND
l_string(1) = gc_esc.
PERFORM line_to_cell_esc_sep USING l_string
l_sic_int
i_separator
i_intern-value.
ELSE.
IF l_sic_int > 0.
i_intern-value = i_line(l_sic_int).
ENDIF.
ENDIF.
IF l_sic_int > 0.
APPEND i_intern.
ENDIF.
l_sic_int = l_sic_int + 1.
i_line = i_line+l_sic_int.
ENDFORM. "line_to_cell_separat
*---------------------------------------------------------------------*
FORM line_to_cell_esc_sep USING i_string
i_sic_int TYPE i
i_separator TYPE c
i_intern_value TYPE ty_d_itabvalue.
DATA: l_int TYPE i,
l_cell_end(2).
FIELD-SYMBOLS: <l_cell>.
l_cell_end = gc_esc.
l_cell_end+1 = i_separator .
IF i_string CS gc_esc.
i_string = i_string+1.
IF i_string CS l_cell_end.
l_int = sy-fdpos.
ASSIGN i_string(l_int) TO <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 2.
i_sic_int = l_int.
i_string = i_string+l_int.
ELSEIF i_string CS gc_esc.
* letzte Celle
l_int = sy-fdpos.
ASSIGN i_string(l_int) TO <l_cell>.
i_intern_value = <l_cell>.
l_int = l_int + 1.
i_sic_int = l_int.
i_string = i_string+l_int.
l_int = strlen( i_string ).
IF l_int > 0 . MESSAGE x001(kx) . ENDIF.
ELSE.
MESSAGE x001(kx) . "was ist mit csv-Format
ENDIF.
ENDIF.
ENDFORM. "line_to_cell_esc_sep
*&---------------------------------------------------------------------*
*& Form read_excel
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM zalsm_excel_to_internal_table
TABLES
INTERN LIKE gt_upload[]
USING
VALUE(FILENAME) LIKE RLGRAP-FILENAME
VALUE(I_BEGIN_COL) TYPE I
VALUE(I_BEGIN_ROW) TYPE I
VALUE(I_END_COL) TYPE I
VALUE(I_END_ROW) TYPE I.
DATA: excel_tab TYPE ty_t_sender.
DATA: ld_separator TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object.
DATA: h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object.
DATA:
ld_rc TYPE i.
* Rückgabewert der Methode "clipboard_export "
* Makro für Fehlerbehandlung der Methods
* check parameters
IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.
IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.
* Get TAB-sign for separation of fields
CLASS cl_ABAP_char_utilities DEFINITION LOAD.
ld_separator = cl_ABAP_char_utilities=>horizontal_tab.
* open file in Excel
IF application-header = space OR application-handle = -1.
CREATE OBJECT application 'Excel.Application'.
m_message.
ENDIF.
CALL METHOD OF application 'Workbooks' = workbook.
m_message.
CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.
m_message.
* set property of application 'Visible' = 1.
* m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
* mark whole spread sheet
CALL METHOD OF worksheet 'Cells' = h_cell
EXPORTING #1 = i_begin_row #2 = i_begin_col.
m_message.
CALL METHOD OF worksheet 'Cells' = h_cell1
EXPORTING #1 = i_end_row #2 = i_end_col.
m_message.
CALL METHOD OF worksheet 'RANGE' = range
EXPORTING #1 = h_cell #2 = h_cell1.
m_message.
CALL METHOD OF range 'SELECT'.
m_message.
* copy marked area (whole spread sheet) into Clippboard
CALL METHOD OF range 'COPY'.
m_message.
* read clipboard into ABAP
CALL METHOD cl_gui_frontend_services=>clipboard_import
IMPORTING
data = excel_tab
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
IF sy-subrc <> 0.
MESSAGE a037(alsmex).
ENDIF.
PERFORM separated_to_intern_convert TABLES excel_tab intern
USING ld_separator.
* clear clipboard
REFRESH excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
* ERROR_NO_GUI = 2
* NOT_SUPPORTED_BY_GUI = 3
OTHERS = 4
.
* quit Excel and free ABAP Object - unfortunately, this does not kill
* the Excel process
CALL METHOD OF application 'QUIT'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT h_cell. m_message.
FREE OBJECT h_cell1. m_message.
FREE OBJECT range. m_message.
FREE OBJECT worksheet. m_message.
FREE OBJECT workbook. m_message.
FREE OBJECT application. m_message.
* <<<<< End of change note 575877
ENDForm. "zalsm_excel_to_internal_table
*&---------------------------------------------------------------------*
*& Event START-OF-SELECTION
*&---------------------------------------------------------------------*
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
mask = ',Microsoft Excel Files,*.xls'
static = 'X'
CHANGING
file_name = p_file.
*&---------------------------------------------------------------------*
*& Event START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.
PERFORM ZALSM_EXCEL_TO_INTERNAL_TABLE
TABLES
gt_upload
USING
p_file
1
1
9
5000.
LOOP AT GT_UPLOAD.
WRITE GT_UPLOAD-VALUE.
ENDLOOP.
