WDA将内表下载成Excel和上载Excel到内表（.xlsx格式）

在WDA中下载数据时需要转换数据格式为xstring，然后调用如下代码即可自动弹出下载对话框（浏览器自带的）

 1   cl_wd_runtime_services=>attach_file_to_response(
 2 
 3       EXPORTING i_filename      = l_filename
 4 
 5                 i_content       = l_xstring
 6 
 7 *                i_mime_type     = 'APPLICATION/XLSX'
 8                 i_mime_type     = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
 9 
10                 i_in_new_window = abap_false ).

下载xstring到指定格式

如何将一个内表数据转换成xstring，本人编写了一个公用FUNCTION，可以传入任意格式的内表（即动态内表），传出参数为xstring

  1 FUNCTION z_download_table_to_excel.
  2 *"----------------------------------------------------------------------
  3 *"*"局部接口：
  4 *"  EXPORTING
  5 *"     VALUE(EV_XSTRING) TYPE  XSTRING
  6 *"  TABLES
  7 *"      IT_TABLE TYPE  TABLE
  8 *"      IT_DYN_FIELD_INFO STRUCTURE  ZWDA_S_FIELD_INFO OPTIONAL
  9 *"----------------------------------------------------------------------
 10 *根据传入的动态内表，返回XSTRING
 11 
 12 * Internal tables declaration
 13 
 14   DATA:
 15 
 16 *        lt_file_download                                    TYPE wd_this->elements_file_download,
 17 *
 18 *        ls_file_download                                   TYPE wd_this->element_file_download,
 19 
 20         lt_column                                             TYPE if_fdt_doc_spreadsheet=>t_column.
 21 
 22 * Structures declaration
 23 
 24   DATA:
 25 
 26         ls_column                                             TYPE LINE OF if_fdt_doc_spreadsheet=>t_column.
 27 
 28 * Local variables declaration
 29 
 30   DATA:
 31 
 32         lv_proceed                                            TYPE boolean VALUE abap_true,
 33 
 34         lv_xstring                                              TYPE xstring.
 35 
 36 * References declaration
 37 
 38   DATA:
 39 
 40         lo_nd_file_download                              TYPE REF TO if_wd_context_node,
 41 
 42         lref_string                                             TYPE REF TO cl_abap_datadescr,
 43 
 44         lref_data                                               TYPE REF TO data.
 45 
 46 * Field symbols declaration
 47 
 48   FIELD-SYMBOLS:
 49 
 50         <fs_contents>                                       TYPE STANDARD TABLE,
 51 
 52         <fs_header>                                          TYPE any,
 53 
 54         <fs_comp>                                 TYPE any,
 55 
 56         <l_column>                                TYPE LINE OF if_fdt_doc_spreadsheet=>t_column..
 57 
 58   DATA dyn_wa_source TYPE REF TO data.
 59   DATA dyn_table_source TYPE REF TO data.
 60   DATA lr_table_source TYPE REF TO cl_abap_tabledescr.
 61 
 62   FIELD-SYMBOLS:
 63   <dyn_table_source> TYPE STANDARD TABLE,
 64   <dyn_wa_source> TYPE any.
 65 
 66   DATA:ls_stru TYPE REF TO cl_abap_structdescr,
 67        ls_comp TYPE abap_componentdescr,
 68        lt_comp TYPE STANDARD TABLE OF abap_componentdescr WITH KEY name.
 69 
 70   ls_stru ?= cl_abap_typedescr=>describe_by_data( p_data = it_table ).
 71 *参照动态结构类型和动态内表类型创建内表与工作区
 72   CREATE DATA dyn_wa_source TYPE HANDLE ls_stru.
 73   ASSIGN dyn_wa_source->* TO <fs_header>.
 74   lt_comp = ls_stru->get_components( ).
 75 * Create data object
 76 
 77 *动态结构
 78   CREATE DATA lref_data TYPE HANDLE ls_stru.
 79 *根据动态结构创建动态内表类型
 80   CALL METHOD cl_abap_tabledescr=>create
 81     EXPORTING
 82       p_line_type = ls_stru
 83     RECEIVING
 84       p_result    = lr_table_source.
 85 
 86   CREATE DATA dyn_table_source TYPE HANDLE lr_table_source.
 87   ASSIGN dyn_table_source->* TO <dyn_table_source>.
 88 
 89   LOOP AT it_table ASSIGNING <dyn_wa_source>.
 90     APPEND <dyn_wa_source> TO <dyn_table_source>.
 91     CLEAR <dyn_wa_source>.
 92   ENDLOOP.
 93 
 94   DATA:l_field_desc TYPE string,
 95        lt_ddic_info      TYPE ddfields,
 96        l_dfies TYPE dfies,
 97        lo_elem_descr     TYPE REF TO cl_abap_elemdescr.
 98 
 99 
