SAP ABAP 内表导出EXCEL 新方法
ABAP 内表导出EXCEL可以用OLE方法，也可以用SAP 已有的函数:
RH_START_EXCEL_WITH_DATA,
GUI_DOWNLOAD,
还可以：
data GT_HEADER TYPE LVC_T_FCAT."需要导出EXCEL的标题
data: r_result_data type ref to cl_salv_ex_result_data_table.
data: lr_data type ref to data.
data: l_xml type xstring.
DATA: lr_excel2007 type ref to CL_SALV_BS_OFFICE2007_BASE.
CHECK OUT_DATA[] IS NOT INITIAL. "内表数据
get reference of OUT_DATA[] into lr_data.
r_result_data = cl_salv_ex_util=>FACTORY_RESULT_DATA_TABLE(
* T_SELECTED_ROWS = lt_lvc_row
* T_SELECTED_COLUMNS = lt_sel_cols
* T_SELECTED_CELLS = lt_sel_cells
R_DATA = lr_data
* S_LAYOUT = gs_layo
T_FIELDCATALOG = GT_HEADER[]
* T_SORT = gt_sort
* T_FILTER = gt_filter
* T_HYPERLINKS = me->mt_hyperlinks
* S_CURRENT_CELL = ls_cur_cell
* HYPERLINK_ENTRY_COLUMN = ls_hyper_entry
* DROPDOWN_ENTRY_COLUMN = ls_dropdown_entry
* T_DROPDOWN_VALUES = lt_drdn )
* r_top_of_list = lr_form_tol
* r_end_of_list = lr_form_eol
).
CREATE OBJECT lr_excel2007 TYPE CL_SALV_BS_EX_OFFICE2007
EXPORTING
r_result_data = r_result_data.
if lr_excel2007 is not initial.
call method lr_excel2007->transform
receiving
excel_xml = L_xml.
endif.
CALL FUNCTION 'XML_EXPORT_DIALOG'
EXPORTING
I_XML = L_XML
I_DEFAULT_EXTENSION = 'XLSX'
I_INITIAL_DIRECTORY = ''
I_DEFAULT_FILE_NAME = 'EXPORT.XLSX'
I_MASK = 'Excel (*.XLSX)|*.XLSX'
* I_APPLICATION =
EXCEPTIONS
APPLICATION_NOT_EXECUTABLE = 1
OTHERS = 2
.
下面介绍一种新的方法：适用于要求EXCEL较复杂的格式，样式。但此方法不能实现EXCEL中有图片等VBA按件，而且只能是2003格式，下载来的文件扩展名是.xls 。
1.先编辑好EXCEL文件，
2. 另存为XML文本文件
xml 文件是这个样子的
3. 接下来就是拼字符串了。复制 xml 文件中的所有文本到ABAP代码中，根据内表数据填充xml字符中的数据部分。
*--------------------------------------------------------------------*
FORM create_excel TABLES t_header STRUCTURE zxlsh " zxlsh 结构包含 LVC_S_FCAT，增加了字段COLWIDTH来设置EXCEL的列宽
itab "内表数据
CHANGING xls_string type string. "拼接的字符串
data l_coln type i.
data l_rown type i.
l_coln = lines( t_header ). "列数
l_rown = lines( itab ) + 1.
xls_string =
`<?xml version="1.0"?>` &&
`<?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>2019-09-20T05:38:22Z</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>12648</WindowHeight>` &&
`<WindowWidth>22260</WindowWidth>` &&
`<WindowTopX>0</WindowTopX>` &&
`<WindowTopY>0</WindowTopY>` &&
`<ProtectStructure>False</ProtectStructure>` &&
`<ProtectWindows>False</ProtectWindows>` &&
`</ExcelWorkbook>` &&
`<Styles>` &&
`<Style ss:ID="Default" ss:Name="Normal">` &&
`<Alignment ss:Vertical="Bottom"/>` &&
`<Borders/>` &&
`<Font ss:FontName="等线" x:CharSet="134" ss:Size="10" ss:Color="#000000"/>` &&
`<Interior/>` &&
`<NumberFormat/>` &&
`<Protection/>` &&
`</Style>` &&
`<Style ss:ID="s62">` &&
`<NumberFormat ss:Format="@"/>` &&
`</Style>` &&
`<Style ss:ID="s63">` &&
`<NumberFormat ss:Format="Short Date"/>` &&
`</Style>` &&
`<Style ss:ID="s100">` &&
`<Font ss:FontName="等线" x:CharSet="134" ss:Size="10" ss:Color="#DDEBF7" ss:Bold="1"/>` &&
`<Interior ss:Color="#333F4F" ss:Pattern="Solid"/>` &&
`<NumberFormat ss:Format="@"/>` &&
`</Style>` &&
`<Style ss:ID="s101">` &&
`<Font ss:FontName="等线" x:CharSet="134" ss:Size="10" ss:Color="#DDEBF7" ss:Bold="1"/>` &&
`<Interior ss:Color="#333F4F" ss:Pattern="Solid"/>` &&
`</Style>` &&
`<Style ss:ID="s102">` &&
`<Font ss:FontName="等线" x:CharSet="134" ss:Size="10" ss:Color="#DDEBF7" ss:Bold="1"/>` &&
`<Interior ss:Color="#333F4F" ss:Pattern="Solid"/>` &&
`<NumberFormat ss:Format="Short Date"/>` &&
`</Style>` &&
`</Styles>` &&
`<Worksheet ss:Name="Sheet1">` &&
* 注意 ExpandedColumnCount 参数必须 >= 内表列数, ExpandedRowCount参数必须 >= 内表行数
`<Table ss:ExpandedColumnCount="` && l_coln && `" ss:ExpandedRowCount="` && l_rown && `" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="13.8">`.
DATA row_string TYPE string.
DATA col_string TYPE string.
DATA h_labes(30) TYPE c.
DATA stype(20) TYPE c.
FIELD-SYMBOLS:<fv>.
"设置列的样式，和标题行
row_string = `<Row ss:AutoFitHeight="0" ss:Height="24">`.
LOOP AT t_header.
CLEAR h_labes.
IF t_header-colwidth IS INITIAL. "列宽
t_header-colwidth = 60.
ENDIF.
col_string = col_string && `<Column ss:AutoFitWidth="0" ss:Width="` && t_header-colwidth && `"/>`.
"列标题
IF t_header-coltext <> ''.
h_labes = t_header-coltext.
ELSEIF t_header-scrtext_s <> ''.
h_labes = t_header-scrtext_s.
ELSEIF t_header-scrtext_m <> ''.
h_labes = t_header-scrtext_m.
ELSEIF t_header-scrtext_l <> ''.
h_labes = t_header-scrtext_l.
ELSEIF t_header-reptext <> ''.
h_labes = t_header-reptext.
ENDIF.
row_string = row_string && `<Cell ss:StyleID="s100"><Data ss:Type="String">` && h_labes && `</Data></Cell>`.
ENDLOOP.
row_string = col_string && row_string && `</Row>`.
"填充单元格数据
LOOP AT itab.
row_string = row_string && `<Row>`.
LOOP AT t_header.
CASE t_header-inttype.
WHEN 'C'.
stype = 'String'.
WHEN 'D'.
stype = 'String'. "此处如果设为 DateTime,则必须要将内表中的日期转换成电脑的日期格式,否则打开excel错误
WHEN 'P'.
stype = 'Number'.
WHEN OTHERS.
stype = 'String'.
ENDCASE.
ASSIGN COMPONENT t_header-fieldname OF STRUCTURE itab TO <fv>.
IF sy-subrc <> 0.
ASSIGN '' TO <fv>.
ENDIF.
row_string = row_string && `<Cell><Data ss:Type="` && stype && `">` && <fv> && `</Data></Cell>`.
ENDLOOP.
row_string = row_string && `</Row>`.
ENDLOOP.
xls_string = xls_string && row_string &&
`</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>` &&
`<Print>` &&
`<ValidPrinterInfo/>` &&
`<PaperSizeIndex>9</PaperSizeIndex>` &&
`<HorizontalResolution>600</HorizontalResolution>` &&
`<VerticalResolution>600</VerticalResolution>` &&
`</Print>` &&
`<Selected/>` &&
`<Panes>` &&
`<Pane>` &&
`<Number>3</Number>` &&
`<ActiveRow>2</ActiveRow>` &&
`<ActiveCol>8</ActiveCol>` &&
`</Pane>` &&
`</Panes>` &&
`<ProtectObjects>False</ProtectObjects>` &&
`<ProtectScenarios>False</ProtectScenarios>` &&
`</WorksheetOptions>` &&
`</Worksheet>` &&
`</Workbook>`.
ENDFORM.
4. 拼接的字符串转内表，并下载.
DATA: lv_mod TYPE string,
xls_data TYPE string,
l_str TYPE string,
l_lines TYPE i,
l_filename TYPE string,
l_size TYPE i,
l_path TYPE string,
l_action TYPE i,
l_fullpath TYPE string,
l_encoding TYPE abap_encoding.
DATA: BEGIN OF ls_bin,
data(255) TYPE x,
END OF ls_bin,
lt_bin LIKE STANDARD TABLE OF ls_bin.
PERFORM convert_to_utf8 TABLES lt_bin USING xls_data l_size.
"保存文件对话框
CALL METHOD cl_gui_frontend_services=>file_save_dialog
EXPORTING
with_encoding = 'X'
CHANGING
filename = l_filename
path = l_path
fullpath = l_fullpath
user_action = l_action
file_encoding = l_encoding
EXCEPTIONS
OTHERS = 1.
IF sy-subrc <> 0 OR
l_action <> cl_gui_frontend_services=>action_ok.
EXIT.
ENDIF.
"下载
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = l_fullpath
filetype = 'BIN'
bin_filesize = l_size
TABLES
data_tab = lt_bin
EXCEPTIONS
OTHERS = 0.
*--------------------------------------------------------------------*
FORM convert_to_utf8 TABLES t_attachment USING i_xml type string
i_size type i.
DATA: l_xml TYPE string,
l_utf8 TYPE xstring,
l_save TYPE xfeld,
l_fullpath TYPE string,
l_filename TYPE string,
l_size TYPE i,
l_path TYPE string,
l_action TYPE i,
l_encoding TYPE abap_encoding.
DATA: BEGIN OF ls_bin,
data(255) TYPE x,
END OF ls_bin,
lt_bin LIKE STANDARD TABLE OF ls_bin.
CALL METHOD cl_co_xmltool=>convert_xml_to_utf8
EXPORTING
i_xml = i_xml
IMPORTING
e_utf8 = l_utf8.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = l_utf8
IMPORTING
output_length = i_size
TABLES
binary_tab = lt_bin.
t_attachment[] = lt_bin[].
ENDFORM.