diff options
| -rw-r--r-- | lib/axlsx/util/constants.rb | 7 | ||||
| -rw-r--r-- | lib/axlsx/util/validators.rb | 32 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb | 4 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb | 50 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/auto_filter/filters.rb | 206 | ||||
| -rw-r--r-- | test/workbook/worksheet/auto_filter/tc_auto_filter.rb (renamed from test/workbook/worksheet/tc_auto_filter.rb) | 0 | ||||
| -rw-r--r-- | test/workbook/worksheet/auto_filter/tc_filter_column.rb | 8 | ||||
| -rw-r--r-- | test/workbook/worksheet/auto_filter/tc_filters.rb | 36 |
8 files changed, 304 insertions, 39 deletions
diff --git a/lib/axlsx/util/constants.rb b/lib/axlsx/util/constants.rb index 28460e8b..a83f76e6 100644 --- a/lib/axlsx/util/constants.rb +++ b/lib/axlsx/util/constants.rb @@ -263,11 +263,14 @@ module Axlsx # error message for RegexValidator ERR_REGEX = "Invalid Data. %s does not match %s." + # error message for RangeValidator + ERR_RANGE = "Invalid Data. %s must be between %s and %s, (inclusive:%s) you gave: %s" + # error message for sheets that use a name which is longer than 31 bytes ERR_SHEET_NAME_TOO_LONG = "Your worksheet name '%s' is too long. Worksheet names must be 31 characters (bytes) or less" - + # error message for sheets that use a name which includes a colon - + ERR_SHEET_NAME_COLON_FORBIDDEN = "Your worksheet name '%s' contains a colon, which is not allowed by MS Excel and will cause repair warnings. Please change the name of your sheet." # error message for duplicate sheet names diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb index f168bb5e..142f6dde 100644 --- a/lib/axlsx/util/validators.rb +++ b/lib/axlsx/util/validators.rb @@ -14,6 +14,24 @@ module Axlsx end end + # Validate that the value provided is between a specific range + # Note that no data conversions will be done for you! + # Comparisons will be made using < and > or <= and <= when the inclusive parameter is true + class RangeValidator + # @param [String] name The name of what is being validated + # @param [Any] min The minimum allowed value + # @param [Any] max The maximum allowed value + # @param [Any] value The value to be validated + # @param [Boolean] inclusive Flag indicating if the comparison should be inclusive. + def self.validate(name, min, max, value, inclusive = true) + passes = if inclusive + min <= value && value <= max + else + min < value && value < max + end + raise ArgumentError, (ERR_RANGE % [v.inspect, min.to_s, max.to_s, inclusive]) unless passes + end + end # Validates the value against the regular expression provided. class RegexValidator # @param [String] name The name of what is being validated. This is included in the output when the value is invalid @@ -112,7 +130,7 @@ module Axlsx def self.validate_scale_10_400(v) DataTypeValidator.validate "page_scale", [Fixnum, Integer], v, lambda { |arg| arg >= 10 && arg <= 400 } end - + # Requires that the value is an integer ranging from 10 to 400 or 0. def self.validate_scale_0_10_400(v) DataTypeValidator.validate "page_scale", [Fixnum, Integer], v, lambda { |arg| arg == 0 || (arg >= 10 && arg <= 400) } @@ -129,7 +147,7 @@ module Axlsx # @param [Any] v The value validated def self.validate_pattern_type(v) RestrictionValidator.validate :pattern_type, [:none, :solid, :mediumGray, :darkGray, :lightGray, :darkHorizontal, :darkVertical, :darkDown, :darkUp, :darkGrid, - :darkTrellis, :lightHorizontal, :lightVertical, :lightDown, :lightUp, :lightGrid, :lightTrellis, :gray125, :gray0625], v + :darkTrellis, :lightHorizontal, :lightVertical, :lightDown, :lightUp, :lightGrid, :lightTrellis, :gray125, :gray0625], v end # Requires that the value is one of the ST_TimePeriod types @@ -226,7 +244,7 @@ module Axlsx def self.validate_data_validation_error_style(v) RestrictionValidator.validate :validate_data_validation_error_style, [:information, :stop, :warning], v end - + # Requires that the value is valid data validation operator. # valid operators must be one of lessThan, lessThanOrEqual, equal, # notEqual, greaterThanOrEqual, greaterThan, between, notBetween @@ -234,28 +252,28 @@ module Axlsx def self.validate_data_validation_operator(v) RestrictionValidator.validate :data_validation_operator, [:lessThan, :lessThanOrEqual, :equal, :notEqual, :greaterThanOrEqual, :greaterThan, :between, :notBetween], v end - + # Requires that the value is valid data validation type. # valid types must be one of custom, data, decimal, list, none, textLength, time, whole # @param [Any] v The value validated def self.validate_data_validation_type(v) RestrictionValidator.validate :data_validation_type, [:custom, :data, :decimal, :list, :none, :textLength, :time, :whole], v end - + # Requires that the value is a valid sheet view type. # valid types must be one of normal, page_break_preview, page_layout # @param [Any] v The value validated def self.validate_sheet_view_type(v) RestrictionValidator.validate :sheet_view_type, [:normal, :page_break_preview, :page_layout], v end - + # Requires that the value is a valid active pane type. # valid types must be one of bottom_left, bottom_right, top_left, top_right # @param [Any] v The value validated def self.validate_pane_type(v) RestrictionValidator.validate :active_pane_type, [:bottom_left, :bottom_right, :top_left, :top_right], v end - + # Requires that the value is a valid split state type. # valid types must be one of frozen, frozen_split, split # @param [Any] v The value validated diff --git a/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb b/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb index a2e82835..c2b31156 100644 --- a/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb +++ b/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb @@ -51,7 +51,9 @@ module Axlsx # @return [String] def to_xml_string(str='') return unless range - str << "<autoFilter ref='#{range}'></autoFilter>" + str << "<autoFilter ref='#{range}'>" + colums.each { |filter_column| filter_column.to_xml_string(str) } + str << "</autoFilter>" end end diff --git a/lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb b/lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb index 57f7e23b..3de6d6a6 100644 --- a/lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb +++ b/lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb @@ -5,25 +5,8 @@ module Axlsx # then there is no corresponding filterColumn collection expressed for that column. class FilterColumn - FILTERS = [:filters, :top10, :custom_filters, :dynamic_filters, :color_filters, :icon_filters] - # Zero-based index indicating the AutoFilter column to which this filter information applies. - # @return [Integer] - attr_reader :col_id - - # Flag indicating whether the AutoFilter button for this column is hidden. - # @return [Boolean] - attr_reader :hidden_button - - # Flag indicating whether the filter button is visible. - # When the cell containing the filter button is merged with another cell, - # the filter button can be hidden, and not drawn. - # @return [Boolean] - attr_reader :show_button - - # Filter information applied to the column - # @return [Filters|Top10|CustomFilters|dynamicFilters|colorFilters|iconFilters] The filter - # information for this filter columns. - attr_reader :filter + # Allowed filters + FILTERS = [:filters] #, :top10, :custom_filters, :dynamic_filters, :color_filters, :icon_filters] # Creates a new FilterColumn object # @note This class yeilds its filter object as that is where the vast majority of processing will be done @@ -32,7 +15,6 @@ module Axlsx # @param [Hash] options options for this object and the filter # @option [Boolean] hidden_button @see hidden_button # @option [Boolean] show_button @see show_button - def initialize(col_id, filter_type, options = {}) RestrictionValidator.validate 'FilterColumn.filter', FILTERS, filter_type self.col_id = col_id @@ -43,6 +25,24 @@ module Axlsx yield @filter if block_given? end + # Zero-based index indicating the AutoFilter column to which this filter information applies. + # @return [Integer] + attr_reader :col_id + + # Flag indicating whether the filter button is visible. + # When the cell containing the filter button is merged with another cell, + # the filter button can be hidden, and not drawn. + # @return [Boolean] + def show_button + @show_button ||= true + end + + # Flag indicating whether the AutoFilter button for this column is hidden. + # @return [Boolean] + def hidden_button + @hidden_button ||= false + end + # Sets the col_id attribute for this filter column. # @param [Integer | Cell] column_index The zero based index of the column to which this filter applies. # When you specify a cell, the column index will be read off the cell @@ -53,18 +53,18 @@ module Axlsx @col_id = column_index end - # Sets the button_hidden attribute for this filter column # @param [Boolean] hidden Flag indicating whether the AutoFilter button for this column is hidden. # @return [Boolean] - def button_hidden=(hidden) + def hidden_button=(hidden) DataValidater.validate_boolean hidden - @button_hidden = hidden + @hidden_button = hidden end # Serialize the object to xml def to_xml_string(str='') - + str << "<filterColumn colId='#{@col_id}' hiddenButton='#{@hidden_button}' showButton='#{@show_button}'>" + @filter.to_xml_string(str) + str << "</filterColumn>" end - end end diff --git a/lib/axlsx/workbook/worksheet/auto_filter/filters.rb b/lib/axlsx/workbook/worksheet/auto_filter/filters.rb index f37e2f29..d754f433 100644 --- a/lib/axlsx/workbook/worksheet/auto_filter/filters.rb +++ b/lib/axlsx/workbook/worksheet/auto_filter/filters.rb @@ -1,13 +1,211 @@ module Axlsx + # When multiple values are chosen to filter by, or when a group of date values are chosen to filter by, + # this object groups those criteria together. class Filters + # Allowed calendar types + CALENDAR_TYPES = %w(gregorian gregorianUs gregorianMeFrench gregorianArabic hijri hebrew taiwan japan thai korea saka gregorianXlitEnglish gregorianXlitFrench none) + + # Creates a new Filters object + # @param [Hash] options Options used to set this objects attributes and + # create filter and/or date group items + # @option [Boolean] blank @see blank + # @option [String] calendar_type @see calendar_type + # @option [Array] filter_items An array of values that will be used to create filter objects. + # @option [Array] date_group_items An array of hases defining date group item filters to apply. + # @note The recommended way to interact with filter objects is via AutoFilter#add_column + # @example + # ws.auto_filter.add_column(0, :filters, :blank => true, :calendar_type => 'japan', :filter_items => [100, 'a']) def initialize(options={}) - options[:filter_items].each do |filter| - @filters << Filter.new(filter) + options.each do |key, value| + self.send("#{key}=", value) if self.respond_to? "#{key}=" + end + end + + # Flag indicating whether to filter by blank. + # @return [Boolean] + def blank + @blank ||= false + end + + # Calendar type for date grouped items. + # Used to interpret the values in dateGroupItem. + # This is the calendar type used to evaluate all dates in the filter column, + # even when those dates are not using the same calendar system / date formatting. + def calendar_type + @calendar_type ||= CALENDAR_TYPES.first + end + + # The filter values in this filters object + def filter_items + @filter_items ||= [] + end + + # the date group values in this filters object + def date_group_items + @date_group_items ||= [] + end + + # @see calendar_type + # @param [String] calendar The calendar type to use. This must be one of the types defined in CALENDAR_TYPES + # @return [String] + def calendar_type=(calendar) + RestrictionValidator.validate 'Filters.calendar_type', CALENDAR_TYPES, calendar + @calendar_type = calendar + end + + def blank=(use_blank) + Axlsx.validate_boolean use_blank + @blank = use_blank + end + + # Serialize the object to xml + def to_xml_string(str = '') + str << "<filters blank='#{@blank}' calendarType='#{@calendar_type}'>" + @filters.each { |filter| filter.to_xml_string(str) } + @date_group_items.each { |date_group_item| date_group_item.to_xml_string(str) } + str << '</filters>' + end + + def filter_items=(values) + values.each do |value| + filter_items << Filter.new(value) + end + end + + def date_group_items=(options) + options.each do |date_group| + raise ArgumentError, "date_group_items should be an array of hashes specifying the options for each date_group_item" unless date_group.is_a?(Hash) + date_group_items << DateGroupItem.new(date_group) + end + end + + private + + # This class expresses a filter criteria value. + class Filter + + # Creates a new filter value object + # @param [Any] value The value of the filter. This is not restricted, but + # will be serialized via to_s so if you are passing an object + # be careful. + def initialize(value) + @val = value + end + + + #Filter value used in the criteria. + attr_accessor :val + + # Serializes the filter value object + # @param [String] str The string to concact the serialization information to. + def to_xml_string(str = '') + str << "<filter val='#{@val.to_s}' />" + end + end + + + # This collection is used to express a group of dates or times which are + # used in an AutoFilter criteria. Values are always written in the calendar + # type of the first date encountered in the filter range, so that all + # subsequent dates, even when formatted or represented by other calendar + # types, can be correctly compared for the purposes of filtering. + class DateGroupItem + + DATE_TIME_GROUPING = %w(year month day hour minute second) + + def initialize(options={}) + raise ArgumentError, "You must specify a year for date time grouping" unless options[:year] + raise ArgumentError, "You must specify a date_time_grouping when creating a DateGroupItem for auto filter" unless options[:date_time_grouping] + options.each do |key, value| + self.send("#{key}=", value) if self.respond_to?("#{key}=") + end end - options[:date_group_items].each do |date_group| - @date_group_items << DateGroupItem.new(date_group) + + # Grouping level + # This must be one of year, month, day, hour, minute or second. + # @return [String] + attr_reader :date_time_grouping + + # Year (4 digits) + # @return [Integer|String] + attr_reader :year + + # Month (1..12) + # @return [Integer] + attr_reader :month + + # Day (1-31) + # @return [Integer] + attr_reader :day + + # Hour (0..23) + # @return [Integer] + attr_reader :hour + + # Minute (0..59( + # @return [Integer] + attr_reader :minute + + # Second (0..59) + # @return [Integer] + attr_reader :second + + # The year value for the date group item + # This must be a four digit value + def year=(value) + RegexValidator.validate "DateGroupItem.year", /\d{4}/, value + @year = value + end + + # The month value for the date group item + # This must be between 1 and 12 + def month=(value) + RangeValidator.validate "DateGroupItem.month", 0, 12, value + @month = value + end + + # The day value for the date group item + # This must be between 1 and 31 + # @note no attempt is made to ensure the date value is valid for any given month + def day=(value) + RangeValidator.validate "DateGroupItem.day", 0, 31, value + @day = value + end + + # The hour value for the date group item + # # this must be between 0 and 23 + def hour=(value) + RangeValidator.validate "DateGroupItem.hour", 0, 23, value + @hour = value + end + + # The minute value for the date group item + # This must be between 0 and 59 + def minute=(value) + RangeValidator.validate "DateGroupItem.minute", 0, 59, value + @minute = value + end + + # The second value for the date group item + # This must be between 0 and 59 + def second=(value) + RangeValidator.validate "DateGroupItem.second", 0, 59, value + @second = value + end + + def date_time_grouping=(grouping) + RestrictionValidator.validate 'DateGroupItem.date_time_grouping', DATE_TIME_GROUPING, grouping.to_s + @date_time_grouping = grouping.to_s + end + + # Serialize the object to xml + # @param [String] str The string object this serialization will be concatenated to. + def to_xml_string(str = '') + str << '<dateGroupItem ' + instance_values.each { |key, value| str << "#{key}='#{value.to_s}' " } + str << '/>' end end end diff --git a/test/workbook/worksheet/tc_auto_filter.rb b/test/workbook/worksheet/auto_filter/tc_auto_filter.rb index 77ee2b2c..77ee2b2c 100644 --- a/test/workbook/worksheet/tc_auto_filter.rb +++ b/test/workbook/worksheet/auto_filter/tc_auto_filter.rb diff --git a/test/workbook/worksheet/auto_filter/tc_filter_column.rb b/test/workbook/worksheet/auto_filter/tc_filter_column.rb new file mode 100644 index 00000000..ff3da259 --- /dev/null +++ b/test/workbook/worksheet/auto_filter/tc_filter_column.rb @@ -0,0 +1,8 @@ +require 'tc_helper.rb' + +class TestFilterColumn < Test::Unit::TestCase + + def setup + + end +end diff --git a/test/workbook/worksheet/auto_filter/tc_filters.rb b/test/workbook/worksheet/auto_filter/tc_filters.rb new file mode 100644 index 00000000..dae85b19 --- /dev/null +++ b/test/workbook/worksheet/auto_filter/tc_filters.rb @@ -0,0 +1,36 @@ +require 'tc_helper.rb' + +class TestFilters < Test::Unit::TestCase + def setup + @filters = Axlsx::Filters.new(:filter_items => [1, 'a'], :date_group_items =>[ { :date_time_grouping => :year, :year => 2012 } ] , :blank => true) + end + + def test_initialize + assert_equal Axlsx::Filters::CALENDAR_TYPES.first, @filters.calendar_type + end + + def blank + assert_equal false, @filters.blank + assert_raise(ArgumentError) { @filters.blank = :only_if_you_want_it } + @filters.blank = true + assert_equal true, @filters.blank + end + + def test_calendar_type + assert_raise(ArgumentError) { @filters.calendar_type = 'monkey calendar' } + @filters.calendar_type = 'japan' + assert_equal('japan', @filters.calendar_type) + end + + def test_filters_items + assert @filters.filter_items.is_a?(Array) + assert_equal 2, @filters.filter_items.size + end + + def test_date_group_items + assert @filters.date_group_items.is_a?(Array) + assert_equal 1, @filters.date_group_items.size + end + +end + |