How ABAP2XLSX API can be used to post .XLSX FILE TO FTP SERVER

[former_member605939]

What is ABAP2XLSX API. HOW IT IS USEFULL TO POST .XLSX FILES FROM SAP TO FTP SERVER OR FROM FTP SERVER BACK TO SAP.

.XLS BEFORE 2003 VERSION IS WORKING FINE TO AND FROM FTP SERVER.

MY REQUIREMENT IS TO POST DATA WITH LATEST .XLS ( ABOVE 2007 ) OR .XLSX

THANKS

[Sandra_Rossi]

What is abap2xlsx? -> Read the documentation yourself, please.

FTP is a protocol for transferring any kind of file. I don't see why something should be different for .XLSX files. Can you explain?

.XLS above 2007 is the same as .XLS before 2007.

Please avoid writing text all upper case. Thanks.

[former_member605939]

sandra.rossi

Thank you for reply. (.XLS) files are posted to FTP using SAP program and when we open that file on FTP the file is opening and we can see data.

When we post (.XLSX) files are posted to FTP server but (.XLSX) are not opening , we cannot saw the data.And Excel shows the following ERROR

The Error is "Excel cannot open the file 'test.XLSX' because the file format of file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file'

[Sandra_Rossi]

The Excel message is extremely clear. It means that you did something wrong at a step between reading the Excel file and transferring it to the FTP server.

Or did you just use change the file name from "test.XLS" to "test.XLSX" without converting the inner format? If yes, Excel tells you that the file name indicates the inner format is XLSX, but the inner format is not XLSX. Excel does not tolerate this incoherence.

[former_member605939]

sandra.rossi

Thank you. Your second point seems to be valid that the "inner format is not XLSX."

How to check and verify this point and make the correction ?

[Sandra_Rossi]

Rename the file into "test.XLS" and open it. If it's not XLS format, there's a warning (Excel tolerates the incoherence for XLS). Having the right extension is just okay.

What other correction do you want to do? Why do you want to use XLSX? Why do you want to use abap2xlsx? What is the issue?

[former_member605939]

sandra.rossi

Thanks for information. Actually (.XLS) is transferring and opening and we can see the contents. But when we transfer (,XLSX) the file is moving to FTP but file not opening giving error. I want the (.XLSX) on FTP server because third party software supports the (.XLSX)

That software have to pick the file for further processing.

[Sandra_Rossi]

So that to transfer the file in XLSX format, either convert the inner format from XLS into XLSX (only Excel is able to do it: open XLS, save as XLSX, or probably some third-party tools). Or directly generate the file in XLSX format. How is this XLS file currently created?

[former_member605939]

sandra.rossi

How can i directly generate the file in XLSX. ( as you mentioned , i am not able to get your point please explain any simple example)

for (.XLS) i am doing as below:

prg.txt

[Sandra_Rossi]

I see that you don't even give the right file extension, it should be TXT because it contains tab-delimited values. XLS should be used only for Excel Binary File Format.

Whatever... abap2xlsx has many demo programs. The simplest one for your case, I think, is ZDEMO_EXCEL22.

[former_member605939]

sandra.rossi

Thank you.

As i am passing the file (test.XLS) and it is saving the excel file with

"Microsoft Excel 97-2003 Worksheet'. Is there way that will keep my extension as old (test.XLS) and file will be saved as "Microsoft Excel 2007 Worksheet" .

[Sandra_Rossi]

I don't understand why you say your file is in format "Excel 97-2003" just because you named it with extension .XLS. No, the ABAP code writes a file in format "tab-delimited values" and should have the extension .TXT (it may be better to use format "comma-separated values" because there's a dedicated extension .CSV).

I don't understand why you want to make your file incoherent, with format "Excel 2007" but with extension .XLS instead of .XLSX.

You may name it with the extension you wish, but you will have to assume all the consequences (warning or error popup when you open it in Excel).

[former_member605939]

sandra.rossi

Thankful for correcting and giving the right path .

As i asked about "Excel 97-2003" because the third party software supporting "Excel 2007 worksheet" and not supporting "Excel 97-2003".

According to you either go for "it may be better to use format "comma-separated values" because there's a dedicated extension .CSV)." for this i need to change all my objects ( about 15 objects) to new format .Will do.

And second option is to directly generate the file in .XLSX format.Can you let me know little bit more about second point (any example).

[Sandra_Rossi]

I just said "it may be better to use format "comma-separated values"... .CSV", but I don't know if it's a good idea, you should first ask the opinion of your co-workers/company.

abap2xlsx has many demo programs. The simplest one for your case, I think, is ZDEMO_EXCEL22.

For information, the shortest abap2xlsx code to generate a XLSX file with one cell "Hello World" (into XSTRING variable that you can send to any data medium you want) is:

DATA(lo_excel) = NEW zcl_excel( ).
DATA(lo_worksheet) = lo_excel->get_active_worksheet( ).
lo_worksheet->set_cell( ip_column = 1 ip_row = 1 ip_value = 'Hello World' ).
DATA(lo_writer) = CAST zif_excel_writer( NEW zcl_excel_writer_2007( ) ).
DATA(xstring) = lo_writer->write_file( lo_excel ).

