Download excel with multiple sheets using Transformation

[sankar1781]

Hi Experts,

I have a requirement to download the data into an excel file with multiple/two sheets.

I followed the blog "Internal to Excel with multiple sheets and formatting" and just changed only the below area.

    ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    ls_xls_cell-cell_content = lv_ftext.  "fieldnames
    APPEND ls_xls_cell TO ls_xls_row-cells.

    ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    ls_xls_cell-cell_content = lv_vtext. "fieldvalues
    APPEND ls_xls_cell TO ls_xls_row-cells.

Rest are all same but I am getting the downloaded file data as shown in the image.

Where I run the blog help program, I can see the output as in the blog i.e., Excel sheet with multiple rows and multiple sheets (Sheet 1 and Sheet 2). Whereas I am getting an incorrect one.

Please help.

Thanks in advance.

[Sandra_Rossi]

Use the blog post as it is, don't change the code, and tell us whether it works. Please use the CODE button ("..." then </>) to format your code, to make your question more attractive. Note that you'd better use abap2xlsx or XCO.

    ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    ls_xls_cell-cell_content = lv_ftext.  "fieldnames
    APPEND ls_xls_cell TO ls_xls_row-cells.

    ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    ls_xls_cell-cell_content = lv_vtext. "fieldvalues
    APPEND ls_xls_cell TO ls_xls_row-cells.

[sankar1781]

Hi Sandra_Rossi,

Thanks for your response. 

Use the blog post as it is, don't change the code, and tell us whether it works.: Yes. it works and able to view the output as mentioned in the blog result. 

Instead of the hardcoded text for creating data in A1 cell </>ls_xls_cell-cell_content = 'S21-Includes all formatting', I want to replace/provide my own requirement value that I have it as Field names in the variable lv_fieldtext.

If I modify the below one as "ls_xls_cell-cell_content = lv_fieldtext" , it is considering but writing it along with xml codes and it is not considering the next row of Cell (A2) value and also not writing the Sheet 2. 

My code change is:

     ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    <//> ls_xls_cell-cell_content = lv_fieldtext. <//> "code changed for A1 cell value
    APPEND ls_xls_cell TO ls_xls_row-cells.

ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    <//> ls_xls_cell-cell_content = lv_valuetext.  <//> "code changed for A2 cell value
    APPEND ls_xls_cell TO ls_xls_row-cells.

Rest, I haven't changed any logic either in the Transformation xml codes and in the editor codes. I also used to have multiple sheets as specified in the blog but in the result. the excel has only one sheet and will the xml tags as shown below.

Only sheet1, xml tags are in the A1 cell value instead of lv_fieldtext and no A2 cell value.

Blog code where the only place the change has performed from my end.

ls_xls_cell-cell_content = 'S21-includes all formatting' 

 abap2xlsx : I am using S4H system where this abap2xlsx are not configured. 

[sankar1781]

Hi Sandra_Rossi, Thanks for your response.

[Sandra_Rossi]

If I understand well the blog post, its example writes 3 rows of each 4 cells. I guess you are trying to write 5 cells in the first row, and 4 cells in the next rows. Try 5 cells in each row.

Concerning abap2xlsx, you must install it of course, like you installed the code of the blog post you are mentioning.

[sankar1781]

Hi Sandra_Rossi, thanks for your response again and I am sorry for the long delay in replying to your comment. I am also not clear with your comments. My requirement is in the first work sheet "Sheet 1" of the xls file, the first two rows either in the single cell or in multiple cells as columns, it must have the Header Field names. The 2nd row must have the Item field values. Next "Sheet 2" , I will write the Text ID/Name and Description details for the business user to go, refer for their mapping (into the Sheet 1 again). So, all I need two Sheets in the XLS or CSV file. 1st sheet with 2 rows and the next sheet will have multiple rows or some text information. Help me out with your solution, how to bring 2 /multiple sheets of excel file by using the blog. Again, if I use the same transformation and the ABAP code programs, I am getting the output with 2 sheets but not in my requirement. Kindly help.