100 
101   LOOP AT lt_comp INTO ls_comp.
102     lo_elem_descr ?= ls_comp-type.
103     lo_elem_descr->get_ddic_field(
104 *    EXPORTING
105 *      p_langu      = SY-LANGU
106       RECEIVING
107         p_flddescr   = l_dfies
108       EXCEPTIONS
109         not_found    = 1
110         no_ddic_type = 2
111         OTHERS       = 3
112     ).
113     IF sy-subrc <> 0.
114 *   MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
115 *              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
116     ENDIF.
117 
118     APPEND INITIAL LINE TO lt_column ASSIGNING <l_column>.
119     IF  <l_column> IS ASSIGNED.
120 
121       MOVE: sy-tabix            TO <l_column>-id,
122 
123             ls_comp-name TO <l_column>-name,
124 
125             l_dfies-FIELDTEXT TO <l_column>-display_name,
126 
127              abap_true          TO <l_column>-is_result,
128 
129              ls_comp-type TO <l_column>-TYPE.
130 *如果IT_DYN_FIELD_INFO有传入数据则替换表头描述
131              READ TABLE IT_DYN_FIELD_INFO WITH KEY FIELD_NAME = ls_comp-name.
132              IF SY-subrc EQ 0.
133                MOVE IT_DYN_FIELD_INFO-field_text TO <l_column>-display_name.
134              ENDIF.
135 
136 *      CLEAR lo_datadescr.
137 *
138 *      ASSIGN COMPONENT <l_column>-name
139 *
140 *        OF STRUCTURE <l_header> TO <l_comp>.
141 *
142 *      lo_datadescr ?= cl_abap_datadescr=>describe_by_data( <l_comp> ).
143 *
144 *      MOVE: lo_datadescr TO <l_column>-type.
145 
146     ENDIF.
147   ENDLOOP.
148 
149 
150 *BO------------------------替换输出表头的放阿飞-----------------------------------------
151 * Loop to prepare column header text table
152 
153 *  WHILE lv_proceed = abap_true.
154 *
155 **   Read columnwise entries
156 *
157 *    ASSIGN COMPONENT sy-index OF STRUCTURE <fs_header> TO <fs_comp>.
158 *
159 **   Check if the field is the last column
160 *
161 *    IF <fs_comp> IS NOT ASSIGNED.
162 *
163 **     Set loop flag
164 *
165 *      lv_proceed = abap_false.
166 *
167 **     Exit the loop when a row ends
168 *
169 *      EXIT.
170 *
171 *    ELSE.
172 *
173 **     Read reference of column field name text
174 *
175 *      lref_string ?= cl_abap_datadescr=>describe_by_data( <fs_comp> ).
176 *
177 **     Prepare structure for header text
178 *
179 *      ls_column-id           = sy-index.
180 *
181 *      ls_column-name         = <fs_comp>.
182 *
183 *      ls_column-display_name = <fs_comp>.
184 *
185 *      ls_column-is_result    = abap_true.
186 *
187 *      ls_column-type         = lref_string.
188 *
189 **     Append structure prepared to internal table
190 *
191 *      APPEND ls_column TO lt_column.
192 *
193 *    ENDIF.
194 *
195 **   Unassign field symbol
196 *
197 *    UNASSIGN <fs_comp>.
198 *
199 *  ENDWHILE.
200 *EO -------------------替换输出表头的方法--------------------------
201 
202 * Call class method to convert data to be downloaded to .xlsx compatible xstring
203 
204   cl_fdt_xl_spreadsheet=>if_fdt_doc_spreadsheet~create_document(
205 
206         EXPORTING
207 
208           itab         = dyn_table_source
209 
210           iv_call_type = 2
211 
212           columns      = lt_column
213 
214 *          IV_SHEET_NAME      = 'TEST'
215 
216         RECEIVING
217 
218           xdocument    = ev_xstring ).
219 ENDFUNCTION.

