Skip to Content

 

Preface:

 

 

Today’s blog

The specified item was not found.from Alvaro Tejada  (https://wiki.sdn.sap.com/wiki/x/nDk) has reminded me that we can also use the XML to create formatted, fancy Excel files using ABAP and XML.

 

The advantages of the XML technique over the OLE:

    • Using XML we can send the fancy generated file as an Email Attachment.
    • We can create formatted excel file using the XML technique from the Web applications generated using Web Dynpro or BSP.
    • It’s faster compared OLE technique

 

We can use the CALL TRANSFORMATION  (http://help.sap.com/saphelp_nw04s/helpdata/en/e3/7d4719ca581441b6841f1054ff1326/frameset.htm) to convert our internal table data to the formatted Excel. As release 6.10, ABAP run time environment contains the XSLT processor for executing the transformation. Alternativly, we can use CONCATENATE syntax to XML string using the XML tags and the data, but it would be as clean as CALL TRANSFORMATION.

 

 

 

Code to Generate sample Excel

 

 

*&—-


*

*& Report  ZTEST_NP_EXCEL_XML

*&

*& Download the formatted excel file using XML

*&—-


*

 

REPORT  ztest_np_excel_xml.

 

TYPES: BEGIN OF ty_mara,

matnr TYPE matnr,

maktx TYPE char30,

END   OF ty_mara.

 

DATA: itab TYPE STANDARD TABLE OF ty_mara,

la_tab LIKE LINE OF itab,

xmlstr TYPE string.

 

START-OF-SELECTION.

 

*—-


  • Test table

*—-


la_tab-matnr = ‘TEST1’.

la_tab-maktx = ‘Test description’.

APPEND la_tab TO itab.

 

la_tab-matnr = ‘TEST2’.

la_tab-maktx = ‘Test description 2’.

APPEND la_tab TO itab.

 

*—-


  • Get the XML data excel

*—-


CALL TRANSFORMATION ztest_np_xls

SOURCE table = itab

RESULT XML xmlstr.

 

*—-


  • Download the file

*—-


 

 

  • Fill the table

DATA: xml_table TYPE STANDARD TABLE OF string.

 

APPEND xmlstr TO xml_table.

 

DATA: window_title TYPE string,

fullpath TYPE string,

path TYPE string,

user_action TYPE i,

default_extension TYPE string,

default_file_name TYPE string,

file_filter TYPE  string,

filename TYPE string,

initialpath TYPE string.

 

  • File selection

MOVE ‘.XLS’ TO default_extension.

MOVE ‘XLS files (.XLS)|.XLS’ TO file_filter.

 

CALL METHOD cl_gui_frontend_services=>file_save_dialog

EXPORTING

default_extension = default_extension

default_file_name = default_file_name

file_filter       = file_filter

initial_directory = initialpath

CHANGING

filename          = filename

path              = path

fullpath          = fullpath

user_action       = user_action

EXCEPTIONS

cntl_error        = 1

error_no_gui      = 2

OTHERS            = 3.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

 

  • download file

CALL FUNCTION ‘GUI_DOWNLOAD’

EXPORTING

filename                = fullpath

filetype                = ‘ASC’

TABLES

data_tab                = xml_table

EXCEPTIONS

file_write_error        = 1

no_batch                = 2

gui_refuse_filetransfer = 3

invalid_type            = 4

OTHERS                  = 5.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

 

 

 

 

XML transformation</p><p><textarea cols=”60″ rows=”20″><?sap.transform simple?>

<?mso-application progid=”Excel.Sheet”?>

<tt:transform xmlns:tt=”http://www.sap.com/transformation-templates“>

 

<tt:root name=”table”/>

 

<tt:template>

<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>npatel</Author>

<LastAuthor>npatel</LastAuthor>

<Created>2009-01-01T22:27:09Z</Created>

<Company></Company>

<Version>11.8132</Version>

</DocumentProperties>

<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>

<WindowHeight>12660</WindowHeight>

<WindowWidth>19980</WindowWidth>

<WindowTopX>480</WindowTopX>

<WindowTopY>120</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

<Style ss:ID=”Default” ss:Name=”Normal”>

<Alignment ss:Vertical=”Bottom”></Alignment>

<Borders></Borders>

<Font></Font>

<Interior></Interior>

<NumberFormat/>

<Protection></Protection>

</Style>

<Style ss:ID=”s23″>

<Font ss:Bold=”1″ ss:Size=”26″ x:Family=”Swiss”></Font>

<Interior ss:Color=”#FFFF99″ ss:Pattern=”Solid”></Interior>

</Style>

</Styles>

 

<Worksheet ss:Name=”Sheet1″>

<Table ss:ExpandedColumnCount=”3″ ss:ExpandedRowCount=”25″ x:FullColumns=”1″ x:FullRows=”1″>

<Column ss:Width=”152.25″/>

<Column ss:Width=”180″/>

<Column ss:Width=”117″/>

<Row>

<Cell ss:StyleID=”s23″>

<Data ss:Type=”String”>Material No</Data>

</Cell>

<Cell ss:StyleID=”s23″>

<Data ss:Type=”String”>Material Desc</Data>

</Cell>

</Row>

<tt:loop ref=”.table”>

<Row>

<Cell>

<Data ss:Type=”String”>

<tt:value ref=”MATNR”/>

</Data>

</Cell>

<Cell>

<Data ss:Type=”String”>

<tt:value ref=”MAKTX”/>

</Data>

</Cell>

</Row>

</tt:loop>

</Table>

<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>

<Selected/>

<Panes>

<Pane>

<Number>3</Number>

<ActiveRow>4</ActiveRow>

<ActiveCol>1</ActiveCol>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name=”Sheet2″>

<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name=”Sheet3″>

<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>

 

</tt:template>

 

</tt:transform>

</textarea> </p><p>Output:<br />This test program will generate the formatted excel like this:

image

 

 

 

How to get the proper STYLE tag definition for particular formatting:

 

1. Create an test excel file in the MS Excel with the required formatting

2. Save the file as the XML file.

3. Open the file using the Notepad to know the Style

For example: Font-size 26, Background Color yellow will get us this Style

image

4. Change the XML transformation accordingly

Like:

!https://weblogs.sdn.sap.com/weblogs/images/47199/2_xml_style.png|height=284|alt=image|width=477|src=https://weblogs.sdn.sap.com/weblogs/images/47199/2_xml_style.png|border=1!</body>

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. aditya aghor
    Hi

    Nice blog about using XML for Excel. Just a few questions
    1. Would the generated file work in Open Office (I heard they also use XML for their spreadsheet program)

    2.I also read in a blog that we could use HTML, would’nt it be much simpler? Of course only for display, no fancy drop down boxes can be generated using the HTML method i suppose.

    3. What are the advantages of this approach over the HTML one (the HTML one seems simpler, maybe cause there are less tags there 😛 )

    Regards,
    Aditya

    (0) 
    1. Naimesh Patel Post author
      Hello Aditya,

      I don’t have much idea about using HTML to create Excel. But I will sure give a try and find out how it goes.

      I don’t have Open Office installed so, I would not be able to try it.

      Regards,
      Naimesh Patel

      (0) 
  2. Alvaro Tejada Galindo
    Naimesh:

    Really cool stuff…But can you can handle more complicated scenarios? My ABAP and Excel OLE is very simple…But my actual requirement was to have a line for Personal Information, X lines for Family information, 1 line for Business information, X lines for Communications information…All separated by Titles on Bold and with different colors…Excel OLE worked great for me…Surely, kinda slow…But can you achieve the same using XML transformations?

    Greetings,
    Blag.

    (0) 
    1. Naimesh Patel Post author
      Hello Blag,

      Pleased to hear from you.!

      In that kind of complicated scenarioes, we can create the whole XML string using the CONCATENATE syntax or so. I know, it would be kind of the lengthy and complicated but you can generated rich formatted files from Webpages or sending mail in background. OLE will not be able to handle this.

      The conclusion would be: Selection of the method would depend on the requirement. If it only to generate file in forground mode, than use of OLE is advisable. If we need to send the mail or save file from webpage than using XML would be advisable.

      Regards,
      Naimesh Patel

      (0) 
    2. MF Z
      thanks for your great blog!
      it’s displayed messy code if I use chinese char,
      I think GUI_DOWNLOAD is wrong used,could you help me to solve this problem?thanks !
      (0) 
      1. Naimesh Patel Post author
        Hello MF Z,

        Glad that you liked the idea. I haven’t tried with the chinese character with GUI_DOWNLOAD. But try to google it, you would find solution for proper GUI_DOWNLOAD for chinese character.

        Regards,
        Naimesh Patel

        (0) 
  3. Josin George
    Hello Naimaesh,

    I want to know whether we need to declare two excel meta data tabs while creating multiple sheets. I have created two meta data tabs and have passed the metadata2 table for the worksheet. I need to use the styles declared in the metadata2 for the formatting in sheet 2. Please tell me how to write the simple transformation.

    Thanks

    Josin George

    (0) 
    1. Supratik G
      Hi ,
      Thanks for your post.
      I have used manual concatenation method as my requirement was a bit complex.
      Now I just wanted to it mail as excel file .
      How can i achieve this.
      Please guide me.
      (0) 
  4. Raúl Mora

    Hi Naimesh,

    Nice blog! Can we use conditional formatting using this method? If yes, can you please provide a basic example? I need to change cell backgrounds depending on values.

    Thanks,

    Raúl Mora

    (0) 
    1. Naimesh Patel Post author

      Hello Raul,

      I’m afraid that it XML version of excel won’t store the logic to format the cell based on the conditional formatting. But you can create different style and assign it to the cell while coding. If someone changes the values in the generated Excel, it won’t change the formatting on its own.

      Regards,
      Naimesh Patel

      (0) 
      1. Raúl Mora

        Naimesh,

        How can I do that? In the example, the styles are hardcoded in the cell tag.

             <Cell ss:StyleID=”s23″>

        Thanks,

        Raúl

        (0) 
  5. Manohar B

    Hi Naimesh ,

    This blog is very useful .

    My Requirement is to add color to the specific CELL ,Could you please suggest how that can be done ?

    I have used this XML logic to create an excel and sending as an email attachment with multiple tabs and colored headers but not able to find a logic to color specific cell (For example : row has 3  columns Text 1 text 2 and text3 and i wanted to color text3 with Yellow ,red and green based on conditions ?

    Regards,

    Manohar.

    (0) 

Leave a Reply