SAP ABAP OPENXML生成Excel

已于 2022-08-11 20:09:12 修改
阅读量614 收藏 2
点赞数
分类专栏： ABAP 文章标签： 数据库 前端 java sap abap
于 2022-08-01 10:07:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gui951/article/details/126095426
版权

SAP ABAP OPENXML生成Excel

FUNCTION zfi_create_openxml_excel.
*"----------------------------------------------------------------------
*"*"本地接口：
*"  IMPORTING
*"     REFERENCE(I_DATA) TYPE REF TO  DATA
*"  EXPORTING
*"     REFERENCE(E_DOCUMENT_XSTR) TYPE  XSTRING
*"  TABLES
*"      T_FCAT STRUCTURE  LVC_S_FCAT
*"----------------------------------------------------------------------
  DATA: l_blank_doc   TYPE REF TO cl_abap_zip,
        l_converter   TYPE REF TO cl_abap_conv_out_ce.

  DATA: lv_xml         TYPE string,
        lv_timestamp   TYPE timestamp,
        lv_utc         TYPE string,
        lv_utc_str     TYPE string.

  DATA: lv_worksheet_name TYPE  string VALUE 'Sheet1',
        lv_meta_title TYPE  string,
        lv_meta_subject TYPE  string,
        lv_meta_description TYPE  string,
        lv_meta_keywords TYPE  string.
  DATA: lv_row    TYPE i,
        lv_column TYPE i,
        lv_index TYPE sy-index VALUE 0,
        lv_index_str TYPE string,
        lv_excel_column TYPE char2,
        lv_row_str TYPE string,
        lv_str    TYPE string,
        lv_count TYPE i,
        lv_count_str TYPE string.

  FIELD-SYMBOLS: <fs_table> TYPE ANY TABLE,
                 <ls_value>.

  CHECK i_data IS NOT INITIAL AND t_fcat[] IS NOT INITIAL.

  ASSIGN i_data->* TO <fs_table>.
  lv_row = lines( <fs_table> ) + 1.
  lv_column = lines( t_fcat[] ).

* Create the empty zip package
  CREATE OBJECT l_blank_doc.

* Create the obligatory xl/worksheets/sheet1.xml
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main`. "#EC NOTEXT
  lv_xml = lv_xml && `" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships`. "#EC NOTEXT
  lv_xml = lv_xml && `" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006`. "#EC NOTEXT
  lv_xml = lv_xml && `" mc:Ignorable="x14ac xr xr2 xr3`.    "#EC NOTEXT
  lv_xml = lv_xml && `" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac`. "#EC NOTEXT
  lv_xml = lv_xml && `" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision`. "#EC NOTEXT
  lv_xml = lv_xml && `" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2`. "#EC NOTEXT
  lv_xml = lv_xml && `" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3`. "#EC NOTEXT
  lv_xml = lv_xml && `" xr:uid="{00000000-0001-0000-0000-000000000000}">`. "#EC NOTEXT

  lv_row_str = lv_row.
  PERFORM frm_get_excel_column USING lv_column CHANGING lv_excel_column.
  lv_str = '"A1:' && lv_excel_column && lv_row_str && '"'.
  CONDENSE lv_str NO-GAPS.

  lv_xml = lv_xml && '<dimension ref=' && lv_str && '/>'.   "#EC NOTEXT
  CLEAR: lv_row_str, lv_excel_column, lv_str.

  lv_xml = lv_xml && `<sheetViews><sheetView tabSelected="1" workbookViewId="0"><selection activeCell="A1" sqref="A1"/></sheetView></sheetViews>`. "#EC NOTEXT
* sheetdata
  lv_xml = lv_xml && `<sheetData>`.                         "#EC NOTEXT
