SAP百万数据如何下载EXCEL

1.第一种方法简单粗暴：会根据你的数据生成xml。优点：xml中度大小，速度快，50w数据800M；缺点不能分Sheet

    CALL TRANSFORMATION ztestc33 SOURCE itab = lt_data
                       RESULT XML DATA(xml).
    DATA(lt_rawdata) = cl_bcs_convert=>xstring_to_solix( iv_xstring  = xml ).

    CALL METHOD cl_gui_frontend_services=>gui_download
      EXPORTING
        bin_filesize = xstrlen( xml )
        filename     = 'D:\A.XLS'
        filetype     = 'BIN'
      CHANGING
        data_tab     = lt_rawdata
      EXCEPTIONS
        OTHERS       = 24.

2.第二种方法：想要分sheet页，就不能用常规xml了，第一种方法就不行了，需要手动生成SpreadsheetXML。

缺点是文件很大，50w数据1.5G左右；优点，简单易懂，根据你传入的ITAB来生成sheet页

必须包含特定的命名空间和结构，比如

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="Sheet1">
<Table>
<Row>
<Cell><Data ss:Type="String">Hello</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">
  <tt:root name="ITAB1"/>
  <tt:root name="ITAB2"/>
  <tt:root name="ITAB3"/>
  <tt:root name="ITAB4"/>
  <tt:root name="ITAB5"/>
  <tt:root name="ITAB6"/>
  <tt:template>
    <?mso-application progid="Excel.Sheet"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html=
"http://www.w3.org/TR/REC-html40">
      <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <Created>2015-06-05T18:19:34Z</Created>
        <LastSaved>2020-04-10T08:09:46Z</LastSaved>
        <Version>16.00</Version>
      </DocumentProperties>
      <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
        <AllowPNG/>
        <RemovePersonalInformation/>
      </OfficeDocumentSettings>
      <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowHeight>12645</WindowHeight>
        <WindowWidth>22260</WindowWidth>
        <WindowTopX>0</WindowTopX>
        <WindowTopY>0</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
      </ExcelWorkbook>
      <Styles>

      </Styles>

      <Worksheet ss:Name="制造费用">
        <Table>
          <tt:loop name="I" ref="ITAB1">
            <Row>
              <Cell>
                <Data ss:Type="String">
                  <tt:value ref="$I.RBUKRS"/>
                </Data>
              </Cell>
              <Cell>
                <Data ss:Type="String">
                  <tt:value ref="$I.FISCYEARPER"/>
                </Data>
              </Cell>
              <Cell>
                <Data ss:Type="String">
                  <tt:value ref="$I.GJAHR"/>
                </Data>
              </Cell>
            </Row>
          </tt:loop>
        </Table>
      </Worksheet>

      <Worksheet ss:Name="管理费用">
        <Table>
          <tt:loop name="I" ref="ITAB2">
            <Row>
              <Cell>
                <Data ss:Type="String">
                  <tt:value ref="$I.RBUKRS"/>
                </Data>
              </Cell>
              <Cell>
                <Data ss:Type="String">
                  <tt:value ref="$I.FISCYEARPER"/>
                </Data>
              </Cell>
            </Row>
          </tt:loop>
        </Table>
      </Worksheet>


    </Workbook>
  </tt:template>
</tt:transform>

3.利用if_ixml_document，生成office open XML,数据大了会溢出，dump

4.利用abap2xlsx，和方法3一样，数据大了会溢出，dump

5.利用doi,调用macro:速度快，但是50w数据需要分批填充，“3.分批写”时遇到vba瓶颈，无法彻底解决，也可能是写法问题，如果有人改进了还请留言回复。如果数据量在10W左右还是可以很快导出

