WDA学习（17）：WDA excel upload && download
1.10 Excel upload && download
在WDA中实现excel上传下载。
创建WDA Component,Z_TEST_WDA13。
1.创建Context上下文
创建上传表节点
创建NODE:NODE_SFLIGHT,类型：SFLIGHT;
上传文件信息节点：
创建NODE:NODE_UPLOAD_DATA
创建Attribute:FILENAME,类型String;
创建Attribute:FILETYPE,类型String;
创建Attribute:DATA,类型XString;
2.创建Layout视图
创建E_FILEUP,FILEUPLOAD视图控件，上传文件选择操作;
创建E_BUTTON,BUTTON视图控件，上传动作；
创建E_BUTTON1,BUTTON视图控件,下载动作；
创建E_TABLE,TABLE视图控件，用来显示上传结果；
设置上传按钮text属性，创建对应Action;
设置下载按钮text属性，创建对应Action;
绑定Context节点，data对应NODE_UPLOAD_DATA-DATA，fileName对应NODE_UPLOAD_DATA-FILENAME,mineType对应NODE_UPLOAD_DATA-FILETYPE;
选择E_TABLE,右键选择Creat Binding,绑定上下文节点Context;
3.实现Action等方法
实现SHOW_MSG方法
代码实例：
method SHOW_MSG . "显示信息 DATA: l_current_controller TYPE REF TO if_wd_controller, l_message_manager TYPE REF TO if_wd_message_manager. l_current_controller ?= wd_this->wd_get_api( ). CALL METHOD l_current_controller->get_message_manager RECEIVING message_manager = l_message_manager. * report message IF msg_type = 'S'. CALL METHOD l_message_manager->report_success EXPORTING message_text = msg. ELSEIF msg_type = 'E'. CALL METHOD l_message_manager->report_error_message EXPORTING message_text = msg. ELSEIF msg_type = 'I'. CALL METHOD l_message_manager->report_message EXPORTING message_text = msg. ENDIF. endmethod.
实现相应Action方法
创建INIT_SFLIGHT方法，初始化查询sflight表信息，并且绑定节点Context。
代码实例：
method INIT_SFLIGHT . DATA:lo_node TYPE REF TO if_wd_context_node. DATA:lt_sflight TYPE wd_this->Elements_node_sflight. DATA:ls_sflight LIKE LINE OF lt_sflight. "获取节点 lo_node = wd_context->get_child_node( name = 'NODE_SFLIGHT' ). "查询数据 SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_sflight FROM sflight. "绑定数据 lo_node->bind_table( lt_sflight ). endmethod.
初始化方法调用初始化init_sfight
method WDDOINIT .
init_sflight( ).
endmethod.
实现上传Action方法
method ONACTIONUPLOAD . DATA:lo_node TYPE REF TO if_wd_context_node. DATA:lt_node_sflight TYPE wd_this->Elements_node_sflight. DATA:ls_node_sflight LIKE LINE OF lt_node_sflight. "文件节点数据 DATA:ls_node_upload_data TYPE wd_this->Element_node_upload_data. "文件数据 DATA:lv_file TYPE xstring. "excel文档类对象 DATA:lo_excel TYPE REF TO zcl_excel. "excel worksheet类对象 DATA:lo_worksheet TYPE REF TO zcl_excel_worksheet. "异常类 DATA:lf_cxexcel TYPE REF TO ZCX_EXCEL. "上传excel DATA:cl_reader TYPE REF TO zif_excel_reader. "excel导入内表 TYPES:BEGIN OF s_excel, carrid TYPE string, connid TYPE string, fldate TYPE string, price TYPE string, currency TYPE string, planetype TYPE string, seatsmax TYPE string, seatsocc TYPE string, paymentsum TYPE string, END OF s_excel. DATA:lt_excel TYPE TABLE OF s_excel. DATA:ls_excel LIKE LINE OF lt_excel. "读取行列数 DATA:col_count TYPE I. DATA:row_count TYPE I. DATA:col TYPE I. DATA:row TYPE I. "获取节点数据 lo_node = wd_context->get_child_node( name = 'NODE_UPLOAD_DATA'). lo_node->get_static_attributes( IMPORTING static_attributes = ls_node_upload_data ). lv_file = ls_node_upload_data-data. "是否选择上传文件 IF lv_file IS INITIAL. "报错没有选择文件 wd_comp_controller->show_msg( msg = '请选择上传文件' msg_type = 'E' ). RETURN. ENDIF. TRY. CREATE OBJECT lo_excel. "cl_reader对象 CREATE OBJECT cl_reader TYPE zcl_excel_reader_2007. "加载文件 lo_excel = cl_reader->load( I_EXCEL2007 = lv_file ). lo_worksheet = lo_excel->get_active_worksheet( ). "获取行数，列数,去掉首行 row_count = lo_worksheet->get_highest_row( ) - 1. col_count = lo_worksheet->get_highest_column( ). DO row_count TIMES. row = sy-index + 1. CLEAR ls_excel. DO col_count TIMES. col = sy-index. CASE col. WHEN 1. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-carrid ). WHEN 2. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-connid ). WHEN 3. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-fldate ). WHEN 4. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-price ). WHEN 5. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-currency ). WHEN 6. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-planetype ). WHEN 7. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-seatsmax ). WHEN 8. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-seatsocc ). WHEN 9. lo_worksheet->get_cell( EXPORTING ip_column = col ip_row = row IMPORTING ep_value = ls_excel-paymentsum ). ENDCASE. ENDDO. APPEND ls_excel TO lt_excel. ENDDO. "是否上传数据 IF lt_excel IS INITIAL. wd_comp_controller->show_msg( msg = '上传excel没有数据' msg_type = 'E' ). RETURN. ENDIF. "将数据转换指定类型 LOOP AT lt_excel INTO ls_excel. MOVE-CORRESPONDING ls_excel TO ls_node_sflight. APPEND ls_node_sflight TO lt_node_sflight. ENDLOOP. CATCH ZCX_EXCEL INTO lf_cxexcel. "获取错误信息 DATA:result TYPE string. CALL METHOD lf_cxexcel->IF_MESSAGE~GET_TEXT RECEIVING result = result. wd_comp_controller->show_msg( msg = result msg_type = 'E' ). ENDTRY. "获取节点 lo_node = wd_context->get_child_node( name = 'NODE_SFLIGHT' ). "绑定数据 lo_node->bind_table( lt_node_sflight ). endmethod.
实现下载Action方法
method ONACTIONDOWNLOAD . DATA:lo_node TYPE REF TO if_wd_context_node. DATA:lt_node_sflight TYPE wd_this->Elements_node_sflight. DATA:ls_node_sflight LIKE LINE OF lt_node_sflight. "excel文档类对象 DATA:lo_excel TYPE REF TO zcl_excel. "excel worksheet类对象 DATA:lo_worksheet TYPE REF TO zcl_excel_worksheet. "异常类 DATA:lf_cxexcel TYPE REF TO ZCX_EXCEL. "style类 DATA:lo_style TYPE REF TO zcl_excel_style. "style的guid,header DATA:lv_style_guid TYPE zexcel_cell_style. "文本样式 DATA:lv_style_guid_text TYPE zexcel_cell_style. "列选择对象 DATA:lo_column_dimension TYPE REF TO zcl_excel_worksheet_columndime. "下载显示excel转换 DATA:cl_writer TYPE REF TO zif_excel_writer. DATA:xdata TYPE xstring. "列数对应字母 DATA:col_alpha TYPE zexcel_cell_column_alpha. DATA:lv_row TYPE I. DATA:lv_column TYPE I. FIELD-SYMBOLS:<fs_value> TYPE ANY. TYPES:BEGIN OF s_excel, name TYPE string, desc TYPE string, END OF s_excel. DATA:out_excel TYPE TABLE OF s_excel. DATA:wa_excel LIKE LINE OF out_excel. "文件名 DATA:filename TYPE string. "设置文件名 filename = sy-datum && '.xlsx'. "获取上下文节点数据 lo_node = wd_context->get_child_node( name = 'NODE_SFLIGHT'). lo_node->get_static_attributes_table( IMPORTING table = lt_node_sflight ). "创建导出字段及描述 wa_excel-name = 'CARRID'. wa_excel-desc = '航班ID'. APPEND wa_excel TO out_excel. wa_excel-name = 'CONNID'. wa_excel-desc = 'ID'. APPEND wa_excel TO out_excel. wa_excel-name = 'FLDATE'. wa_excel-desc = '航班日期'. APPEND wa_excel TO out_excel. wa_excel-name = 'PRICE'. wa_excel-desc = '价格'. APPEND wa_excel TO out_excel. wa_excel-name = 'CURRENCY'. wa_excel-desc = '币别'. APPEND wa_excel TO out_excel. wa_excel-name = 'PLANETYPE'. wa_excel-desc = '类型'. APPEND wa_excel TO out_excel. wa_excel-name = 'SEATSMAX'. wa_excel-desc = '座位数'. APPEND wa_excel TO out_excel. wa_excel-name = 'SEATSOCC'. wa_excel-desc = '已占用'. APPEND wa_excel TO out_excel. wa_excel-name = 'PAYMENTSUM'. wa_excel-desc = '合计'. APPEND wa_excel TO out_excel. TRY. "创建excel对象 CREATE OBJECT lo_excel. "获得当前worksheet lo_worksheet = lo_excel->get_active_worksheet( ). "创建一个新style lo_style = lo_excel->add_new_style( ). "加粗 lo_style->font->bold = abap_true. "字体大小 lo_style->font->size = 11. "填充类型 lo_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid. "前景色 lo_style->fill->fgcolor-rgb = 'FF66FFCC'. "背景色 lo_style->fill->bgcolor-rgb = 'FF33CCFF'. "获取style的编码uuid lv_style_guid = lo_style->get_guid( ). "设置excel表头 LOOP AT out_excel INTO wa_excel. lv_column = sy-tabix. "将列数转换成对应列字母 col_alpha = zcl_excel_common=>convert_column2alpha( ip_column = lv_column ). lo_worksheet->set_cell( ip_column = col_alpha ip_row = 1 ip_style = lv_style_guid ip_value = wa_excel-desc ). ENDLOOP. CLEAR lv_column. CLEAR lv_row. LOOP AT lt_node_sflight INTO ls_node_sflight. lv_row = sy-tabix + 1. LOOP AT out_excel INTO wa_excel. lv_column = sy-tabix. "将列数转换成对应列字母 col_alpha = zcl_excel_common=>convert_column2alpha( ip_column = lv_column ). ASSIGN COMPONENT wa_excel-name OF STRUCTURE ls_node_sflight TO <fs_value>. lo_worksheet->set_cell( ip_column = col_alpha ip_row = lv_row ip_value = <fs_value> ). ENDLOOP. ENDLOOP. "cl_writer对象 CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007. xdata = cl_writer->write_file( lo_excel ). CALL METHOD cl_wd_runtime_services=>attach_file_to_response( EXPORTING I_FILENAME = filename I_CONTENT = xdata I_MIME_TYPE = 'EXCEL' I_IN_NEW_WINDOW = abap_false I_INPLACE = abap_false ). CATCH ZCX_EXCEL INTO lf_cxexcel. "获取错误信息 DATA:result TYPE String. CALL METHOD lf_cxexcel->IF_MESSAGE~GET_TEXT RECEIVING result = result. wd_comp_controller->show_msg( msg = result msg_type = 'E' ). ENDTRY. endmethod.
4.创建Application
问题1：上传日期格式时，导出的excel显示为日期格式xxxx/xx/xx,但是上传时先转换为文本格式，excel日期数据转换为文本数字，ABAP并不能识别这个数字，并且转换为对应日期。
ABAP中日期格式转换Function：
CONVERT_DATE_TO_INTERNAL：日期转换MM/DD/YYYY转换为YYYYMMDD;
Excel中设置日期字段为文本格式，日期格式：MM/DD/YYYY
代码实例：
LOOP AT lt_excel INTO ls_excel. "转换日期格式为abap内部日期格式 DATA:lv_date TYPE S_DATE. CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL' EXPORTING DATE_EXTERNAL = ls_excel-fldate IMPORTING DATE_INTERNAL = lv_date. MOVE-CORRESPONDING ls_excel TO ls_node_sflight. ls_node_sflight-fldate = lv_date. APPEND ls_node_sflight TO lt_node_sflight. ENDLOOP.
问题2：导出时，如果不额外设定，日期字段自动和Excel的日期格式相匹配。但是再次原文档上传时，日期格式自动转换为文本格式时，就会出现问题1。
直接下载时，导出为日期格式
ABAP中日期格式转换Function:
CONVERT_DATE_TO_EXTERNAL:日期格式转换YYYYMMDD转换为MM/DD/YYYY。
代码实例：
IF wa_excel-name = 'FLDATE'. "日期格式处理 DATA:lv_date TYPE S_DATE. DATA:lv_date_str TYPE string. ASSIGN COMPONENT wa_excel-name OF STRUCTURE ls_node_sflight TO <fs_value>. lv_date = <fs_value>. CALL FUNCTION 'CONVERT_DATE_TO_EXTERNAL' EXPORTING DATE_INTERNAL = lv_date IMPORTING DATE_EXTERNAL = lv_date_str. lo_worksheet->set_cell( ip_column = col_alpha ip_row = lv_row ip_value = lv_date_str ). ELSE. ASSIGN COMPONENT wa_excel-name OF STRUCTURE ls_node_sflight TO <fs_value>. lo_worksheet->set_cell( ip_column = col_alpha ip_row = lv_row ip_value = <fs_value> ). ENDIF.
问题3：上传错误信息显示字段，如果出现条错误信息，那么错误信息那一栏会很长，错误信息在一行中换行显示。
1.在NODE_SFLIGHT创建Attribute:MSG,数据类型STRING_TABLE
2.修改Layout,Creat Binding,将MSG加入显示。
3.修改INIT_SFLIGHT方法，初始化时，设置错误信息显示效果
method INIT_SFLIGHT . DATA:lo_node TYPE REF TO if_wd_context_node. DATA:lt_sflight TYPE wd_this->Elements_node_sflight. DATA:ls_sflight LIKE LINE OF lt_sflight. DATA:lt_msg TYPE STRING_TABLE. DATA:ls_msg TYPE string. "获取节点 lo_node = wd_context->get_child_node( name = 'NODE_SFLIGHT' ). "查询数据 SELECT * INTO CORRESPONDING FIELDS OF TABLE lt_sflight FROM sflight. "初始化，模拟错误信息 LOOP AT lt_sflight INTO ls_sflight. CLEAR lt_msg[]. ls_msg = '模拟错误信息1;'. APPEND ls_msg TO lt_msg. ls_msg = '模拟错误信息2;'. APPEND ls_msg TO lt_msg. ls_sflight-msg = lt_msg. MODIFY lt_sflight FROM ls_sflight. EXIT. ENDLOOP. "绑定数据 lo_node->bind_table( lt_sflight ). endmethod.
问题4：不同文件类型下载，创建公共方法DOC_DOWNLOAD。输入参数IV_FILENAME，IV_MIME_TYPE类型都为String;
代码实例：
method DOC_DOWNLOAD . DATA:lo_node TYPE REF TO if_wd_context_node. DATA:lo_element TYPE REF TO if_wd_context_element. DATA:lt_sflight TYPE wd_this->elements_node_sflight. DATA:ls_sflight TYPE wd_this->element_node_sflight. DATA:lv_string TYPE string. DATA:lv_xstring TYPE xstring. DATA:lv_price TYPE string. DATA:lv_seatsmax TYPE string. DATA:lv_seatsocc TYPE string. "获取节点 lo_node = wd_context->get_child_node( wd_this->wdctx_node_sflight ). "获取数据 lo_node->get_static_attributes_table( IMPORTING table = lt_sflight ). "连接字符串 LOOP AT lt_sflight INTO ls_sflight. lv_price = ls_sflight-price. lv_seatsmax = ls_sflight-seatsmax. lv_seatsocc = ls_sflight-seatsocc. CONCATENATE lv_string ls_sflight-carrid ls_sflight-connid ls_sflight-fldate lv_price ls_sflight-currency ls_sflight-planetype lv_seatsmax lv_seatsocc cl_abap_char_utilities=>newline INTO lv_string SEPARATED BY cl_abap_char_utilities=>horizontal_tab. ENDLOOP. "将string转换为xstring CALL FUNCTION 'SCMS_STRING_TO_XSTRING' EXPORTING TEXT = lv_string * MIMETYPE = ' ' * ENCODING = IMPORTING BUFFER = lv_xstring EXCEPTIONS FAILED = 1 OTHERS = 2. IF SY-SUBRC <> 0. wd_comp_controller->show_msg( msg = '转换错误' msg_type = 'E' ). RETURN. ENDIF. "返回文檔 wdr_task=>client_window->client->attach_file_to_response( i_filename = iv_filename i_content = lv_xstring i_mime_type = iv_mime_type i_in_new_window = abap_false i_inplace = abap_false ). endmethod.
调用方法,下载Word类型文件;
wd_this->doc_download( iv_filename = 'sflight.doc' iv_mime_type = 'WORD' ).
调用方法，下载Txt类型文件;
wd_this->doc_download( iv_filename = 'sflight.txt' iv_mime_type = 'NOTEPAD' ).
文件类型对应MIME Type
参考地址：https://www.iana.org/assignments/media-types/media-types.xhtml
|
文件后缀 |
文件描述 |
MIME Type |
|
.aac |
AAC audio |
audio/aac |
|
.abw |
AbiWord document |
application/x-abiword |
|
.arc |
Archive document (multiple files embedded) |
application/x-freearc |
|
.avi |
AVI: Audio Video Interleave |
video/x-msvideo |
|
.azw |
Amazon Kindle eBook format |
application/vnd.amazon.ebook |
|
.bin |
Any kind of binary data |
application/octet-stream |
|
.bmp |
Windows OS/2 Bitmap Graphics |
image/bmp |
|
.bz |
BZip archive |
application/x-bzip |
|
.bz2 |
BZip2 archive |
application/x-bzip2 |
|
.csh |
C-Shell script |
application/x-csh |
|
.css |
Cascading Style Sheets (CSS) |
text/css |
|
.csv |
Comma-separated values (CSV) |
text/csv |
|
.doc |
Microsoft Word |
application/msword |
|
.docx |
Microsoft Word (OpenXML) |
application/vnd.openxmlformats-officedocument.wordprocessingml.document |
|
.eot |
MS Embedded OpenType fonts |
application/vnd.ms-fontobject |
|
.epub |
Electronic publication (EPUB) |
application/epub+zip |
|
.gif |
Graphics Interchange Format (GIF) |
image/gif |
|
.htm .html |
HyperText Markup Language (HTML) |
text/html |
|
.ico |
Icon format |
image/vnd.microsoft.icon |
|
.ics |
iCalendar format |
text/calendar |
|
.jar |
Java Archive (JAR) |
application/java-archive |
|
.jpeg .jpg |
JPEG images |
image/jpeg |
|
.js |
JavaScript |
text/javascript |
|
.json |
JSON format |
application/json |
|
.jsonld |
JSON-LD format |
application/ld+json |
|
.mid .midi |
Musical Instrument Digital Interface (MIDI) |
audio/midi audio/x-midi |
|
.mjs |
JavaScript module |
text/javascript |
|
.mp3 |
MP3 audio |
audio/mpeg |
|
.mpeg |
MPEG Video |
video/mpeg |
|
.mpkg |
Apple Installer Package |
application/vnd.apple.installer+xml |
|
.odp |
OpenDocument presentation document |
application/vnd.oasis.opendocument.presentation |
|
.ods |
OpenDocument spreadsheet document |
application/vnd.oasis.opendocument.spreadsheet |
|
.odt |
OpenDocument text document |
application/vnd.oasis.opendocument.text
|
|
.oga |
OGG audio |
audio/ogg |
|
.ogv |
OGG video |
video/ogg |
|
.ogx |
OGG |
application/ogg |
|
.otf |
OpenType font |
font/otf |
|
.png |
Portable Network Graphics |
image/png |
|
|
Adobe Portable Document Format (PDF) |
application/pdf |
|
.ppt |
Microsoft PowerPoint |
application/vnd.ms-powerpoint |
|
.pptx |
Microsoft PowerPoint (OpenXML) |
application/vnd.openxmlformats-officedocument.presentationml.presentation |
|
.rar |
RAR archive |
application/x-rar-compressed |
|
.rtf |
Rich Text Format (RTF) |
application/rtf |
|
.sh |
Bourne shell script |
application/x-sh |
|
.svg |
Scalable Vector Graphics (SVG) |
image/svg+xml |
|
.swf |
Small web format (SWF) or Adobe Flash document |
application/x-shockwave-flash |
|
.tar |
Tape Archive (TAR) |
application/x-tar |
|
.tif .tiff |
Tagged Image File Format (TIFF) |
image/tiff |
|
.ttf |
TrueType Font |
font/ttf |
|
.txt |
Text, (generally ASCII or ISO 8859-n) |
text/plain |
|
.vsd |
Microsoft Visio |
application/vnd.visio |
|
.wav |
Waveform Audio Format |
audio/wav |
|
.weba |
WEBM audio |
audio/webm |
|
.webm |
WEBM video |
video/webm |
|
.webp |
WEBP image |
image/webp |
|
.woff |
Web Open Font Format (WOFF) |
font/woff |
|
.woff2 |
Web Open Font Format (WOFF) |
font/woff2 |
|
.xhtml |
XHTML |
application/xhtml+xml |
|
.xls |
Microsoft Excel |
application/vnd.ms-excel |
|
.xlsx |
Microsoft Excel (OpenXML) |
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |
|
.xml |
XML |
application/xml text/xml |
|
.xul |
XUL |
application/vnd.mozilla.xul+xml |
|
.zip |
ZIP archive |
application/zip |
|
.3gp |
3GPP audio/video container |
video/3gpp audio/3gpp
|
|
.3g2 |
3GPP2 audio/video container |
video/3gpp2 audio/3gpp2 |
|
.7z |
7-zip archive |
application/x-7z-compressed |
本文来自博客园，作者：渔歌晚唱，转载请注明原文链接：https://www.cnblogs.com/tangToms/p/13785905.html
