Hi all,
I would like to share with you a some code development .
There is a constant request to generate Excel report from SAP .
As far as I know there is no built in support to do that from ABAP.
There is also the requirement to run the whole show in back ground job .
The regular “solution” is create html files, CSV files etc.
There is ABAP project http://wiki.scn.sap.com/wiki/display/ABAP/abap2xlsx but not every
organization would like to go this way.
Since I use Java as a hobby I thought I will try and use java to do the job .
The code supplied here is supplied as is. Try it and see if it is good for you.
Steps
- Create XML using ABAP .
The XML will contain meta data and instructions to the java program. - Write the XML to a file .
- Call Java as external command .
- The Java program will parse the XML file and generate the Excel file.
Open source projects used
Apache POI – http://poi.apache.org/ – the Java API for Microsoft Documents
This is main work horse… it will be used to generate the Excel files .
At the time of writing I did not utilize the full potential of this project. It is full of goodies that
are worth exploring…(I plan to try and use the “Formula Support” ).
Apache Commons CLI – http://commons.apache.org/proper/commons-cli
parsing command line options .
Apache Commons Lang – http://commons.apache.org/proper/commons-lang/
StopWatch.
Required jars from the projects (Already included in XmlFileToExcel.jar )
commons-cli-1.2.jar
commons-lang3-3.1.jar
dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar
The environment
Java Editor – Eclipse – http://www.eclipse.org/
The Java code is located here: https://drive.google.com/folderview?id=0B6Cb7sgVnODWNV9DS29kQXdPblE&usp=sharing
The reason for putting the code here is because of the file types involved.
(If someone can point me to a place within scn.sap.com I will be grateful…)
XmlFileToExcel.zip – The whole set of source code is in a this zip file this way the directory structure of the Java project is preserved.
XmlFileToExcel.jar – This is the “.exe” equivalent of Java .
This file contain all the required jars from the projects .
The jar was created by eclipse using the “Runnable JAR File Exporter”
Class main.Main is the start class for this program.
This file is actually a zip file with extension of “jar”.
Y_R_EITAN_TEST_40_02.xml – Sample XML created from SAP .
Y_R_EITAN_TEST_40_02.xlsx – Sample Excel generated by the java program .
Java Setup
- Create in some shared folder accessible from sap the following:
- A folder with the name “jre” this will contain the “Java Runtime Environment”.
- – Install java jre on your PC .
http://www.oracle.com/technetwork/java/javase/downloads/jre7-downloads-1880261.html
– Note the folder where you install the jre (Usually C:\Program Files\Java\jre7 )
– Copy folder “jre7” to folder “jre” . - Create a batch file named Y_JAVA_1.bat
The content of the file:(note the “\bin”)path \\<path to jre>\bin
java.exe -jar %1 %2 %3 %4 %5 %6 %7 %8 %9 - Download XmlFileToExcel.jar and put it next to Y_JAVA_1.bat .
The folder will look like this:
SAP setup (Simple….)
program:( Y_R_EITAN_TEST_40_02 source included)
Upload the source to SAP and activate .
The program use table sbook as input (On our site it contain 100000 records)
The program read the selected records (FORM get_data_1) .
The program generate and write XML file (FORM write_1_to_xml) .
The program Execute an External Command and call java (FORM write_2_to_excel) .
External command
Use Transaction SM69 and add “Y_JAVA_1” as external command.
Y_JAVA_1 will call the batch file Y_JAVA_1.bat with the required parameters.
When you run the program you will be prompted for “My folder” this also needs to be a shared folder accessible from SAP .
All the paths needs to to be in \\host\directoryname structure (Universal Naming Convention) .
Thats all for now. have fun….
Hi,
Good article, thanks.
The drive link seems seems dead though.
Hi,
Sorry about that.
I am retired now .
For the java code you can find a lot of samples usage at poi site.
Program Y_R_EITAN_TEST_40_02:
REPORT y_r_eitan_test_40_02.
TYPE-POOLS: abap .
TYPE-POOLS: rsds .
DATA: st_sbook TYPE sbook .
CONSTANTS: c_java_call TYPE sxpgcolist-name VALUE 'Y_JAVA_1' .
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK block02 WITH FRAME.
SELECT-OPTIONS: s_carrid FOR st_sbook-carrid .
SELECT-OPTIONS: s_connid FOR st_sbook-connid .
SELECT-OPTIONS: s_fldate FOR st_sbook-fldate .
SELECT-OPTIONS: s_bookid FOR st_sbook-bookid .
SELECTION-SCREEN SKIP .
PARAMETERS: p_rows TYPE numrows DEFAULT 25000 .
SELECTION-SCREEN END OF BLOCK block02.
SELECTION-SCREEN BEGIN OF BLOCK block04 WITH FRAME.
SELECTION-SCREEN COMMENT /32(79) fldr_c_1 .
SELECTION-SCREEN COMMENT /32(79) fldr_c_2 .
PARAMETERS: p_file_f TYPE localfile OBLIGATORY .
SELECTION-SCREEN SKIP .
PARAMETERS: p_file_s TYPE localfile MODIF ID mni .
PARAMETERS: p_file_t TYPE localfile MODIF ID mni .
SELECTION-SCREEN END OF BLOCK block04.
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
INITIALIZATION.
PERFORM at_initialization .
AT SELECTION-SCREEN OUTPUT .
PERFORM at_selection_screen_output .
AT SELECTION-SCREEN .
PERFORM at_selection_screen_input .
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
FORM at_initialization .
fldr_c_1 = 'The folder needs to be a shared folder (e.g. \\server\my_folder\). ' .
fldr_c_2 = 'It needs to be accessible from SAP application server. ' .
DATA: it_seltexts TYPE TABLE OF rsseltexts .
DATA: st_seltexts LIKE LINE OF it_seltexts .
st_seltexts-name = 'P_FILE_F' .
st_seltexts-kind = 'P' .
st_seltexts-text = 'My folder ' .
APPEND st_seltexts TO it_seltexts .
st_seltexts-name = 'P_FILE_S' .
st_seltexts-kind = 'P' .
st_seltexts-text = 'XML file name ' .
APPEND st_seltexts TO it_seltexts .
st_seltexts-name = 'P_FILE_T' .
st_seltexts-kind = 'P' .
st_seltexts-text = 'Excel file name ' .
APPEND st_seltexts TO it_seltexts .
CALL FUNCTION 'SELECTION_TEXTS_MODIFY'
EXPORTING
program = sy-cprog
TABLES
seltexts = it_seltexts
EXCEPTIONS
program_not_found = 1
program_cannot_be_generated = 2
OTHERS = 3.
IF sy-subrc NE 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
PERFORM set_file_names .
ENDFORM. "at_initialization
*----------------------------------------------------------------------*
FORM at_selection_screen_output .
LOOP AT SCREEN .
CASE screen-group1 .
WHEN 'MNI' ."No input
screen-input = '0' .
ENDCASE .
MODIFY SCREEN .
ENDLOOP.
ENDFORM . "at_selection_screen_output
*----------------------------------------------------------------------*
FORM set_file_names .
CONCATENATE sy-cprog '.xml' INTO p_file_s .
CONCATENATE sy-cprog '.xlsx' INTO p_file_t .
ENDFORM. "set_file_names
*----------------------------------------------------------------------*
FORM at_selection_screen_input .
PERFORM set_file_names .
CHECK sy-ucomm EQ 'ONLI' OR sy-ucomm EQ 'PRIN' .
DATA: it_sbook TYPE ty_bookings .
PERFORM get_data_1
CHANGING
it_sbook .
IF it_sbook[] IS INITIAL .
MESSAGE e668(vl).
ENDIF .
DATA: st_bal_s_log TYPE bal_s_log .
st_bal_s_log-extnumber = sy-title .
st_bal_s_log-aluser = sy-uname.
st_bal_s_log-alprog = sy-repid.
CALL FUNCTION 'BAL_LOG_CREATE'
EXPORTING
i_s_log = st_bal_s_log
EXCEPTIONS
OTHERS = 1.
IF sy-subrc NE 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
PERFORM write_1_to_xml
USING
it_sbook .
PERFORM write_2_to_excel .
DATA: st_display_profile TYPE bal_s_prof.
* get a prepared profile
CALL FUNCTION 'BAL_DSP_PROFILE_POPUP_GET'
IMPORTING
e_s_display_profile = st_display_profile
EXCEPTIONS
OTHERS = 1.
st_display_profile-use_grid = abap_true .
CALL FUNCTION 'BAL_DSP_LOG_DISPLAY'
EXPORTING
i_s_display_profile = st_display_profile
i_amodal = abap_true
EXCEPTIONS
OTHERS = 1.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE 'S' NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM . "at_selection_screen_input
*----------------------------------------------------------------------*
FORM get_random_color
CHANGING
color TYPE string .
STATICS: it_color TYPE TABLE OF string .
STATICS: lines TYPE i .
STATICS: ob_abap_random_1 TYPE REF TO cl_abap_random_int .
IF it_color[] IS INITIAL .
APPEND 'AQUA ' TO it_color .
APPEND 'BLACK ' TO it_color .
APPEND 'BLUE ' TO it_color .
APPEND 'BLUE_GREY ' TO it_color .
APPEND 'BRIGHT_GREEN ' TO it_color .
APPEND 'BROWN ' TO it_color .
APPEND 'CORAL ' TO it_color .
APPEND 'CORNFLOWER_BLUE ' TO it_color .
APPEND 'DARK_BLUE ' TO it_color .
APPEND 'DARK_GREEN ' TO it_color .
APPEND 'DARK_RED ' TO it_color .
APPEND 'DARK_TEAL ' TO it_color .
APPEND 'DARK_YELLOW ' TO it_color .
APPEND 'GOLD ' TO it_color .
APPEND 'GREEN ' TO it_color .
APPEND 'GREY_25_PERCENT ' TO it_color .
APPEND 'GREY_40_PERCENT ' TO it_color .
APPEND 'GREY_50_PERCENT ' TO it_color .
APPEND 'GREY_80_PERCENT ' TO it_color .
APPEND 'INDIGO ' TO it_color .
APPEND 'LAVENDER ' TO it_color .
APPEND 'LEMON_CHIFFON ' TO it_color .
APPEND 'LIGHT_BLUE ' TO it_color .
APPEND 'LIGHT_CORNFLOWER_BLUE ' TO it_color .
APPEND 'LIGHT_GREEN ' TO it_color .
APPEND 'LIGHT_ORANGE ' TO it_color .
APPEND 'LIGHT_TURQUOISE ' TO it_color .
APPEND 'LIGHT_YELLOW ' TO it_color .
APPEND 'LIME ' TO it_color .
APPEND 'MAROON ' TO it_color .
APPEND 'OLIVE_GREEN ' TO it_color .
APPEND 'ORANGE ' TO it_color .
APPEND 'ORCHID ' TO it_color .
APPEND 'PALE_BLUE ' TO it_color .
APPEND 'PINK ' TO it_color .
APPEND 'PLUM ' TO it_color .
APPEND 'RED ' TO it_color .
APPEND 'ROSE ' TO it_color .
APPEND 'ROYAL_BLUE ' TO it_color .
APPEND 'SEA_GREEN ' TO it_color .
APPEND 'SKY_BLUE ' TO it_color .
APPEND 'TAN ' TO it_color .
APPEND 'TEAL ' TO it_color .
APPEND 'TURQUOISE ' TO it_color .
APPEND 'VIOLET ' TO it_color .
APPEND 'WHITE ' TO it_color .
APPEND 'YELLOW ' TO it_color .
lines = LINES( it_color ) .
ob_abap_random_1 = cl_abap_random_int=>create( min = 1 max = lines ) .
ENDIF .
DATA: tabix_1 TYPE sytabix .
tabix_1 = ob_abap_random_1->get_next( ) .
READ TABLE it_color INTO color INDEX tabix_1 .
ENDFORM . "get_random_color
*----------------------------------------------------------------------*
FORM get_data_1
USING
it_sbook TYPE ty_bookings .
SELECT * INTO TABLE it_sbook
FROM sbook
UP TO p_rows ROWS
WHERE
carrid IN s_carrid AND
connid IN s_connid AND
fldate IN s_fldate AND
bookid IN s_bookid .
ENDFORM . "GET_DATA_1
*----------------------------------------------------------------------*
FORM write_1_to_xml
USING
it_data TYPE table .
DATA: message TYPE bapiret2-message .
MESSAGE s001(00) WITH 'Start the XML phase' INTO message .
PERFORM add_message .
* Converting table it_data to XML DOM using cl_ixml .
IF LINES( it_data ) EQ 0 .
MESSAGE s560(oc).
RETURN .
ENDIF .
DATA: it_ddfields TYPE ddfields .
FIELD-SYMBOLS: <st_ddfields> LIKE LINE OF it_ddfields .
* Get DDIC Information
CALL METHOD cl_salv_ddic=>get_by_data
EXPORTING
data = it_data
RECEIVING
t_dfies = it_ddfields.
DELETE it_ddfields WHERE fieldname EQ 'MANDT' .
* Take care of invalid names (name space etc.).
LOOP AT it_ddfields ASSIGNING <st_ddfields> .
IF <st_ddfields>-lfieldname+0(1) = '/' .
SHIFT <st_ddfields>-lfieldname LEFT BY 1 PLACES .
TRANSLATE <st_ddfields>-lfieldname USING '/_' .
ENDIF .
ENDLOOP .
FIELD-SYMBOLS: <st_data> TYPE ANY .
FIELD-SYMBOLS: <component> TYPE ANY .
DATA: dref TYPE REF TO data .
CREATE DATA dref LIKE LINE OF it_data .
ASSIGN dref->* TO <st_data> .
DATA: buffer TYPE char64 .
DATA: ob_ixml TYPE REF TO if_ixml .
DATA: ob_ixml_document TYPE REF TO if_ixml_document ,
ob_ixml_encoding TYPE REF TO if_ixml_encoding .
ob_ixml = cl_ixml=>create( ).
ob_ixml_document = ob_ixml->create_document( ).
ob_ixml_encoding = ob_ixml->create_encoding(
byte_order = if_ixml_encoding=>co_little_endian
character_set = 'utf-8' ) .
ob_ixml_document->set_encoding( ob_ixml_encoding ) .
DATA: ob_root TYPE REF TO if_ixml_element .
DATA: ob_sheet TYPE REF TO if_ixml_element .
DATA: ob_rowx TYPE REF TO if_ixml_element .
DATA: ob_colx TYPE REF TO if_ixml_element .
DATA: string TYPE string .
ob_root = ob_ixml_document->create_simple_element(
name = 'root' parent = ob_ixml_document ) .
WRITE sy-datum TO buffer .
WRITE sy-uzeit TO buffer+12 .
string = buffer .
ob_root->set_attribute( name = 'Time' value = string ) .
ob_sheet = ob_ixml_document->create_simple_element(
name = 'sheet' parent = ob_root ) .
* Freeze pane directive.
ob_sheet->set_attribute( name = 'rowSplit' value = '1' ) .
ob_sheet->set_attribute( name = 'colSplit' value = '5' ) .
* Create the header row - Headings,metadata etc.
ob_rowx = ob_ixml_document->create_simple_element(
name = 'header' parent = ob_root ) .
LOOP AT it_ddfields ASSIGNING <st_ddfields> .
string = 'headerCell' .
ob_colx = ob_ixml_document->create_simple_element(
name = string parent = ob_rowx ) .
string = <st_ddfields>-fieldname .
ob_colx->set_attribute( name = 'NAME' value = string ) .
string = <st_ddfields>-reptext .
ob_colx->set_attribute( name = 'REPTEXT' value = string ) .
string = <st_ddfields>-scrtext_m .
ob_colx->set_attribute( name = 'SCRTEXT_M' value = string ) .
string = <st_ddfields>-inttype .
ob_colx->set_attribute( name = 'INTTYPE' value = string ) .
string = <st_ddfields>-datatype .
ob_colx->set_attribute( name = 'DATATYPE' value = string ) .
ob_colx->set_attribute( name = 'Boldweight' value = 'BOLDWEIGHT_BOLD' ) .
ob_colx->set_attribute( name = 'ForegroundColor' value = 'BLACK' ) .
ob_colx->set_attribute( name = 'FontColor' value = 'GOLD' ) .
ob_colx->set_attribute( name = 'Alignment' value = 'ALIGN_CENTER' ) .
ENDLOOP .
* Create the details rows
LOOP AT it_data ASSIGNING <st_data> .
string = sy-tabix .
ob_rowx = ob_ixml_document->create_simple_element(
name = 'detail'
parent = ob_root ) .
ob_rowx->set_attribute( name = 'line' value = string ) .
LOOP AT it_ddfields ASSIGNING <st_ddfields> .
string = 'detailCell' .
ob_colx = ob_ixml_document->create_simple_element(
name = string
parent = ob_rowx ) .
string = <st_ddfields>-fieldname .
ob_colx->set_attribute( name = 'NAME' value = string ) .
PERFORM get_random_color
CHANGING
string .
ob_colx->set_attribute( name = 'ForegroundColor' value = string ) .
PERFORM get_random_color
CHANGING
string .
ob_colx->set_attribute( name = 'FontColor' value = string ) .
ASSIGN COMPONENT <st_ddfields>-fieldname OF STRUCTURE <st_data> TO <component> .
CASE <st_ddfields>-inttype .
WHEN 'P' OR 'I' OR 'S' OR 'N' .
WRITE <component> TO buffer . CONDENSE buffer .
string = buffer .
WHEN 'D' .
WRITE <component>+0(4) TO buffer .
WRITE '-' TO buffer+4(1) .
WRITE <component>+4(2) TO buffer+5(2) .
WRITE '-' TO buffer+7(1) .
WRITE <component>+6(2) TO buffer+8(2) .
WRITE <component> TO buffer .
* buffer = <component> .
CONDENSE buffer .
string = buffer .
WHEN OTHERS.
buffer = <component> .
string = buffer .
CALL METHOD cl_http_utility=>if_http_utility~escape_html
EXPORTING
unescaped = string
RECEIVING
escaped = string.
ENDCASE.
ob_colx->if_ixml_node~set_value( string ).
* ob_colx->set_attribute( name = 'VALUE' value = string ) .
ENDLOOP .
ENDLOOP.
DATA: ob_ixml_stream_factory TYPE REF TO if_ixml_stream_factory.
DATA: ob_ostream TYPE REF TO if_ixml_ostream .
DATA: xstring TYPE xstring.
ob_ixml_stream_factory = ob_ixml->create_stream_factory( ).
ob_ostream = ob_ixml_stream_factory->create_ostream_xstring( xstring ).
ob_ixml_document->render( ostream = ob_ostream ) .
DATA: debug TYPE string.
* For debug purpose only
ob_ixml_stream_factory = ob_ixml->create_stream_factory( ).
ob_ostream = ob_ixml_stream_factory->create_ostream_cstring( debug ).
ob_ixml_document->render( ostream = ob_ostream ) .
DATA: filesize TYPE i.
filesize = XSTRLEN( xstring ).
DATA: file_name TYPE string .
CONCATENATE p_file_f p_file_s INTO file_name .
DATA: mess TYPE string .
OPEN DATASET file_name FOR OUTPUT IN BINARY MODE MESSAGE mess .
IF sy-subrc NE 0.
MESSAGE e306(f7) WITH file_name .
RETURN .
ENDIF.
TRANSFER xstring TO file_name .
CLOSE DATASET file_name .
MESSAGE s838(5z) WITH file_name INTO message .
PERFORM add_message .
ENDFORM. "write_1_to_xml
*----------------------------------------------------------------------*
FORM write_2_to_excel .
DATA: message TYPE bapiret2-message .
MESSAGE s001(00) WITH 'Start the Excel phase' INTO message .
PERFORM add_message .
* Use Transaction SM69 Maintain External OS Commands.
* Use Java program via external command .
DATA: status TYPE extcmdexex-status ,
exitcode TYPE extcmdexex-exitcode .
DATA: it_exec_protocol TYPE TABLE OF btcxpm .
DATA: additional_parameters TYPE sxpgcolist-parameters .
DATA: file_name TYPE string .
CONCATENATE p_file_f 'XmlFileToExcel.jar main.Main' INTO additional_parameters .
CONCATENATE p_file_f p_file_s INTO file_name .
CONCATENATE additional_parameters '-s' file_name INTO additional_parameters SEPARATED BY space .
CONCATENATE p_file_f p_file_t INTO file_name .
CONCATENATE additional_parameters '-t' file_name INTO additional_parameters SEPARATED BY space .
CALL FUNCTION 'SXPG_COMMAND_EXECUTE'
EXPORTING
commandname = c_java_call
additional_parameters = additional_parameters
IMPORTING
status = status
exitcode = exitcode
TABLES
exec_protocol = it_exec_protocol
EXCEPTIONS
no_permission = 1
command_not_found = 2
parameters_too_long = 3
security_risk = 4
wrong_check_call_interface = 5
program_start_error = 6
program_termination_error = 7
x_error = 8
parameter_expected = 9
too_many_parameters = 10
illegal_command = 11
wrong_asynchronous_parameters = 12
cant_enq_tbtco_entry = 13
jobcount_generation_error = 14
OTHERS = 15.
IF sy-subrc NE 0 .
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 .
ENDIF .
DATA: BEGIN OF st_ms .
DATA: msgv1 TYPE symsgv ,
msgv2 TYPE symsgv ,
msgv3 TYPE symsgv ,
msgv4 TYPE symsgv .
DATA: END OF st_ms .
FIELD-SYMBOLS: <st_exec_protocol> LIKE LINE OF it_exec_protocol .
LOOP AT it_exec_protocol ASSIGNING <st_exec_protocol>.
st_ms = <st_exec_protocol>-message .
MESSAGE s001(00) WITH st_ms-msgv1 st_ms-msgv2 st_ms-msgv3 st_ms-msgv4 INTO message .
PERFORM add_message .
ENDLOOP.
ENDFORM. "write_2_to_Excel_file
*----------------------------------------------------------------------*
FORM add_message .
DATA: st_bal_s_msg TYPE bal_s_msg.
st_bal_s_msg-msgty = sy-msgty.
st_bal_s_msg-msgid = sy-msgid.
st_bal_s_msg-msgno = sy-msgno.
st_bal_s_msg-msgv1 = sy-msgv1.
st_bal_s_msg-msgv2 = sy-msgv2.
st_bal_s_msg-msgv3 = sy-msgv3.
st_bal_s_msg-msgv4 = sy-msgv4.
CALL FUNCTION 'BAL_LOG_MSG_ADD'
EXPORTING
i_s_msg = st_bal_s_msg
EXCEPTIONS
log_not_found = 0
OTHERS = 1.
IF sy-subrc NE 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
ENDFORM . "add_message
*Selection texts
*----------------------------------------------------------
* P_FILE D .
* P_ROWS D .
* P_TABLE D .
* S_BOOKID D .
* S_CARRID D .
* S_CONNID D .
* S_FLDATE D .
*Messages
*----------------------------------------------------------
*
* Message class: 00
*001 &1&2&3&4&5&6&7&8
*
* Message class: 5Z
*838 XML file created successfully: &
*
* Message class: F7
*306 File & could not be opened
*
* Message class: OC
*560 Download cancelled - no data found
*
* Message class: VL
*668 No entry found for selection
----------------------------------------------------------------------------------
Extracted by Mass Download version 1.5.2 - E.G.Mellodew. 1998-2018. Sap Release 701