Background Job - Export and Mail

[Former Member]

Hi,

We've got several reports ( ALV ).

Got functions to export an ALV to a binary MSExcel format.

Got function to mail the excel file to recipient.

All of this is working from frontend.

Wanted to make a job so that 'registered' users automatically receive their report by mail in excel format.

Here is the problem, files aren't created , mail aren't sent.

TO create my excel binary I use an extension of TRUX function group ( SAP_CONVERT_TO_EXCEL )

DO I need to install MSOffice on the main sap server ?

To send mails I use CL_BCS, is there any incompatibility with a background job ?

[former_member588853]

Hi,

Please check the spool f back groud job sm36..

There might b e problem , be clear with the issues/error..

You can debug the code and check the reason for not sending mails etc.

regards,

Nazeer

[Clemenss]

Hi Edouard,

we are on ECC600 SAP_BASIS 700.

Function module SAP_CONVERT_TO_EXCEL does not exist in our System.

I have never seen an ABAP program creating an excel file without desktop office integration: In Foreground, the excel application is called via SAPGUI on your desktop (client). The server transfers the data via OLE desktop office integration.

In background you have no excel and so can not create an excel file.

You can create a tab-delimited text file. This can be opened by excel appliction.

I think it could be possible to create an MHTML file for excel that behaves like a 'real' excel file with more than one sheet, formats, colors and so on. This should be possible without frontend excel support but I donÄt know any function that provides the functionality.

Regards,

Clemens

[Former Member]

The exact name is SAP_CONVERT_TO_XLS_FORMAT ... SM36 looks good

thought it was OLE link ... and seen that if MSExcel is not on the frontend computer it does not work.

If I install a MSOffice on the SAP server ( sounds hugly said like that ) do I have any chance to get my xls file ?

but it does not explain why mail are not sent.

Will investigate on other file format and try to be persuasive with my users.

[Clemenss]

Hi Edouard,

even if your SAP runs on Windows Server, I have severe doubts that its worth the try - as far as I know, Office programs are not intended to run without a screen.

Today I had a quick look on MHTML download, SE16 display, download ->spreadsheet->mhtml format: Before excel is launched, some methods of classes CL_SALV_BS_TT_UTIL, CL_SALV_FORM,CL_SALV_BS_DATA_TABLE_ACTIONS are used to create an xml file from internal table, then FUNCTION 'SCMS_XSTRING_TO_BINARY' converts the xml. then it is downloaded by cl_gui_frontend_services=>gui_download.

As soon as I have time, I will try to

- create SAL object for internal table

- see that I get the xml

- convert it to binary

- write it to server dataset

- open with excel

I you are successful with that sooner, please let me know...

Regards,

Clemens

[Clemenss]

Hi Edouard,

just as I guessed: Look at the code of function SAP_CONVERT_TO_XLS_FORMAT:

perform create_spreadshee
...
perform get_spreadsheet_interface
...
form get_spreadsheet_interface
...
* don't do anything in batch, because there is no GUI...
  check sy-batch is initial.

I

Regards,

Clemens

[Former Member]

Thanks for the help.

finally reached this wiki page : http://wiki.sdn.sap.com/wiki/display/ABAP/Workingwithfiles#Workingwithfiles-Excelfiletypes

guess I will create a xml and give it with a .xls extenssion to my users.

tried this in a simple way before the week end and returns are good enough.

[Clemenss]

Hi Edouard,

I tried this: Create SALV object from internal table, download to presentation server. For application server ist should be even easier because there is no need to convert the xml xtring to table, just TRANSFER as is, will try next.

DATA:
  lr_data       TYPE REF TO data,
  lr_salv_table TYPE REF TO cl_salv_table,
  lv_xstring    TYPE xstring,
  lv_xlen       TYPE int4.
FIELD-SYMBOLS:
  <tab> TYPE table,
  <xtab> TYPE table,
  <any> TYPE any.
PARAMETERS:
  p_tab TYPE tabname DEFAULT 'T001',
  p_max TYPE sy-dbcnt DEFAULT 100.
CLASS lcl DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS:
      get_salv
        IMPORTING ir_table TYPE REF TO data
        RETURNING value(rr_salv) TYPE  REF TO cl_salv_table.
