Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Export to Excel: XML transformation

Former Member
0 Kudos
798

Hi,

I have to export the result set on the Grid in an Excel file( fancy report..:) ). The result set has to be modified based on the other tables before downloading. The resulting file has to have the various formatting like color, bold, font size, etc..

As OLE is slow so I am thinking to go for the XML transformation.

Issue:

1. The table had around 100 columns, so is there any way to dynamically read the number of columns being displayed? (The user can change the layout).

Kindly suggest.

Regards

s@k

13 REPLIES 13

Sandra_Rossi
Active Contributor
0 Kudos
149

> As OLE is slow so I am thinking to go for the XML transformation.

Note that you may also use Desktop Office Integration (DOI) as it contains mass update methods and use Data Provider to communicate quicker than "CREATE OBJECT ... OF" abap statement. And you may also create and call VBA macros to do the formatting (colors, ...) if you need.

> 1. The table had around 100 columns, so is there any way to dynamically read the number of columns being displayed? (The user can change the layout).

Yes you can read the layout. Which ALV technology do you use? (REUSE_ALV_DISPLAY, CL_GUI_ALV_GRID, CL_SALV...)

0 Kudos
149

Dear Sandra,

Thanks for your response..:)

I will try to give a clear picture of the requirement..

I have to add a button on the screen of the standard transaction MMCL, the program is MILL_CLMMBE. When the user presses this button, the formatted excel file should be generated. The FM to display the result set is 'REUSE_ALV_HIERSEQ_LIST_DISPLAY'.

As suggested by you, I will try with the DOI.

How do I read the field catalog so that I can use it and create the Excel file only with those columns?

Kindly provide your opinion.

Regards

s@k

0 Kudos
149

In the callback routine, call function module REUSE_ALV_LIST_LAYOUT_INFO_GET. It will return the layout (columns, sort, filter, other options), as changed by the user (or the initial layout if he didn't change it). Note that you can't call it outside the callback routine.

0 Kudos
149

Dear Sandra,

I am trying with the simple XML transformation and I am able to generate the XML. Now when I try to download using the 'cl_gui_frontend_services=>gui_download', it gives a short dump. Following is the code snippet. The table 'lt_xml' is a table of type string and holds the XML data.


        DATA:
       lt_xml      TYPE      srmttstring,

      cl_gui_frontend_services=>gui_download(
        EXPORTING
          filename                  =  lv_loc_dir
          filetype                  = 'BIN'
          confirm_overwrite         = 'X'
        CHANGING
          data_tab                  = lt_xml
        EXCEPTIONS
          file_write_error          = 1
          ... .......
          .... .......

I have tried with the other file types but in vain.

Can you please suggest?

Regards

s@k

0 Kudos
149

It must be a table of character (type c) or table of x, not string (nor xstring). BTW don't forget to provide the exception name, we can't guess (usually).

Edited by: Sandra Rossi on Mar 31, 2010 2:10 PM (table of x added)

OttoGold
Active Contributor
0 Kudos
149

What about this option: /people/otto.gold/blog/2010/02/11/happy-reporting-with-excel-ii

Regards Otto

Former Member
0 Kudos
149

Dear Sandra & Otto,

Thanks a lot for your responses..:)

@ Otto

Your blog was really helpful..:)

Regards

s@k

Former Member
0 Kudos
149

Dear Sandra & Otto,

Sorry to bother you again...

As I mentioned above, I was able to generate the XML file. The type for the cells were 'String'.

The client has changed the requirement and wants to display the 'Date' as well in the table.

To display the Numbers and the Dates in the table, I am using the types as: Number and DateTime. But now while generating the XML file, there occurs an error in the Table section. I tried to debug but I cannot set any break point in the transformation.

Following are the issues which I am currently facing:

1. Displaying the formats: Number & Date.

Do I have to create a separate Style for these? If so, how do I pass the style ID because I am already using a Style. How do I declare it in the transformation. Can I hard code the style ID.

2. How do I debug the Simple Transformation? I have googled and found materials only for XSLT transformation debugging.

Kindly help..

Regards

s@k

Edited by: siemens.a.k on Apr 2, 2010 10:43 AM

Edited by: siemens.a.k on Apr 2, 2010 10:59 AM

0 Kudos
149

BTW you didn't mention whether REUSE_ALV_LIST_LAYOUT_INFO_GET was useful or not?

