diff options
| -rw-r--r-- | CHANGELOG.md | 1 | ||||
| -rw-r--r-- | examples/images/sort_state_example_1.png | bin | 0 -> 62716 bytes | |||
| -rw-r--r-- | examples/images/sort_state_example_2.png | bin | 0 -> 64787 bytes | |||
| -rw-r--r-- | examples/sort_state_example.md | 42 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb | 61 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/auto_filter/sort_condition.rb | 51 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/auto_filter/sort_state.rb | 56 | ||||
| -rw-r--r-- | test/workbook/worksheet/auto_filter/tc_sort_condition.rb | 37 | ||||
| -rw-r--r-- | test/workbook/worksheet/auto_filter/tc_sort_state.rb | 36 |
9 files changed, 281 insertions, 3 deletions
diff --git a/CHANGELOG.md b/CHANGELOG.md index c7e0fc8b..67ca1cfe 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -7,6 +7,7 @@ CHANGELOG - Remove ability to set `u=` to true in favor of using :single or one of the other underline options - Fix `Workbook#sheet_by_name` not returning sheets with encoded characters in the name - Raise exception if `axlsx_styler` gem is present as its code was merged directly into `caxlsx` in v3.3.0 + - Add 'SortState' and 'SortCondition' classes to the 'AutoFilter' class to add sorting to the generated file. - **April.23.23**: 3.4.1 - [PR #209](https://github.com/caxlsx/caxlsx/pull/209) - Revert characters other than `=` being considered as formulas. diff --git a/examples/images/sort_state_example_1.png b/examples/images/sort_state_example_1.png Binary files differnew file mode 100644 index 00000000..fb13d387 --- /dev/null +++ b/examples/images/sort_state_example_1.png diff --git a/examples/images/sort_state_example_2.png b/examples/images/sort_state_example_2.png Binary files differnew file mode 100644 index 00000000..c84b6848 --- /dev/null +++ b/examples/images/sort_state_example_2.png diff --git a/examples/sort_state_example.md b/examples/sort_state_example.md new file mode 100644 index 00000000..d0258c31 --- /dev/null +++ b/examples/sort_state_example.md @@ -0,0 +1,42 @@ +## Description + +You could add sort conditions to the sort state of an auto filtered table + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Sort State') do |sheet| + sheet.add_row ['Number', 'Letter', 'Priority'] + sheet.add_row [1, 'B', 'high'] + sheet.add_row [2, 'B', 'low'] + sheet.add_row [3, 'B', 'medium'] + sheet.add_row [4, 'B', 'high'] + sheet.add_row [5, 'B', 'low'] + sheet.add_row [6, 'B', 'medium'] + sheet.add_row [7, 'A', 'high'] + sheet.add_row [8, 'A', 'low'] + sheet.add_row [9, 'A', 'medium'] + sheet.add_row [10, 'A', 'high'] + sheet.add_row [11, 'A', 'low'] + sheet.add_row [12, 'A', 'medium'] + sheet.auto_filter = 'A1:C13' + sheet.auto_filter.sort_state.add_sort_condition column_index: 1 + sheet.auto_filter.sort_state.add_sort_condition column_index: 2, custom_list: ['low', 'medium', 'high'] + sheet.auto_filter.sort_state.add_sort_condition column_index: 0, order: :desc +end + +p.serialize 'sort_state_example.xlsx' +``` + +## Output + + + +After adding the sort conditions: + + 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 diff --git a/test/workbook/worksheet/auto_filter/tc_sort_condition.rb b/test/workbook/worksheet/auto_filter/tc_sort_condition.rb new file mode 100644 index 00000000..67e4bdac --- /dev/null +++ b/test/workbook/worksheet/auto_filter/tc_sort_condition.rb @@ -0,0 +1,37 @@ +# frozen_string_literal: true + +require 'tc_helper' + +class TestSortCondition < Test::Unit::TestCase + def setup + ws = Axlsx::Package.new.workbook.add_worksheet + ws.add_row ['first', 'second', 'third'] + 3.times { |index| ws.add_row [1 * index, 2 * index, 3 * index] } + ws.auto_filter = 'A1:C4' + @auto_filter = ws.auto_filter + @auto_filter.sort_state.add_sort_condition(column_index: 0) + @auto_filter.sort_state.add_sort_condition(column_index: 1, order: :desc) + @auto_filter.sort_state.add_sort_condition(column_index: 2, custom_list: ['low', 'middle', 'high']) + @sort_state = @auto_filter.sort_state + @sort_conditions = @sort_state.sort_conditions + end + + def test_ref_to_single_column + assert_equal('A2:A4', @sort_conditions[0].ref_to_single_column('A2:C4', 0)) + end + + def test_to_xml_string + doc = Nokogiri::XML(@sort_state.to_xml_string) + + assert_equal(3, doc.xpath("sortState//sortCondition").size) + assert_equal('A2:A4', doc.xpath("sortState//sortCondition")[0].attribute('ref').value) + assert_nil doc.xpath("sortState//sortCondition")[0].attribute('descending') + assert_nil doc.xpath("sortState//sortCondition")[0].attribute('customList') + assert_equal('1', doc.xpath("sortState//sortCondition")[1].attribute('descending').value) + assert_equal('B2:B4', doc.xpath("sortState//sortCondition")[1].attribute('ref').value) + assert_nil doc.xpath("sortState//sortCondition")[1].attribute('customList') + assert_equal('C2:C4', doc.xpath("sortState//sortCondition")[2].attribute('ref').value) + assert_equal('low,middle,high', doc.xpath("sortState//sortCondition")[2].attribute('customList').value) + assert_nil doc.xpath("sortState//sortCondition")[2].attribute('descending') + end +end diff --git a/test/workbook/worksheet/auto_filter/tc_sort_state.rb b/test/workbook/worksheet/auto_filter/tc_sort_state.rb new file mode 100644 index 00000000..db3d03c0 --- /dev/null +++ b/test/workbook/worksheet/auto_filter/tc_sort_state.rb @@ -0,0 +1,36 @@ +# frozen_string_literal: true + +require 'tc_helper' + +class TestSortState < Test::Unit::TestCase + def setup + ws = Axlsx::Package.new.workbook.add_worksheet + ws.add_row ['first', 'second', 'third'] + 3.times { |index| ws.add_row [1 * index, 2 * index, 3 * index] } + ws.auto_filter = 'A1:C4' + @auto_filter = ws.auto_filter + @auto_filter.sort_state.add_sort_condition(column_index: 0) + @sort_state = @auto_filter.sort_state + end + + def test_sort_conditions + assert @sort_state.sort_conditions.is_a?(Axlsx::SimpleTypedList) + assert_equal @sort_state.sort_conditions.allowed_types, [Axlsx::SortCondition] + end + + def test_add_sort_conditions + @sort_state.add_sort_condition(column_index: 0) do |condition| + assert condition.is_a? SortCondition + end + end + + def test_increment_cell_value + assert_equal('A2', @sort_state.increment_cell_value('A1')) + end + + def test_to_xml_string + doc = Nokogiri::XML(@sort_state.to_xml_string) + + assert_equal('A2:C4', doc.xpath("sortState")[0].attribute('ref').value) + end +end |
