Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
naimesh_patel
Active Contributor


 

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:



 

 

 

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



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=...!</body>

16 Comments
Labels in this area