Class: Axlsx::AutoFilter

Inherits:
Object
  • Object
show all
Defined in:
lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb

Overview

This class represents an auto filter range in a worksheet

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(worksheet) ⇒ AutoFilter

creates a new Autofilter object

Parameters:

Raises:

  • (ArgumentError) 


8
9
10
11
12
13
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 8

def initialize(worksheet)
  raise ArgumentError, 'you must provide a worksheet' unless worksheet.is_a?(Worksheet)

  @worksheet = worksheet
  @sort_on_generate = true
end

Instance Attribute Details

#range ⇒ String

The range the autofilter should be applied to. This should be a string like 'A1:B8'

Returns:

  • (String) 


20
21
22
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 20

def range
  @range
end

#sort_on_generate ⇒ Object

Returns the value of attribute sort_on_generate.



15
16
17
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 15

def sort_on_generate
  @sort_on_generate
end

#worksheet ⇒ Object (readonly)

Returns the value of attribute worksheet.



15
16
17
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 15

def worksheet
  @worksheet
end

Instance Method Details

#add_column(col_id, filter_type, options = {}) ⇒ FilterColumn

Adds a filter column. This is the recommended way to create and manage filter columns for your autofilter. In addition to the require id and type parameters, options will be passed to the filter column during instantiation.

Parameters:

  • col_id (String) —

    Zero-based index indicating the AutoFilter column to which this filter information applies.

  • filter_type (Symbol) —

    A symbol representing one of the supported filter types.

  • options (Hash) (defaults to: {}) —

    a hash of options to pass into the generated filter

Returns:



62
63
64
65
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 62

def add_column(col_id, filter_type, options = {})
  columns << FilterColumn.new(col_id, filter_type, options)
  columns.last
end

#apply ⇒ Object

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.



69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 69

def apply
  first_cell, last_cell = range.split(':')
  start_point = Axlsx.name_to_indices(first_cell)
  end_point = Axlsx.name_to_indices(last_cell)
  # 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 == 0
      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|
      next if row.hidden

      column.apply(row, column_offset)
    end
  end
end

#columns ⇒ SimpleTypedList

A collection of filterColumns for this auto_filter

Returns:

  • (SimpleTypedList) 


52
53
54
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 52

def columns
  @columns ||= SimpleTypedList.new FilterColumn
end

#defined_name ⇒ String

the formula for the defined name required for this auto filter This prepends the worksheet name to the absolute cell reference e.g. A1:B2 -> 'Sheet1'!$A$1:$B$2

Returns:

  • (String) 


26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 26

def defined_name
  return unless range

  cells = range.split(':').collect do |name|
    cell = worksheet.name_to_cell(name)
    next cell if cell

    # We're calculating the defined_name for the AutoFilter just before serializing,
    # so in theory adding new rows or columns should not cause weird offset issues
    col_index, row_index = *Axlsx.name_to_indices(name)
    while (row = worksheet.rows[row_index]).nil?
      worksheet.add_row
    end

    while (cell = row[col_index]).nil?
      row.add_cell
    end

    cell
  end

  Axlsx.cell_range(cells)
end

#sort_state ⇒ SortState

the SortState object for this AutoFilter

Returns:



123
124
125
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 123

def sort_state
  @sort_state ||= SortState.new self
end

#to_xml_string(str = +'')) ⇒ String

serialize the object

Returns:

  • (String) 


138
139
140
141
142
143
144
145
146
147
 
# File 'lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb', line 138

def to_xml_string(str = +'')
  return unless range

  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