diff options
Diffstat (limited to 'lib')
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 |