[Sandra_Rossi]

What did you understand from my answer, what didn't you understand?

[sankar1781]

Hi Sandra_Rossi, I apologize for not clear in my comment and not answered exactly as mentioned in the cell_content to your expectation. Yes, in the blog example, it writes 3 rows of each cell(A1,A2, A3). But my requirements here is to write one row with multiple cells (A1, B1, C1 and it goes) as it covers the Field names based on my requirement/logic. 2nd row will have the associated values. This will be displayed in the Sheet 1. And, my requirement should also cover to have multiple sheet for Ex: Sheet 2. It will have another set of data will have the constant values of few domain descriptions. Here, I followed the blog and try to input my header field names (as lv_string1) as specified in the blog "Create data for A1 cell" but what I got a different one with xml tags. Second, I also followed "To Add multiple sheets in the excel Write below code"  to add Sheet 2 but I didn't get it. 

I guess you are trying to write 5 cells in the first row, and 4 cells in the next rows. Try 5 cells in each row.: I try to write 2 rows but multiple cells in the Sheet 1 and Domain descriptions in the Sheet 2 (multiple rows in the Cell A/Column A).

you installed the code of the blog post: I didn't install any. I followed the steps (creating Transformation and program) and it worked. Able to view the excel with multiple sheets as in the blog result.

$ABAPGIT : Not installed in our system.

[Sandra_Rossi]

"Installing" the code of the blog post means copy/paste manually the code of the objects in this case. No problem at my side to adapt the code (5th column added). Sorry, I don't understand what the difficulty is.

[sankar1781]

Hi Sandra_Rossi,

"Installing" the code of the blog post means copy/paste manually the code of the objects in this case. No problem at my side to adapt the code (5th column added). Sorry, I don't understand what the difficulty is.

The difficulty in my end is, I adapted the same code as in the blog and I just tried to change the cell content value by replacing the hard coded text to my own variable (lv_ftext which has multiple header field names with ','). I have not changed any other codes including the transformation codes. But, I got the output result in excel sheet as xml codes along with my lv_ftext values (please check once my shared images/attachments). 

ls_xls_cell-cell_content = lv_ftext.  "fieldnames

Second, I am not getting the Sheet 2 (multiple sheet) and only sheet 1 though I added the logic as mentioned in the blog.

Sorry really and much appreciated for extending your response still though my request is not clear to you. 

All I need, download an excel with multiple sheets using this blog but failed. 

Thanks.

[Sandra_Rossi]

