Skip to Content

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>

9 Comments
You must be Logged on to comment or reply to a post.
  • Hi,

    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

  • Hi Michael,

    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

  • Hi Michael,

    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