Download MS Excel with formatting options using XS...

Former Member · ‎09-11-2013

OBJECTIVE:

Download the contents of the internal table in an excel sheet with formatting options. OLE technique can be used for this requirement. But download using OLE technique is very slow for enormous amount of data. Our objective is to find a solution which provides better means of downloading the data.

NEW APPROACH:

In this approach, we are using XSL Transformation technique to download data. This way, we are able to download the data faster than OLE approach.

Step by step details of this approach is explained below:

Step 1:

Open an Excel sheet and do the necessary formatting. Fill in some sample data (just one record will do) to know how our output is going to be. Refer the screenshot below for a sample excel sheet with necessary formatting.

In this Excel sheet, the following formatting has been done.

The header row is highlighted.
The header text is made bold.
Borders are given for the desired cells.
Conditional formatting is given for the cell G2. The condition is such that if the value in the cell is greater than 100, it is made Red Bold.
Formula is given for the column H. Remaining capacity = Max. Capacity minus Occupied.

Likewise do all the necessary formatting.

Step 2:

After the formatting is done, SAVE the file. While saving, save it as XML spreadsheet. Refer the below screenshot. The extension will be “.xml”.

Step 3:

Right click on the saved file and open the same in Notepad. We will be able to see the XML code which gets generated. The styles and properties of the cells will be represented in this generated XML code. Copy this code since we will be using this in the XSL transformation which is explained in the next step.

Refer the below screenshot to see how the generated code looks like.

Step 4:

Go to Transaction code XSLT_TOOL. Enter a Transformation name and click on create.

Select the tab ‘SourceCode’. In this tab we will be able to see some empty tags. Paste the code which is copied from the Notepad in the place where the arrow mark points (refer below screenshot). That is under the tag <tt:template>.

Step 5:

At this level, on executing this transformation, we will be able to download the excel sheet which we have prepared earlier with the sample data. To bring our own data in to the excel sheet we need to make some modifications to this generated XML code.

Please note the previous screen shot. By default, the code will show <tt:root name = “ROOT”>. This is kind of import parameter for the XSL Transformation. I can create my own import parameters for an XSLT. I would need to pass an internal table and a variable from my program to this Transformation and then show these details in my Excel sheet. So I declare two import parameters (one for the table and other for the variable) as shown below:

Now we will start with the modifications that I have to do to my generated XML code.

Keep scrolling down till we see the code as shown in the below screenshot. These are the codes generated for the sample row we inserted in our excel sheet. Now our aim is to fill the cells with our data in the internal table. Hence we have to modify the code slightly.

Please be noted that we are going to pass our internal table data into this XSLT and inside the XSLT, we will be holding the records of the internal table in the parameter “table”. Similarly we are going to pass a variable into this XSLT and inside the XSLT, we will be holding the value of that variable in the parameter “var”.
First thing that I am going to do is, put the highlighted row in the previous screenshot inside a LOOP. Means that, I am going to loop “table”. The syntax is as shown in the screenshot. Also refer help.sap.com for more syntax.

Inside the loop, the cell values have hard coded values (like AA, 64 etc.) which we provided while creating the sample Excel file. These values need to be dynamically replaced with values in internal table. Hence I make the below modification:

Note:

Here CARRID refers to the name of the field in the internal table. I have declared the data type as String here. String is the safest option to keep for data type error scenarios.

Likewise make the modifications for all the cells. Then the code will look like below:

Also for populating the variable I do the below change:

Step 6:

We are done with the modifications in the XSLT. Save the XSLT and activate.

The XSLT code is attached here:

<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">

<tt:root name="table"/>
<tt:root name="var"/>

<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">
<Author>Manikandan Jeyaram Nirmala Devi</Author>
<LastAuthor>Manikandan Jeyaram Nirmala Devi</LastAuthor>
<Created>2013-09-10T10:01:00Z</Created>
<LastSaved>2013-09-10T10:57:13Z</LastSaved>
<Version>14.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>7680</WindowHeight>
<WindowWidth>20115</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>165</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
</Style>
<Style ss:ID="s67">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
    ss:Bold="1"/>
   <Interior ss:Color="#C4BD97" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s68">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
</Style>
<Style ss:ID="s72">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Interior ss:Color="#C5D9F1" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s73">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
</Style>
<Style ss:ID="s74">
   <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
</Style>
<Style ss:ID="s75">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <NumberFormat ss:Format="Short Date"/>
</Style>
<Style ss:ID="s76">
   <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="100" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:AutoFitWidth="0" ss:Width="54.75"/>
   <Column ss:Width="72.75"/>
   <Column ss:Width="45.75"/>
   <Column ss:Width="37.5"/>
   <Column ss:Width="56.25"/>
   <Column ss:Width="67.5"/>
   <Column ss:Width="48.75"/>
   <Column ss:Width="97.5"/>
   <Row>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Airline</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Flight Number</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Date</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Airfare</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Plane Type</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Max. capacity</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Occupied</Data></Cell>
    <Cell ss:StyleID="s67"><Data ss:Type="String">Remaining Capacity</Data></Cell>
   </Row>

   <tt:loop ref=".table">
   <Row>
    <Cell ss:StyleID="s73">
     <Data ss:Type="String">
      <tt:value ref="CARRID"/>
     </Data>
    </Cell>

</Data>
</Cell>

</Row>
</tt:loop>

<Row ss:AutoFitHeight="0">
<Cell ss:Index="2" ss:StyleID="s72"><Data ss:Type="String">Variable</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/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
     <ActiveCol>7</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<ConditionalFormatting xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R2C7:R100C7</Range>
   <Condition>
    <Qualifier>Greater</Qualifier>
    <Value1>100</Value1>
    <Format Style='color:red;font-weight:700'/>
   </Condition>
</ConditionalFormatting>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
</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>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
</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>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

</tt:template>

</tt:transform>

This is the end of part 1. In the next part we will see how to use this XSL Transformation in program to download the Excel.

Part 2: http://scn.sap.com/people/manikandan.jeyaram/blog/2013/09/12/download-ms-excel-with-formatting-optio...

Thanks.

Download MS Excel with formatting options using XSLT - Part 1

Are you there, SAP? It's me, Jelena

Integration Point of MM-FI-SD in SAP ERP

SAP Project System - A ready Reference ( Part 1 )