Even if you changed a few lines, probably you did a big mistake, so please post the whole code (and formatting by using "..." and "</>", otherwise I won't look at it).

[sankar1781]

Hi Sandra_Rossi,

FUNCTION zofimmfm_mdg_mat_mass_excel_dl.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(IV_TYPE1) TYPE  CHAR10
*"     VALUE(IV_TYPE2) TYPE  CHAR10
*"     VALUE(IV_TYPE3) TYPE  CHAR10
*"     VALUE(IV_MATNR) TYPE  MATNR
*"     VALUE(IV_WERKS) TYPE  WERKS_D
*"     VALUE(IV_VKORG) TYPE  VKORG
*"     VALUE(IV_VTWEG) TYPE  VTWEG
*"     VALUE(IV_LGORT) TYPE  LGORT_D
*"     VALUE(IV_LGNUM) TYPE  LGNUM

  DATA lt_dfis      TYPE TABLE OF dfies.

  DATA lt_fields    TYPE TABLE OF /inowits/mass_f.
  DATA ls_fields    TYPE          /inowits/mass_f.
  DATA lv_rows      TYPE          i.
  DATA lv_app_typ   TYPE          /inowits/mdm_typ.
  DATA lv_tablename TYPE          tabname.
  DATA lv_fieldname TYPE          fieldname.
  DATA lv_filename  TYPE          string.
  DATA ls_dfis      TYPE          dfies.
  DATA lv_dftext    TYPE          string.
  DATA lv_ftext     TYPE          string.
  DATA lv_vtext     TYPE          string.
  DATA lv_xtext     TYPE          xstring.
  DATA lv_stext     TYPE          string.
  DATA lv_index     TYPE          sy-tabix.
  DATA lv_fval      TYPE          string.

  DATA: gt_table    TYPE REF TO data,
        gs_new_line TYPE REF TO data.

  FIELD-SYMBOLS: <fs_table> TYPE table,
                 <fs_line>  TYPE any,
                 <fs_line1> TYPE any.

  DATA: lv_mc_obj LIKE bapi1003_key-object,

        lv_mc_tab LIKE bapi1003_key-objecttable VALUE 'MARA',

        lv_mc_typ LIKE bapi1003_key-classtype.

  DATA: lt_alloclist TYPE TABLE OF bapi1003_alloc_list,
        lt_val_char  TYPE TABLE OF bapi1003_alloc_values_char,
        lt_return    TYPE TABLE OF bapiret2.

  DATA: lv_textname TYPE tdobname.

  DATA: lv_data_assign TYPE c.     "Flagging indicator

  DATA:
    gdo_data      TYPE REF TO data.

  DATA:
    lv_objek       TYPE ausp-objek,
    lt_class       TYPE TABLE OF sclass,
    lt_classifdata TYPE TABLE OF clobjdat.

  DATA:
    lt_mattxt   TYPE TABLE OF tline,
    lt_porgtxt  TYPE TABLE OF tline,
    lt_salestxt TYPE TABLE OF tline.

  FIELD-SYMBOLS: <fs_str> TYPE any.
  FIELD-SYMBOLS: <fs_data> TYPE any.

  DATA: lw_mara TYPE mara.

  DATA: lv_inc TYPE n LENGTH 2.

  "Writing the data into Excel fil
  CONSTANTS c_file_extension TYPE string VALUE 'csv' ##NO_TEXT.
  CONSTANTS c_file_filter TYPE string VALUE '*.csv' ##NO_TEXT.
  CONSTANTS c_path TYPE string VALUE 'C:\Users\sankar.babu\Downloads\'.

  DATA: lv_file        TYPE string,
        lv_path        TYPE string,
        lv_fullpath    TYPE string,
        lv_user_action TYPE i,
        ls_fieldname   TYPE string,
        lt_fieldname   LIKE STANDARD TABLE OF ls_fieldname.

  DATA: lv_file_name TYPE string.
  DATA: lt_return2   TYPE bapiret2_tab.

  "Selecting Mass update fields
  SELECT   f~app_typ
           f~fld_nam
           f~tbl_nam
           f~dat_ele
           f~use_rul
           f~mandatory
           f~read_only
           f~priority
                     FROM /inowits/mass_f  AS f INNER JOIN
                     /inowits/mass_in AS i
                     ON f~fld_nam EQ i~fld_nam
                     INTO CORRESPONDING FIELDS OF TABLE lt_fields
                     WHERE f~app_typ EQ 'M'         "Material
                     AND   i~app_typ EQ 'M'         "Material
                     AND   f~type1   EQ iv_type1    "mat->Mat Type,
                     AND   f~type2   EQ iv_type2    "mat->Plant
                     AND   f~type3   EQ iv_type3    "mat->val class/Sales org
*                   AND   f~type4   EQ lv_type4
                     AND   f~use_rul EQ 'U'
                     ORDER BY i~indx.

  DESCRIBE TABLE lt_fields LINES lv_rows. " To get no. of rows

  lv_mc_typ = '001'.  "Material class
  lv_mc_tab = 'MARA'.

  " Selecting Material data from MARA
  SELECT SINGLE * FROM mara INTO @DATA(ls_mara)
                     WHERE matnr = @iv_matnr.
  IF sy-subrc EQ 0.
    "Selecting description
    SELECT SINGLE * FROM makt INTO @DATA(ls_makt)
                              WHERE matnr = @iv_matnr
                              AND   spras = @SY-langu.
    CLEAR lv_textname.
    lv_textname = iv_matnr.
    "Selecting Purchase org text
    SELECT SINGLE * FROM stxh INTO @DATA(ls_basic_text)
      WHERE tdobject   = 'MATERIAL'
      AND   tdname     = @lv_textname
      AND   tdid       = 'GRUN'.
    IF sy-subrc EQ 0.
      CALL FUNCTION 'READ_TEXT'
        EXPORTING
          id                      = ls_basic_text-tdid
          language                = ls_basic_text-tdspras
          name                    = ls_basic_text-tdname
          object                  = ls_basic_text-tdobject
        TABLES
          lines                   = lt_mattxt
        EXCEPTIONS
          id                      = 1
          language                = 2
          name                    = 3
          not_found               = 4
          object                  = 5
          reference_check         = 6
          wrong_access_to_archive = 7
          OTHERS                  = 8.
      IF sy-subrc <> 0.
* Implement suitable error handling here
      ENDIF.

    ENDIF.
    "Fetching data from MARC table
    SELECT SINGLE * FROM marc INTO @DATA(ls_marc)
      WHERE matnr = @iv_matnr
        AND werks = @iv_werks.
    IF sy-subrc = 0.
      CLEAR lv_textname.
      lv_textname = iv_matnr.
      "Selecting Purchase org text
      SELECT SINGLE * FROM stxh INTO @DATA(ls_purorg_text)
        WHERE tdobject   = 'MATERIAL'
        AND   tdname     = @lv_textname
        AND   tdid       = 'BEST'.
      IF sy-subrc EQ 0.
        CALL FUNCTION 'READ_TEXT'
          EXPORTING
            id                      = ls_purorg_text-tdid
            language                = ls_purorg_text-tdspras
            name                    = ls_purorg_text-tdname
            object                  = ls_purorg_text-tdobject
          TABLES
            lines                   = lt_porgtxt
          EXCEPTIONS
            id                      = 1
            language                = 2
            name                    = 3
            not_found               = 4
            object                  = 5
            reference_check         = 6
            wrong_access_to_archive = 7
            OTHERS                  = 8.
        IF sy-subrc <> 0.
*   Implement suitable error handling here
        ENDIF.
      ENDIF.
    ENDIF.
    "Fetching data from MVKE table
    SELECT SINGLE * FROM mvke INTO @DATA(ls_mvke)
      WHERE matnr = @iv_matnr
        AND vkorg = @iv_vkorg
        AND vtweg = @iv_vtweg.
    IF sy-subrc = 0.
      CLEAR lv_textname.
      lv_textname = iv_matnr.
      lv_textname+40(30) = iv_vkorg && iv_vtweg.
      "Selecting sales texts
      SELECT SINGLE * FROM stxh INTO @DATA(ls_sales_text)
        WHERE tdobject   = 'MVKE'
        AND   tdname = @lv_textname
        AND   tdid       = '0001'.
      IF sy-subrc EQ 0.
        CALL FUNCTION 'READ_TEXT'
          EXPORTING
            id                      = ls_sales_text-tdid
            language                = ls_sales_text-tdspras
            name                    = ls_sales_text-tdname
            object                  = ls_sales_text-tdobject
          TABLES
            lines                   = lt_salestxt
          EXCEPTIONS
            id                      = 1
            language                = 2
            name                    = 3
            not_found               = 4
            object                  = 5
            reference_check         = 6
            wrong_access_to_archive = 7
            OTHERS                  = 8.
        IF sy-subrc <> 0.
*   Implement suitable error handling here
        ENDIF.
      ENDIF.
    ENDIF.                         "End of mvke
    "Fetching data from MARD table
    SELECT SINGLE * FROM mard INTO @DATA(ls_mard)
      WHERE matnr = @iv_matnr
      AND werks = @iv_werks
      AND lgort = @iv_lgort.
    IF sy-subrc = 0.
    ENDIF.
    "Fetching data from MARM table
    SELECT SINGLE * FROM marm INTO @DATA(ls_marm)
      WHERE matnr = @iv_matnr.
    IF sy-subrc = 0.
      "Fetching data from MBEW table
    ENDIF.
    SELECT SINGLE * FROM mbew INTO @DATA(ls_mbew)
      WHERE matnr = @iv_matnr
        AND bwkey = @iv_werks "change if necessary
        AND bwtar = @iv_type3."Change if necessary
    IF sy-subrc = 0.

    ENDIF.
    ""Fetching data from MLGN table
    SELECT SINGLE * FROM mlgn INTO @DATA(ls_mlgn)
      WHERE matnr = @iv_matnr
        AND lgnum = @iv_lgnum.
    IF sy-subrc = 0.

    ENDIF.
    "Forecast data
    SELECT SINGLE * INTO @DATA(ls_prop)
                         FROM mapr INNER JOIN prop
                          ON    mapr~pnum1  = prop~pnum1
                          WHERE mapr~matnr = @iv_matnr
                          AND   mapr~werks = @iv_werks
                          AND   prop~hsnum = '00'.
    IF sy-subrc EQ 0.

    ENDIF.
    CALL FUNCTION 'CLAF_CLASSIFICATION_OF_OBJECTS'
      EXPORTING
        class              = 'ZGT_MATECLASS'
        classtext          = 'X'
        classtype          = '001'
*       CLINT              = 0
*       FEATURES           = 'X'
        language           = sy-langu
        object             = lv_objek      "AUSP-OBJEK
*       OBJECTTABLE        = ' '
        key_date           = sy-datum
        initial_charact    = 'X'
        no_value_descript  = 'X'
        change_service_clf = 'X'
*       INHERITED_CHAR     = ' '
*       CHANGE_NUMBER      = ' '
      TABLES
        t_class            = lt_class
        t_objectdata       = lt_classifdata
*       I_SEL_CHARACTERISTIC       =
*       T_NO_AUTH_CHARACT  =
      EXCEPTIONS
        no_classification  = 1
        no_classtypes      = 2
        invalid_class_type = 3
        OTHERS             = 4.
    IF sy-subrc <> 0.
* Implement suitable error handling here
    ENDIF.

  ENDIF.

  "Writing fieldname and corresponding fieldvalue
  LOOP AT lt_fields INTO ls_fields.

    REFRESH: lt_dfis.
    CLEAR: lv_dftext, lv_fieldname, lv_tablename, lv_index, ls_dfis, lt_dfis.
    CLEAR: lv_textname, lv_data_assign, lv_fval.

    MOVE sy-tabix TO lv_index.
    MOVE ls_fields-fld_nam TO lv_fieldname. " Field Name
    MOVE ls_fields-tbl_nam TO lv_tablename. " Table Name

    " FM to get the Field Info (Data Element Information)
    CALL FUNCTION 'DDIF_FIELDINFO_GET'
      EXPORTING
        tabname        = lv_tablename
        fieldname      = lv_fieldname
        langu          = sy-langu
      TABLES
        dfies_tab      = lt_dfis
      EXCEPTIONS
        not_found      = 1
        internal_error = 2
        OTHERS         = 3.

    READ TABLE lt_dfis INTO ls_dfis   " To get Field Description
      WITH KEY fieldname = lv_fieldname.

    IF sy-subrc EQ 0.
      MOVE ls_dfis-scrtext_m TO lv_dftext.  " Field Description
      IF lv_ftext IS INITIAL.
        MOVE ls_dfis-fieldtext TO lv_dftext.  " Field Description
      ENDIF.
    ELSE.
      MOVE lv_fieldname TO lv_dftext.
    ENDIF.

    "Mandatory
    IF ls_fields-mandatory IS NOT INITIAL.
      CONCATENATE lv_ftext '*' INTO lv_ftext.
    ENDIF.

    CONCATENATE lv_ftext lv_dftext INTO lv_ftext.

    IF lv_app_typ = 'C'.
*      MOVE 'XLS' TO  lv_format.
      CONCATENATE lv_ftext cl_abap_char_utilities=>horizontal_tab INTO lv_ftext .
    ELSE.
      CONCATENATE lv_ftext ',' INTO lv_ftext .
    ENDIF.

*    "Check if data is not assigned above
*    IF lv_data_assign EQ abap_false.
    "Get MARA values and move it into another variable
    ASSIGN ls_mara TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
*        IF lv_fieldname = 'MATNR'.
*          lv_mc_obj = <fs_data>.
*          CALL FUNCTION 'BAPI_OBJCL_GETCLASSES'
*            EXPORTING
*              objectkey_imp   = lv_mc_obj
*              objecttable_imp = lv_mc_tab
*              classtype_imp   = lv_mc_typ
*              read_valuations = 'X'
**             KEYDATE         = SY-DATUM
**             LANGUAGE        = SY-LANGU
**             OBJECTKEY_IMP_LONG       =
*            TABLES
*              alloclist       = lt_alloclist
*              allocvalueschar = lt_val_char
**             ALLOCVALUESCURR =
**             ALLOCVALUESNUM  =
*              return          = lt_return.
*        ENDIF.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.
*    ENDIF.

    "Get MAKT values and move it into another variable
    ASSIGN ls_makt TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.

*    "Check if data is not assigned above
*    IF lv_data_assign EQ abap_false.
    "Get MARC values and move it into another variable
    ASSIGN ls_marc TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.

*    "Check if data is not assigned above
*    IF lv_data_assign EQ abap_false.
    "Get MVKE values and move it into another variable
    ASSIGN ls_mvke TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.
*    ENDIF.

*    "Check if data is not assigned above
*    IF lv_data_assign EQ abap_false.
    "Get MARD values and move it into another variable
    ASSIGN ls_mard TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.
*    ENDIF.

    "Check if data is not assigned above
*    IF lv_data_assign EQ abap_false.
    "Get MARM values and move it into another variable
    ASSIGN ls_marm TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
    ENDIF.
    UNASSIGN <fs_str>.
*    ENDIF.

*    "Check if data is not assigned above
*    IF lv_data_assign EQ abap_false.
    "Get MBEW values and move it into another variable
    ASSIGN ls_mbew TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.
*    ENDIF.

*    "Check if data is not assigned above
*    IF lv_data_assign EQ abap_false.
    "Get MLGN values and move it into another variable
    ASSIGN ls_mlgn TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.
*    ENDIF.

*    "Check if data is not assigned above
**    IF lv_data_assign EQ abap_false.
    "Get PROP values and move it into another variable
    ASSIGN ls_prop TO <fs_str>.
    IF <fs_str> IS ASSIGNED.
      ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_str> TO <fs_data>.
      IF <fs_data> IS ASSIGNED.
        lv_data_assign = abap_true.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = <fs_data>.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        UNASSIGN <fs_data>.
        IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
      UNASSIGN <fs_str>.
    ENDIF.
    "Read from classfication data
    READ TABLE lt_classifdata INTO DATA(ls_classifdata) WITH KEY
                                                        atnam = lv_fieldname.
    IF sy-subrc EQ 0.
      IF ls_fields-mandatory IS NOT INITIAL.
        CONCATENATE lv_vtext '*' INTO lv_vtext.
      ENDIF.
      IF ls_classifdata-ausp1 EQ '?'.
        CLEAR ls_classifdata-ausp1.
      ENDIF.
      lv_fval = ls_classifdata-ausp1.
      CONCATENATE lv_vtext lv_fval INTO lv_vtext.
      IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
        CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
      ELSE.
        CONCATENATE lv_vtext ',' INTO lv_vtext .
      ENDIF.
      CONTINUE.
    ENDIF.
    "Material text data
    IF lv_fieldname = 'AD_FLANGUAGE_1'.
      IF ls_basic_text IS NOT INITIAL.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = ls_basic_text-tdspras.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        IF lv_app_typ = 'C'.
*          MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
    ENDIF.
    IF lv_fieldname = 'AD_TEXT_1'.
      LOOP AT lt_mattxt INTO DATA(ls_line).
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = ls_line-tdline.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        CLEAR ls_line.
      ENDLOOP.
      IF lv_app_typ = 'C'.
*         MOVE 'XLS' TO  lv_format.
        CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
      ELSE.
        CONCATENATE lv_vtext ',' INTO lv_vtext .
      ENDIF.
      CONTINUE.
    ENDIF.
    "Material Sales text data
    IF lv_fieldname = 'SLS_FLANGUAGE_1'.
      IF ls_sales_text IS NOT INITIAL.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = ls_sales_text-tdspras.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        IF lv_app_typ = 'C'.
*          MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
    ENDIF.
    IF lv_fieldname = 'SLS_TEXT_1'.
      CLEAR ls_line.
      LOOP AT lt_salestxt INTO ls_line.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = ls_line-tdline.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        CLEAR ls_line.
      ENDLOOP.
      IF lv_app_typ = 'C'.
*         MOVE 'XLS' TO  lv_format.
        CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
      ELSE.
        CONCATENATE lv_vtext ',' INTO lv_vtext .
      ENDIF.
      CONTINUE.
    ENDIF.
    "Material Purchase org text data
    IF lv_fieldname = 'PUR_FLANGUAGE_1'.
      IF ls_purorg_text IS NOT INITIAL.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = ls_purorg_text-tdspras.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        IF lv_app_typ = 'C'.
*          MOVE 'XLS' TO  lv_format.
          CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
        ELSE.
          CONCATENATE lv_vtext ',' INTO lv_vtext .
        ENDIF.
        CONTINUE.
      ENDIF.
    ENDIF.
    IF lv_fieldname = 'PUR_TEXT_1'.
      CLEAR ls_line.
      LOOP AT lt_porgtxt INTO ls_line.
        IF ls_fields-mandatory IS NOT INITIAL.
          CONCATENATE lv_vtext '*' INTO lv_vtext.
        ENDIF.
        lv_fval = ls_line-tdline.
        CONCATENATE lv_vtext lv_fval INTO lv_vtext.
        CLEAR ls_line.
      ENDLOOP.
      IF lv_app_typ = 'C'.
*         MOVE 'XLS' TO  lv_format.
        CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
      ELSE.
        CONCATENATE lv_vtext ',' INTO lv_vtext .
      ENDIF.
      CONTINUE.
    ENDIF.
    "If fieldname is not initial but fieldvalue is initial
    IF NOT lv_fieldname IS INITIAL AND lv_fval IS INITIAL.
      IF lv_app_typ = 'C'.
*        MOVE 'XLS' TO  lv_format.
        CONCATENATE lv_vtext cl_abap_char_utilities=>horizontal_tab INTO lv_vtext .
      ELSE.
        CONCATENATE lv_vtext ',' INTO lv_vtext .
      ENDIF.
    ENDIF.

    CLEAR ls_fields.
  ENDLOOP.

*  "Combining std. text fieldnames
*  CONCATENATE lv_ftext lv_stext INTO lv_ftext.

<....>"Blog: Download Excel with multiple sheets using Transformation<...>
*************Writing into XML string*************
    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,
          li_xls_row2 LIKE TABLE OF ls_xls_row.

    DATA: 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.

    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.

    DATA:  lt_binary_content TYPE solix_tab.

   "Appending row to write Field names from the A1 cell.
    ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    </>ls_xls_cell-cell_content = lv_ftext.</>
    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 = 1 .
    ls_column_width-col_width = 50 .
    APPEND ls_column_width TO li_column_width .

    ls_column_width-col_index = 2 .
    ls_column_width-col_width = 200 .
    APPEND ls_column_width TO li_column_width .

    sheets-colwidth = li_column_width .

    "Adding another row to write the Item/Values from the A2 cell.
    CLEAR ls_xls_cell.
    ls_xls_cell-styleid = 'Default'.
    CLEAR ls_xls_cell-cell_content.
    ls_xls_cell-type = 'String'.
    </>ls_xls_cell-cell_content = lv_vtext</>.
    APPEND ls_xls_cell TO ls_xls_row-cells.

    "Sheet 1
    sheets-sheetx = li_xls_row .
    sheets-sheetname =  'Sheet 1' .
    TRANSLATE sheets-sheetname TO UPPER CASE .
    APPEND  sheets TO it_sheets.    
    CLEAR sheets.

    "Sheet 2
    sheets-sheetx = li_xls_row .
    sheets-sheetname =  'Sheet 2' .
    TRANSLATE sheets-sheetname TO UPPER CASE .
    APPEND  sheets TO it_sheets.
    CLEAR sheets.

    ls_excelmeta-documentproperties-author = sy-uname.

************Call the Transformation ID*********

    CALL TRANSFORMATION zexcel_xml_trans_demo
          SOURCE excelmeta = ls_excelmeta
                 sheets = it_sheets
          RESULT XML  lv_xml_string.

***********************************************
*  Func module to dispaly data in EXcel sheet
***********************************************

    IF NOT lv_xml_string IS INITIAL.
*    CALL FUNCTION 'SCMS_STRING_TO_XSTRING'
*      EXPORTING
*        text   = lv_ftext
*      IMPORTING
*        buffer = lv_xtext.
*
      CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
        EXPORTING
          buffer     = lv_xml_string
        TABLES
          binary_tab = lt_binary_content.

      CONCATENATE  'C:\Users\sankar.babu\Downloads\TestExcel' '\Material_Mass_Creation_' sy-datum '_' sy-uzeit '.csv'  INTO lv_filename.

      CONDENSE lv_filename .

      CALL FUNCTION 'GUI_DOWNLOAD'
        EXPORTING
          filename = lv_filename
          filetype = 'BIN'
        TABLES
          data_tab = lt_binary_content.
    ENDIF.


ENDFUNCTION.

I have enclosed my full code here and you can trace by <....> and </> which are all the indicators that takes you to blog logic and the changes which I made.

Please note, for Transformation, I just created with the same set of codes and no changes into that and the used the transformation in the above FM.

Please review and let me know. 

Ton of thanks to you for your response.  

[Sandra_Rossi]

Okay, I ran your program, and to simplify my answer, I show you what you are generating.

First of all, as you can see, the two sheets are generated.

You are generating a lot of content in cell A1 instead of placing data in all the cells you want (I don't understand why you insert lots of horizontal tabulations in the cell, maybe you incorrectly think that an horizontal tabulation is a kind of command which instructs to place the next text in the next cell).

Either you didn't understand the blog post and its code, or you didn't provide an example which can be used to understand your issue.

[sankar1781]

Hi Sandra_Rossi,

First of all, as you can see, the two sheets are generated : I was surprised to see this, because for me only one sheet is generating (using SAP 7.70 version)

You are generating a lot of content in cell A1 instead of placing data in all the cells you want : Yes, my requirement is to right the header fields in the first row but not in the same cell. Let me try to change it. 

Either you didn't understand the blog post and its code, or you didn't provide an example which can be used to understand your issue: As, I already stated here, my requirement or example to create excel with multiple sheets. Sheet 1 must have the Header Field names and the values in the 2nd row. Sheet 2 will have the other information. Please find the attached image here. 

Let me know if you need more information and sorry if I am still not clear. Thanks.

SHEET 1

SHEET 2

[Sandra_Rossi]

To write to several cells, you must use one APPEND to the internal table per cell.

    ls_xls_cell-cell_content = fieldname_1.
    APPEND ls_xls_cell TO ls_xls_row-cells. " column A

    ls_xls_cell-cell_content = fieldname_2.
    APPEND ls_xls_cell TO ls_xls_row-cells. " column B

    ls_xls_cell-cell_content = fieldname_3.
    APPEND ls_xls_cell TO ls_xls_row-cells. " column C

What you did is to write to only one cell (i.e. only one APPEND), with field names separated with a horizontal tabulation, which writes all the field names into the same cell, not in several cells.

Concerning your issue about only one sheet being generated, I cannot reproduce, my program (based on yours with all non-compiling stuff removed) generates two sheets successfully.