Module: Writexlsx::Worksheet::Autofilter

Includes:
Constants
Included in:
Writexlsx::Worksheet
Defined in:
lib/write_xlsx/worksheet/autofilter.rb

Overview

Autofilter-related operations extracted from Worksheet to slim the main class.

Constant Summary

Constants included from Constants

Constants::COL_MAX, Constants::ROW_MAX, Constants::SHEETNAME_MAX, Constants::STR_MAX

Instance Method Summary collapse

Instance Method Details

#autofilter(row1, col1 = nil, row2 = nil, col2 = nil) ⇒ Object

:call-seq:

autofilter(first_row, first_col, last_row, last_col)

Set the autofilter area in the worksheet.



17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 17

def autofilter(row1, col1 = nil, row2 = nil, col2 = nil)
  if (row_col_array = row_col_notation(row1))
    _row1, _col1, _row2, _col2 = row_col_array
  else
    _row1 = row1
    _col1 = col1
    _row2 = row2
    _col2 = col2
  end
  return if [_row1, _col1, _row2, _col2].include?(nil)

  # Reverse max and min values if necessary.
  _row1, _row2 = _row2, _row1 if _row2 < _row1
  _col1, _col2 = _col2, _col1 if _col2 < _col1

  @autofilter_area = convert_name_area(_row1, _col1, _row2, _col2)
  @autofilter_ref  = xl_range(_row1, _row2, _col1, _col2)
  @filter_range    = [_col1, _col2]

  # Store the filter cell positions for use in the autofit calculation.
  (_col1.._col2).each do |col|
    @filter_cells["#{_row1}:#{col}"] = 1
  end
end

#filter_column(col, expression) ⇒ Object

Set the column filter criteria.

The filter_column method can be used to filter columns in a autofilter range based on simple conditions.



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 48

def filter_column(col, expression)
  raise "Must call autofilter before filter_column" unless @autofilter_area

  col = prepare_filter_column(col)

  tokens = extract_filter_tokens(expression)

  raise "Incorrect number of tokens in expression '#{expression}'" unless [3, 7].include?(tokens.size)

  tokens = parse_filter_expression(expression, tokens)

  # Excel handles single or double custom filters as default filters. We need
  # to check for them and handle them accordingly.
  if tokens.size == 2 && tokens[0] == 2
    # Single equality.
    filter_column_list(col, tokens[1])
  elsif tokens.size == 5 && tokens[0] == 2 && tokens[2] == 1 && tokens[3] == 2
    # Double equality with "or" operator.
    filter_column_list(col, tokens[1], tokens[4])
  else
    # Non default custom filter.
    @filter_cols[col] = Array.new(tokens)
    @filter_type[col] = 0
  end

  @filter_on = 1
end

#filter_column_list(col, *tokens) ⇒ Object

Set the column filter criteria in Excel 2007 list style.



79
80
81
82
83
84
85
86
87
88
89
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 79

def filter_column_list(col, *tokens)
  tokens.flatten!
  raise "Incorrect number of arguments to filter_column_list" if tokens.empty?
  raise "Must call autofilter before filter_column_list" unless @autofilter_area

  col = prepare_filter_column(col)

  @filter_cols[col] = tokens
  @filter_type[col] = 1           # Default style.
  @filter_on        = 1
end

#prepare_filter_column(col) ⇒ Object

:nodoc:



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 91

def prepare_filter_column(col) # :nodoc:
  # Check for a column reference in A1 notation and substitute.
  if col.to_s =~ /^\D/
    col_letter = col

    # Convert col ref to a cell ref and then to a col number.
    _dummy, col = substitute_cellref("#{col}1")
    raise "Invalid column '#{col_letter}'" if col >= COL_MAX
  end

  col_first, col_last = @filter_range

  # Reject column if it is outside filter range.
  raise "Column '#{col}' outside autofilter column range (#{col_first} .. #{col_last})" if col < col_first || col > col_last

  col
end

#write_auto_filter ⇒ Object

Write the element.



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 112

def write_auto_filter # :nodoc:
  return unless autofilter_ref?

  attributes = [
    ['ref', @autofilter_ref]
  ]

  if filter_on?
    # Autofilter defined active filters.
    @writer.tag_elements('autoFilter', attributes) do
      write_autofilters
    end
  else
    # Autofilter defined without active filters.
    @writer.empty_tag('autoFilter', attributes)
  end
end

#write_autofilters ⇒ Object

Function to iterate through the columns that form part of an autofilter range and write the appropriate filters.



134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 134

def write_autofilters # :nodoc:
  col1, col2 = @filter_range

  (col1..col2).each do |col|
    # Skip if column doesn't have an active filter.
    next unless @filter_cols[col]

    # Retrieve the filter tokens and write the autofilter records.
    tokens = @filter_cols[col]
    type   = @filter_type[col]

    # Filters are relative to first column in the autofilter.
    write_filter_column(col - col1, type, *tokens)
  end
end

#write_custom_filter(operator, val) ⇒ Object

Write the element.



220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 220

def write_custom_filter(operator, val) # :nodoc:
  operators = {
    1  => 'lessThan',
    2  => 'equal',
    3  => 'lessThanOrEqual',
    4  => 'greaterThan',
    5  => 'notEqual',
    6  => 'greaterThanOrEqual',
    7  => 'startsWith',
    8  => 'notStartsWith',
    9  => 'endsWith',
    10 => 'notEndsWith',
    11 => 'contains',
    12 => 'notContains',
    13 => 'between',
    14 => 'notBetween'
  }

  attributes = [
    ['operator', operators[operator]],
    ['val', val]
  ]

  @writer.empty_tag('customFilter', attributes)
end

#write_custom_filters(*tokens) ⇒ Object

Write the element.



195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 195

def write_custom_filters(*tokens) # :nodoc:
  if tokens.size == 2
    # One filter expression only.
    @writer.tag_elements('customFilters') { write_custom_filter(*tokens) }
  else
    # Two filter expressions.

    # Check if the "join" operand is "and" or "or".
    attributes = if tokens[2] == 0
                   [['and', 1]]
                 else
                   [['and', 0]]
                 end

    # Write the two custom filters.
    @writer.tag_elements('customFilters', attributes) do
      write_custom_filter(tokens[0], tokens[1])
      write_custom_filter(tokens[3], tokens[4])
    end
  end
end

#write_filter(val) ⇒ Object

Write the element.



188
189
190
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 188

def write_filter(val) # :nodoc:
  @writer.empty_tag('filter', [['val', val]])
end

#write_filter_column(col_id, type, *filters) ⇒ Object

Write the element.



153
154
155
156
157
158
159
160
161
162
163
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 153

def write_filter_column(col_id, type, *filters) # :nodoc:
  @writer.tag_elements('filterColumn', [['colId', col_id]]) do
    if type == 1
      # Type == 1 is the new XLSX style filter.
      write_filters(*filters)
    else
      # Type == 0 is the classic "custom" filter.
      write_custom_filters(*filters)
    end
  end
end

#write_filters(*filters) ⇒ Object

Write the element.



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/write_xlsx/worksheet/autofilter.rb', line 168

def write_filters(*filters) # :nodoc:
  non_blanks = filters.reject { |filter| filter.to_s =~ /^blanks$/i }
  attributes = []

  attributes = [['blank', 1]] if filters != non_blanks

  if filters.size == 1 && non_blanks.empty?
    # Special case for blank cells only.
    @writer.empty_tag('filters', attributes)
  else
    # General case.
    @writer.tag_elements('filters', attributes) do
      non_blanks.sort.each { |filter| write_filter(filter) }
    end
  end
end