Autofilter on the wrong Row when you have an header line

[rotda]

Hi ,

I use the template function of abap2xlsx but my excel have an header line and then the autfilter line.
In this case there is an bug in ZCL_EXCEL line 423 ( see screenshot) . It set the rowstart to 1 when row_start > = row_end. In my case then autfilter is on header row :-(

When I comment the line it works with out any problem

[sandraros]

@rotda Thank you. I reproduced. I think that the original developer had in mind that a filter could be only at least two rows and consequently did an arbitrary fix to extend a one-row filter to two rows. Note that when the method set_filter_area is called with at least two rows, the block above in method validate_area is not executed and there's no problem.

To reproduce:

• With the below program which defines the autofilter on row 2 only, we can see the filter is incorrectly set on row 1 and applies to row 2, which results in something weird:
  
• After fixing the code temporarily as you say, the filter is displayed correctly:
  
• Program to reproduce:

  REPORT zabap2xlsx_bug_546.
  CONSTANTS: gc_save_file_name TYPE string VALUE 'issue_546.xlsx'.
  INCLUDE zdemo_excel_outputopt_incl.
  START-OF-SELECTION.
  DATA(lo_excel) = NEW zcl_excel( ).
  DATA(lo_worksheet) = lo_excel->get_active_worksheet( ).
  lo_worksheet->set_cell( ip_column = 2 ip_row = 2 ip_value = 'HeadA' ).
  lo_worksheet->set_cell( ip_column = 3 ip_row = 2 ip_value = 'HeadB' ).
  lo_worksheet->set_cell( ip_column = 4 ip_row = 2 ip_value = 'HeadC' ).
  DATA(lo_autofilter) = lo_excel->add_new_autofilter( io_sheet = lo_worksheet ) .
  lo_autofilter->set_filter_area( VALUE #( row_start = 2 col_start = 1
                                           row_end   = 2 col_end   = 3 ) ).
  *  lo_autofilter->set_value( i_column = 3 i_value = 'B1' ).
  lcl_output=>output( lo_excel ).
  

I don't see any side effect to do as you said. I would just delete the whole block from line 423 to 429 (as per your screenshot), and I would add a condition that if row_start > row_end, their values are switched e.g. if row_start = 6 and row_to = 2, we switch them to become row_start = 2 and row_to = 6 (technically speaking, in Excel, doing a filter from row 6 to row 2 works the same as a filter from row 2 to row 6, but I'd like to not allow it, maybe that would avoid future problems and at least we address/document explicitly the case in the program...).

@AndreaBorgia-Abo What do you think? Thank you!

[sandraros]

@AndreaBorgia-Abo See my proposal in the PR. Thanks! 👍

[AndreaBorgia-Abo]

@sandraros I should be able to do this tomorrow, will let you know.

[AndreaBorgia-Abo]

With Excel 2013 it seems to work as expected, however Libreoffice 7.1 won't show the autofilter when opening the file, both before and after the fix.... I've also tried to place column headings in row 1 but it makes no difference, weird.
Anyhow, fine by me, PR is ok.