BIND_TABLE and autofilter

[TR-MG]

Hi,

there is an existing report which creates an Excel file via ABAP2XLSX.
I received the requirement to add filter values when the Excel file is created.
It sounded doable as there is demo program ZDEMO_EXCEL33 which demonstrates how to add an autofilter.

The filtering was added and the Excel file created.
When opening the Excel file an error is shown.
Sorry I only have the screenshot from a system in German:

The Excel file from demo program ZDEMO_EXCEL33 could be opened without issues.
Thus I checked what differs between the reports.
I found the difference in how the data is added to the worksheet.
ZDEMO_EXCEL33 uses zcl_excel_converter->convert, the report which should be enhanced uses zcl_excel_worksheet->bind_table.

I could recreate the issue by modifying ZDEMO_EXCEL33:

REPORT ztr_demo_excel33_bind.

TYPES: ty_t005t_lines TYPE TABLE OF t005t.

DATA: lo_excel      TYPE REF TO zcl_excel,
      lo_worksheet  TYPE REF TO zcl_excel_worksheet,
      lo_converter  TYPE REF TO zcl_excel_converter,
      lo_autofilter TYPE REF TO zcl_excel_autofilter.

DATA lt_test TYPE ty_t005t_lines.

DATA: l_cell_value TYPE zexcel_cell_value,
      ls_area      TYPE zexcel_s_autofilter_area.
DATA: ls_option TYPE zexcel_s_converter_option.

CONSTANTS: c_airlines TYPE string VALUE 'Airlines'.

CONSTANTS: gc_save_file_name TYPE string VALUE '33_autofilter_bind.xlsx'.
INCLUDE zdemo_excel_outputopt_incl.

PARAMETERS p_convex AS CHECKBOX.

START-OF-SELECTION.

  " Creates active sheet
  CREATE OBJECT lo_excel.

  " Get active sheet
  lo_worksheet = lo_excel->get_active_worksheet( ).
  lo_worksheet->set_title( ip_title = 'Internal table' ).

  PERFORM load_fixed_data CHANGING lt_test.

" bind instead of convert
" -> leads to message when opening excel that it needs to be repaired
  lo_worksheet->bind_table(  ip_table          = lt_test
                             is_table_settings = VALUE zexcel_s_table_settings(  top_left_column = 'A'
                                                                                 top_left_row = 1
                                                                                 " removes on error when opening
                                                                                 nofilters = abap_true ) ).

*  CREATE OBJECT lo_converter.
*
*  ls_option-conv_exit_length = p_convex.
*  lo_converter->set_option( ls_option ).
*  lo_converter->convert( EXPORTING
*                            it_table     = lt_test
*                            i_row_int    = 1
*                            i_column_int = 1
*                            io_worksheet = lo_worksheet
*                         CHANGING
*                            co_excel     = lo_excel ) .
*  PERFORM set_column_headers USING lo_worksheet 'Client;Language;Country;Name;Nationality;Long name;Nationality'.



  lo_autofilter = lo_excel->add_new_autofilter( io_sheet = lo_worksheet ) .

  ls_area-row_start = 1.
  ls_area-col_start = 1.
  ls_area-row_end = lo_worksheet->get_highest_row( ).
  ls_area-col_end = lo_worksheet->get_highest_column( ).

  lo_autofilter->set_filter_area( is_area = ls_area ).

  lo_worksheet->get_cell( EXPORTING
                             ip_column    = 'B'
                             ip_row       = 2
                          IMPORTING
                             ep_value     = l_cell_value ).
  lo_autofilter->set_value( i_column = 2
                            i_value  = l_cell_value ).

*** Create output
  lcl_output=>output( lo_excel ).