* rows
  DO lv_row TIMES.

    lv_row_str = sy-index.
    lv_row_str = lv_row_str.
    CONDENSE lv_row_str NO-GAPS.
    lv_xml = lv_xml && '<row r="' && lv_row_str && '" spans="1:13" x14ac:dyDescent="0.25">'. "#EC NOTEXT

    DO lv_column TIMES.
      PERFORM frm_get_excel_column USING sy-index CHANGING lv_excel_column.
      lv_index_str = lv_index.
      CONDENSE lv_index_str NO-GAPS.
      lv_xml = lv_xml && '<c r="' && lv_excel_column && lv_row_str && '" t="s"><v>' && lv_index_str && '</v></c>'. "#EC NOTEXT
      lv_index = lv_index + 1.
      CLEAR: lv_excel_column, lv_index_str.
    ENDDO.
    lv_xml = lv_xml && '</row>'.                            "#EC NOTEXT

    CLEAR: lv_row_str.
  ENDDO.

  lv_xml = lv_xml && `</sheetData>`.                        "#EC NOTEXT
  lv_xml = lv_xml && `<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `</worksheet>`.                        "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = 'xl/worksheets/sheet1.xml' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory xl/sharedStrings.xml
  lv_count = lv_row * lv_column.
  lv_count_str = lv_count.
  CONDENSE lv_count_str NO-GAPS.

  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="`. "#EC NOTEXT
  lv_xml = lv_xml && lv_count_str && '" uniqueCount="' && lv_count_str && '">'. "#EC NOTEXT

  LOOP AT t_fcat INTO DATA(lw_fcat).
    lv_xml = lv_xml && '<si><t>' && lw_fcat-scrtext_l && '</t><phoneticPr fontId="1" type="noConversion"/></si>'. "#EC NOTEXT
    CLEAR: lw_fcat.
  ENDLOOP.

  LOOP AT <fs_table> ASSIGNING FIELD-SYMBOL(<ls_data>).
    DO lv_column TIMES.
      ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <ls_value>.
      IF sy-subrc = 0.
        lv_str = <ls_value>.
        REPLACE ALL OCCURRENCES OF '&' IN lv_str WITH '&amp;'.
        REPLACE ALL OCCURRENCES OF '<' IN lv_str WITH '&lt;'.
        REPLACE ALL OCCURRENCES OF '>' IN lv_str WITH '&gt;'.
        REPLACE ALL OCCURRENCES OF `'` IN lv_str WITH '&apos;'.
        REPLACE ALL OCCURRENCES OF '"' IN lv_str WITH '&quot;'.
        lv_xml = lv_xml && '<si><t>' && lv_str && '</t><phoneticPr fontId="1" type="noConversion"/></si>'. "#EC NOTEXT
      ENDIF.
      CLEAR: lv_str.
    ENDDO.
  ENDLOOP.
  lv_xml = lv_xml && `</sst>`.                              "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = 'xl/sharedStrings.xml' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory [Content_Types].xml
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">`. "#EC NOTEXT
  lv_xml = lv_xml && `<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Default Extension="xml" ContentType="application/xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `</Types>`.                            "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = '[Content_Types].xml' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory _rels/.rels
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `</Relationships>`.                    "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = '_rels/.rels' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory docProps/app.xml
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" `. "#EC NOTEXT
  lv_xml = lv_xml && `xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">`. "#EC NOTEXT
  lv_xml = lv_xml && `<Application>SAP NetWeaver Application Server</Application>`. "#EC NOTEXT
  lv_xml = lv_xml && `<DocSecurity>0</DocSecurity>`.        "#EC NOTEXT
  lv_xml = lv_xml && `<TitlesOfParts>`.                     "#EC NOTEXT
  lv_xml = lv_xml && `<vt:vector size="2" baseType="lpstr"><vt:lpstr>` && lv_worksheet_name && `</vt:lpstr>`. "#EC NOTEXT
  lv_xml = lv_xml && `<vt:lpstr>HELP</vt:lpstr></vt:vector>`. "#EC NOTEXT
  lv_xml = lv_xml && `</TitlesOfParts>`.                    "#EC NOTEXT
  lv_xml = lv_xml && `<Company>SAP</Company>`.              "#EC NOTEXT
  lv_xml = lv_xml && `<AppVersion>7.1000</AppVersion>`.     "#EC NOTEXT
  lv_xml = lv_xml && `</Properties>`.                       "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = 'docProps/app.xml' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory docProps/core.xml
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" `. "#EC NOTEXT
  lv_xml = lv_xml && `xmlns:dc="http://purl.org/dc/elements/1.1/" `. "#EC NOTEXT
  lv_xml = lv_xml && `xmlns:dcterms="http://purl.org/dc/terms/" `. "#EC NOTEXT
  lv_xml = lv_xml && `xmlns:dcmitype="http://purl.org/dc/dcmitype/" `. "#EC NOTEXT
  lv_xml = lv_xml && `xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">`. "#EC NOTEXT
  lv_xml = lv_xml && `<dc:title>` && lv_meta_title && `</dc:title>`. "#EC NOTEXT
  lv_xml = lv_xml && `<dc:subject>` && lv_meta_subject && `</dc:subject>`. "#EC NOTEXT
  lv_xml = lv_xml && `<dc:creator>` && sy-uname && `</dc:creator>`. "#EC NOTEXT
  lv_xml = lv_xml && `<cp:keywords>` && lv_meta_keywords && `</cp:keywords>`. "#EC NOTEXT
  lv_xml = lv_xml && `<dc:description>` && lv_meta_description && `</dc:description>`. "#EC NOTEXT
  GET TIME STAMP FIELD lv_timestamp. " Get current UTC
  lv_utc = lv_timestamp.
  CONCATENATE lv_utc+0(4) '-' lv_utc+4(2) '-' lv_utc+6(2) 'T' lv_utc+8(2) ':' lv_utc+10(2) ':' lv_utc+12(2) 'Z'  INTO lv_utc_str. "#EC NOTEXT
  lv_xml = lv_xml && `<dcterms:created xsi:type="dcterms:W3CDTF">` && lv_utc_str && `</dcterms:created>`. "#EC NOTEXT
  lv_xml = lv_xml && `<dcterms:modified xsi:type="dcterms:W3CDTF">` && lv_utc_str && `</dcterms:modified>`. "#EC NOTEXT
  lv_xml = lv_xml && `</cp:coreProperties>`.                "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = 'docProps/core.xml' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory xl/_rels/workbook.xml.rels
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `</Relationships>`.                    "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = 'xl/_rels/workbook.xml.rels' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory xl/styles.xml
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">`. "#EC NOTEXT
  lv_xml = lv_xml && `<fonts count="2">`.                   "#EC NOTEXT
  lv_xml = lv_xml && `<font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/><scheme val="minor"/></font>`. "#EC NOTEXT
  lv_xml = lv_xml && `<font><b/><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/><scheme val="minor"/></font>`. "#EC NOTEXT
  lv_xml = lv_xml && `</fonts>`.                            "#EC NOTEXT
  lv_xml = lv_xml && `<fills>`.                             "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="none"/></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="gray125" /></fill>`. "#EC NOTEXT
  "lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor theme="0" tint="-0.14999847407452621" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FFDBD5BF" /></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FFE2EBF1" /></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FFF3E9BE" /></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FFEDF4F8" /></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor theme="9" tint="0.59999389629810485" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FFFC6868" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor theme="4" tint="0.59999389629810485" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor theme="6" tint="0.59999389629810485" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FFFFFF00" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FFFF3300" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `<fill><patternFill patternType="solid"><fgColor rgb="FF92D050" /><bgColor indexed="64"/></patternFill></fill>`. "#EC NOTEXT
  lv_xml = lv_xml && `</fills>`.                            "#EC NOTEXT
  lv_xml = lv_xml && '<borders count="2"><border><left/><right/><top/><bottom/><diagonal/></border>'. "#EC NOTEXT
  lv_xml = lv_xml && '<border><left style="thin"><color rgb="FFFFFFFF" /></left><right style="thin"><color rgb="FFFFFFFF" /></right>'. "#EC NOTEXT
  lv_xml = lv_xml && '<top style="thin"><color rgb="FFFFFFFF" /></top><bottom style="thin"><color rgb="FFFFFFFF" /></bottom><diagonal /></border>'. "#EC NOTEXT
  lv_xml = lv_xml && '</borders>'.                          "#EC NOTEXT
  lv_xml = lv_xml && `<cellStyleXfs>`.                      "#EC NOTEXT
  lv_xml = lv_xml && `<xf numFmtId="49" fontId="0" fillId="0" borderId="0"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<xf numFmtId="49" applyProtection="0" fontId="1" fillId="2" borderId="1" applyFont="0"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<xf numFmtId="49" applyProtection="0" fontId="1" fillId="3" borderId="1" applyFont="0"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<xf numFmtId="49" applyProtection="0" fontId="1" fillId="4" borderId="1" applyFont="0"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<xf numFmtId="49" applyProtection="0" fontId="1" fillId="5" borderId="1" applyFont="0"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `</cellStyleXfs>`.                     "#EC NOTEXT
  lv_xml = lv_xml && `<cellXfs count="2"><xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<xf numFmtId="49" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" /></cellXfs>`. "#EC NOTEXT
  lv_xml = lv_xml && `<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>`. "#EC NOTEXT
  lv_xml = lv_xml && `<dxfs count="0"/><tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `</styleSheet>`.                       "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = 'xl/styles.xml' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

* Create the obligatory xl/workbook.xml
  lv_xml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>`. "#EC NOTEXT
  lv_xml = lv_xml && `<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">`. "#EC NOTEXT
  lv_xml = lv_xml && `<fileVersion appName="xl" lastEdited="1" lowestEdited="1" rupBuild="4505"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<workbookPr filterPrivacy="1" defaultThemeVersion="124226"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `<bookViews><workbookView xWindow="240" yWindow="105" windowWidth="14805" windowHeight="8010"/></bookViews>`. "#EC NOTEXT
  lv_xml = lv_xml && `<sheets>`.                            "#EC NOTEXT
  lv_xml = lv_xml && `<sheet name="` && lv_worksheet_name && `" sheetId="1" r:id="rId1"/>`. "#EC NOTEXT
  lv_xml = lv_xml && `</sheets>`.                           "#EC NOTEXT
  lv_xml = lv_xml && `<calcPr calcId="124519"/>`.           "#EC NOTEXT
  lv_xml = lv_xml && `</workbook>`.                         "#EC NOTEXT
  l_converter = cl_abap_conv_out_ce=>create( ).
  l_converter->write( data = lv_xml ).
  l_blank_doc->add( name = 'xl/workbook.xml' content = l_converter->get_buffer( ) ). "#EC NOTEXT
  FREE l_converter.

  e_document_xstr = l_blank_doc->save( ).



  DATA:  l_length TYPE i,
  l_xml_stream TYPE xml_rawdata.

  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
    EXPORTING
      buffer        = e_document_xstr
    IMPORTING
      output_length = l_length
    TABLES
      binary_tab    = l_xml_stream.

  CALL METHOD cl_gui_frontend_services=>gui_download
    EXPORTING
      bin_filesize = l_length
      filetype     = 'BIN'
      filename     = 'E:/123RRR.XLSX'
    CHANGING
      data_tab     = l_xml_stream
    EXCEPTIONS
      OTHERS       = 1.


ENDFUNCTION.
*&---------------------------------------------------------------------*
*&      Form  FRM_GET_EXCEL_COLUMN
*&---------------------------------------------------------------------*
FORM frm_get_excel_column  USING uv_column CHANGING cv_excel_column.
  DATA: lv_mod TYPE i,
        lv_div TYPE i.
  lv_mod = uv_column MOD 26 - 1."余数
  lv_div = ( uv_column - 1 ) DIV 26 - 1."商
  IF lv_div >= 0.
    cv_excel_column = sy-abcde+lv_div(1) && sy-abcde+lv_mod(1).
  ELSE.
    cv_excel_column = sy-abcde+lv_mod(1).
  ENDIF.
  CLEAR: lv_mod, lv_div.
ENDFORM.                    " FRM_GET_EXCEL_COLUMN
令狐九剑
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
用abap程序把SAP表定义导出到EXCEL
hgx0312的博客
07-21 444
通过输入表ID的开头字符，导出所有满足条件的表的定义到EXCEL文档中，比如输入Z后执行，将把所有Z开头的表及构造导出到同一个EXCEL里，稍加修改也可以把每个表的定义导出到各自EXCEL里（FUNCTION 'WS_DOWNLOAD'放到LOOP里面）......
abap2xlsx:从ABAP生成专业的Excel电子表格
05-14
abap2xlsx-使用ABAP读取并生成Excel电子表格 有关一般信息，请参阅博客系列和。 请参阅官方Wiki以获取。 讨论板 我们使用作为讨论板。 您可以在发布新问题之前。 您还可以来限制搜索。 当您要时，请使用此链接，标签abap2xlsx将被预填充）。 松弛 我们在SAP Mentors＆Friends Slack中拥有频道。 您可以。
sap abap通过STRANS导出excel（XLST程序）
qq_38913810的博客
09-15 384
打开这个xml文件，用浏览器打开 ，得到模板源码。新建stans-简单转换。结构定义两个字段就行。
ABAP XML填充Excel表单
xunmeng07的博客
09-07 555
填充Excel表单的方式有很多，例如常见的OLE、函数、DOI，今天来分享一种不常见的xml的填充表单的方式
openxml创建excel文件
qq_42678477的博客
02-09 392
// WorkbookPart -> Workbook // (上面 (wbPart.AddNewPart<WorksheetPart>) )WorksheetPart -> Worksheet -> SheetData //Sheets Sheet WorksheetPart Worksh...
ABAP下载excel-方法XML
weixin_55593130的博客
08-23 884
ABAP下载excel-方法XML
ABAP读取Excel方法大全
u012232542的博客
11-20 3009
前言 ABAP读取Excel文件可以使用多种方式实现，OLE是最常用的技术，但速度及兼容性较差；也可以使用DOI接口读取，最新的技术则通过XML技术进行解析，采用最新的面向对象编程，容易理解，且兼容性较好。 方法 标准函数 函数名 说明 TEXT_CONVERT_XLS_TO_SAP 基于OLE，需要内表结构与Exce列顺序一致 ALSM_EXCEL_TO_INTERNAL_TABL 基于OLE,按单元读取出来，最长只支持50个字符 UPLOAD_XLS_FILE_2_ITAB （尚
ABAP EXCEL通用类
06-20
总的来说，ZCL_EXCEL是SAP ABAP开发者处理Excel文件的重要工具，它提供了一套全面的API来创建、修改和读取Excel文档，极大地扩展了ABAP在数据处理和报告生成方面的功能。无论是生成报表、进行数据分析还是自动化数据...
SAP ABAP 电子书
10-19
SAP ABAP（Advanced Business Application Programming）是SAP公司推出的一种高级业务应用编程语言，用于开发在SAP R/3和SAP NetWeaver平台上的应用程序。这些电子书提供了全面的资源，帮助学习者深入理解并掌握SAP ...
SAP ABAP ZALSM-EXCEL-TO-INTERNAL-TABLE 完整实施附件（源代码、说明）
01-06
在 SAP ABAP 开发环境中，处理 Excel 文件是常见的任务，特别是在数据导入导出或批量处理场景下。ZALSM_EXCEL_TO_INTERNAL_TABLE 是一个自定义增强函数，它提供了将 Excel 数据转换为 SAP 内部表的功能，使得开发...
生成XML文件ABAP程序
07-09
生成XML文件ABAP程序 REPORT zams_xml_dom_create . TYPE-POOLS: ixml. CLASS cl_ixml DEFINITION LOAD. TYPES: BEGIN OF xml_line, data(256) TYPE x, END OF xml_line. DATA: l_ixml TYPE REF TO if_ixml, l_streamfactory TYPE REF TO if_ixml_stream_factory, l_ostream TYPE REF TO if_ixml_ostream, l_renderer TYPE REF TO if_ixml_renderer, l_document TYPE REF TO if_ixml_document. DATA:l_element_ArchiveBatch TYPE REF TO if_ixml_element, l_element_BatchNumber TYPE REF TO if_ixml_element, l_element_TotalFiles TYPE REF TO if_ixml_element, l_element_TotalVolumes TYPE REF TO if_ixml_element, l_element_TotalPieces TYPE REF TO if_ixml_element, l_element_Entries TYPE REF TO if_ixml_element, l_element_Entry TYPE REF TO if_ixml_element, l_element_RecordsCombiningType TYPE REF TO if_ixml_element, l_element_FilePath TYPE REF TO if_ixml_element, l_element_SourceFileName TYPE REF TO if_ixml_element, l_element_ClassificationCode TYPE REF TO if_ixml_element, l_element_ElectronicRecordCode TYPE REF TO if_ixml_element, l_value TYPE string, l_name type string. DATA: l_xml_table TYPE TABLE OF xml_line, l_xml_size TYPE i, l_rc TYPE i. START-OF-SELECTION. *Creating a ixml factory l_ixml = cl_ixml=>create( ). *Creating the dom object model l_document = l_ixml->create_document( ). *Fill root node with value ArchiveBatch l_element_ArchiveBatch = l_document->create_simple_element( name = 'ArchiveBatch' parent = l_document ). * clear l_value. * l_value = '"http://www.w3.org/2001/XMLSchema-instance"'. * l_rc = l_element_ArchiveBatch->set_attribute( name = 'xmlns: *xsi' * value = l_value ). * * clear l_value. * concatenate '"D:\dev\vs\projects\AMS\DotNet\Sinopec.Ams.' * 'Integration\bin\Debug\config\AmsIntegration.xsd"' * into l_value. * l_name = 'xsi:noNamespaceSchemaLocation'. * l_rc = l_element_ArchiveBatch->set_attribute( name = l_name *value = l_value ). *BatchNumber l_element_BatchNumber = l_document->create_simple_element( name = 'BatchNumber' value = '20100609001' parent = l_element_ArchiveBatch ). *TotalFiles l_element_TotalFiles = l_document->create_simple_element( name = 'TotalFiles' value = '5' parent = l_element_ArchiveBatch ). *TotalVolumes l_element_TotalVolumes = l_document->create_simple_element( name = 'TotalVolumes' value = '3' parent = l_element_ArchiveBatch ). *TotalPieces l_element_TotalPieces = l_document->create_simple_element( name = 'TotalPieces' value = '5' parent = l_element_ArchiveBatch ). *Entries l_element_Entries = l_document->create_simple_element( name = 'Entries' parent = l_element_ArchiveBatch ). *Entry1 l_element_Entry = l_document->create_simple_element( name = 'Entry' parent = l_element_Entries ). *Entry1下面的子项 *RecordsCombiningType l_element_RecordsCombiningType = l_document->create_simple_element( name = 'RecordsCombiningType' value = 'Volume' parent = l_element_Entry ). *FilePath l_element_FilePath = l_document->create_simple_element( name = 'FilePath' value = '全宗001/目录001/案卷001/档案一.docx' parent = l_element_Entry ). *SourceFileName l_element_SourceFileName = l_document->create_simple_element( name = 'SourceFileName' value = '档案一' parent = l_element_Entry ). *ClassificationCode l_element_ClassificationCode = l_document->create_simple_element( name = 'ClassificationCode' value = 'wenshu' parent = l_element_Entry ). *ElectronicRecordCode l_element_ElectronicRecordCode = l_document->create_simple_element( name = 'ElectronicRecordCode' value = 'BJSH-001' parent = l_element_Entry ). *Entry1 * Creating a stream factory l_streamfactory = l_ixml->create_stream_factory( ). * Connect internal XML table to stream factory l_ostream = l_streamfactory->create_ostream_itable( table = l_xml_table ). * Rendering the document l_renderer = l_ixml->create_renderer( ostream = l_ostream document = l_document ). l_rc = l_renderer->render( ). * Saving the XML document l_xml_size = l_ostream->get_num_written_raw( ). CALL METHOD cl_gui_frontend_services=>gui_download EXPORTING bin_filesize = l_xml_size filename = 'c:\temp\metas.xml' filetype = 'BIN' CHANGING data_tab = l_xml_table EXCEPTIONS OTHERS = 24. IF sy-subrc <> 0. MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4. ENDIF.
ABAP数据快速填充EXCEL
03-18
ABAP数据快速填充EXCEL，比传统的OLE方式要运行速度快。
SAP ABAP初级培训手册
08-22
SAP ABAP初级培训手册 本手册旨在为初级学员提供SAP ABAP的基本知识和实践经验，帮助他们快速掌握ABAP语言的基本语法和开发技术。以下是本手册的知识点概要： 一、ABAP基础语法 * ABAP语言的基本结构和组成 * ...
SAP ABAP开发快捷键
05-12
SAP ABAP开发快捷键 SAP ABAP开发是一种功能强大且灵活的编程语言，用于开发SAP系统中的业务逻辑。然而，在ABAP开发过程中，效率是一个非常重要的因素。为了提高开发效率，熟悉ABAP开发快捷键是非常必要的。在本文...
SAP ABAP编程 数据库内容导出到excel
心丨悦 的专栏
09-12 2611
& 数据库内容导出到excel，
使用openXML 不用插件导出excel
weixin_30267691的博客
04-14 145
注释很详细，不做解释了，有疑问可以提问 using System.IO; using System.Text; namespace iLIS.Common { /// <summary> /// 生成Excel文档内容 /// 存入工作流 /// </summary> public class...
sap abap通过STRANS导出excel （简单转换）
最新发布
qq_38913810的博客
09-15 542
打开这个xml文件，用浏览器打开 ，得到模板源码。新建stans-简单转换 事务码strans。
使用OPENXML函数将XML文档转换为行结果集
weixin_34082177的博客
12-10 84
FOR XML子句都是将行结果集转换为XML结果集，那么如果想要将XML文档转换成行结果集，这时就要使用OPENXML函数。OPENXML在SQL Server 2000中就已经提供，但是在SQL Server 2005对该函数进行了增强。 OPENXML的语法为： OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags...
通过XML转换下载.xlsx格式的excel文件
banin4739的博客
08-30 777
在SAP系统中，通过SE11显示一个table的内容，可以发现ALV grid的显示界面上有个spread sheet的标准按钮，点击这个按钮可以将显示的内容下载成excel文件，其中也包括扩展名.xlsx的文件。 &amp;lt;img class="alignnone size-full wp-image-2802" src="http://www.baidusap.c...
sap abap excel
08-18
如果您想在 ABAP 中处理 Excel 文件，可以使用 SAP 提供的功能模块和类来实现。 以下是一些在 SAP ABAP 中处理 Excel 的常见方法： 1. 使用函数模块：SAP 提供了一些函数模块，如 'ALSM_EXCEL_TO_INTERNAL_TABLE' ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

令狐九剑

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值