> 1. Displaying the formats: Number & Date. Do I have to create a separate Style for these? If so, how do I pass the style ID because I am already using a Style. How do I declare it in the transformation. Can I hard code the style ID.

I don't understand why it would solve the error. What exact error message do you have? Could you paste code? (please respect 2500 characters maximum)

> 2. How do I debug the Simple Transformation? I have googled and found materials only for XSLT transformation debugging.

I think you can't, it's one of the differences between the 2. Convert your ST into XSLT (manually ) then you'll be able to debug... (did someone create an ST to XSLT converter?)

0 Kudos
149

Dear Sandra,

Thanks a lot..:)

1. REUSE_ALV_LIST_LAYOUT_INFO_GET

As mentioned the standard program is MILL_CLMMBE. The FM REUSE_ALV_LIST_LAYOUT_INFO_GET is not used here. I tried a different approach to gain access to the field catalog..:)


DATA:
                 lv_prog(50)        TYPE      c   VALUE '(SAPLKKBL)GT_STACK-IT_FIELDCAT'.
FIELD-SYMBOLS:
     <fs_fcat>          TYPE              ANY.

  ASSIGN (lv_prog) TO <fs_fcat>.
  IF sy-subrc = 0.
    lt_fieldcat[] = <fs_fcat>.
  ELSE.
   " Process the error
  ENDIF.

2. Error message

If I use the cell type as 'String' for all the cell content, it works perfectly fine. The file is generated and I can see the output in excel..:) But when I try to change the cell type to 'Number' for the cells having the content as Numbers, It shows error while opening the file in Excel - 'Problem came up in the following area during load'.

While preparing the data to be passed to the transformation, depending on the internal type of the field, I am assigning the types as 'String' or 'Number'.


          CASE <fs_fieldcat_tmp>-inttype.
            WHEN 'C'.
              wa_xls_cell-type = 'String'.
              wa_xls_cell-cell_content = <fs_dfield>.
            WHEN 'P' OR 'N'.
              wa_xls_cell-type = 'Number'.
              wa_xls_cell-cell_content = <fs_dfield>.
            WHEN 'D'.
              wa_xls_cell-type = 'DateTime'.
              WRITE <fs_dfield> TO lv_date.
              wa_xls_cell-cell_content = lv_date.
          ENDCASE.

Code for transformation in the following post....:)

0 Kudos
149

Code for Transformation


<?sap.transform simple?>
<tt:transform xmlns:tt="http://www.sap.com/transformation-templates">
  <tt:root name="excelMeta"/>
  <tt:root name="excelDataTab"/>
  <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"/>
              <Font>
                <tt:attribute name="x:Family" value-ref="$Styles.Font.Family"/>
              </Font>
              <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
            </Style>
          </tt:loop>
        </Styles>
      </tt:serialize>

      <Worksheet>
        <tt:attribute name="ss:Name" value-ref="excelMeta.Worksheet.Name"/>
        <Table>
          <tt:loop name="line" ref=".excelDataTab">
            <Row>
              <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>
    </Workbook>
  </tt:template>
</tt:transform>

0 Kudos
149

I don't have access to the program you mention, but your code seems to do exactly the same thing as REUSE_ALV_LIST_LAYOUT_INFO_GET. I don't understand why it wouldn't work.

About fields, you should respect Excel formats:

Time 03:00:00 : <Data ss:Type="DateTime">1899-12-31T03:00:00.000</Data>

Date 12/31/2010 : <Data ss:Type="DateTime">2010-12-31T00:00:00.000</Data>

Number -1000.5 : <Data ss:Type="Number">-1000.5</Data>

BTW could you edit your last post and insert a newline in the workbook tag so that to make the page more narrow.

0 Kudos
149

Dear Sandra,

I located the cause for the error...:)


          CASE <fs_fieldcat_tmp>-inttype.
            WHEN 'P' OR 'N'.
              wa_xls_cell-type = 'Number'. 
              CONDENSE wa_xls_cell-cell_content NO-GAPS.   <--- Added this statement
            WHEN 'D'.
              WRITE <fs_dfield> TO lv_date.
              wa_xls_cell-cell_content = lv_date.
            WHEN OTHERS.
              " Do nothing
          ENDCASE.

I added the code at the position specified above and now it is working perfectly fine...:)

Thanks a lot for your time and effort...:)

Regards

s@k