Added sorting to the AutoFilter class - add sort conditions to the xml (#286)

---------

Co-authored-by: SarahVanHaute <[email protected]>
Co-authored-by: Geremia Taglialatela <[email protected]>

3 files changed, 165 insertions, 3 deletions

diff --git a/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb b/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb
index b8acfc51..56192336 100644
--- a/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb
+++ b/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb
@@ -2,6 +2,7 @@
 
 require 'axlsx/workbook/worksheet/auto_filter/filter_column'
 require 'axlsx/workbook/worksheet/auto_filter/filters'
+require 'axlsx/workbook/worksheet/auto_filter/sort_state'
 
 module Axlsx
   # This class represents an auto filter range in a worksheet
@@ -12,9 +13,10 @@ module Axlsx
       raise ArgumentError, 'you must provide a worksheet' unless worksheet.is_a?(Worksheet)
 
       @worksheet = worksheet
+      @sort_on_generate = true
     end
 
-    attr_reader :worksheet
+    attr_reader :worksheet, :sort_on_generate
 
     # The range the autofilter should be applied to.
     # This should be a string like 'A1:B8'
@@ -48,8 +50,8 @@ module Axlsx
       columns.last
     end
 
-    # actually performs the filtering of rows who's cells do not
-    # match the filter.
+    # Performs the sorting of the rows based on the sort_state conditions. Then it actually performs
+    # the filtering of rows who's cells do not match the filter.
     def apply
       first_cell, last_cell = range.split(':')
       start_point = Axlsx.name_to_indices(first_cell)
@@ -57,6 +59,41 @@ module Axlsx
       # The +1 is so we skip the header row with the filter drop downs
       rows = worksheet.rows[(start_point.last + 1)..end_point.last] || []
 
+      # the sorting of the rows if sort_conditions are available.
+      if !sort_state.sort_conditions.empty? && sort_on_generate
+        sort_conditions = sort_state.sort_conditions
+        sorted_rows = rows.sort do |row1, row2|
+          comparison = 0
+
+          sort_conditions.each do |condition|
+            cell_value_row1 = row1.cells[condition.column_index + start_point.first].value
+            cell_value_row2 = row2.cells[condition.column_index + start_point.first].value
+            custom_list = condition.custom_list
+            comparison = if cell_value_row1.nil? || cell_value_row2.nil?
+                           cell_value_row1.nil? ? 1 : -1
+                         elsif custom_list.empty?
+                           condition.order == :asc ? cell_value_row1 <=> cell_value_row2 : cell_value_row2 <=> cell_value_row1
+                         else
+                           index1 = custom_list.index(cell_value_row1) || custom_list.size
+                           index2 = custom_list.index(cell_value_row2) || custom_list.size
+
+                           condition.order == :asc ? index1 <=> index2 : index2 <=> index1
+                         end
+
+            break unless comparison.zero?
+          end
+
+          comparison
+        end
+        insert_index = start_point.last + 1
+
+        sorted_rows.each do |row|
+          # Insert the row at the specified index
+          worksheet.rows[insert_index] = row
+          insert_index += 1
+        end
+      end
+
       column_offset = start_point.first
       columns.each do |column|
         rows.each do |row|
@@ -67,6 +104,21 @@ module Axlsx
       end
     end
 
+    # the SortState object for this AutoFilter
+    # @return [SortState]
+    def sort_state
+      @sort_state ||= SortState.new self
+    end
+
+    # @param [Boolean] Flag indicating whether the AutoFilter should sort the rows when generating the
+    # file. If false, the sorting rules will need to be applied manually after generating to alter
+    # the order of the rows.
+    # @return [Boolean]
+    def sort_on_generate=(v)
+      Axlsx.validate_boolean v
+      @sort_on_generate = v
+    end
+
     # serialize the object
     # @return [String]
     def to_xml_string(str = +'')
@@ -74,6 +126,9 @@ module Axlsx
 
       str << "<autoFilter ref='#{range}'>"
       columns.each { |filter_column| filter_column.to_xml_string(str) }
+      unless @sort_state.nil?
+        @sort_state.to_xml_string(str)
+      end
       str << "</autoFilter>"
     end
   end
diff --git a/lib/axlsx/workbook/worksheet/auto_filter/sort_condition.rb b/lib/axlsx/workbook/worksheet/auto_filter/sort_condition.rb
new file mode 100644
index 00000000..1d5e9241
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/auto_filter/sort_condition.rb
@@ -0,0 +1,51 @@
+# frozen_string_literal: true
+
+module Axlsx
+  # This class represents a individual sort condition belonging to the sort state of an auto filter
+  class SortCondition
+    # Creates a new SortCondition object
+    # @param [Integer] column_index Zero-based index indicating the AutoFilter column to which the sorting should be applied to
+    # @param [Symbol] The order the column should be sorted on, can only be :asc or :desc
+    # @param [Array] An array containg a custom sorting list in order.
+    def initialize(column_index:, order:, custom_list:)
+      Axlsx.validate_int column_index
+      @column_index = column_index
+
+      RestrictionValidator.validate 'SortCondition.order', [:asc, :desc], order
+      @order = order
+
+      DataTypeValidator.validate :sort_condition_custom_list, Array, custom_list
+      @custom_list = custom_list
+    end
+
+    attr_reader :column_index, :order, :custom_list
+
+    # converts the ref String from the sort_state to a string representing the ref of a single column
+    # for the xml string to be returned.
+    def ref_to_single_column(ref, column_index)
+      first_cell, last_cell = ref.split(':')
+
+      start_point = Axlsx.name_to_indices(first_cell)
+
+      first_row = first_cell[/\d+/]
+      last_row = last_cell[/\d+/]
+
+      first_column = Axlsx.col_ref(column_index + start_point.first)
+      last_column = first_column
+
+      "#{first_column}#{first_row}:#{last_column}#{last_row}"
+    end
+
+    # serialize the object
+    # @return [String]
+    def to_xml_string(str, ref)
+      ref = ref_to_single_column(ref, column_index)
+
+      str << "<sortCondition "
+      str << "descending='1' " if order == :desc
+      str << "ref='#{ref}' "
+      str << "customList='#{custom_list.join(',')}' " unless custom_list.empty?
+      str << "/>"
+    end
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/auto_filter/sort_state.rb b/lib/axlsx/workbook/worksheet/auto_filter/sort_state.rb
new file mode 100644
index 00000000..baa6c134
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/auto_filter/sort_state.rb
@@ -0,0 +1,56 @@
+# frozen_string_literal: true
+
+require 'axlsx/workbook/worksheet/auto_filter/sort_condition'
+
+module Axlsx
+  # This class performs sorting on a range in a worksheet
+  class SortState
+    # creates a new SortState object
+    # @param [AutoFilter] the auto_filter that this sort_state belongs to
+    def initialize(auto_filter)
+      @auto_filter = auto_filter
+    end
+
+    # A collection of SortConditions for this sort_state
+    # @return [SimpleTypedList]
+    def sort_conditions
+      @sort_conditions ||= SimpleTypedList.new SortCondition
+    end
+
+    # Adds a SortCondition to the sort_state. This is the recommended way to add conditions to it.
+    # It requires a column_index for the sorting, descending and the custom order are optional.
+    # @param [Integer] column_index Zero-based index indicating the AutoFilter column to which the sorting should be applied to
+    # @param [Symbol] The order the column should be sorted on, can only be :asc or :desc
+    # @param [Array] An array containg a custom sorting list in order.
+    # @return [SortCondition]
+    def add_sort_condition(column_index:, order: :asc, custom_list: [])
+      sort_conditions << SortCondition.new(column_index: column_index, order: order, custom_list: custom_list)
+      sort_conditions.last
+    end
+
+    # method to increment the String representing the first cell of the range of the autofilter by 1 row for the sortCondition
+    # xml string
+    def increment_cell_value(str)
+      letter = str[/[A-Za-z]+/]
+      number = str[/\d+/].to_i
+
+      incremented_number = number + 1
+
+      "#{letter}#{incremented_number}"
+    end
+
+    # serialize the object
+    # @return [String]
+    def to_xml_string(str = +'')
+      return if sort_conditions.empty?
+
+      ref = @auto_filter.range
+      first_cell, last_cell = ref.split(':')
+      ref = "#{increment_cell_value(first_cell)}:#{last_cell}"
+
+      str << "<sortState xmlns:xlrd2='http://schemas.microsoft.com/office/spreadsheetml/2017/richdata2' ref='#{ref}'>"
+      sort_conditions.each { |sort_condition| sort_condition.to_xml_string(str, ref) }
+      str << "</sortState>"
+    end
+  end
+end