Another way to create Excel output
<body><p>The following BSP program will create a list from the famous flights data for LH flights. It uses BSP as the generation. Just create a page with flow logic called something like start.xml and enter the code. After activation you can then test it. It doesn’t prompt for a filename to save the XML, I just use “File -> Save as” from my browser.</p><p> </p><textarea cols=”60″ rows=”30″><%@page language=”abap”%>
<?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“>
<Styles>
<Style ss:ID=”s21″>
<Interior ss:Color=”#FFFF00″ ss:Pattern=”Solid”></Interior>
</Style>
<Style ss:ID=”s23″>
<NumberFormat ss:Format=”0″/>
</Style> </Styles>
<Worksheet ss:Name=”Flights”>
<Table>
<Row>
<Cell ss:StyleID=”s21″> <Data ss:Type=”String”>Carrier ID</Data> </Cell>
<Cell ss:StyleID=”s21″> <Data ss:Type=”String”>Connection ID</Data> </Cell>
<Cell ss:StyleID=”s21″> <Data ss:Type=”String”>Country From</Data> </Cell>
<Cell ss:StyleID=”s21″> <Data ss:Type=”String”>Airport From</Data> </Cell>
<Cell ss:StyleID=”s21″> <Data ss:Type=”String”>Country To</Data> </Cell>
<Cell ss:StyleID=”s21″> <Data ss:Type=”String”>Airport To</Data> </Cell>
</Row>
<%
DATA: t_spfli TYPE STANDARD TABLE OF spfli,
l_spfli like line of t_spfli.
SELECT carrid connid countryfr cityfrom
airpfrom countryto cityto airpto
INTO corresponding fields of TABLE t_spfli
FROM spfli
WHERE carrid EQ ‘LH’.
loop at t_spfli into l_spfli.%>
<%= ‘<Row>’.%>
<%= ‘<Cell> <Data ss:Type=”String”>’%><%= l_spfli-carrid %><%= ‘</Data> </Cell> ‘.%>
<%= ‘<Cell ss:StyleID=”s23″> <Data ss:Type=”Number”>’%><%= l_spfli-connid %><%= ‘</Data> </Cell> ‘.%>
<%= ‘<Cell> <Data ss:Type=”String”>’%><%= l_spfli-countryfr %><%= ‘</Data> </Cell> ‘.%>
<%= ‘<Cell> <Data ss:Type=”String”>’%><%= l_spfli-airpfrom %><%= ‘</Data> </Cell> ‘.%>
<%= ‘<Cell> <Data ss:Type=”String”>’%><%= l_spfli-countryto %><%= ‘</Data> </Cell> ‘.%>
<%= ‘<Cell> <Data ss:Type=”String”>’%><%= l_spfli-airpto %><%= ‘</Data> </Cell> ‘.%>
<%= ‘</Row>’.%>
<%
endloop.
%>
</Table>
<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</textarea> <p>The output might look like this: </p><p><img /></body>
I just want to add that it is possible to use same trick without using BSP. Developer just needs to generate XML file with XML office structure in ABAP.
Cheers
Yes, it was just a bit easier to show in BSP without having to do a lot of move statements to set up the structures...
Cheers
I guess it would be very possible to output Open XML format (the format of Office 2007 documents) from an ABAP internal table using XML capabilities (CALL TRANSFORMATION, Simple Transformation).
Don't you think ?
That would allow easy conversions from Web Dynpro ABAP, BSP or classic ABAP (I even think of RFC-enabling this transformation for use within Visual Composer, Portal-based applications...)
Best regards,
Guillaume
I think I got my answer in the meanwhile !
ABAP and Excel - Create Formatted Excel using XML
Best regards,
Guillaume
I like the lateral thinking, but didn't seem to work on my system with Office 2003. Maybe I just needed a plug-in to get it to work but will be good when everyone support Office.
Much quicker than anything OLE based!
Regards,
Matt
... with this content:
I think we're touching a common problem here. Business Users want rich Excel Sheets which are nicely formated. The default for ABAP Reports is the CSV Download or the clipboard. Blag and you have shown manual ways to create Excel files. What I'm missing is something like PHPExcel which provides a great API to produce Excel for different Office versions. BW seems to support a generic way of creating Excel Files. The Excel Files I can download from a web based query look nicely formated. Is anyone familiar with that technique?
Best regards
Gregor
I posted a similar blog "Downloading data into Excel with Format Options (from SAP Web Applications)" Downloading data into Excel with Format Options (from SAP Web Applications)
where I taken advantage of HTML compatibility with the spreesheet application.
It would be very useful to have direct and powerful classes to generated true Excel files even to generate them via background processes.
Sergio
Very nice...Never though about that option using BSP, look nicer and faster -;)
Greetings,
Blag.