SAP STRANS 导出EXCEL详解编程语言

第一次用strans是2014年，第二次就是2017年。。。最近看到有人在问EXCEL双表头的问题，想着就把STRANS导出EXCEL的方法保存下来吧。方便大家参考。

1.已经记不清到底改做哪些类型的了，所以就自己做了个EXCEL模板。

这里用的是Excel 2016 (PS:其实和哪个版本关系不太大，毕竟最后都是存成XML格式的。)

2.另存EXCEL为XML。

这样就得到了模板EXCEL的XML格式文件。这是待处理的原xml

3.进入SAP，输入事务代码STRANS，新建ZLY_STRANS，并选择简单模板

<transform>这是整个strans的主体部分，root是跟节点，这个是调用strans导出时对应的节点名称。

<template>这是文件的主体部分，我们要做的就是把我们的EXCEL对应的XML文件流，嵌入到这个模板中间。然后点击代码格式化(这一步很重要)

注意：如果xml中有些特殊格式违反XML格式规则，需要单独出来。（如，换行回车 …）

这时候就可以写代码进行测试了。

测试运行

结束后到桌面打开文件，这里有个提示扩展名不匹配。因为我们用的是2016，xml是03excel。。。

选择是。看看效果：

看起来没问题。

格式问题先不管，我们来做数据替换。

我要把2，5，8，11这三个数对应位置上改成 25811，25811，25811，25811.

这时候需要去strans里改对应位置的value取值。（注意，加root值处理，需要先确定好作用域，如果只是针对这一行，可以把选值范围放在row里）

（这里直接把ROOT当作一个结构，然后用value1-4作为root的字段目录，然后把value1-4一一对应到2，5，8，11值的位置）

如上。这时候我们去修改程序，新建一个value1-4的结构，调用试试。

结果如下：

这样我们就修改了对应EXCEL中值了。

到这里就做好了一些简单的excel处理。

下面再处理一些内表数据，如模板中FIELD1,2,3,4,5,6,7…..下面对应的值，这里因为前面有个固定文本，开发，新技术，这些字段。这样就影响到我们数据的处理了，可以把文本也放到table里传进来。单是前面有固定的合并单元格，所以这里做不了。

不过没关系，这里把 ‘内部培训’ ‘日常签到’取消了，一切为了输出内表。

假设我的ROOT结构里还有一个ITB字段，这个ITB是个内表，对，这样ROOT就是个深层次结构了。

为了输出这个内表，我们需要把STRANS里那几个VALUE101-201行取消了，只留一行，作为循环结构。(为了简单，这里只把VALUE101-VALUE201两行用内表展示出来)

先去STRANS里改取值。

先删除’开发’文本所在行

修改’新技术’所在行。

row前加上循环：

row结束后取消循环

修改循环中的取值

这时候就可以去改程序，测试了。

结果如下：

附上最终STRANS和测试代码：

