[SAP ABAP] Import and Export document Part 2
Working with Microsoft Office files
Every Windows system user has heard of the Microsoft Office package. Microsoft Word or Microsoft Excel formats are the most recognizable and characteristic extensions for Windows operating systems. The Office package is also very well integrated with SAP. We have the possibility to export the result to an Excel spreadsheet in many standard transactions. Exporting to Excel is also part of standard ABAP List Viewer (ALV) functionality. In this section, we dive deeper into SAP and Microsoft integration. Reading, writing, and editing examples will be covered.
Let's assume some external system created a report in Excel format. We need to create an ABAP program that is capable of reading this file. Create a new program,
ZMSA_R_CHAPTER4_5, and copy the report pattern from Appendix A, Assessments.
We need to declare variables. The
lv_filename will be used to store the filename and file path on our local PC. The variable lt_excel stores values in a special cell-addressing format:DATA: lv_filename TYPE localfile VALUE 'c:/temp/testfile4_5.xlsx'.
DATA: lt_excel TYPE TABLE OF alsmex_tabline.
For reading Excel files, we have to use the function module
alsm_excel_to_internal_table. You can get a sample Excel file from GitHub or create a file yourself in the temp folder. Use a pattern framework to get a function call structure. The call will look like this:CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename =
i_begin_col =
i_begin_row =
i_end_col =
i_end_row =
TABLES
intern =
* EXCEPTIONS
* INCONSISTENT_PARAMETERS = 1
* UPLOAD_OLE = 2
* OTHERS = 3
.
IF sy-subrc <> 0.
* Implement suitable error handling here
ENDIF.
Set
i_end_col and i_end_row to 1000. These two parameters have a limitation, which can vary depending on your local system and server configuration. Also remember that indexation starts from 1, so the parameters i_begin_col and i_begin_row have to be set at least to 1. If you open alms_excel_to_internal_table and analyze the code behind it, you will find two important things. This function is just wrapper to an Object Linking and Embedding (OLE) mechanism, which we will use in all other examples. It's also interesting that this function uses some strange tricks. The Excel file is opened in the background and all cells are selected. Then we use the Copy to clipboard mechanism, and the algorithm reads values from the clipboard and assigns the values of individual cells to the ABAP table. This has some limitations, for example, a cell will be copied with its display value and not the real value.
Add the
display_data method from cl_demo_output:cl_demo_output=>display_data( lt_excel ).
The common business case for using OLE is to generate custom-format Excel files generated in an ABAP report. Please create a new ABAP program,
ZMSA_R_CHAPTER4_6, and copy the report pattern from Appendix A, Assessments. If you checked the alms_excel_to_internal_table function module, you should know how OLE works. It's important to include just before our class definition OLE type information:INCLUDE ole2incl.
We need to declare the following variables:
DATA: lv_filename TYPE localfile VALUE 'c:\temp\testfile4_6.xls'.
DATA: lo_excel TYPE ole2_object.
DATA: lo_workbook TYPE ole2_object.
DATA: lo_sheet TYPE ole2_object.
DATA: lo_cell TYPE ole2_object.
For some systems, to make this example work, instead of using (
/) in the path name, you have to use (\) . This is only a problem if a path is hardcoded. Now we need to do some basic setup:CREATE OBJECT lo_excel 'EXCEL.APPLICATION'.
SET PROPERTY OF lo_excel 'visible' = 1.
CALL METHOD OF lo_excel 'Workbooks' = lo_workbook.
CALL METHOD OF lo_workbook 'Add'.
CALL METHOD OF lo_excel 'Worksheets' = lo_sheet
EXPORTING #1 = 1.
CALL METHOD OF lo_sheet 'Activate'.
SET PROPERTY OF lo_sheet 'Name' = 'TestSheet'.
This will initialize both
lo_workbook and lo_sheet. lo_workbook represents our entire workbook and lo_sheet is the equivalent of our single spreadsheet. You can also change the name using the 'NAME' property on the sheet object. If you run this program, Excel will be opened on your local desktop. If you change the 'visible' parameter to 0, the process will be run in the background.
Now we want to put some information into Excel. To do this, we will get a
lo_cell object from lo_sheet and set the 'Value' property:DO 10 TIMES.
CALL METHOD OF lo_sheet 'Cells' = lo_cell EXPORTING #1 = sy-index #2 = 1.
SET PROPERTY OF lo_cell 'Value' = sy-index.
ENDDO.
We have two exporting parameters here; the first is for the row and the second is for a column. We will put the value of the current index in individual cells. The last thing to do is save the file and close the Excel OLE objects:
CALL METHOD OF lo_sheet 'SaveAs'
EXPORTING
#1 = lv_filename
#2 = 1.
SET PROPERTY OF lo_excel 'visible' = 0.
CALL METHOD OF lo_sheet 'CLOSE'
EXPORTING
#1 = 'YES'.
CALL METHOD OF lo_excel 'QUIT'.
FREE OBJECT: lo_excel,
lo_sheet.
If everything went okay, you should have a new Excel file in the
C:/temp directory. Go there and verify that the content is correct:
In the case of Microsoft Word, we will focus only on writing values to the file due to the fact that reading from a Microsoft Word document is not especially interesting or useful. Word documents can have infinite numbers of layouts. It's much easier to work with structured Excel, where we always expect rows and columns, than with Word, where the table can have any format.
Create a new program,
ZMSA_R_CHAPTER4_7, and copy the report pattern from Appendix A, Assessments. Add an obligatory INCLUDE statement to the OLE type-pool just before the class definition: INCLUDE ole2incl.
Declare a few variables in the
main method:DATA: lo_word TYPE ole2_object.
DATA: lo_doc TYPE ole2_object.
DATA: lo_selection TYPE ole2_object.
DATA: lo_font TYPE ole2_object.
DATA: lo_paragraph TYPE ole2_object.
In the next step, we will initialize and get the handler of all needed objects:
CREATE OBJECT lo_word 'Word.Application'.
CALL METHOD OF lo_word 'Documents' = lo_doc.
CALL METHOD OF lo_doc 'Add'.
GET PROPERTY OF lo_word 'Selection' = lo_selection.
GET PROPERTY OF lo_selection 'ParagraphFormat' = lo_paragraph.
GET PROPERTY OF lo_selection 'Font' = lo_font.
You can also prepare your font and paragraph format, and therefore specify the final layout of the document's content:
SET PROPERTY OF lo_word 'Visible' = 1.
SET PROPERTY OF lo_font 'Size' = 22.
SET PROPERTY OF lo_font 'Bold' = 1.
SET PROPERTY OF lo_paragraph 'Alignment' = 1. " Centered
To write something, you will use the
'TypeText' method on the lo_selecton object. The exporting parameters represent the text you want to put into Word document:CALL METHOD OF lo_selection 'TypeText'
EXPORTING
#1 = 'First Word Report of Airlines with OLE'.
CALL METHOD OF lo_selection 'TypeParagraph'.
To make this example more reasonable, we will select data from a SAP IDES standard data flight model. Let's say we'd like to list every single airline from the current system. We need an additional variable and a new select statement:
DATA: lt_carrname TYPE TABLE OF s_carrname.
DATA: lv_carrname TYPE s_carrname.
SELECT carrname FROM scarr INTO TABLE lt_carrname.
The list of the airlines should be written with a smaller font than the header; because of that, we will change a few properties:
SET PROPERTY OF lo_font 'Size' = 10.
SET PROPERTY OF lo_font 'Bold' = 0.
SET PROPERTY OF lo_paragraph 'Alignment' = 0.
To write the airline list into Word, we will again use the
'TypeText' method of the lo_selection class:LOOP AT lt_carrname INTO lv_carrname.
CALL METHOD OF lo_selection 'TypeText'
EXPORTING
#1 = lv_carrname.
CALL METHOD OF lo_selection 'TypeParagraph'.
ENDLOOP.
If everything went well, you should be able to see the Word document with the airlines report in it:
DOI is an ABAP object interface that can be used to work with Office applications using the OLE2 interface. You can use it to edit the Office format, but it can also be used to integrate Office applications into ABAP reports. This could be bi-directional integration, meaning the ABAP report can react to Office events and Office can react based on ABAP logic. DOI significantly extends the possibilities presented in previous chapters carried out using OLE classes.
Using DOI is also much more complicated than using OLE objects, so we will focus on only one example of integrating a Word document into the ABAP program. Forget for a moment about our standard program template and create an empty new program,
ZMSA_R_CHAPTER4_8. ABAP and the structure of ABAP programs are not flexible when it comes to programs based on classic Dynpro. Using the object, in this case, is not so easy, and in the end, some fragments would have to be made non-objected anyway. First, create a few variables. We also need to include the soi as a TYPE-POOLS:TYPE-POOLS: soi.
DATA: lo_container TYPE REF TO cl_gui_custom_container.
DATA: lo_control TYPE REF TO i_oi_container_control.
DATA: lo_proxy TYPE REF TO i_oi_document_proxy.
DATA: lv_okcode TYPE syst_ucomm.
In the next step, we need to create a new screen. This screen will encapsulate our integrated Word application and ABAP-based buttons for interacting with our Word document. To create the screen, right-click on your report name in the navigation bar and choose
Create|Screen:
A small popup will appear where you can set a new screen number. You can choose whatever four-digit number you like. When you extend standard SAP reports, it should be in the range of
9000+, but in the case of custom development, it can be anything, such as 0100. Be aware that if you change this number you will also have to remember to change it in other parts of this example:
After clicking the green check mark, you will jump to a new screen where all basic configurations can be set. For this example, only a short description is obligatory:
In the next step, we will create a layout for our screen. To jump into the
Screen Editor, you can hit Ctrl + F7 on the keyboard or click on the Layout button on the toolbar. On your new screen, create three objects (one custom container and two buttons). Use the icons on the left to create everything:
It's very important to include the names and function codes (
FctCode) as in this example. For BTN_OPEN, use OPEN and for BTN_CLOSE use CLOSE:
Custom containers also need to have reference names, but
FctCode is not needed. You can verify names either in the layout object preferences panel or in the Element list tab when you close the Screen Editor. You should also enter an element name for the OK command object:
Go to the
Flow logic tab and remove everything:
Put the following code there. It will be needed later to handle screen-processing events:
PROCESS BEFORE OUTPUT.
MODULE INIT.
PROCESS AFTER INPUT.
MODULE EXIT AT EXIT-COMMAND.
MODULE USER_COMMAND_0100.
Activate the screen and go back to your main application. Include some code to handle the three new modules we just created in the
Flow logic tab:MODULE INIT OUTPUT.
ENDMODULE.
MODULE EXIT INPUT.
ENDMODULE.
MODULE user_command_0100 INPUT.
ENDMODULE.
The
INIT module will be started just before screen 0100 starts. EXIT and user_command_0100 modules are called every time the user does something on the screen. Now we need to call our screen just at the beginning of program execution. This should be placed just after the data declaration:SET SCREEN 100.
If you run your program, you should already be able to see your screen:
Nothing will work yet, and two very important parts are missing. We need to create a GUI status or
GUI Titles. Right-click on the report name and select Create | Gui Titles:
Choose a custom name and select
Title Code:
Create
GUI Status:
In the
GUI Status popup, fill in a description and number for GUI Status:
After clicking the green check mark, you will be forwarded to a
GUI Status setup window. In our case, we need to set up only the STOP code:
Activate this and go back to the main program code. First, we will start by handling the
INIT function code. Put the following source code in the INIT module:SET PF-STATUS '0100'.
SET TITLEBAR '0100'.
This will set the earlier-created GUI Status and GUI Titlebar. Move one and add the following calls:
CALL METHOD c_oi_container_control_creator=>get_container_control
IMPORTING
control = lo_control.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
This will initialize the
lo_control object. The method show_message can help you in case of any troubleshooting. Now add the following code: CREATE OBJECT lo_container
EXPORTING
container_name = 'CUSTOMCONTAINER'.
CALL METHOD lo_container->set_visible EXPORTING visible = abap_false.
CALL METHOD lo_control->init_control
EXPORTING
r3_application_name = 'R/3 Basis'
inplace_enabled = abap_true
inplace_scroll_documents = abap_true
parent = lo_container
register_on_close_event = abap_true
register_on_custom_event = abap_true
no_flush = abap_false.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
Finally, initialize the document proxy object:
CALL METHOD lo_control->get_document_proxy
EXPORTING
document_type = 'Word.Document.8'
no_flush = abap_false
IMPORTING
document_proxy = lo_proxy.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
One last thing is missing in the
INIT module. We need to create mechanisms that will avoid creating and initializing objects after every user action. For this, we will create a global variable. We can also create a second global variable that will be useful for us later:DATA: lv_init TYPE boolean.
DATA: lv_closed TYPE i.
Use this variable in the
INIT module. The first line of the following coding should be put at the beginning of the module and the second at the end: CHECK lv_init = abap_false.
lv_init = abap_true.
So far, so good. Now we need to add buttons logic. We have two actions,
OPEN and CLOSE. CLOSE will also save a document into a database table. Put the following code in the USER_COMMAND_0100 module: CASE lv_okcode.
WHEN 'OPEN'.
CALL METHOD lo_proxy->is_destroyed
IMPORTING
ret_value = lv_closed.
CHECK NOT lv_closed IS INITIAL.
CALL METHOD lo_container->set_visible
EXPORTING
visible = abap_true.
CALL METHOD lo_proxy->create_document
EXPORTING
open_inplace = abap_true
document_title = 'DOI Test Document'
no_flush = abap_false.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
WHEN'CLOSE'.
ENDCASE.
CLEAR: lv_okcode.
The first method checks if a document proxy exists; we don't want to create it again in such a case. We will also make our container visible. The final step, the
create_document method, creates an actual document. The parameter open_inplace determines whether the Word application should be opened embedded in the GUI or as a new window. If you execute your program now and click on the Open button, you will see the Word application:
From a functional perspective, our program is still missing two things—saving and reopening the document. First, create three additional variables:
DATA: lv_changed TYPE i.
TYPES: ty_row TYPE x LENGTH 2048.
DATA: lt_doc_table TYPE STANDARD TABLE OF ty_row.
DATA: lv_doc_size TYPE i.
Now add the following code into the
USER_COMMAND_0100 module; this module concerns the CLOSE command in the CASE statement: CALL METHOD lo_proxy->is_destroyed
IMPORTING
ret_value = lv_closed.
IF lv_closed IS INITIAL.
CALL METHOD lo_proxy->close_document
EXPORTING
do_save = abap_true
IMPORTING
has_changed = lv_changed.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
IF NOT lv_changed IS INITIAL.
CALL METHOD lo_proxy->save_document_to_table
CHANGING
document_table = lt_doc_table
document_size = lv_doc_size.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
ENDIF.
CALL METHOD lo_proxy->release_document.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
ENDIF.
CALL METHOD lo_container->set_visible EXPORTING visible = abap_false.
The
is_destoryed method checks whether our document exists or not. We cannot save or release a document if it doesn't exist. save_document_to_table saves document contents into a local ABAP table. The call of the method set_visible hides the container. We can already open and close the document, but now we need to take care of reopening it. We need to delete everything from the open part of the USER_COMMAND_0100 module and paste the following: CALL METHOD lo_proxy->is_destroyed
IMPORTING
ret_value = lv_closed.
CHECK NOT lv_closed IS INITIAL.
CALL METHOD lo_container->set_visible
EXPORTING
visible = abap_true.
IF lv_doc_size > 0.
CALL METHOD lo_proxy->open_document_from_table
EXPORTING
document_table = lt_doc_table
document_size = lv_doc_size
document_title = 'DOI Test Document'
open_inplace = abap_true.
ELSE.
CALL METHOD lo_proxy->create_document
EXPORTING
open_inplace = abap_true
document_title = 'DOI Test Document'
no_flush = abap_false.
ENDIF.
CALL METHOD c_oi_errors=>show_message EXPORTING type = 'E'.
Some parts of the code are the same as earlier. The new part concerns a condition statement, where we check whether the size of a closed document is not initial. If not, we will open the previously—saved document. Please test it by clicking
Open, writing something, closing the document, and clicking Open again:
The last formality is to create a mechanism to turn off the program. Copy and paste the following code into the
EXIT module. This will release all memory, close all objects, and exit the program: CASE lv_okcode.
WHEN 'STOP'.
IF NOT lo_proxy IS INITIAL.
CALL METHOD lo_proxy->close_document.
FREE lo_proxy.
ENDIF.
IF NOT lo_control IS INITIAL.
CALL METHOD lo_control->destroy_control.
FREE lo_control.
ENDIF.
LEAVE PROGRAM.
ENDCASE.
Comments
Post a Comment