Introduction
Downloading internal table as excel file is very easy in SAP, But The real struggle starts when you got a requirement like download an excel with multiple sheets or with some cell’s text in different color /Font / Bold /Italic .
This can be done by OLE but i got it very confusing.
So, Here I got a solution in which you can do all these formatting easily .
So let’s Start
first you need to create a Transformation from Tcode:- STRANS
Create New Transformation with name :- ZEXCEL_XML_TRANS (Or Name of your Choice)
Next you will get below Screen, put description of your choice and select Type as Simple Transformation
Now Put below Code in SourceCode Tab.
Code to be Written Here:-
<!--***************************************************************************
**************** Transformation ZEXCEL_XML_TRANS
***************************************************************************
* Description : Make Xml For Excel file
* Module : HCM
* Trans. code :
* Author : Navneet Anand
*Date & Time : 29.07.2019 17:55:35
***************************************************************************
* -->
<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">
<tt:root name="excelMeta"/>
<tt:root name="sheets"/>
<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" tt:extensible="deep-static">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>
<tt:value ref="excelMeta.DocumentProperties.Author"/>
</Author>
</DocumentProperties>
<tt:serialize>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
</Style>
<tt:loop name="Styles" ref=".excelMeta.Styles">
<Style>
<tt:attribute name="ss:ID" value-ref="$Styles.ID"/>
<Alignment>
<tt:cond check="not-initial($Styles.Alignment.Horizontal)">
<tt:attribute name="ss:Horizontal" value-ref="$Styles.Alignment.Horizontal"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Alignment.Vertical)">
<tt:attribute name="ss:Vertical" value-ref="$Styles.Alignment.Vertical"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Alignment.WrapText)">
<tt:attribute name="ss:WrapText" value-ref="$Styles.Alignment.WrapText"/>
</tt:cond>
</Alignment>
<Borders>
<Border ss:Position="Bottom" >
<tt:cond check="not-initial($Styles.border.weight)">
<tt:attribute name="ss:Weight" value-ref="$Styles.border.weight"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.linestyle.Bottom)">
<tt:attribute name="ss:LineStyle" value-ref="$Styles.border.linestyle.Bottom"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.Color.Bottom)">
<tt:attribute name="ss:Color" value-ref="$Styles.border.Color.Bottom"/>
</tt:cond>
</Border>
<Border ss:Position="Top" >
<tt:cond check="not-initial($Styles.border.weight)">
<tt:attribute name="ss:Weight" value-ref="$Styles.border.weight"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.linestyle.Top)">
<tt:attribute name="ss:LineStyle" value-ref="$Styles.border.linestyle.Top"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.Color.Top)">
<tt:attribute name="ss:Color" value-ref="$Styles.border.Color.Top"/>
</tt:cond>
</Border>
<Border ss:Position="Left" >
<tt:cond check="not-initial($Styles.border.weight)">
<tt:attribute name="ss:Weight" value-ref="$Styles.border.weight"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.linestyle.Left)">
<tt:attribute name="ss:LineStyle" value-ref="$Styles.border.linestyle.Left"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.Color.left)">
<tt:attribute name="ss:Color" value-ref="$Styles.border.Color.left"/>
</tt:cond>
</Border>
<Border ss:Position="Right" >
<tt:cond check="not-initial($Styles.border.weight)">
<tt:attribute name="ss:Weight" value-ref="$Styles.border.weight"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.linestyle.Right)">
<tt:attribute name="ss:LineStyle" value-ref="$Styles.border.linestyle.Right"/>
</tt:cond>
<tt:cond check="not-initial($Styles.border.Color.Right)">
<tt:attribute name="ss:Color" value-ref="$Styles.border.Color.Right"/>
</tt:cond>
</Border>
</Borders>
<Font>
<tt:cond check="not-initial($Styles.Font.fontname)">
<tt:attribute name="ss:FontName" value-ref="$Styles.Font.fontname"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Font.Family)">
<tt:attribute name="x:Family" value-ref="$Styles.Font.Family"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Font.Color)">
<tt:attribute name="ss:Color" value-ref="$Styles.Font.Color"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Font.Bold)">
<tt:attribute name="ss:Bold" value-ref="$Styles.Font.Bold"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Font.Italic)">
<tt:attribute name="ss:Italic" value-ref="$Styles.Font.Italic"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Font.Underline)">
<tt:attribute name="ss:Underline" value-ref="$Styles.Font.Underline"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Font.Font_Size)">
<tt:attribute name="ss:Size" value-ref="$Styles.Font.Font_Size"/>
</tt:cond>
</Font>
<Interior>
<tt:cond check="not-initial($Styles.Interior.Colorindex)">
<tt:attribute name="ss:Color" value-ref="$Styles.Interior.Colorindex"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Interior.Pattern)">
<tt:attribute name="ss:Pattern" value-ref="$Styles.Interior.Pattern"/>
</tt:cond>
<tt:cond check="not-initial($Styles.Interior.patterncolorindex)">
<tt:attribute name="ss:PatternColor" value-ref="$Styles.Interior.patterncolorindex"/>
</tt:cond>
</Interior>
</Style>
</tt:loop>
</Styles>
</tt:serialize>
<tt:loop name="sheet" ref=".sheets">
<Worksheet>
<tt:attribute name="ss:Name" value-ref="$sheet.Sheetname"/>
<Table>
<tt:cond check="not-initial($sheet.ColWidth)">
<tt:loop name="ColWidth" ref="$sheet.ColWidth">
<Column>
<tt:attribute name="ss:Index" value-ref="$ColWidth.Col_index"/>
<tt:attribute name="ss:Width" value-ref="$ColWidth.Col_width"/>
</Column>
</tt:loop>
</tt:cond>
<tt:loop name="line" ref="$sheet.sheetx">
<Row>
<tt:cond check="not-initial($line.RowHight)">
<tt:attribute name="ss:Height" value-ref="$line.RowHight"/>
</tt:cond>
<tt:loop name="Cell" ref="$line.Cells">
<Cell>
<tt:cond>
<tt:attribute name="ss:StyleID" value-ref="$Cell.StyleID"/>
</tt:cond>
<tt:cond>
<Data>
<tt:attribute name="ss:Type" value-ref="$Cell.Type"/>
<tt:value ref="$Cell.cell_content"/>
</Data>
</tt:cond>
</Cell>
</tt:loop>
</Row>
</tt:loop>
</Table>
</Worksheet>
</tt:loop>
</Workbook>
</tt:template>
</tt:transform>Now Save and Activate this transformation. First activity ends here
Now you need to Write some code in your report/function module/Class to generate the excel file .
In top Or where ever you have defined your types and data .
define these below types and internal tables:-
DATA binary_content TYPE solix_tab.
DATA :BEGIN OF ls_xls_cell,
styleid TYPE string,
type TYPE string,
cell_content TYPE string,
END OF ls_xls_cell,
li_xls_cells LIKE TABLE OF ls_xls_cell,
BEGIN OF ls_column_width,
col_index TYPE i ,
col_width TYPE i ,
end of ls_column_width ,
li_column_width like TABLE OF ls_column_width ,
BEGIN OF ls_xls_row,
rownr TYPE i,
RowHight TYPE i ,
cells LIKE li_xls_cells,
END OF ls_xls_row ,
li_xls_row LIKE TABLE OF ls_xls_row.
DATA : BEGIN OF sheets ,
sheetname TYPE string,
sheetx LIKE li_xls_row,
ColWidth like li_column_width,
END OF sheets ,
it_sheets LIKE TABLE OF sheets.
DATA : lv_xml_string TYPE xstring,
BEGIN OF ls_documentproperties,
author TYPE string,
END OF ls_documentproperties,
BEGIN OF ls_font,
family TYPE string VALUE 'Swiss' ,
FontName TYPE string VALUE 'Arial' ,
Font_Size TYPE i VALUE 10 ,
color TYPE String ,
bold TYPE char1,
italic TYPE char1 ,
Underline TYPE string ,
END OF ls_font,
BEGIN OF ls_alignment ,
Horizontal TYPE char10 , "VALUE 'Left',
vertical TYPE char10 , "VALUE 'Bottom',
WrapText TYPE char1 ,
END OF ls_alignment ,
BEGIN OF ls_border_face,
top TYPE string ,
bottom TYPE string ,
left TYPE string ,
right TYPE string ,
END OF ls_border_face,
BEGIN OF ls_border,
linestyle like ls_border_face,
weight TYPE char1,
Color like ls_border_face,
END OF ls_border,
BEGIN OF ls_interior,
colorindex TYPE string,
pattern TYPE string,
patterncolorindex TYPE string,
END OF ls_interior,
BEGIN OF ls_style,
id TYPE string,
font LIKE ls_font,
Alignment like ls_alignment ,
border LIKE ls_border,
interior LIKE ls_interior,
END OF ls_style,
BEGIN OF ls_excelmeta,
documentproperties LIKE ls_documentproperties,
styles LIKE TABLE OF ls_style,
END OF ls_excelmeta .",Config part is done.
Now generate the excel file with data :-
1. create Metadata like author :-
ls_excelmeta-documentproperties-author = sy-uname.2. Create Style for the sheet :-
***********includes all Excel style formatting
CLEAR: ls_style.
ls_style-id = 's21'. " Style 1 for includes all formatting
ls_style-font-family = 'Script' ."'Swiss'.
ls_style-font-fontname = 'Ink Free' .
ls_style-font-bold = '1'.
ls_style-font-italic = '0'.
ls_style-font-underline = 'Single' . " 'Double' .
ls_style-font-color = '#92D050' .
ls_style-alignment-horizontal = 'Center' .
ls_style-alignment-vertical = 'Center' .
ls_style-alignment-wraptext = '1' .
ls_style-border-linestyle-bottom =
ls_style-border-linestyle-top =
ls_style-border-linestyle-left =
ls_style-border-linestyle-right = 'SlantDashDot'. "'Continuous' . "'SlantDashDot' "'DashDotDot "'Double' "'DashDot' ..
ls_style-border-weight = '1'.
ls_style-border-color-top =
ls_style-border-color-bottom =
ls_style-border-color-left =
ls_style-border-color-right = '#FF0000' .
ls_style-interior-colorindex = '#FFFFF1'.
ls_style-interior-pattern = 'Gray0625'. "'HorzStripe'.
ls_style-interior-patterncolorindex = '#FF0000'.
APPEND ls_style TO ls_excelmeta-styles.Now apply this style to Excel Cells :-
create data for A1 cell by below Cell Code :-
ls_xls_cell-type = 'String'.
ls_xls_cell-styleid = 's21'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = 'S21-includes all formatting' .
APPEND ls_xls_cell TO ls_xls_row-cells.Repeat the code for next cell data like for A2, A3,A4…….
Done with First row Data
write below code to change Row :-
ls_xls_row-rowhight = 100 . " Row 1 Hight will be 100
APPEND ls_xls_row TO li_xls_row.
CLEAR ls_xls_cell-cell_content.
CLEAR ls_xls_row.now repeat cell code to create data for B1, B2 , B3 ……
if you want to fix column width then write below code
ls_column_width-col_index = 2 .
ls_column_width-col_width = 200 .
append ls_column_width to li_column_width .done with all the data then save all these data to one sheet .
sheets-colwidth = li_column_width .
sheets-sheetx = li_xls_row .
sheets-sheetname = 'Sheet 1' .
translate sheets-sheetname TO UPPER CASE .
APPEND sheets TO it_sheets.
CLEAR sheets.Now Call Our Transformation to generate Excel and download
CALL TRANSFORMATION ZGEN9_139391
SOURCE excelmeta = ls_excelmeta
sheets = it_sheets
RESULT XML lv_xml_string.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = lv_xml_string
TABLES
binary_tab = binary_content.
DATA : filename TYPE string .
CONCATENATE 'C:\TestExcel' '\Excel_sheet_' sy-datum '_' sy-uzeit '.XLS' INTO filename .
CONDENSE filename .
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = filename
filetype = 'BIN'
TABLES
data_tab = binary_content .Now Open the excel sheet downloaded in C -> TestExcel folder .
Now come to The formatting part One by One :-
Author :-
If you wish to add author of the sheet then put your value in below field
ls_excelmeta-documentproperties-author = sy-uname.Style id is a required field if you are creating any style then you must enter style id with properties.
if you wish to download the excel with plain texts then write the the cell code with styleId as ‘Default’
Font :-
To set Font for the text in the cell write below code with the corresponding Font Name and Font Family Name .
ls_style-font-family = 'Script' ."'Swiss'.
ls_style-font-fontname = 'Ink Free' .Bold:-
To Write Font in Bold.
ls_style-font-bold = '1'."BoldItalic:-
To Write Font in Italic .
ls_style-font-italic = '1'.Underline:-
Put Underline for text .
ls_style-font-underline = 'Single' . " 'Double' .Font Color :-
Set font color .(Write Color in Hex code)
ls_style-font-color = '#92D050' .Now come to Text Alignment inside the cell .
Vertical alignment :-
ls_style-alignment-vertical = 'Center' ."'Bottom' . "'Top'. "'Justify'."'Distributed'Horizontal Alignment :-
ls_style-alignment-horizontal = 'Center' ."'Left'."'Right'."'Fill'."'Justify' ....Wrap Text :-
Wrap Text inside the cell .
ls_style-alignment-wraptext = '1' .Border Definition :-
Every Cell Has 4 borders so can have different design and color on Sides .
color:-
ls_style-border-color-top =
ls_style-border-color-bottom =
ls_style-border-color-left =
ls_style-border-color-right = '#FF0000' .LineStyle:-
ls_style-border-linestyle-bottom =
ls_style-border-linestyle-top =
ls_style-border-linestyle-left =
ls_style-border-linestyle-right = 'SlantDashDot'. "'Continuous' . "'SlantDashDot' "'DashDotDot "'Double' "'DashDot' ..Thickness:-
Border Thickness
ls_style-border-weight = '1'.Cell Fillings :-
Cell Color:-
ls_style-interior-colorindex = '#FFFFF1'.Cell Fill Pattern :-
ls_style-interior-pattern = 'Gray0625'. "'HorzStripe'.Cell Fill Pattern Color :-
ls_style-interior-patterncolorindex = '#FF0000'.Fix Row Width :-
Before appending Row data add this field value other wise it will automatically take default vale
ls_xls_row-rowhight = 100 .Cell Data Type :-
ls_xls_cell-type = 'String'."'Number' .Fix Column Width :-
Before Appending Sheet Data just add column width as below by mentioning column index and size .
ls_column_width-col_index = 3 .
ls_column_width-col_width = 400 .
append ls_column_width to li_column_width .
sheets-colwidth = li_column_width .To Add multiple sheets in the excel Write below code :-
sheets-sheetx = li_xls_row1 . "Sheet 1 Data
sheets-sheetname = 'Sheet 1' .
translate sheets-sheetname TO UPPER CASE .
APPEND sheets TO it_sheets.
sheets-sheetx = li_xls_row2 . "Sheet 2 Data
sheets-sheetname = 'Sheet 2' .
translate sheets-sheetname TO UPPER CASE .
APPEND sheets TO it_sheets.You Will Get Clear understanding by looking in the below report code :-
REPORT Z_DOWNLOAD_EXCEL.
DATA binary_content TYPE solix_tab.
DATA :BEGIN OF ls_xls_cell,
styleid TYPE string,
type TYPE string,
cell_content TYPE string,
END OF ls_xls_cell,
li_xls_cells LIKE TABLE OF ls_xls_cell,
BEGIN OF ls_column_width,
col_index TYPE i ,
col_width TYPE i ,
end of ls_column_width ,
li_column_width like TABLE OF ls_column_width ,
BEGIN OF ls_xls_row,
rownr TYPE i,
RowHight TYPE i ,
cells LIKE li_xls_cells,
END OF ls_xls_row ,
li_xls_row LIKE TABLE OF ls_xls_row.
DATA : BEGIN OF sheets ,
sheetname TYPE string,
sheetx LIKE li_xls_row,
ColWidth like li_column_width,
END OF sheets ,
it_sheets LIKE TABLE OF sheets.
DATA : lv_xml_string TYPE xstring,
BEGIN OF ls_documentproperties,
author TYPE string,
END OF ls_documentproperties,
BEGIN OF ls_font,
family TYPE string VALUE 'Swiss' ,
FontName TYPE string VALUE 'Arial' ,
Font_Size TYPE i VALUE 10 ,
color TYPE String ,
bold TYPE char1,
italic TYPE char1 ,
Underline TYPE string ,
END OF ls_font,
BEGIN OF ls_alignment ,
Horizontal TYPE char10 , "VALUE 'Left',
vertical TYPE char10 , "VALUE 'Bottom',
WrapText TYPE char1 ,
END OF ls_alignment ,
BEGIN OF ls_border_face,
top TYPE string ,
bottom TYPE string ,
left TYPE string ,
right TYPE string ,
END OF ls_border_face,
BEGIN OF ls_border,
linestyle like ls_border_face,
weight TYPE char1,
Color like ls_border_face,
END OF ls_border,
BEGIN OF ls_interior,
colorindex TYPE string,
pattern TYPE string,
patterncolorindex TYPE string,
END OF ls_interior,
BEGIN OF ls_style,
id TYPE string,
font LIKE ls_font,
Alignment like ls_alignment ,
border LIKE ls_border,
interior LIKE ls_interior,
END OF ls_style,
BEGIN OF ls_excelmeta,
documentproperties LIKE ls_documentproperties,
styles LIKE TABLE OF ls_style,
END OF ls_excelmeta .",
ls_excelmeta-documentproperties-author = sy-uname.
* SET THE HEADER STYLE
***********includes all Excel style formatting
CLEAR: ls_style.
ls_style-id = 's21'. " Style 1 for includes all formatting
ls_style-font-family = 'Script' ."'Swiss'.
ls_style-font-fontname = 'Ink Free' .
ls_style-font-bold = '1'.
ls_style-font-italic = '0'.
ls_style-font-underline = 'Single' . " 'Double' .
ls_style-font-color = '#92D050' .
ls_style-alignment-horizontal = 'Center' .
ls_style-alignment-vertical = 'Center' .
ls_style-alignment-wraptext = '1' .
ls_style-border-linestyle-bottom =
ls_style-border-linestyle-top =
ls_style-border-linestyle-left =
ls_style-border-linestyle-right = 'SlantDashDot'. "'Continuous' . "'SlantDashDot' "'DashDotDot "'Double' "'DashDot' ..
ls_style-border-weight = '1'.
ls_style-border-color-top =
ls_style-border-color-bottom =
ls_style-border-color-left =
ls_style-border-color-right = '#FF0000' .
ls_style-interior-colorindex = '#FFFFF1'.
ls_style-interior-pattern = 'Gray0625'. "'HorzStripe'.
ls_style-interior-patterncolorindex = '#FF0000'.
APPEND ls_style TO ls_excelmeta-styles.
**** Text with Font 'BOLD' 'ITALIC' 'SINGLE UNDERLINE' AND 'FONT FAMILY "Decorative" '
CLEAR: ls_style.
ls_style-id = 's22'. " Style 2 for text formattign
ls_style-font-family = 'Decorative'.
ls_style-font-fontname = 'Tempus Sans ITC' .
ls_style-font-bold = '1'.
ls_style-font-italic = '1'.
ls_style-font-underline = 'Single' . " 'Double' .
ls_style-font-color = '#00B0F0' .
APPEND ls_style TO ls_excelmeta-styles.
**** Text with Font :- 'ITALIC' 'DOUBLE UNDERLINE' AND 'FONT FAMILY "Decorative" '
CLEAR: ls_style.
ls_style-id = 's23'. " Style 3 for text formattign
ls_style-font-family = 'Decorative'.
ls_style-font-fontname = 'Tempus Sans ITC' .
ls_style-font-italic = '1'.
ls_style-font-underline = 'Double' .
ls_style-font-color = '#7030A0' .
APPEND ls_style TO ls_excelmeta-styles.
**** Text with Font :- 'BOLD' AND 'FONT FAMILY default different font colour '
CLEAR: ls_style.
ls_style-id = 's24'. " Style 3 for text formattign
ls_style-alignment-WrapText = '1' .
ls_style-font-bold = '1'.
ls_style-font-color = '#FA0000' .
APPEND ls_style TO ls_excelmeta-styles.
********************* Border design and formatting
*********************** Border with all DashDotDot and diff col
CLEAR: ls_style.
ls_style-id = 'b21'.
ls_style-border-linestyle-bottom = 'DashDotDot'.
ls_style-border-linestyle-top = 'DashDotDot'.
ls_style-border-linestyle-left = 'DashDotDot'.
ls_style-border-linestyle-right = 'DashDotDot'.
ls_style-border-weight = '2'.
ls_style-border-color-top = '#7030A0' .
ls_style-border-color-bottom = '#7030A0' .
ls_style-border-color-left = '#7030A0' .
ls_style-border-color-right = '#7030A0' .
APPEND ls_style TO ls_excelmeta-styles.
*********************** Border with bottom and right double and top DashDotDot and diff col
CLEAR: ls_style.
ls_style-id = 'b22'.
ls_style-border-linestyle-bottom = 'Double'.
ls_style-border-linestyle-top = 'DashDotDot'.
* ls_style-border-linestyle-left = 'Double'.
ls_style-border-linestyle-right = 'Double'.
ls_style-border-weight = '2'.
ls_style-border-color-top = '#00B0F0' .
ls_style-border-color-bottom = '#00B0F0' .
ls_style-border-color-left = '#00B0F0' .
ls_style-border-color-right = '#00B0F0' .
APPEND ls_style TO ls_excelmeta-styles.
*********************** Border with all DashDot and diff col
CLEAR: ls_style.
ls_style-id = 'b23'.
ls_style-border-linestyle-bottom =
ls_style-border-linestyle-top =
ls_style-border-linestyle-left =
ls_style-border-linestyle-right = 'DashDot'. "'Continuous' . "'SlantDashDot' "'DashDotDot "'Double' "'DashDot' ..
ls_style-border-weight = '2'.
ls_style-border-color-top = '#7030A0' .
ls_style-border-color-bottom = '#7030A0' .
ls_style-border-color-left = '#7030A0' .
ls_style-border-color-right = '#7030A0' .
APPEND ls_style TO ls_excelmeta-styles.
********************** Interior cell formatting
********************** Cell With pattern without colour
CLEAR: ls_style.
ls_style-id = 'I21'.
* ls_style-interior-colorindex = '#FFFFF1'.
ls_style-interior-pattern = 'HorzStripe'.
ls_style-interior-patterncolorindex = '#FF0000'.
APPEND ls_style TO ls_excelmeta-styles.
********************** Cell With pattern without colour
CLEAR: ls_style.
ls_style-id = 'I22'.
* ls_style-interior-colorindex = '#FFFFF1'.
ls_style-interior-pattern = 'ThinHorzCross'.
ls_style-interior-patterncolorindex = '#FF0000'.
APPEND ls_style TO ls_excelmeta-styles.
********************** Cell With pattern with colour
CLEAR: ls_style.
ls_style-id = 'I23'.
ls_style-font-font_size = 24 .
ls_style-interior-colorindex = '#F5909C'.
ls_style-interior-pattern = 'ThinHorzCross'.
ls_style-interior-patterncolorindex = '#7030A0'.
APPEND ls_style TO ls_excelmeta-styles.
CLEAR ls_xls_row.
********************************* Create Header for Excel output **************************************************
ls_xls_cell-type = 'String'.
ls_xls_cell-styleid = 's21'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = 'S21-includes all formatting' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 's22'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = ' ''BOLD'' ''ITALIC'' ''SINGLE UNDERLINE'' AND ''FONT FAMILY "Decorative"'' ' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 's23'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = '''ITALIC'' ''DOUBLE UNDERLINE'' AND ''FONT FAMILY ''Decorative'' ' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 's24'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = '''BOLD'' AND ''FONT FAMILY default different font colour'' ' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_row-rowhight = 100 . " Row 1 Hight will be 100
APPEND ls_xls_row TO li_xls_row.
CLEAR ls_xls_cell-cell_content.
CLEAR ls_xls_row.
ls_xls_cell-type = 'String'.
ls_xls_cell-styleid = 'b21'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = 'Border with all DashDotDot and diff color' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 'b22'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = 'Border with bottom and right double and top DashDotDot and diff col' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 'b23'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-type = 'String'.
ls_xls_cell-cell_content = 'Border with all DashDot and diff col' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 'Default'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-type = 'Number'.
ls_xls_cell-cell_content = '0002345677' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_row-rowhight = 50 .
APPEND ls_xls_row TO li_xls_row.
CLEAR ls_xls_cell-cell_content.
CLEAR ls_xls_row.
ls_xls_cell-type = 'String'.
ls_xls_cell-styleid = 'I21'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = 'Cell With pattern without colour' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 'I22'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-cell_content = 'diff pattern without colour' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 'I23'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-type = 'String'.
ls_xls_cell-cell_content = 'Cell With pattern with colour' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_cell-styleid = 'I23'.
CLEAR ls_xls_cell-cell_content.
ls_xls_cell-type = 'Number'.
ls_xls_cell-cell_content = '87656545343' .
APPEND ls_xls_cell TO ls_xls_row-cells.
ls_xls_row-rowhight = 150 .
APPEND ls_xls_row TO li_xls_row.
CLEAR ls_xls_cell-cell_content.
CLEAR ls_xls_row.
ls_column_width-col_index = 2 .
ls_column_width-col_width = 200 .
append ls_column_width to li_column_width .
ls_column_width-col_index = 3 .
ls_column_width-col_width = 400 .
append ls_column_width to li_column_width .
sheets-colwidth = li_column_width .
sheets-sheetx = li_xls_row .
sheets-sheetname = 'Sheet 1' .
translate sheets-sheetname TO UPPER CASE .
APPEND sheets TO it_sheets.
CLEAR sheets.
sheets-sheetx = li_xls_row .
sheets-sheetname = 'Sheet 2' .
translate sheets-sheetname TO UPPER CASE .
APPEND sheets TO it_sheets.
CLEAR sheets.
CALL TRANSFORMATION ZEXCEL_XML_TRANS
SOURCE excelmeta = ls_excelmeta
sheets = it_sheets
RESULT XML lv_xml_string.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = lv_xml_string
TABLES
binary_tab = binary_content.
DATA : filename TYPE string .
CONCATENATE 'C:\TestExcel' '\Excel_sheet_' sy-datum '_' sy-uzeit '.XLS' INTO filename .
CONDENSE filename .
CALL FUNCTION 'GUI_DOWNLOAD'
EXPORTING
filename = filename
filetype = 'BIN'
TABLES
data_tab = binary_content .Exicute above report , it will download a excel file in your C drive which will have the excel formattigns as shown below .
