SAP ABAP不依赖OLE和开源程序导出Excel
·
小甲：老师，我最近又遇到一个问题，客户要求导出内表为XLSX文件，但是不允许使用像ZCL_EXCEL，ABAP2XLSX等开源程序，又因为系统是比较老的ECC系统，没有CL_EHFND_XLSX等CLASS可以使用，再有，有时候还需要后台JOB导出，也不能使用OLE，我一时找不到好办法了呢，请老师指点迷津
老白：仅仅是导出内容吗？没有要求乱七八糟的格式？
小甲：没有格式要求呢，只要导出内容就可以了。我本来是用TXT导出的，然后再导入到Excel里面，但是他们嫌麻烦，想直接导出来Excel文件。
老白：那好说呀，最简单的XLSX文件其实就是几个符合OPENXML规范的文本文件的组合，再用ZIP格式压缩一下就行了，你现在找一个XLSX文件，把扩展名改为ZIP，然后打开看看。
小甲：果然是的呢，里面是一些文件夹和文件
老白：你再把里面的XML文件解压出来一个打开看看
小甲：是一个XML文件，呶~
老白：是的，最简单的XLSX文件就是由这样一些XML文件组成的，既然是纯文本，那么我们大力出奇迹，根据内表的内容拼出来这些XML文件，然后压缩成XLSX不就行了吗？
小甲：话是这么说，我们怎么能知道这些XML的格式是什么呢？
老白：嘿嘿，要是搁在以前呢，需要自己去查一下OPENXML的标准，但是现在咱们不是有Deepseek了嘛，AI写完整的程序目前还不是很成熟，但是单个的知识点对他们来说非常Easy，请看DeepSeek给的答案！
小甲：好家伙！好厉害的AI啊，老师看了咱们也要与时俱进啊，多利用AI解决问题才是王道。
老白：那必~须滴~~，现在文件结构和XML的格式都出来了，我们只需要使用ABAP来实现就行了。
小甲：是不是只要把内表的内容全部按照这个格式搞到XML就行了呢？
老白：那不行，还是有一些东西需要考虑的，比如内部格式要转成外部格式（物料去前导零/单位转为外部单位等），比如数值格式可以使用常规的单元格格式但是其他的最好是使用字符串单元格格式，免得Excel瞎操心给弄个科学计数法等等，还是有些东西需要仔细考虑一下的。
小甲：老师说的是，我试着写一下代码~~欧了，请看！
REPORT zitabtoxlsx NO STANDARD PAGE HEADING.
DATA: BEGIN OF itab1 OCCURS 0, mblnr TYPE mseg-mblnr, mjahr TYPE mseg-mjahr, matnr TYPE mseg-matnr, menge TYPE mseg-menge, meins TYPE mseg-meins, cpudt_mkpf TYPE mseg-cpudt_mkpf, cputm_mkpf TYPE mseg-cputm_mkpf, END OF itab1.DATA itab2 TYPE TABLE OF ekpo.DATA gt_head TYPE TABLE OF char20 WITH HEADER LINE.DATA gv_xstr TYPE xstring.
START-OF-SELECTION. APPEND '物料凭证' TO gt_head. APPEND '年度' TO gt_head. APPEND '物料' TO gt_head. APPEND '数量' TO gt_head. APPEND '单位' TO gt_head. APPEND '日期' TO gt_head. APPEND '时间' TO gt_head.
SELECT * INTO CORRESPONDING FIELDS OF TABLE itab1 FROM mseg UP TO 20 ROWS. PERFORM itabtoxlsx TABLES itab1 gt_head CHANGING gv_xstr. PERFORM downxstr USING gv_xstr '' 'TEST'.
CLEAR gt_head[]. SELECT * INTO TABLE itab2 FROM ekpo UP TO 40 ROWS. PERFORM itabtoxlsx TABLES itab2 gt_head CHANGING gv_xstr. PERFORM downxstr USING gv_xstr '' 'TEST2'.
*&---------------------------------------------------------------------**& 内表转为XLXS格式的XSTRING字符串*&---------------------------------------------------------------------*FORM itabtoxlsx TABLES t_intab t_head CHANGING cv_xstr. DATA lr_tdescr TYPE REF TO cl_abap_tabledescr. DATA lr_sdescr TYPE REF TO cl_abap_structdescr. DATA ls_comps LIKE LINE OF cl_abap_structdescr=>components. DATA lv_xmlstr TYPE string. DATA lo_zip TYPE REF TO cl_abap_zip. DATA lv_rows TYPE i. DATA lv_cols TYPE i. DATA lv_rown TYPE n LENGTH 6. DATA lv_indn TYPE string. DATA lv_alpha TYPE c LENGTH 4. DATA lv_index TYPE sy-index. DATA lv_tabix TYPE sy-tabix. DATA lv_type TYPE c. DATA lv_char TYPE char256. DATA lt_numer TYPE TABLE OF sy-tabix WITH HEADER LINE. DATA lt_ipf TYPE TABLE OF char256 WITH HEADER LINE. DATA lt_share TYPE TABLE OF string WITH HEADER LINE. DATA: BEGIN OF lt_rowcol OCCURS 0, row TYPE i, col TYPE i, str TYPE string, END OF lt_rowcol. FIELD-SYMBOLS: <lv_fval> TYPE any.
lr_tdescr ?= cl_abap_typedescr=>describe_by_data( t_intab[] ). lr_sdescr ?= lr_tdescr->get_table_line_type( ).
CREATE OBJECT lo_zip. lv_rows = lines( t_intab ) + 1. lv_cols = lines( t_head ). IF lv_cols = 0. lv_cols = lines( lr_sdescr->components ). ENDIF.
LOOP AT lr_sdescr->components INTO ls_comps WHERE type_kind CA 'IPFaebs'. APPEND sy-tabix TO lt_numer. ENDLOOP.
*** xl/sharedStrings.xml CLEAR lv_xmlstr. CONCATENATE `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` `<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">` INTO lv_xmlstr.
IF t_head[] IS INITIAL. LOOP AT lr_sdescr->components INTO ls_comps. lt_rowcol-row = 1. lt_rowcol-col = sy-tabix. lt_rowcol-str = ls_comps-name. APPEND lt_rowcol. lt_share = ls_comps-name. COLLECT lt_share. ENDLOOP. ELSE. LOOP AT t_head. lt_rowcol-row = 1. lt_rowcol-col = sy-tabix. lt_rowcol-str = t_head. APPEND lt_rowcol. lt_share = t_head. COLLECT lt_share. ENDLOOP. ENDIF.
LOOP AT t_intab. lt_rowcol-row = sy-tabix + 1. DO lv_cols TIMES. lt_rowcol-col = sy-index. READ TABLE lt_numer WITH KEY table_line = sy-index. IF sy-subrc NE 0. ASSIGN COMPONENT sy-index OF STRUCTURE t_intab TO <lv_fval>. IF sy-subrc = 0. DESCRIBE FIELD <lv_fval> TYPE lv_type. CASE lv_type. WHEN 'D' OR 'T'. IF <lv_fval> IS INITIAL OR <lv_fval> = ''. lv_char = ''. ELSE. WRITE <lv_fval> TO lv_char. ENDIF. WHEN 'X' OR 'y' OR 'g'. lv_char = <lv_fval>. WHEN OTHERS. WRITE <lv_fval> TO lv_char. ENDCASE.
REPLACE ALL OCCURRENCES OF '&' IN lv_char WITH '&'. REPLACE ALL OCCURRENCES OF '<' IN lv_char WITH '<'. REPLACE ALL OCCURRENCES OF '>' IN lv_char WITH '>'. REPLACE ALL OCCURRENCES OF `'` IN lv_char WITH '''. REPLACE ALL OCCURRENCES OF '"' IN lv_char WITH '"'. lt_share = lv_char. COLLECT lt_share. lt_rowcol-str = lv_char. APPEND lt_rowcol. ENDIF. ELSE. ASSIGN COMPONENT sy-index OF STRUCTURE t_intab TO <lv_fval>. lv_char = abs( <lv_fval> ). IF <lv_fval> < 0. CONCATENATE '-' lv_char INTO lv_char. ENDIF. CONDENSE lv_char NO-GAPS. APPEND lv_char TO lt_ipf. ENDIF. ENDDO. ENDLOOP.
SORT lt_share. LOOP AT lt_share. CONCATENATE lv_xmlstr `<si><t>` lt_share `</t></si>` INTO lv_xmlstr. ENDLOOP. CONCATENATE lv_xmlstr `</sst>` INTO lv_xmlstr. PERFORM addtozip USING lo_zip lv_xmlstr 'xl/sharedStrings.xml'.
*** xl/worksheets/sheet1.xml CONCATENATE `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">` `<sheetData>` INTO lv_xmlstr.
DO lv_rows TIMES. lv_tabix = sy-index. lv_rown = lv_tabix. CONCATENATE lv_xmlstr `<row r="` lv_rown `">` INTO lv_xmlstr. DO lv_cols TIMES. lv_index = sy-index. PERFORM int2excelcol USING lv_index CHANGING lv_alpha.
READ TABLE lt_numer WITH KEY table_line = lv_index. IF sy-subrc NE 0 OR lv_tabix = 1. READ TABLE lt_rowcol WITH KEY row = lv_tabix col = lv_index BINARY SEARCH. READ TABLE lt_share TRANSPORTING NO FIELDS WITH KEY table_line = lt_rowcol-str BINARY SEARCH. lv_indn = sy-tabix - 1. CONCATENATE lv_xmlstr `<c r="` lv_alpha lv_rown `" t="s" s="0"><v>` lv_indn `</v></c>` INTO lv_xmlstr. ELSE. READ TABLE lt_ipf INDEX 1. DELETE lt_ipf INDEX 1. CONCATENATE lv_xmlstr `<c r="` lv_alpha lv_rown `" s="1"><v>` lt_ipf `</v></c>` INTO lv_xmlstr. ENDIF. ENDDO. CONCATENATE lv_xmlstr `</row>` INTO lv_xmlstr. ENDDO. CONCATENATE lv_xmlstr `</sheetData></worksheet>` INTO lv_xmlstr. PERFORM addtozip USING lo_zip lv_xmlstr 'xl/worksheets/sheet1.xml'.
*** [Content_Types].xml CLEAR lv_xmlstr. CONCATENATE `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` `<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">` `<Default Extension="xml" ContentType="application/xml"/>` `<Default Extension="rels" ContentType="application/vnd.` `openxmlformats-package.relationships+xml"/>` `<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-o` `fficedocument.spreadsheetml.sharedStrings+xml"/>` `<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedoc` `ument.spreadsheetml.styles+xml"/>` `<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-office` `document.spreadsheetml.sheet.main+xml"/>` `<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlforma` `ts-officedocument.spreadsheetml.worksheet+xml"/>` `</Types>` INTO lv_xmlstr. PERFORM addtozip USING lo_zip lv_xmlstr '[Content_Types].xml'.
***_rels/.rels CLEAR lv_xmlstr. CONCATENATE `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` `<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/` `relationships/officeDocument" Target="xl/workbook.xml"/>` `</Relationships>` INTO lv_xmlstr. PERFORM addtozip USING lo_zip lv_xmlstr '_rels/.rels'.
*** xl/_rels/workbook.xml.rels CLEAR lv_xmlstr. CONCATENATE `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">` `<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/` `relationships/worksheet" Target="worksheets/sheet1.xml"/>` `<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/` `relationships/styles" Target="styles.xml"/>` `<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/` `relationships/sharedStrings" Target="sharedStrings.xml"/>` `</Relationships>` INTO lv_xmlstr. PERFORM addtozip USING lo_zip lv_xmlstr 'xl/_rels/workbook.xml.rels'.
*** xl/styles.xml CLEAR lv_xmlstr. CONCATENATE `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` `<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">` `<numFmts count="2">` `<numFmt numFmtId="164" formatCode="@"/>` `</numFmts>` `<fonts count="1"> <font><sz val="11"/><name val="宋体"/></font> </fonts>` `<fills count="1">` `<fill><patternFill patternType="none"/></fill>` `</fills>` `<borders count="1">` `<border><left/><right/><top/><bottom/></border>` `</borders>` `<cellStyleXfs count="1">` `<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>` `</cellStyleXfs>` `<cellXfs count="2">` `<xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>` `<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>` `</cellXfs>` `</styleSheet>` INTO lv_xmlstr. PERFORM addtozip USING lo_zip lv_xmlstr `xl/styles.xml`.
*** xl/workbook.xml CLEAR lv_xmlstr. CONCATENATE `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>` `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" ` `xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">` `<sheets>` `<sheet name="Sheet1" sheetId="1" r:id="rId1"/>` `</sheets>` `</workbook>` INTO lv_xmlstr. PERFORM addtozip USING lo_zip lv_xmlstr 'xl/workbook.xml' .
cv_xstr = lo_zip->save( ).ENDFORM. "itabtoxlsx
*&---------------------------------------------------------------------**& Form addtozip*&---------------------------------------------------------------------*FORM addtozip USING po_zip TYPE REF TO cl_abap_zip pv_str pv_name. DATA lv_xstr TYPE xstring.
CALL FUNCTION 'SCMS_STRING_TO_XSTRING' EXPORTING text = pv_str encoding = '4110' IMPORTING buffer = lv_xstr. po_zip->add( name = pv_name content = lv_xstr ).ENDFORM. "addtozip
*&---------------------------------------------------------------------**& Form int2excelcol*&---------------------------------------------------------------------*FORM int2excelcol USING value(pv_int) CHANGING cv_col. DATA: lv_mod TYPE i. STATICS: BEGIN OF lt_buff OCCURS 0, int TYPE i, out TYPE string, END OF lt_buff.
CLEAR cv_col. READ TABLE lt_buff WITH KEY int = pv_int BINARY SEARCH. IF sy-subrc = 0. cv_col = lt_buff-out. ELSE. lt_buff-int = pv_int. DO. lv_mod = pv_int MOD 26. pv_int = pv_int DIV 26. IF pv_int = 0 AND lv_mod = 0. EXIT. ELSEIF lv_mod = 0. pv_int = pv_int - 1. CONCATENATE 'Z' cv_col INTO cv_col. ELSE. lv_mod = lv_mod - 1. CONCATENATE sy-abcde+lv_mod(1) cv_col INTO cv_col. ENDIF. ENDDO.
lt_buff-out = cv_col. INSERT lt_buff INDEX sy-tabix. ENDIF.ENDFORM. "int2excelcol
*&---------------------------------------------------------------------**& Form downxstr*&---------------------------------------------------------------------*FORM downxstr USING pv_xstr pv_file pv_default. DATA lv_blen TYPE i. DATA lt_btab TYPE TABLE OF w3mime. DATA lv_path TYPE string. DATA lv_file TYPE string.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY' EXPORTING buffer = pv_xstr IMPORTING output_length = lv_blen TABLES binary_tab = lt_btab. IF pv_file IS INITIAL. cl_gui_frontend_services=>file_save_dialog( EXPORTING default_extension = 'XLSX' default_file_name = pv_default file_filter = 'Excel文件(*.XLSX)|*.XLSX' CHANGING filename = lv_path path = lv_path fullpath = lv_file EXCEPTIONS OTHERS = 4 ). ELSE. lv_file = pv_file. ENDIF. CHECK lv_file IS NOT INITIAL. CALL FUNCTION 'GUI_DOWNLOAD' EXPORTING bin_filesize = lv_blen filename = lv_file filetype = 'BIN' confirm_overwrite = '' TABLES data_tab = lt_btab EXCEPTIONS OTHERS = 22.ENDFORM. "downxstr老白：nice~，运行一下看看
小甲：好嘞~
老白：棒棒的，可以出徒啦~
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
0
0- 0
已为社区贡献11条内容
所有评论(0)