正文

excel通过转成xml格式模板，下载成excel文件

IP云
IP云V管理员/0 阅读
11/29
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
源代码：

report ztest_down_excel.

data: begin of i_file occurs ,

        val() type c,

      end of i_file.

data begin of gt_test occurs .

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 ''.

"将内表转换为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           =

    error_no_gui         =

    not_supported_by_gui =

    others               = .

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 .

  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>

相关阅读

目录[+]