excel通过转成xml格式模板,下载成excel文件
源代码: report ztest_down_excel. data: begin of i_file occurs 0, val(255) type c, end of i_file. data begin of gt_test occurs 0. data matnr like mara-matnr. data maktx like makt-maktx. data end of gt_test. data v_filestr type string. data v_filename type string. data v_path type string. data v_fn type string. data v_len type i. select mara~matnr makt~maktx into corresponding fields of table gt_test from mara inner join makt on mara~matnr eq makt~matnr where makt~spras eq '1'. "将内表转换为xml call transformation ztest_down_excel source table = gt_test[] * head = v_head "由于Transformation中不能出现中文,表头中文以结构变量传入 result xml v_filestr. "获取保存路径及保存文件名 call method cl_gui_frontend_services=>file_save_dialog exporting window_title = 'Download Files' "窗口标题 default_extension = 'xls' default_file_name = 'Download Test' "初始化文件名 file_filter = '(电子表格EXCEL)' changing filename = v_filename "文件名 path = v_path "路径(除了文件名) fullpath = v_fn "完整路径(包含文件名) exceptions cntl_error = 1 error_no_gui = 2 not_supported_by_gui = 3 others = 4. clear i_file[]. call function 'SCMS_STRING_TO_FTEXT' exporting text = v_filestr importing length = v_len tables ftext_tab = i_file. call function 'GUI_DOWNLOAD' exporting filename = v_fn filetype = 'BIN' tables data_tab = i_file[]. if sy-subrc eq 0. write 'Download successful!'. else. write 'Download failed!'. endif.
excel文件变成xml:
Exce转换XMl注意: 1、单元格格式类型全部改为:string。 2、不能出现中文,全部改为对应的英文, 3、Worksheet 下table行列条件删除。 <…ss:ExpandeRowCount="30"…> 4、XML文件转换loop循环后: <Row ss:Index="6"> 这里出现在循环后面不能限定index值,应删除 5、自动换行参数<Row ss:AutoFitHeight="1"……> 6、loop循环属性一样的空行直接删除 XML导出EXCEL纸张方向控制: <PageSetup> <Layout x:Orientation="Landscape"/> ''横向 <Header x:Margin="0.31458333333333333"/> <Footer x:Margin="0.31458333333333333"/> <PageMargins x:Bottom="0.63888888888888884" x:Left="0.52986111111111112" x:Right="0.5" x:Top="0.37986111111111109"/> </PageSetup> XML导出EXCEL打印缩放比例控制: <Print> <ValidPrinterInfo/> <PaperSizeIndex>9</PaperSizeIndex> <Scale>77</Scale> <HorizontalResolution>300</HorizontalResolution> <VerticalResolution>300</VerticalResolution> </Print> XML导出 EXCEL单元格文本自动换行: <Style ss:ID="m79594592"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"/> </Borders> <Font ss:FontName="Tahoma" x:CharSet="134" ss:Size="10.5" ss:Color="#000000"/> </Style> Transformation源码: <?sap.transform simple?> <tt:transform xmlns:tt="http://www.sap.com/transformation-templates"> <tt:root name="TABLE"/> <tt:root name="HEAD"/> <tt:template> <?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>2006-09-16T00:00:00Z</Created> <LastSaved>2014-12-25T10:26:33Z</LastSaved> <Version>14.00</Version> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <AllowPNG/> <RemovePersonalInformation/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>7980</WindowHeight> <WindowWidth>14805</WindowWidth> <WindowTopX>240</WindowTopX> <WindowTopY>135</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="Tahoma" x:CharSet="134" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s17"> <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/> <Font ss:FontName="Tahoma" x:CharSet="134" x:Family="Swiss" ss:Color="#000000"/> </Style> <Style ss:ID="s18"> <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/> <Font ss:FontName="Tahoma" x:CharSet="134" x:Family="Swiss" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s19"> <Alignment ss:Vertical="Center"/> </Style> </Styles> <Worksheet ss:Name="SHEET1"> <Table x:FullColumns="1" x:FullRows="1" ss:StyleID="s17" ss:DefaultColumnWidth="76.5" ss:DefaultRowHeight="16.3125"> <!-- ---删除限制行/列数量语句--> <Column ss:StyleID="s18" ss:AutoFitWidth="0"/> <Column ss:Index="6" ss:StyleID="s18" ss:AutoFitWidth="0" ss:Span="2"/> <Column ss:Index="10" ss:StyleID="s18" ss:AutoFitWidth="0" ss:Span="7"/> <Column ss:Index="20" ss:StyleID="s18" ss:AutoFitWidth="0" ss:Span="3"/> <Column ss:Index="24" ss:StyleID="s18" ss:AutoFitWidth="0" ss:Width="114.75"/> <Column ss:StyleID="s18" ss:AutoFitWidth="0" ss:Span="3"/> <Row ss:Height="13.5" ss:StyleID="s19"> <Cell><Data ss:Type="String"><tt:value ref="head.h001"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h002"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h003"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h004"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h005"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h006"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h007"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h008"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h009"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h010"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h011"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h012"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h013"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h014"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h015"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h016"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h017"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h018"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h019"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h020"/></Data></Cell> <!-- <Cell><Data ss:Type="String"><tt:value ref="head.h021"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h022"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h023"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="head.h024"/></Data></Cell>--> </Row> <tt:loop ref=".table"> <Row ss:Height="13.5" ss:StyleID="s19"> <Cell><Data ss:Type="String"><tt:value ref="ZART"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="PASTRTERM"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="ZWEEK"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="PRUEFLOS"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="SELMATNR"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="MAKTX"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="NORMT"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="ZSCQY"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="LOSMENGE"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="MENGENEINH"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="KOSCH"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="ZPZWH"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="RAUBE"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="CHARG"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="ZMM_LICHA"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="ZMM_HSDAT"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="ZMM_VFDAT"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="LAGORTCHRG"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="LGOBE"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="LGPBE"/></Data></Cell> <!-- <Cell><Data ss:Type="String"><tt:value ref="ZYHFF"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="ZZLZK"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="UNAME"/></Data></Cell> <Cell><Data ss:Type="String"><tt:value ref="UDATE"/></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> <Unsynced/> <Print> <ValidPrinterInfo/> <PaperSizeIndex>9</PaperSizeIndex> <HorizontalResolution>600</HorizontalResolution> <VerticalResolution>600</VerticalResolution> </Print> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveRow>6</ActiveRow> <ActiveCol>1</ActiveCol> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook> </tt:template> </tt:transform>