FORM load_fixed_data CHANGING ct_test TYPE ty_t005t_lines.
  DATA: lt_lines  TYPE TABLE OF string,
        lv_line   TYPE string,
        lt_fields TYPE TABLE OF string,
        lv_comp   TYPE i,
        lv_field  TYPE string,
        ls_test   TYPE t005t.
  FIELD-SYMBOLS: <lv_field> TYPE simple.

  APPEND '001 E AD Andorra    Andorran    Andorra    Andorran   ' TO lt_lines.
  APPEND '001 E BE Belgium    Belgian     Belgium    Belgian    ' TO lt_lines.
  APPEND '001 E DE Germany    German      Germany    German     ' TO lt_lines.
  APPEND '001 E FM Micronesia Micronesian Micronesia Micronesian' TO lt_lines.
  LOOP AT lt_lines INTO lv_line.
    CONDENSE lv_line.
    SPLIT lv_line AT space INTO TABLE lt_fields.
    lv_comp = 1.
    LOOP AT lt_fields INTO lv_field.
      ASSIGN COMPONENT lv_comp OF STRUCTURE ls_test TO <lv_field>.
      <lv_field> = lv_field.
      lv_comp = lv_comp + 1.
    ENDLOOP.
    APPEND ls_test TO ct_test.
  ENDLOOP.
ENDFORM.

FORM set_column_headers
    USING io_worksheet TYPE REF TO zcl_excel_worksheet
          iv_headers   TYPE csequence
    RAISING zcx_excel.

  DATA: lt_headers TYPE TABLE OF string,
        lv_header  TYPE string,
        lv_tabix   TYPE i.

  SPLIT iv_headers AT ';' INTO TABLE lt_headers.
  LOOP AT lt_headers INTO lv_header.
    lv_tabix = sy-tabix.
    io_worksheet->set_cell( ip_row = 1 ip_column = lv_tabix ip_value = lv_header ).
  ENDLOOP.

ENDFORM.

When parameter nofilters is set to abap_false then an additional error is shown when opening the Excel file (Autofilter).
My assumption was that an autofilter could be added also when BIND_TABLE is used.
The errors shown when opening the Excel file point only to the XML files but unfortunately no further pointers.
I renamed the file extension to ZIP and had a look at the XML file from the error message.
But as I am no Excel XML expert I do not know how/where to look further.

Any feedback, workaround or solution are highly appreciated:-)

Best regards,
Thomas

[sandraros]

Thanks for the detailed description. I'll check the issue tomorrow. For information, I'm using a ZIP comparison tool to compare the Excel file generated versus the same version after being repaired by Excel, and also to compare what exact xlsx change is done after any edit via Excel (e.g. adding filters to a table).

[sandraros]

Bad news, the filter feature is not implemented at all for Excel Tables (the type of tables created by BIND_TABLE).