ENDCLASS.                    "lcl DEFINITION
CREATE DATA lr_data TYPE TABLE OF (p_tab).
ASSIGN lr_data->* TO <tab>.
SELECT *
  INTO CORRESPONDING FIELDS OF TABLE <tab>
  UP TO p_max ROWS
  FROM (p_tab).
lr_salv_table = lcl=>get_salv( lr_data ).
lv_xstring = lr_salv_table->to_xml( '04' ) .
CREATE DATA lr_data TYPE  x LENGTH 100.
ASSIGN lr_data->* to <any>.
create data lr_data like table of <any>.
ASSIGN lr_data->* to <xtab>.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
  EXPORTING
    buffer        = lv_xstring
  IMPORTING
    output_length = lv_xlen
  TABLES
    binary_tab    = <xtab>.
CALL METHOD cl_gui_frontend_services=>gui_download
  EXPORTING
    bin_filesize = lv_xlen
    filetype     = 'BIN'
    filename     = 'C:\Documents and Settings\*******\Desktop\x.xls'
  CHANGING
    data_tab     = <tab>
  EXCEPTIONS
    OTHERS       = 4.
CLASS lcl IMPLEMENTATION.
  METHOD get_salv.
    FIELD-SYMBOLS:
      <tab> TYPE table.
    ASSIGN ir_table->* TO <tab>.
    cl_salv_table=>factory(
       IMPORTING
         r_salv_table   = rr_salv
      CHANGING
        t_table        = <tab>   ).
  ENDMETHOD.                    "lcl
ENDCLASS.                    "lcl IMPLEMENTATION 

If I have time will write a blog.

Regards

Clemens

[Former Member]

Hi ,

first of all thanks. It was helpfull.

The mailing trouble was because of my alv structure (splitter and other things ...).

When launched in background it was stopped early.

I did a good use of sy-batch ...

But I've got another trouble, guess it is due to my leak of knowledge about alv.

looks like my report stops immediatly after my grid->set table for first display

how could I just get the table and layout without stopping it?

[Clemenss]

Hi Edouard,

probably it is time to switch to SALV

cl_salv_table=>factory(
       IMPORTING
         r_salv_table   = rr_salv
      CHANGING
        t_table        = <tab>   ).

The salv factory will not display at all, if the display method is not called. I did not play with layouts and I don't know if the layout is applied before calling lr_salv_table->to_xml( '04' ) . If so, we could define the layout online, set it as default layout and get the results in background. Interesting stuff - if I only had time...

Regards,

Clemens

[Former Member]

I had in thought something like that. (use of salv instead of alv).

I'll investigate in 'the cost' of changing alv into salv into existing reports.

I'll first try on a dummy report.

The online definition is exactly what we need / were thinking about.

Currently,

User get reports (alv) , they can set their favourite layout

In the future,

they want to be able to subscribe for a recurent emailing of 'the report they want' with 'their favourite corresponding layout'

[Former Member]

Well seems to bo nice with salv

can do whatever I want ...

guess I 'll be able to manage the layout (already ok in interactive mode)

but can't get any export to xml function.

tried :

gr_functions->set_export_xml( abap_true ).

for testing in interactie mode -> activztion error no function called : set_export_xml

tried your piece of code to_xml does not exist.

running ECC6.0 here, is there anthing to activate xml export ? is our CL_SALV version too old ?

any tip ?

[Clemenss]

Hi Edouard,

happy you are happy with SALV.

We are on ECC600, class CL_SALV_TABLE has a method TO_XML returning the XML xstring. The version is dated 28.01.2008, support packages are

SAP_BASIS 700 0013 SAPKB70013

SAP_ABA 700 0013 SAPKA70013

If you are on a lower level you may search for something useful: I entered CL_SALV* for class and xml for mehtod in repository information system and got a lot of hits.

Regards,

Clemens

[Former Member]

Seems like it is a SP12 feature

[Clemenss]

Too bad!

I think you will not have this standard export function in SALV for export in MHTML format - this starts excel directly online but uses the same methods to create the xml.

If you have the export MHTML via toolbar, you may use debugger to see how to get the string.

Regards,

Clemens.