Skip to Content

How to unit test a method that reads an excel file

Feb 09 at 08:36 AM


avatar image

I have implemented a static method which read excel file and exporting internal table with data. The definition of the method is similar to the following:

      read_file IMPORTING iv_file_name  TYPE localfile
                EXPORTING et_materials TYPE ztt_mm10_material
                RAISING   lcx_exception.

What is the best way to unit tests for this method?

In other programming languages, we can approach the topic in a different way but in ABAP, according to my knowledge, this is not possible. Here are examples of how to solve my problem in python or Java:
Unit Testing code with IO file operations (in Python)

You can create a file as part of the test, no need to mock it out.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Mike Pokraka Feb 09 at 10:49 AM

Unit testing also involves coding appropriately in first place, which is why I would consider your code problematic. Your method does two things: Read excel and convert its contents into the table of your choice.

I don't know how you read the Excel file, but I assume you end up with some form of intermediate table or class returned by whatever FM or class you use. So then in a subclass or alternate implementation you can replace the Excel read with data of your choice. I'm going to simplify it a bit by just hardcoding, but you can also store various datasets in ECATT:

class lcl_conversion. 
  public section. 
    methods get_data_from_excel importing i_filename 
                                returning result.
private section. 
  methods read_excel. 
  methods convert_to_ztab.

class lcl_conversion implementation.
method get_data_from_excel. 
result = convert_to_ztab( read_excel( i_filename ) ). 
endmethod. ... endclass. class ltcl_conversion for testing. ... methods no_data for testing. methods valid_data for testing. methods invalid_data for testing. ... method no_data. data(converted_data) = convert_to_ztab( value #( ) ). cl_abap_unit_assert=>assert_initial( converted_data ). endmethod. method valid_data. data(excel_data) = value whatever( ( col = 1 row = 1 value = "Blah" ) ( col = 2 row = 1 value = ... ) ... ( col = 2 row = 3 value = 'XYZ' ) ). data(converted_data) = convert_to_ztab( excel_data ). cl_abap_unit_assert=>assert_equals( act = lines( converted_data ) exp = 2 ). "1 header and two data lines ... a few more asserts to check results. endmethod. method invalid_data. ... "Put some text into numeric fields or whatever try. data(converted_data) = convert_to_ztab( excel_data ). cl_abap_unit_assert=>fail( 'Exception was not raised' ). catch lcx_error. "As expected endtry. endmethod. endclass.
Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hello Mike,
Very good approach, I have already implemented changes in my code according to your suggestion.
But now, I have to make a test class a friend of the main class (lcl_conversion). The second downside is testing the private method (convert_to_ztab). Of course, I can instead create some fake classes that inherit from the main class and redefine the read_excel method. With this approach for three tests, I would have to create three fake classes. I do not like it too much.


It was a simple example. There are a few ways to do this, which which was what I meant with "in a subclass or alternate implementation"

If you are working with interfaces it's easy, your test class can become the redefinition. And you can access private methods of your tested class using local friend declaration in your test class section.

class ltcl_test definition deferred. 
class lcl_main definition local friends ltcl_test.
class ltcl_test definition for testing.

Personally I'd consider the Excel and business data conversions diverse enough to put them in their own classes. Then ZCL_EXCEL and ZCL_CONVERSION have their own unit tests and you can inject a custom ZIF_EXCEL as part of your ZCL_CONVERSION unit test, either via an optional constructor parameter or you can build this into a factory framework or other.

Example, you could create a series of test classes:

class ltcl_some_scenario definition for testing.
  public section. 
    interfaces zif_excel partially implemented. 
    methods zif_excel~read redefinition. 
private section.
    methods run_test for testing.

class ltcl_some_scenario implementation. 
methods zif_excel~read. 
  result = value #( ... ).

methods test. 
  data(conversion) = new zcl_conversion( iv_excel_provider = me ). "<-- test injection
  data(test_data) = conversion->run( ).     "<-- now calls my READ method above
  cl_abap_unit_assert=>assert_equals( act = lines( test_data ) 
                                      exp = 2 ).

In real life, I would typically be defining the injection on a factory class or method that uses a singleton.

So in my conversion tests I will first override the default zcl_excel with my own instance:

zcl_excel_factory=>set_excel( me ). 

Injection is now global, whatever code now runs will at some point call the factory and receive my test instance.

data(excel) = zcl_excel_factory=>get_excel( ).

Thank you Mike for the detailed answer.


You can offer friendship to an interface instead.

Horst Keller
Feb 09 at 11:25 AM
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hello Horst,
I thought I knew all the news from Netweaver 7.5 but you always surprise me with something new.
"Test Seams and Test Injections" would solve my problem with testing private methods. Unfortunately, I work on the 7.4 system and I do not like the idea with a free-style test flag.