Reader is freezing the wrong rows/columns

[sandraros]

In my workbook, I have the three first rows and three first columns frozen (red cross added over the screenshot). When I saved it, the top left cell of the scrollable view (part not frozen) was "E26":

Unfortunately, zcl_excel_reader_2007 thinks that the frozen part is indicated by the top left cell of the scrollable view, and it thinks that the 25 first rows are frozen instead of 3, as can be seen if the file is written via zcl_excel_writer_2007:

To reproduce:

1. Download Three rows and columns frozen.xlsx
2. Run zdemo_excel_37 to duplicate this file via abap2xlsx
3. Open the copied file

Debug of zcl_excel_reader_2007, method load_worksheet:

https://github.com/abap2xlsx/abap2xlsx/blob/6bb02920dfca9a210f1d623d2c82daddf17a0ca5/src/zcl_excel_reader_2007.clas.abap#L2925-L2949

[sandraros]

Solution:

      fill_struct_from_attributes( EXPORTING ip_element = lo_ixml_pane_elem CHANGING cp_structure = ls_excel_pane ).
*      " Issue #194
*      " Replace REGEX with method from the common class
*      lcl_excel_common=>convert_columnrow2column_a_row( EXPORTING
*                                                          i_columnrow = ls_excel_pane-topleftcell
*                                                        IMPORTING
*                                                          e_column    = lv_pane_cell_col_a    " Cell Column
*                                                          e_row       = lv_pane_cell_row ).   " Natural number
*      lv_pane_cell_col = lcl_excel_common=>convert_column2int( lv_pane_cell_col_a ).
*      SUBTRACT 1 FROM: lv_pane_cell_col,
*                       lv_pane_cell_row.
      lv_pane_cell_col = ls_excel_pane-xsplit. "<=====
      lv_pane_cell_row = ls_excel_pane-ysplit. "<=====
      IF    lv_pane_cell_col > 0
        AND lv_pane_cell_row > 0.
        io_worksheet->freeze_panes( ip_num_rows    = lv_pane_cell_row
                                    ip_num_columns = lv_pane_cell_col ).
      ELSEIF lv_pane_cell_row > 0.
        io_worksheet->freeze_panes( ip_num_rows    = lv_pane_cell_row ).
      ELSEIF lv_pane_cell_col > 0. "<=====
        io_worksheet->freeze_panes( ip_num_columns = lv_pane_cell_col ).
      ENDIF.

[sandraros]

For information (may not concern the fix), here are examples of <sheetViews> depending on frozen rows and columns:

1. Nothing frozen.xlsx = nothing frozen, active selection in cell C4, top left cell is B2:

   <sheetViews>
      <sheetView tabSelected="1" topLeftCell="B2" zoomScaleNormal="100" zoomScaleSheetLayoutView="100" workbookViewId="0">
          <selection activeCell="C4" sqref="C4"/>
      </sheetView>
   </sheetViews>

2. two rows frozen.xlsx Screenshot when opening the file, notice the positioning (rows/columns scrolled): \

   <sheetViews>
      <sheetView tabSelected="1" topLeftCell="B1" workbookViewId="0">
          <pane ySplit="2" topLeftCell="A4" activePane="bottomLeft" state="frozen"/>
          <selection pane="bottomLeft" activeCell="B3" activeCellId="4" sqref="F3:G4 D5 B4 C1:D1 B3"/>
      </sheetView>
   </sheetViews>

3. two columns frozen.xlsx Screenshot when opening the file, notice the positioning (rows/columns scrolled): \

   <sheetViews>
      <sheetView tabSelected="1" workbookViewId="0">
          <pane xSplit="2" topLeftCell="D1" activePane="topRight" state="frozen"/>
          <selection pane="topRight" activeCell="D4" sqref="D3:D4 A2:A3"/>
      </sheetView>
   </sheetViews>

4. Three rows and columns frozen.xlsx:

   <sheetViews>
      <sheetView tabSelected="1" workbookViewId="0">
          <pane xSplit="3" ySplit="3" topLeftCell="E26" activePane="bottomRight" state="frozen"/>
          <selection pane="topRight" activeCell="D1" sqref="D1"/>
          <selection pane="bottomLeft" activeCell="A4" sqref="A4"/>
          <selection pane="bottomRight" activeCell="E26" sqref="E26"/>
      </sheetView>
   </sheetViews>