Skip to Content
Technical Articles

Internal to Excel with multiple sheets and formatting

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)

Create%20New%20Transformation

Create New Transformation

 

Next you will get below Screen, put description of your choice and select Type as Simple Transformation

Select%20Simple%20Transformation

Select Simple Transformation

Now Put below Code in SourceCode Tab . 

Enter%20Code%20in%20SourceCode%20section%20as%20shown%20here

Enter Code in SourceCode section as shown here

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'."Bold

Italic:-

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 .

 

 

Hope This will help you in your develoment  .

You can do more by changing the XML code .

As I feel, that will not be required .

 

Thanks

Navneet Anand

 

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