09-24-2013 8:39 PM
Hi Gurus,
I have a requirement of creating an excel sheet which has company LOGO at the header, Rows headings with Colors, Data to be displayed and another LOGO at the footer.
Since color schema is not possible in SMARTFORM, we have decided to check the feasibility using STRANS - Call Transformation. One doubt about this call transformation is that can we include LOGOs in XML source code? If yes, then how can we mention source location of the LOGO.
I have searched SDN and Google but couldnt find any relevant information.
I request you to please provide your suggestions and inputs.
09-25-2013 8:15 AM
My suggestion is to have a look at abap2xlsx project here in scn.
09-25-2013 8:27 AM
Hi Anand,
Use the following code. It might help you.
ABAP Program Code:
*&---------------------------------------------------------------------*
*& 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.
Code for XML Transformation ( Double click on the name ztest_np_xls after statement Call Transformation) :
<?sap.transform simple?>
<?mso-application progid=”Excel.Sheet”?>
<tt:transform xmlns:tt=”http://www.sap.com/transformation-templates”>
This test program will generate the formatted excel like this:
<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>
The output of the program ( including XML Transormation) is shown below.
Note: It works fine with MS Excel. For others package like Open Office, it wont work.
Hope It Helps.
Thanks,
Arun.
12-11-2013 12:39 AM
Hi Anand,
If you want to use STRANS, you may check below links to get how to assign media type to binary to XML file, I think you should convert your logo into binary data before including to XML source code:
But I think color schema is supported in SMARTFORM, you may see below 2 pictures:
P.S. I'll show you a picture and below is it's binary data:
Content-Type: application/octet-stream; name="email.jpg"
Content-Transfer-Encoding: base64
Content-ID: <EE389C42-C8F4-4325-A1F8-068CE672628C>
/9j/4AAQSkZJRgABAgAAZABkAAD/7AARRHVja3kAAQAEAAAAZAAA/+4ADkFkb2JlAGTAAAAAAf/b
AIQAAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQICAgICAgICAgIC
AwMDAwMDAwMDAwEBAQEBAQECAQECAgIBAgIDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMD
AwMDAwMDAwMDAwMDAwMDAwMD/8AAEQgADwAPAwERAAIRAQMRAf/EAFcAAQEBAAAAAAAAAAAAAAAA
AAgJCgEBAAAAAAAAAAAAAAAAAAAAABAAAgIDAQADAQAAAAAAAAAAAwYEBQECBwgAEQkSEQEAAAAA
AAAAAAAAAAAAAAAA/9oADAMBAAIRAxEAPwDRr5u7V6K5p+n3oTh/rLrxALfWKkjB51V7OAYCI2Qo
VyMShjmM2RY7QlOyhqIZUWxrB6lJa2Yi7GKSUAZJAXD+AB/fPGfLHd0RYQfQPUUfjT1KtjTODdFu
HNYTXtdeIxq3GJCLve29PLYRkmEhaT60BM4kY2BnGwZOsWQIC1564J+rlQx+iFHuPpVctUe141do
PI3wekKytN3uUsTaND6KvgqolOwqViqydtJl5vYamNZS84/jErf6miD/2Q==
Hope it helps,
Cee.