<?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"> 
<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> 
<Style ss:ID="Default" ss:Name="Normal"> 
<Alignment ss:Vertical="Bottom"/> 
<Borders/> 
<Font ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
<Interior/> 
<NumberFormat/> 
<Protection/> 
</Style> 
<Style ss:ID="m264764296"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="2"/> 
</Borders> 
<Font ss:Bold="1" ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
</Style> 
<Style ss:ID="m264761264"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="2"/> 
</Borders> 
<Interior ss:Color="#FFFF00" ss:Pattern="Solid"/> 
</Style> 
<Style ss:ID="m264761284"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="2"/> 
</Borders> 
</Style> 
<Style ss:ID="m264761324"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/> 
</Borders> 
</Style> 
<Style ss:ID="m264761344"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/> 
</Borders> 
</Style> 
<Style ss:ID="s16"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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> 
</Style> 
<Style ss:ID="s17"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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"/> 
</Borders> 
</Style> 
<Style ss:ID="s18"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
</Style> 
<Style ss:ID="s22"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/> 
</Borders> 
</Style> 
<Style ss:ID="s23"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> 
<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="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="2"/> 
</Borders> 
</Style> 
<Style ss:ID="s25"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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="2"/> 
</Borders> 
</Style> 
<Style ss:ID="s26"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/> 
</Borders> 
</Style> 
<Style ss:ID="s30"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/> 
</Borders> 
</Style> 
<Style ss:ID="s35"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
</Borders> 
</Style> 
<Style ss:ID="s36"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
</Borders> 
<Font ss:Bold="1" ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
</Style> 
<Style ss:ID="s37"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
</Borders> 
<Font ss:Bold="1" ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
</Style> 
<Style ss:ID="s38"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="2"/> 
</Borders> 
<Font ss:Bold="1" ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
</Style> 
<Style ss:ID="s39"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/> 
</Borders> 
<NumberFormat ss:Format="0%"/> 
</Style> 
<Style ss:ID="s44"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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"/> 
</Borders> 
<Interior ss:Color="#00B050" ss:Pattern="Solid"/> 
</Style> 
<Style ss:ID="s45"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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"/> 
</Borders> 
<Interior ss:Color="#00B0F0" ss:Pattern="Solid"/> 
</Style> 
<Style ss:ID="s46"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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"/> 
</Borders> 
<Interior ss:Color="#0070C0" ss:Pattern="Solid"/> 
</Style> 
<Style ss:ID="s47"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<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"/> 
</Borders> 
<Interior ss:Color="#FFC000" ss:Pattern="Solid"/> 
</Style> 
<Style ss:ID="s48"> 
<Alignment ss:Vertical="Bottom"/> 
<Font ss:Color="#000000" ss:FontName="仿宋" ss:Size="11" x:CharSet="134" x:Family="Modern"/> 
</Style> 
<Style ss:ID="s57"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="1"/> 
</Borders> 
<Font ss:Color="#FF0000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
</Style> 
<Style ss:ID="s63"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Font ss:Bold="1" ss:Color="#000000" ss:FontName="仿宋" ss:Size="16" x:CharSet="134" x:Family="Modern"/> 
</Style> 
<Style ss:ID="s65"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Left" ss:Weight="2"/> 
<Border ss:LineStyle="Continuous" ss:Position="Right" ss:Weight="1"/> 
<Border ss:LineStyle="Continuous" ss:Position="Top" ss:Weight="2"/> 
</Borders> 
<Font ss:Bold="1" ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
</Style> 
<Style ss:ID="s66"> 
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/> 
<Borders> 
<Border ss:LineStyle="Continuous" ss:Position="Bottom" ss:Weight="2"/> 
<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="2"/> 
</Borders> 
<Font ss:Bold="1" ss:Color="#000000" ss:FontName="等线" ss:Size="11" x:CharSet="134"/> 
</Style> 
</Styles> 
<Worksheet ss:Name="Sheet1"> 
<Table ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25" ss:ExpandedColumnCount="17" ss:ExpandedRowCount="14" x:FullColumns="1" x:FullRows="1"> 
<Column ss:Span="13" ss:StyleID="s18"/> 
<Row ss:AutoFitHeight="0"> 
<Cell ss:MergeAcross="13" ss:MergeDown="1" ss:StyleID="s63"> 
<Data ss:Type="String">2020年度工作总结</Data> 
</Cell> 
<Cell ss:StyleID="s48"/> 
<Cell ss:StyleID="s48"/> 
<Cell ss:StyleID="s48"/> 
</Row> 
<Row ss:Height="15"> 
<Cell ss:Index="15" ss:StyleID="s48"/> 
<Cell ss:StyleID="s48"/> 
<Cell ss:StyleID="s48"/> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:MergeAcross="1" ss:MergeDown="1" ss:StyleID="m264761284"/> 
<Cell ss:MergeAcross="2" ss:StyleID="s65"> 
<Data ss:Type="String">一季度</Data> 
</Cell> 
<Cell ss:MergeAcross="2" ss:StyleID="s66"> 
<Data ss:Type="String">二季度</Data> 
</Cell> 
<Cell ss:MergeAcross="2" ss:StyleID="s66"> 
<Data ss:Type="String">三季度</Data> 
</Cell> 
<Cell ss:MergeAcross="2" ss:StyleID="m264764296"> 
<Data ss:Type="String">四季度</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:Index="3" ss:StyleID="s36"> 
<Data ss:Type="String">1月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">2月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">3月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">4月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">5月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">6月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">7月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">8月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">9月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">10月</Data> 
</Cell> 
<Cell ss:StyleID="s37"> 
<Data ss:Type="String">11月</Data> 
</Cell> 
<Cell ss:StyleID="s38"> 
<Data ss:Type="String">12月</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:MergeDown="1" ss:StyleID="m264761264"> 
<Data ss:Type="String">开发任务</Data> 
</Cell> 
<Cell ss:StyleID="s23"> 
<Data ss:Type="String">新需求</Data> 
</Cell> 
<Cell ss:StyleID="s35"> 
<Data ss:Type="Number">1</Data> 
</Cell> 
<Cell ss:StyleID="s44"> 
<Data ss:Type="Number"> 
<tt:value ref="ROOT.VALUE1"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s17"> 
<Data ss:Type="Number">3</Data> 
</Cell> 
<Cell ss:StyleID="s17"> 
<Data ss:Type="Number">4</Data> 
</Cell> 
<Cell ss:StyleID="s46"> 
<Data ss:Type="Number"> 
<tt:value ref="ROOT.VALUE2"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s17"> 
<Data ss:Type="Number">6</Data> 
</Cell> 
<Cell ss:StyleID="s17"> 
<Data ss:Type="Number">7</Data> 
</Cell> 
<Cell ss:StyleID="s45"> 
<Data ss:Type="Number"> 
<tt:value ref="ROOT.VALUE3"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s17"> 
<Data ss:Type="Number">9</Data> 
</Cell> 
<Cell ss:StyleID="s17"> 
<Data ss:Type="Number">10</Data> 
</Cell> 
<Cell ss:StyleID="s47"> 
<Data ss:Type="Number"> 
<tt:value ref="ROOT.VALUE4"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s17"> 
<Data ss:Type="Number">12</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:Index="2" ss:StyleID="s25"> 
<Data ss:Type="String">运维需求</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="Number">21</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">22</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">23</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">24</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">25</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">26</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">27</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">28</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">29</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">30</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">31</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="Number">32</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:MergeDown="1" ss:StyleID="s57"> 
<Data ss:Type="String">公司指派外部项目</Data> 
</Cell> 
<Cell ss:StyleID="s26"> 
<Data ss:Type="String">海外项目</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.01</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.02</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.03</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.04</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.05</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.06</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">7.0000000000000007E-2</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.08</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.09</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.1</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.11</Data> 
</Cell> 
<Cell ss:StyleID="s39"> 
<Data ss:Type="Number">0.12</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:Index="2" ss:StyleID="s26"> 
<Data ss:Type="String">国内项目</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">A</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">B</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">C</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">D</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">E</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">F</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">G</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">H</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">I</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">J</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">K</Data> 
</Cell> 
<Cell ss:StyleID="s16"> 
<Data ss:Type="String">L</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:MergeDown="2" ss:StyleID="m264761324"> 
<Data ss:Type="String">内部培训</Data> 
</Cell> 
<Cell ss:StyleID="s26"> 
<Data ss:Type="String">业务</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD1</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD2</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD3</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD4</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD5</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD6</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD7</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD8</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD9</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD10</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD11</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">FIELD12</Data> 
</Cell> 
</Row> 
<tt:loop name="I" ref="ROOT.ITB"> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:Index="2" ss:StyleID="s26"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE01"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE02"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE03"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE04"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE05"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE06"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE07"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE08"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE09"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE10"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE11"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE12"/> 
</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String"> 
<tt:value ref="$I.VALUE13"/> 
</Data> 
</Cell> 
</Row> 
</tt:loop> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:MergeDown="2" ss:StyleID="m264761344"> 
<Data ss:Type="String">日常签到</Data> 
</Cell> 
<Cell ss:StyleID="s26"> 
<Data ss:Type="String">正常考勤</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE301</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE302</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE303</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE304</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE305</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE306</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE307</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE308</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE309</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE310</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE311</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE312</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:Index="2" ss:StyleID="s26"> 
<Data ss:Type="String">缺打卡</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE401</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE402</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE403</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE404</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE405</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE406</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE407</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE408</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE409</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE410</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE411</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE412</Data> 
</Cell> 
</Row> 
<Row ss:AutoFitHeight="0" ss:Height="20.0625"> 
<Cell ss:Index="2" ss:StyleID="s30"> 
<Data ss:Type="String">请假</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE501</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE502</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE503</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE504</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE505</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE506</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE507</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE508</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE509</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE510</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE511</Data> 
</Cell> 
<Cell ss:StyleID="s22"> 
<Data ss:Type="String">VALUE512</Data> 
</Cell> 
</Row> 
</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>300</HorizontalResolution> 
<VerticalResolution>300</VerticalResolution> 
</Print> 
<Selected/> 
<Panes> 
<Pane> 
<Number>3</Number> 
<ActiveRow>10</ActiveRow> 
<ActiveCol>16</ActiveCol> 
</Pane> 
</Panes> 
<ProtectObjects>False</ProtectObjects> 
<ProtectScenarios>False</ProtectScenarios> 
</WorksheetOptions> 
</Worksheet> 
</Workbook> 
</tt:template> 
</tt:transform>

