04-23-2020 10:36 AM
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
04-23-2020 1:41 PM
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.
04-23-2020 2:18 PM
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'
04-23-2020 2:23 PM
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.
04-23-2020 2:43 PM
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 ?
04-23-2020 2:50 PM
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?
04-23-2020 3:18 PM
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.
04-23-2020 3:23 PM
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?
04-23-2020 3:49 PM
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:
04-23-2020 5:14 PM
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.
04-25-2020 6:36 AM
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" .
04-25-2020 6:50 AM
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).
04-25-2020 7:11 AM
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).
04-25-2020 7:15 AM
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 ).
04-27-2020 6:32 AM
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.
04-27-2020 7:00 AM
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 ).
04-29-2020 7:43 AM
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
04-29-2020 8:53 AM
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.
04-29-2020 9:06 AM
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
04-29-2020 10:28 AM
sandra.rossi
For all valuable answers suggestions and guidelines.
Thanks
05-01-2020 6:51 AM
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.
...