• In the xlsx file, filters outside Excel Tables must be defined in the "xl\worksheets\sheet#.xml" files, while filters inside Excel Tables must be defined in the "xl\tables\table#.xml" files (both using the <autoFilter> element).
• Currently, the <autoFilter> element is created only in the method add_autofilter of the local class lcl_create_xl_sheet of the global class zcl_excel_writer_2007, so only for worksheets.
  

  abap2xlsx/src/zcl_excel_writer_2007.clas.locals_imp.abap

  Lines 779 to 840 in 35425c3

  	METHOD add_autofilter.
  	DATA:
  	lo_element TYPE REF TO if_ixml_element,
  	lo_element_2 TYPE REF TO if_ixml_element,
  	lo_element_3 TYPE REF TO if_ixml_element,
  	lo_element_4 TYPE REF TO if_ixml_element,
  	lv_value TYPE string,
  	lv_column TYPE zexcel_cell_column,
  	lt_values TYPE zexcel_t_autofilter_values,
  	ls_values TYPE zexcel_s_autofilter_values,
  	lo_autofilters TYPE REF TO zcl_excel_autofilters,
  	lo_autofilter TYPE REF TO zcl_excel_autofilter,
  	lv_ref TYPE string.
  	lo_autofilters = o_excel_ref->excel->get_autofilters_reference( ).
  	lo_autofilter = lo_autofilters->get( io_worksheet = o_worksheet ) .
  	IF lo_autofilter IS BOUND.
  	* Create node autofilter
  	lo_element = o_document->create_simple_element( name = lc_xml_node_autofilter
  	parent = o_document ).
  	lv_ref = lo_autofilter->get_filter_range( ) .
  	CONDENSE lv_ref NO-GAPS.
  	lo_element->set_attribute_ns( name = lc_xml_attr_ref
  	value = lv_ref ).
  	lt_values = lo_autofilter->get_values( ) .
  	IF lt_values IS NOT INITIAL.
  	* If we filter we need to set the filter mode to 1.
  	lo_element_2 = o_document->find_from_name( name = lc_xml_node_sheetpr ).
  	lo_element_2->set_attribute_ns( name = lc_xml_attr_filtermode
  	value = '1' ).
  	* Create node filtercolumn
  	CLEAR lv_column.
  	LOOP AT lt_values INTO ls_values.
  	IF ls_values-column <> lv_column.
  	IF lv_column IS NOT INITIAL.
  	lo_element_2->append_child( new_child = lo_element_3 ).
  	lo_element->append_child( new_child = lo_element_2 ).
  	ENDIF.
  	lo_element_2 = o_document->create_simple_element( name = lc_xml_node_filtercolumn
  	parent = lo_element ).
  	lv_column = ls_values-column - lo_autofilter->filter_area-col_start.
  	lv_value = lv_column.
  	CONDENSE lv_value NO-GAPS.
  	lo_element_2->set_attribute_ns( name = lc_xml_attr_colid
  	value = lv_value ).
  	lo_element_3 = o_document->create_simple_element( name = lc_xml_node_filters
  	parent = lo_element_2 ).
  	lv_column = ls_values-column.
  	ENDIF.
  	lo_element_4 = o_document->create_simple_element( name = lc_xml_node_filter
  	parent = lo_element_3 ).
  	lo_element_4->set_attribute_ns( name = lc_xml_attr_val
  	value = ls_values-value ).
  	lo_element_3->append_child( new_child = lo_element_4 ). " value node
  	ENDLOOP.
  	lo_element_2->append_child( new_child = lo_element_3 ).
  	lo_element->append_child( new_child = lo_element_2 ).
  	ENDIF.
  	o_element_root->append_child( new_child = lo_element ).
  	ENDIF.
  	ENDMETHOD.

• The method create_xl_table of the global class zcl_excel_writer_2007 is only able to activate the filter buttons without any filter criteria.
  

  abap2xlsx/src/zcl_excel_writer_2007.clas.abap

  Lines 5683 to 5693 in 35425c3

  	" autoFilter
  	IF io_table->settings-nofilters EQ abap_false.
  	lo_element = lo_document->create_simple_element( name = 'autoFilter'
  	parent = lo_document ).
  	lv_ref = io_table->get_reference( ip_include_totals_row = abap_false ).
  	lo_element->set_attribute_ns( name = 'ref'
  	value = lv_ref ).
  	lo_element_root->append_child( new_child = lo_element ).
  	ENDIF.

• I guess the things to change are zcl_excel->add_new_autofilter (new parameter io_table type ref to zcl_excel_table), writer class (add_autofilter should be used by both lcl_create_xl_sheet and create_xl_table). The reader class should also be changed if possible.

That would be great if you could implement the feature and propose a pull request.

[TR-MG]

@sandraros
Thank you for the analysis and hints.
For now the requirement for filtering was postponed as other requirements had a higher priority.

[darnoc312]

Hello,

some time ago i developed something corresponding in my ABAP2XLSX.
But this creates also some side effects like filters depending on each other like in the following excel:

33_autofilter_tabledemo.xlsx

The document contains 1 sheet- and 3 table-autofilters. You find the 3rd table if you remove the sheet autofilter in excel.
Filter area only works for the sheet autofilter. XML autofilter's 'ref' attribute is ignored by excel for tables. So always the complete table is filter area then.
The method set_column_headers of ZDEMO_EXCEL33 is only applicable for the sheet autofilter. You know: table headers are protected from overwriting.

Bernd