[former_member605939]

sandra.rossi

Thanks for all valuable knowledge. The above code is in classes concept and using inline declaration. I am not good enough on classes concept and moreover working on ecc6.5 the platform is old. So will try for .CSV.

[Sandra_Rossi]

Your name is "ABAP LEARNER", so do it, learn 🙂

Here is the equivalent ABAP code in syntax valid before 7.40 (I didn't try it):

DATA: lo_excel     TYPE REF TO zcl_excel,
      lo_worksheet TYPE REF TO zcl_excel_worksheet,
      lo_writer    TYPE REF TO zif_excel_writer,
      xstring      TYPE xstring.

CREATE OBJECT lo_excel.
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_cell( ip_column = 1 ip_row = 1 ip_value = 'Hello World' ).
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
xstring = lo_writer->write_file( lo_excel ).

[former_member605939]

sandra.rossi

When transferring the the ABAP code to FTP , i am facing the problem of "The Error is "Excel cannot open the file 'test.xls' because the file format of file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file'"

And as the message indicates that "The file is not compatible with the Excel-version"

I am attaching the code and not able to find the point where logic is not working for extension . you can see the code, the point

WA_EXCEL-WRK_DELIM1 = CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB.

is for columns separation for excel

and the point where assigning the ,xls .ie,

WRK_FILE = '\Main\TEST\Material_Stock.xls'.

i want the point where making mistake and want the output as .xls and on ftp file will open without showing error screentest.txt

[Sandra_Rossi]

It's not an error, it's a warning.

Back to my answer 5 days ago, because the situation didn't change:

I see that you don't even give the right file extension, it should be TXT 
because it contains tab-delimited values. XLS should be used only for 
Excel Binary File Format.

(Excel Binary File Format = format named "Excel 5.0/95" when you do "save as" or "save a copy" in Excel; I also mentioned "Excel 97/2003" above, it's the same)

If you don't want this message, tell your client change your program to generate a file with extension .xlsx AND data in true XLSX format via abap2xlsx (Excel 2007, also known as Excel Office Open XML).

It was my last message. Feel free to open a new question to clarify your question with all information as you understand the situation now. Thank you.

[Sandra_Rossi]

I post your code so that people can have a look at it directly:

REPORT zmm_material_stock_sap_to_ftp.
TYPES:BEGIN OF ty_mard, "MATERIAL STOCK TABLE
        werks TYPE mard-werks, "Plant
        matnr TYPE mard-matnr, "MATERIAL CODE
        labst TYPE mard-labst, "Valuated Unrestricted-Use Stock
        lgort TYPE mard-lgort, "Storage Location
      END OF ty_mard,
      BEGIN OF ty_fin,
        werks     TYPE c LENGTH 10, "MARD-WERKS, "Plant
        matnr     TYPE c LENGTH 18, "MARD-MATNR, "MATERIAL CODE
        labst(20) TYPE c, "N, "Valuated Unrestricted-Use Stock MARD-LABST,
        lgort     TYPE c LENGTH 10, "MARD-LGORT, "Storage Location
      END OF ty_fin,
      BEGIN OF excel_st,
        werks      TYPE c LENGTH 10, "MARD-WERKS, "Plant
        wrk_delim1 TYPE c, "X,
        matnr      TYPE c LENGTH 18, "MARD-MATNR, "MATERIAL CODE
        wrk_delim2 TYPE c, "N,
        labst(20)  TYPE c, "N, "Valuated Unrestricted-Use Stock MARD-LABST,
        wrk_delim3 TYPE c , "X
        lgort      TYPE c LENGTH 10, "MARD-LGORT, "Storage Location,
      END OF excel_st.
DATA:it_mard  TYPE STANDARD TABLE OF ty_mard,
     it_fin   TYPE STANDARD TABLE OF ty_fin,
     it_excel TYPE STANDARD TABLE OF excel_st.
DATA:wa_fin   TYPE ty_fin,
     wa_mard  TYPE ty_mard,
     wa_excel TYPE excel_st.
TABLES:mard.

SELECTION-SCREEN:BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS:p_date LIKE sy-datum DEFAULT sy-datum.
SELECTION-SCREEN:END OF BLOCK b1.

INITIALIZATION.
  CLEAR: wa_mard,
  wa_excel,
  wa_fin.
  REFRESH:it_mard,
  it_excel,
  it_fin.

START-OF-SELECTION.
  PERFORM final_table_header.
  PERFORM get_mard_table_details.
  PERFORM excel_format_template.
  PERFORM sap_stock_excel_file_to_ftp.

FORM get_mard_table_details .
  SELECT mard~werks mard~matnr mard~labst mard~lgort INTO TABLE it_mard FROM mard
  INNER JOIN mara ON ( mara~matnr = mard~matnr )
  WHERE mara~mtart = 'ZFRT'
  AND werks IN ('5000',
  '6000')
  AND lgort IN ('1001','1101').

  IF sy-subrc = 0.
    SORT it_mard BY matnr werks lgort.
    LOOP AT it_mard INTO wa_mard.
      CLEAR:wa_fin.
      wa_fin-werks = wa_mard-werks.
      wa_fin-matnr = wa_mard-matnr.
      wa_fin-labst = wa_mard-labst.
      wa_fin-lgort = wa_mard-lgort.
      APPEND wa_fin TO it_fin.
      CLEAR:wa_fin,wa_mard.
    ENDLOOP.
  ENDIF.
ENDFORM. " GET_MARD_TABLE_DETAILS

FORM excel_format_template .
  LOOP AT it_fin INTO wa_fin.
    MOVE-CORRESPONDING wa_fin TO wa_excel.
    wa_excel-wrk_delim1 = cl_abap_char_utilities=>horizontal_tab. " or |\t|
    wa_excel-wrk_delim2 = cl_abap_char_utilities=>horizontal_tab. " or |\t|
    wa_excel-wrk_delim3 = cl_abap_char_utilities=>horizontal_tab. " or |\t|
    APPEND wa_excel TO it_excel.
    CLEAR:wa_mard,wa_excel.
  ENDLOOP.
ENDFORM. " EXCEL_FORMAT_TEMPLATE

FORM sap_stock_excel_file_to_ftp .
  DATA:l_user(30) TYPE c VALUE 'test123', "user name of ftp server
       l_pwd(30)  TYPE c VALUE '1234567', "password of ftp server
       l_host(64) TYPE c VALUE 'XXX.XX.XXX.X', "ip address of FTP server
       l_dest     LIKE rfcdes-rfcdest VALUE 'SAPFTPA'. "Background RFC destination
  DATA:w_hdl  TYPE i,
       c_key  TYPE i VALUE 26101957,
       l_slen TYPE i.
  DATA: wrk_file TYPE char200.
  CLEAR:wrk_file.
  wrk_file = '\Main\TEST\Material_Stock.xls'.
  SET EXTENDED CHECK OFF.
  l_slen = strlen( l_pwd ).
  CALL FUNCTION 'HTTP_SCRAMBLE'
    EXPORTING
      source      = l_pwd
      sourcelen   = l_slen
      key         = c_key
    IMPORTING
      destination = l_pwd.
* To Connect to the Server using FTP
  CALL FUNCTION 'FTP_CONNECT'
    EXPORTING
      user            = l_user
      password        = l_pwd
      host            = l_host
      rfc_destination = l_dest
    IMPORTING
      handle          = w_hdl
    EXCEPTIONS
      OTHERS          = 1.
  CALL FUNCTION 'FTP_R3_TO_SERVER'
    EXPORTING
      handle         = w_hdl
      fname          = wrk_file
      character_mode = 'X'
    TABLES
      text           = it_excel
    EXCEPTIONS
      tcpip_error    = 1
      command_error  = 2
      data_error     = 3
      OTHERS         = 4.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
    RAISING invalid_output_file.
  ENDIF.
  CALL FUNCTION 'FTP_DISCONNECT'
    EXPORTING
      handle = w_hdl.
  CALL FUNCTION 'RFC_CONNECTION_CLOSE'
    EXPORTING
      destination = l_dest
    EXCEPTIONS
      OTHERS      = 1.
ENDFORM. " SAP_STOCK_EXCEL_FILE_TO_FTP

FORM final_table_header .
  wa_fin-werks = 'Plant'.
  wa_fin-matnr = 'Material'.
  wa_fin-labst = 'Stock Qty. '.
  wa_fin-lgort = 'Location'.
  APPEND wa_fin TO it_fin.
  CLEAR:wa_fin.
ENDFORM. " FINAL_TABLE_HEADER

[former_member605939]

sandra.rossi

For all valuable answers suggestions and guidelines.

Thanks

[Sandra_Rossi]

Please use the COMMENT button for comments, questions, adding details, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area.

A valid answer would be: "abap2xlsx can be used to generate an XLSX file, for instance:

DATA: lo_excel     TYPE REF TO zcl_excel,
      lo_worksheet TYPE REF TO zcl_excel_worksheet,
      lo_writer    TYPE REF TO zif_excel_writer,
      xstring      TYPE xstring.

CREATE OBJECT lo_excel.
lo_worksheet = lo_excel->get_active_worksheet( ).
lo_worksheet->set_cell( ip_column = 1 ip_row = 1 ip_value = 'Hello World' ).
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
xstring = lo_writer->write_file( lo_excel ).

...

" etc. code for converting XSTRING to BLOB/BINARY table (LT_BINARY) and transferring it to FTP

...

  CALL FUNCTION 'FTP_R3_TO_SERVER'
    EXPORTING
      handle         = w_hdl
      fname          = wrk_file
      character_mode = ABAP_FALSE
      blob_length    = length       " the exact number of bytes in LT_BINARY (xstrlen( xstring ))
    TABLES
      blob           = lt_binary
    EXCEPTIONS
      OTHERS         = 4.

...