'=================================================================
' 分批写入 R3 大数据版本
' 替换原 FillTableFromR3()
'=================================================================
Public Function FillTableFromR3NEW()
    
    Const BLOCK_SIZE As Long = 10000          '每块行数，可改
    Dim rngTarget As Range, ws As Worksheet
    Dim vData As Variant, vHead As Variant
    Dim tblData As Object, tblHead As Object
    Dim nRow As Long, nCol As Long
    Dim iStart As Long, iEnd As Long, p As Long
    Dim R3Table As Object
    Dim R3TableHeader As Object
    Dim Row As Object
    Dim ExcelRange As Excel.Range
    Dim ExcelRangeHeader As Excel.Range

     
    
    '===== 1. 写表头 =====
    If ThisWorkbook.Container.Tables("MARKETDATA_HEADER").Table Is Nothing Then
    Else
     Set R3TableHeader = ThisWorkbook.Container.Tables("MARKETDATA_HEADER").Table
     
     MaxNumColumns = R3TableHeader.Columns.Count
     Set ExcelRangeHeader = ThisWorkbook.Sheets(1).Range(ThisWorkbook.Sheets(1).Cells(1, 1), ThisWorkbook.Sheets(1).Cells(1, MaxNumColumns))
     ExcelRangeHeader.Value = R3TableHeader.DataEnd If
    


    
    '===== 2. 有数据表才继续 =====
    If ThisWorkbook.Container.Tables("MARKETDATA_FROM_R3").Table Is Nothing Then
    Else
        
        Set tblData = ThisWorkbook.Container.Tables("MARKETDATA_FROM_R3").Table
        vData = tblData.Data                            '二维数组
        nRow = UBound(vData, 1)
        nCol = UBound(vData, 2)
        
        
        '===== 3. 分批写 =====
        Application.ScreenUpdating = False
        For iStart = 0 To nRow - 1 Step BLOCK_SIZE
            DoEvents                                    '允许 ESC 中断
            If Len(Application.StatusBar) = 0 Then Application.StatusBar = "正在写入 " & iStart & "/" & nRow
            iEnd = Application.Min(iStart + BLOCK_SIZE - 1, nRow - 1)
            
            Set rngTarget = ThisWorkbook.Sheets(1).Range(ws.Cells(iStart + 2, 1), _
                                     ThisWorkbook.Sheets(1).Cells(iStart + 2 + (iEnd - iStart), nCol))
            
            'Index 一次性拿出一块数组
            rngTarget.Value = Application.Index(vData, _
                              Evaluate("ROW(" & (iStart + 1) & ":" & (iEnd + 1) & ")"), _
                              Evaluate("COLUMN(1:" & nCol & ")"))
        Next iStart
        
        Application.StatusBar = False
        Application.ScreenUpdating = True
    End If
    
End Function

6.利用CL_XLSX_DOCUMENT，生成xlsx。

优点：xlsx通过共享数据，将数据进行排序压缩，文件小，打开快，50w数据只需要100M左右；传统xls的SpreadsheetXML可能需要1.5G，文件大小对比明显。

缺点：速度慢，因为要生成共享数据，sharedStrings.xml，所有数据都会一次插入，50w数据会生成很久。标准类只能生成1个sheet页，如果多sheet填充，需要对类进行改造，并增强重写sheetname，重写初始化workbook.xml。

REPORT ztestc_xlsx.


CLASS zcl_itab_to_excel DEFINITION." PUBLIC FINAL.
  PUBLIC SECTION.
    METHODS itab_to_xstring
      IMPORTING ir_data_ref       TYPE REF TO data
      RETURNING VALUE(rv_xstring) TYPE xstring.
ENDCLASS.

CLASS zcl_itab_to_excel IMPLEMENTATION.
  METHOD itab_to_xstring.
    FIELD-SYMBOLS: <fs_data> TYPE ANY TABLE.
    CLEAR rv_xstring.
    ASSIGN ir_data_ref->* TO <fs_data>.
    TRY.
        cl_salv_table=>factory(
        IMPORTING r_salv_table = DATA(lo_table)
        CHANGING t_table = <fs_data>
        ).

        DATA(lt_fcat) = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
        r_columns = lo_table->get_columns( )
        r_aggregations = lo_table->get_aggregations( )
        ).

        DATA(lo_result) = cl_salv_ex_util=>factory_result_data_table(
        r_data = ir_data_ref
        t_fieldcatalog = lt_fcat
        ).

        zcl_salv_bs_tt_util=>transform(
        EXPORTING
        xml_type = if_salv_bs_xml=>c_type_xlsx
        xml_version = cl_salv_bs_a_xml_base=>get_version( )
        it_result_data = VALUE #( ( sheet_name = 'test1' data = lo_result )
*                                  ( sheet_name = 'test2' data = lo_result )
                                )
        xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export
        gui_type = if_salv_bs_xml=>c_gui_type_gui
        IMPORTING xml = rv_xstring
        ).


      CATCH cx_root.
        CLEAR rv_xstring.
    ENDTRY.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.

  SELECT *
   FROM acdoca
    UP TO 500000 ROWS
  INTO TABLE @DATA(lt_data).

  DATA(lv_xstring) = NEW zcl_itab_to_excel( )->itab_to_xstring( REF #( lt_data ) ).
  DATA(lt_rawdata) = cl_bcs_convert=>xstring_to_solix( iv_xstring  = lv_xstring ).

  CALL METHOD cl_gui_frontend_services=>gui_download
    EXPORTING
      bin_filesize = xstrlen( lv_xstring )
      filename     = 'D:\A.XLSX'
      filetype     = 'BIN'
    CHANGING
      data_tab     = lt_rawdata
    EXCEPTIONS
      OTHERS       = 24.