REPORT zly_strans. 
TYPES:BEGIN OF itb, 
value01 TYPE string, 
value02 TYPE string, 
value03 TYPE string, 
value04 TYPE string, 
value05 TYPE string, 
value06 TYPE string, 
value07 TYPE string, 
value08 TYPE string, 
value09 TYPE string, 
value10 TYPE string, 
value11 TYPE string, 
value12 TYPE string, 
value13 TYPE string, 
END OF itb. 
DATA:itab TYPE TABLE OF itb. 
TYPES:BEGIN OF ty_out, 
value1 TYPE string, 
value2 TYPE string, 
value3 TYPE string, 
value4 TYPE string, 
itb    LIKE itab, 
END OF ty_out. 
DATA:gt_out TYPE TABLE OF ty_out, 
gw_out TYPE ty_out. 
DATA:g_xmlstr TYPE string, 
gt_xml   TYPE STANDARD TABLE OF string, 
gw_xml   LIKE LINE OF gt_xml. 
DATA g_filename TYPE string. 
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-001. 
PARAMETERS: p_down  LIKE rlgrap-filename DEFAULT 'D:/'. 
SELECTION-SCREEN END OF BLOCK blk1. 
INITIALIZATION. 
*&———————————————————————* 
* AT SELECTION-SCREEN 
*&———————————————————————* 
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_down. 
CALL FUNCTION 'WS_FILENAME_GET' 
EXPORTING 
def_path         = p_down 
title            = 'choose your file' 
mode             = 'S' 
IMPORTING 
filename         = p_down 
EXCEPTIONS 
inv_winsys       = 1 
no_batch         = 2 
selection_cancel = 3 
selection_error  = 4 
OTHERS           = 5. 
CHECK sy-subrc = 0 AND NOT p_down IS INITIAL. 
START-OF-SELECTION. 
DATA:v_t LIKE LINE OF itab. 
v_t-value01 = '开发'. 
v_t-value02 = '我是11'. 
v_t-value03 = '我是12'. 
v_t-value04 = '我是13'. 
v_t-value05 = '我是14'. 
v_t-value06 = '我是15'. 
v_t-value07 = '我是16'. 
v_t-value08 = '我是17'. 
v_t-value09 = '我是18'. 
v_t-value10 = '我是19'. 
v_t-value11 = '我是20'. 
v_t-value12 = '我是21'. 
v_t-value13 = '我是22'. 
APPEND v_t TO gw_out-itb. 
v_t-value01 = '新技术'. 
v_t-value02 = '我是31'. 
v_t-value03 = '我是32'. 
v_t-value04 = '我是33'. 
v_t-value05 = '我是34'. 
v_t-value06 = '我是35'. 
v_t-value07 = '我是36'. 
v_t-value08 = '我是37'. 
v_t-value09 = '我是38'. 
v_t-value10 = '我是39'. 
v_t-value11 = '我是40'. 
v_t-value12 = '我是41'. 
v_t-value13 = '我是42'. 
APPEND v_t TO gw_out-itb. 
gw_out-value1 = '25811'. 
gw_out-value2 = '25811'. 
gw_out-value3 = '25811'. 
gw_out-value4 = '25811'. 
*  gw_out-itb[] = itb[]. 
CALL TRANSFORMATION zly_strans 
SOURCE root = gw_out 
RESULT XML g_xmlstr. 
REPLACE FIRST OCCURRENCE OF 'utf-16' IN g_xmlstr WITH 'gbk'. 
APPEND g_xmlstr TO gt_xml. 
g_filename = p_down. 
CALL FUNCTION 'GUI_DOWNLOAD' 
EXPORTING 
filename = g_filename 
codepage = '8400' 
filetype = 'ASC' 
TABLES 
data_tab = gt_xml. 
IF sy-subrc EQ 0. 
MESSAGE 'success' TYPE 'S'. 
ELSE. 
MESSAGE 'download FILE fail' TYPE 'I'. 
ENDIF.

最后说下一个小问题，在strans转xml的时候其实是用的CL_BCS_CONVERT,这里在处理的时候会给xml加个不起眼的小标记：CL_ABAP_CHAR_UTILITIES=>BYTE_ORDER_MARK_LITTLE

这是各种codepage的(BOM:Byte Order Mark)

这是SAP标准加的，对应的属性在这：

这是不同格式加的。。。如果单独不想要。。。

CALL METHOD cl_abap_conv_in_ce=>create 
EXPORTING 
input = lv_request_xml_xstr (Xstring variable with XML) 
RECEIVING 
conv  = lr_conv. 
CALL METHOD lr_conv->read 
IMPORTING 
data = lv_request_xml. (String variable with XML Encoding UTF-8)

系统默认的是utf-16en。。。。=

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/18596.html

SAP STRANS 导出EXCEL详解编程语言

相关推荐

发表回复