背景:下载excel方法很多,这个xml方法下载速度快,支持excel比较复杂多样的格式
- 下载功能,报表代码
1、alv的user_command
FORM frm_user_command USING r_ucomm LIKE sy-ucomm
selfield TYPE slis_selfield.
DATA lo_grid TYPE REF TO cl_gui_alv_grid.
CALL FUNCTION 'GET_GLOBALS_FROM_SLVC_FULLSCR'
IMPORTING
e_grid = lo_grid.
CASE r_ucomm.
WHEN 'EXCEL'.
PERFORM frm_excel_download.
ENDCASE.
selfield-refresh = 'X'.
ENDFORM.
2、FORM:frm_excel_download
FORM frm_excel_download .
"使用xml的下载方式
DATA: lv_path TYPE rlgrap-filename.
DATA: lt_xml_table TYPE STANDARD TABLE OF string,
lv_xmlstr TYPE string.
DATA: lv_count TYPE n LENGTH 10.
FIELD-SYMBOLS: <fs_value> TYPE any.
"弹出窗口选择保存路径
PERFORM frm_save_path CHANGING lv_path.
********关键代码部分*****************************************
DESCRIBE TABLE gt_alv_out LINES lv_count.
lv_count = lv_count + 3.
CALL TRANSFORMATION zfir045_1
SOURCE root = gt_alv_out[]
RESULT XML lv_xmlstr.
REPLACE FIRST OCCURRENCE OF 'encoding="utf-16"' IN lv_xmlstr WITH 'encoding="gbk"'."显示中文
REPLACE FIRST OCCURRENCE OF 'cs_count' IN lv_xmlstr WITH lv_count."行数,替代xml文件里的cs_count
APPEND lv_xmlstr TO lt_xml_table.
********关键代码部分*****************************************
DATA: lv_file TYPE string.
lv_file = lv_path.
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = lv_file
filetype = 'ASC'
TABLES
data_tab = lt_xml_table
EXCEPTIONS
file_write_error = 1
no_batch = 2
gui_refuse_filetransfer = 3
invalid_type = 4
OTHERS = 5.
MESSAGE '数据下载成功!' TYPE 'S'.
ENDFORM.
3、FORM 弹出窗口选择保存路径
FORM frm_save_path CHANGING pv_path TYPE rlgrap-filename.
DATA:lv_filename TYPE string,
lv_path TYPE string.
"名字指定
IF p_mx = 'X'.
CONCATENATE '进耗存报表_明细_' sy-datum '.XLS' INTO lv_filename.
ELSEIF p_hz = 'X'.
CONCATENATE '进耗存报表_汇总_' sy-datum '.XLS' INTO lv_filename.
ENDIF.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_filename = lv_filename
def_path = 'D:W'
mask = '.XLS'
mode = 'S'
title = 'DOWNLOAD'
IMPORTING
filename = pv_path
* RC =
EXCEPTIONS
inv_winsys = 1
no_batch = 2
selection_cancel = 3
selection_error = 4
OTHERS = 5.
IF pv_path IS INITIAL. "选择取消的情况
MESSAGE e000(su) WITH '已取消操作!'.
ENDIF.
ENDFORM.
4、CALL TRANSFORMATION zfir045_1
这个是重点!
1)需要事务码:strans,创建转换:zfir045_1
2)需要对xml进行编辑处理(以下xml处理细节仅供参考,并非以上程序中涉及的代码)
(1)<tt:loop ref=".ROOT"> 该标签可以实现循环数据行功能,将 root 表中的数据,循环写入模板
(2)ss:ExpandedRowCount="cs_count",这个属性值,改为"cs_count",会默认为excel模板行数,需要我们自己编辑
<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">
<tt:root name="ROOT"/>
<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">
<Author>马宇</Author>
<LastAuthor>马宇</LastAuthor>
<Created>2019-07-29T09:17:10Z</Created>
<LastSaved>2019-07-29T09:41:41Z</LastSaved>
<Version>16.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7500</WindowHeight>
<WindowWidth>20490</WindowWidth>
<WindowTopX>32767</WindowTopX>
<WindowTopY>32767</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Alignment ss:Vertical="Top"/>
</Style>
<Style ss:ID="s63">
<Alignment ss:Vertical="Top"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s64">
<Alignment ss:Horizontal="Right" ss:Vertical="Top"/>
<NumberFormat ss:Format="#,##0.000"/>
</Style>
<Style ss:ID="s65">
<Alignment ss:Horizontal="Right" ss:Vertical="Top"/>
<NumberFormat ss:Format="[$-F400]h:mm:ss\ AM/PM"/>
</Style>
<Style ss:ID="s66">
<Alignment ss:Horizontal="Right" ss:Vertical="Top"/>
<NumberFormat ss:Format="Short Date"/>
</Style>
<Style ss:ID="s67">
<Alignment ss:Vertical="Top"/>
<Borders>
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/>
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/>
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/>
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/>
</Borders>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25" ss:ExpandedColumnCount="29" ss:ExpandedRowCount="cs_count" x:FullColumns="1" x:FullRows="1">
<Row>
<Cell ss:StyleID="s67">
<Data ss:Type="String">计划日期</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">线别</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">生产顺序</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">工厂</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">客户</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">客户名称</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">成品料号</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">品名</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">原计划订单</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">订单</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">订单总数量</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">订单累计数量</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">计划数量</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">物料</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">单台用量倍率</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">订单开始日期</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">订单开始时间</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">订单结束日期</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">订单结束时间</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">交货日期</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">交货时间</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">交货数量</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">物料描述</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">物料组描述</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">供应商</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">供应商描述</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">采购组</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">采购组描述</Data>
</Cell>
<Cell ss:StyleID="s67">
<Data ss:Type="String">备注</Data>
</Cell>
</Row>
<tt:loop ref=".ROOT">
<Row>
<Cell ss:StyleID="s66">
<Data ss:Type="String"><tt:value ref='PSTTR'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='FEVOR'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='APRIO'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='WERKS'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='KUNNR'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='NAME1_KH'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='MATNR'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='WGBEZ'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='ZPLNUM'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='AUFNR'/></Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"><tt:value ref='GAMNG'/></Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"><tt:value ref='GSMNG_TJ'/></Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"><tt:value ref='GSMNG'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='IDNRK'/></Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"><tt:value ref='MNGKO'/></Data>
</Cell>
<Cell ss:StyleID="s66">
<Data ss:Type="String"><tt:value ref='GSTRP'/></Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String"><tt:value ref='GSUZP'/></Data>
</Cell>
<Cell ss:StyleID="s66">
<Data ss:Type="String"><tt:value ref='GLTRP'/></Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String"><tt:value ref='GLUZP'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='EEIND'/></Data>
</Cell>
<Cell ss:StyleID="s65">
<Data ss:Type="String"><tt:value ref='TIME_JH'/></Data>
</Cell>
<Cell ss:StyleID="s64">
<Data ss:Type="String"><tt:value ref='GSMNG_JH'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='MAKTX'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='WGBEZ1'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='LIFNR'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='NAME1_CS'/></Data>
</Cell>
<Cell ss:StyleID="s63">
<Data ss:Type="String"><tt:value ref='EKGRP'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='EKNAM'/></Data>
</Cell>
<Cell ss:StyleID="s62">
<Data ss:Type="String"><tt:value ref='REMARK'/></Data>
</Cell>
</Row>
</tt:loop>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>4</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</tt:template>
</tt:transform>
END.