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 |