转换动态内表为Xstring

 

上载Excel并转换为动态内表，本人亦封装了一个FUNCTION，传入为xstring(WDA上载控件可直接获得xstring内容），传出参数为data类型的动态内表

  1 FUNCTION z_upload_excel_to_table .
  2 *"----------------------------------------------------------------------
  3 *"*"局部接口：
  4 *"  IMPORTING
  5 *"     VALUE(IV_FILENAME) TYPE  STRING
  6 *"     VALUE(IV_FILECONTENTS) TYPE  XSTRING
  7 *"     VALUE(IV_FILETYPE) TYPE  STRING OPTIONAL
  8 *"     VALUE(IV_BEGIN_ROW) TYPE  I OPTIONAL
  9 *"     VALUE(IV_END_ROW) TYPE  I OPTIONAL
 10 *"     VALUE(IV_BEGIN_COL) TYPE  I OPTIONAL
 11 *"     VALUE(IV_END_COL) TYPE  I OPTIONAL
 12 *"     VALUE(IV_DYNAMIC_TYPE) TYPE  STRING OPTIONAL
 13 *"     REFERENCE(I_DYNAMIC_STRUCT) TYPE REF TO  CL_ABAP_STRUCTDESCR
 14 *"       OPTIONAL
 15 *"  EXPORTING
 16 *"     REFERENCE(EO_DYN_TABLE) TYPE REF TO  DATA
 17 *"  TABLES
 18 *"      ET_TABLE TYPE  TABLE OPTIONAL
 19 *"      ET_MESSAGES STRUCTURE  BAPIRET1 OPTIONAL
 20 *"----------------------------------------------------------------------
 21 *该函数适用于从WDA页面上传的xlsx文件内容（xstring）根据传入的动态结构转换内表
 22 *注意：IV_DYNAMIC_TYPE与I_DYNAMIC_STRUCT务必传入其中一个，否则会dump
 23 
 24   DATA:
 25 
 26         lt_worksheets                             TYPE STANDARD TABLE OF string,
 27 
 28         lt_contents                                 TYPE string_table,
 29 
 30         lt_final_contents                          TYPE TABLE OF znp21_s_wkl_upload.
 31 
 32 
 33 * Local variables declaration
 34 
 35   DATA:
 36 
 37         lv_name                                       TYPE string,
 38 
 39         lv_string                                       TYPE string,
 40 
 41         lv_msg                                         TYPE string,
 42 
 43         lv_flag                                          TYPE boolean,
 44 
 45         lv_message                                  TYPE string.
 46 
 47 * References declarations
 48 
 49   DATA:
 50 
 51         lref_excel                                     TYPE REF TO cl_fdt_xl_spreadsheet,
 52 
 53         lref_excel_core                             TYPE REF TO cx_fdt_excel_core,
 54 
 55         lref_data                                       TYPE REF TO data,
 56 
 57         lref_dref                                        TYPE REF TO data,
 58 
 59         lo_nd_file_upload                          TYPE REF TO if_wd_context_node,
 60 
 61         lo_el_file_upload                           TYPE REF TO if_wd_context_element.
 62 
 63 * Field symbols declarations
 64 
 65   FIELD-SYMBOLS:
 66 
 67         <fs_table>                                  TYPE table,
 68 
 69         <fs_data>                                   TYPE any,
 70 
 71         <fs_data_str>                              TYPE any,
 72 
 73         <fs_comp>                                 TYPE any,
 74 
 75         <fs_output>                                TYPE string.
 76 
 77   DATA : dy_table TYPE REF TO data,
 78          wa_line TYPE REF TO data.
 79 
 80   FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,
 81                  <dyn_wa> TYPE any,
 82                  <field_value> TYPE any.
 83 
 84   DATA lr_table_source TYPE REF TO cl_abap_tabledescr.
 85 
 86   DATA:l_tabix TYPE sy-tabix.
 87 
 88   TRY.
 89 
 90 *     Create object of class to read .xlsx file contents
 91 
 92       CREATE OBJECT lref_excel
 93         EXPORTING
 94           document_name = iv_filename
 95           xdocument     = iv_filecontents.
 96 
 97     CATCH cx_fdt_excel_core INTO lref_excel_core.
 98 
 99       CLEAR lv_msg.
100 
101 *     Call method to get error message text
102 
103       CALL METHOD lref_excel_core->if_message~get_text
104         RECEIVING
105           result = lv_msg.
106 
107 *<< Display error message returned in lv_msg >>
108       PERFORM frm_add_messages TABLES et_messages USING 'E' '000' '创建Excel对象失败:' lv_msg '' ''.
109       RETURN.
110 
111   ENDTRY.
112 
113 * Call method to get list of worksheets in the .xlsx file
114 
115   lref_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
116 
117       IMPORTING
118 
119         worksheet_names = lt_worksheets ).
120 
121 * Condition to check whether .xlsx file has any active worksheets
122 
123   IF lt_worksheets IS NOT INITIAL.
124 
125 *   Read active worksheet
126 
127     READ TABLE lt_worksheets INDEX 1 INTO lv_name.
128 
129   ELSE.
130 
131 *<< Display error message >>
132     PERFORM frm_add_messages TABLES et_messages USING 'E' '000' '打开工作区失败,请检查文档' '' '' ''.
133     RETURN.
134 
135   ENDIF.
136 
137 * Get reference of .xlsx file contents in the active worksheet
138 
139   lref_data = lref_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lv_name ).
140 
141 * Fetch all records in the active worksheet
142 
143   ASSIGN lref_data->* TO <fs_table>.
144 
145 * Prepare exporting table with .xlsx file contents
146 
147   IF <fs_table> IS NOT ASSIGNED.
148 
149 *<< Display error message >>
150     PERFORM frm_add_messages TABLES et_messages USING 'E' '000' '获取工作区数据失败,请检查文档' '' '' ''.
151     RETURN.
152 
153   ENDIF.
154 
155 *创建动态内表，以便接收Excel数据
156   IF iv_dynamic_type IS NOT INITIAL."根据数据字典中的结构
157     CREATE DATA dy_table TYPE TABLE OF (iv_dynamic_type).
158     ASSIGN dy_table->* TO <dyn_table>.
159 
160     CREATE DATA wa_line LIKE LINE OF <dyn_table>.
161     ASSIGN wa_line->* TO <dyn_wa>.
162   ELSE."根据运行时的结构
163 *根据动态结构创建动态内表类型
164   CALL METHOD cl_abap_tabledescr=>create
165     EXPORTING
166       p_line_type = i_dynamic_struct
167     RECEIVING
168       p_result    = lr_table_source.
169     CREATE DATA dy_table TYPE HANDLE lr_table_source.
170     ASSIGN dy_table->* TO <dyn_table>.
171 
172     CREATE DATA wa_line LIKE LINE OF <dyn_table>.
173     ASSIGN wa_line->* TO <dyn_wa>.
174   ENDIF.
175 
176 * Loop dynamic table to prepare final table contents to pass in exporting parameter
177 
178   LOOP AT <fs_table> ASSIGNING <fs_data>.
179     l_tabix = sy-tabix.
180     IF iv_begin_row IS NOT INITIAL AND sy-tabix < iv_begin_row.
181       CONTINUE.
182     ENDIF.
183 
184     IF iv_end_row IS NOT INITIAL AND l_tabix > iv_end_row.
185       EXIT.
186     ENDIF.
187 *   Initialize flag
188 
189     lv_flag = abap_true.
190 
191     WHILE lv_flag = abap_true.
192 
193       IF iv_begin_col IS NOT INITIAL AND sy-index < iv_begin_col.
194         CONTINUE.
195       ENDIF.
196       IF iv_end_col IS NOT INITIAL AND sy-index > iv_end_col.
197         lv_flag = abap_false.
198         EXIT.
199       ENDIF.
200 *     Read columnwise entries
201 
202       ASSIGN COMPONENT sy-index OF STRUCTURE <fs_data> TO <fs_comp>.
203 
204       IF <fs_comp> IS NOT ASSIGNED.
205 
206         lv_flag = abap_false.
207 
208 *       Exit the loop when a row ends
209 
210         EXIT.
211 
212       ELSE.
213 
214 *       Concatenate each cell data in a row into string seperated by '||'
215 
216 *        CONCATENATE lv_string <fs_comp> INTO lv_string SEPARATED BY '||'.
217         ASSIGN COMPONENT sy-index OF STRUCTURE <dyn_wa> TO <field_value>.
218         <field_value> = <fs_comp>.
219 
220       ENDIF.
221 
222 *     Unassign field symbol
223 
224       UNASSIGN <fs_comp>.
225 
226     ENDWHILE.
227 
228     APPEND <dyn_wa> TO <dyn_table>.
229     CLEAR <dyn_wa>.
230 
231 
232 **   Shift final string having a row left by 2 places to remove leading '||'
233 *
234 *    SHIFT lv_string LEFT BY 2 PLACES.
235 *
236 **   Append prepared row data to exporting parameter
237 *
238 *    APPEND lv_string TO lt_contents.
239 *
240 **   Clear variable having row data
241 *
242 *    CLEAR lv_string.
243 
244   ENDLOOP.
245 
246 *  ET_TABLE = <dyn_table>."这条语句总是dump
247   eo_dyn_table = dy_table.
248 
249 
250 *   Loop internal table to split records and fill in target internal table
251 *  DELETE lt_contents INDEX 1.
252 *  LOOP AT lt_contents ASSIGNING <fs_output>.
253 
254 *     Split file contents returned at '||'
255 
256 *    SPLIT <fs_output>
257 *
258 *      AT '||'
259 *
260 *      INTO ls_contents-object_id
261 *           ls_contents-object_type
262 *           ls_contents-description
263 *           ls_contents-created_at
264 *           ls_contents-created_at_t
265 *           ls_contents-created_by.
266 
267 *     Append split records in internal table
268 
269 *    APPEND ls_contents TO lt_final_contents.
270 
271 *  ENDLOOP.
272 ENDFUNCTION.
273 
274 *&---------------------------------------------------------------------*
275 *&      Form  FRM_ADD_MESSAGES
276 *&---------------------------------------------------------------------*
277 *       text
278 *----------------------------------------------------------------------*
279 *      -->PT_MESSAGES  text
280 *      -->P_TYPE       text
281 *      -->P_NUMBER     text
282 *      -->P1           text
283 *      -->P2           text
284 *      -->P3           text
285 *      -->P4           text
286 *----------------------------------------------------------------------*
287 FORM frm_add_messages TABLES pt_messages USING p_type p_number p1 p2 p3 p4.
288   DATA:lw_messages TYPE bapiret1.
289 
290   lw_messages-id = 'ZXXX'.
291   lw_messages-number = p_number.
292   lw_messages-type = p_type.
293   lw_messages-message_v1 = p1.
294   lw_messages-message_v2 = p2.
295   lw_messages-message_v3 = p3.
296   lw_messages-message_v4 = p4.
297   APPEND lw_messages TO pt_messages.
298 
299 ENDFORM.                    "FRM_ADD_MESSAGES

将Xstring转换为动态内表

 

转载于:https://www.cnblogs.com/duanqingxia/p/10810335.html