diff options
20 files changed, 727 insertions, 56 deletions
@@ -149,6 +149,13 @@ This gem has 100% test coverage using test/unit. To execute tests for this gem, #Change log --------- +- **September.??.12**: 1.2.4 + - added stored autowidth filter values and date grouping items + - Improved support for autowidth when custom styles are applied + - Added support for table style info that lets you take advantage of + all the predefined table styles. + - Improved style management for fonts so they merge undefined values + from the initial master. - **September.8.12**: 1.2.3 - enhance exponential float/bigdecimal values rendering as strings intead of 'numbers' in excel. @@ -258,6 +265,9 @@ done without the help of the people below. [straydogstudio](https://github.com/straydocstudio) - For making an AWESOME axlsx templating gem for rails. [MitchellAJ](https://github.com/MitchellAJ) - For catching a bug in font_size calculations, finding some old code in an example and above all for reporting all of that brilliantly + +[ebenoist](https://github.com/ebenoist) - For taking control of control characters and keeping what is between the lines, between the lines. + #Copyright and License ---------- diff --git a/examples/auto_filter.rb b/examples/auto_filter.rb new file mode 100644 index 00000000..fcfe2e90 --- /dev/null +++ b/examples/auto_filter.rb @@ -0,0 +1,16 @@ +#!/usr/bin/env ruby -w -s +# -*- coding: utf-8 -*- + +$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" +require 'axlsx' +Axlsx::Package.new do |p| + p.workbook.add_worksheet(:name => "Table") do |sheet| + sheet.add_row ["Build Matrix"] + sheet.add_row ["Build", "Duration", "Finished", "Rvm"] + sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] + sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] + sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] + sheet.auto_filter = 'A2:D5' + sheet.auto_filter.add_column 3, :filters, :filter_items => ['1.9.2'] + end +end.serialize('auto_filter.xlsx') diff --git a/examples/example.rb b/examples/example.rb index 3af276d5..9ba1ab73 100755 --- a/examples/example.rb +++ b/examples/example.rb @@ -309,13 +309,13 @@ end ##Tables #```ruby -wb.add_worksheet(:name => "Table", :style_info => { :name => "TableStyleMedium23" }) do |sheet| +wb.add_worksheet(:name => "Table") do |sheet| sheet.add_row ["Build Matrix"] sheet.add_row ["Build", "Duration", "Finished", "Rvm"] sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.add_table "A2:D5", :name => 'Build Matrix' + sheet.add_table "A2:D5", :name => 'Build Matrix', :style_info => { :name => "TableStyleMedium23" } end #``` diff --git a/lib/axlsx.rb b/lib/axlsx.rb index 50f34c70..85a7b7c9 100644 --- a/lib/axlsx.rb +++ b/lib/axlsx.rb @@ -95,6 +95,7 @@ module Axlsx # @param [String] s The snake case string to camelize # @return [String] def self.camel(s="", all_caps = true) + s = s.to_s s = s.capitalize if all_caps s.gsub(/_(.)/){ $1.upcase } end 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..417de908 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 @@ -73,7 +91,7 @@ module Axlsx # @raise [ArgumentError] raised if the value is not a Fixnun, Integer, Float value greater or equal to 0 # @return [Boolean] true if the data is valid def self.validate_unsigned_numeric(v) - DataTypeValidator.validate("Invalid column width", [Fixnum, Integer, Float], v, lambda { |arg| arg.respond_to?(:>=) && arg >= 0 }) + DataTypeValidator.validate("Invalid column width", [Fixnum, Integer, Float], v, lambda { |arg| arg.respond_to?(:>=) && arg.to_i >= 0 }) end # Requires that the value is a Fixnum or Integer @@ -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/workbook.rb b/lib/axlsx/workbook/workbook.rb index 3ff61c07..cdc73360 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -1,6 +1,6 @@ # -*- coding: utf-8 -*- module Axlsx -require 'axlsx/workbook/worksheet/auto_filter.rb' +require 'axlsx/workbook/worksheet/auto_filter/auto_filter.rb' require 'axlsx/workbook/worksheet/date_time_converter.rb' require 'axlsx/workbook/worksheet/protected_range.rb' require 'axlsx/workbook/worksheet/protected_ranges.rb' diff --git a/lib/axlsx/workbook/worksheet/auto_filter.rb b/lib/axlsx/workbook/worksheet/auto_filter.rb deleted file mode 100644 index 7d9fbcfc..00000000 --- a/lib/axlsx/workbook/worksheet/auto_filter.rb +++ /dev/null @@ -1,35 +0,0 @@ -module Axlsx - - #This class represents an auto filter range in a worksheet - class AutoFilter - - # creates a new Autofilter object - # @param [Worksheet] worksheet - def initialize(worksheet) - raise ArgumentError, 'you must provide a worksheet' unless worksheet.is_a?(Worksheet) - @worksheet = worksheet - end - - attr_reader :worksheet - - # The range the autofilter should be applied to. - # This should be a string like 'A1:B8' - # @return [String] - attr_accessor :range - - # the formula for the defined name required for this auto filter - # @return [String] - def defined_name - return unless range - Axlsx.cell_range(range.split(':').collect { |name| worksheet.name_to_cell(name)}) - end - - # serialize the object - # @return [String] - def to_xml_string(str='') - return unless range - str << "<autoFilter ref='#{range}'></autoFilter>" - end - - end -end diff --git a/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb b/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb new file mode 100644 index 00000000..4cf98ad7 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/auto_filter/auto_filter.rb @@ -0,0 +1,60 @@ + +require 'axlsx/workbook/worksheet/auto_filter/filter_column.rb' +require 'axlsx/workbook/worksheet/auto_filter/filters.rb' + +module Axlsx + + #This class represents an auto filter range in a worksheet + class AutoFilter + + # creates a new Autofilter object + # @param [Worksheet] worksheet + def initialize(worksheet) + raise ArgumentError, 'you must provide a worksheet' unless worksheet.is_a?(Worksheet) + @worksheet = worksheet + end + + attr_reader :worksheet + + # The range the autofilter should be applied to. + # This should be a string like 'A1:B8' + # @return [String] + attr_accessor :range + + # 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 + # @return [String] + def defined_name + return unless range + Axlsx.cell_range(range.split(':').collect { |name| worksheet.name_to_cell(name)}) + end + + # A collection of filterColumns for this auto_filter + # @return [SimpleTypedList] + def columns + @columns ||= SimpleTypedList.new FilterColumn + end + + # 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. + # @param [String] col_id Zero-based index indicating the AutoFilter column to which this filter information applies. + # @param [Symbol] filter_type A symbol representing one of the supported filter types. + # @param [Hash] options a hash of options to pass into the generated filter + # @return [FilterColumn] + def add_column(col_id, filter_type, options = {}) + columns << FilterColumn.new(col_id, filter_type, options) + columns.last + end + + # serialize the object + # @return [String] + def to_xml_string(str='') + return unless range + str << "<autoFilter ref='#{range}'>" + columns.each { |filter_column| filter_column.to_xml_string(str) } + str << "</autoFilter>" + end + + end +end diff --git a/lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb b/lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb new file mode 100644 index 00000000..6e8db16e --- /dev/null +++ b/lib/axlsx/workbook/worksheet/auto_filter/filter_column.rb @@ -0,0 +1,96 @@ +module Axlsx + # The filterColumn collection identifies a particular column in the AutoFilter + # range and specifies filter information that has been applied to this column. + # If a column in the AutoFilter range has no criteria specified, + # then there is no corresponding filterColumn collection expressed for that column. + class FilterColumn + + # 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 + # @param [Integer|Cell] col_id The zero based index for the column to which this filter will be applied + # @param [Symbol] filter_type The symbolized class name of the filter to apply to this column. + # @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 + #Axlsx::validate_unsigned_int(col_id) + self.col_id = col_id + options.each do |o| + self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" + end + @filter = Axlsx.const_get(Axlsx.camel(filter_type)).new(options) + 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 + + # The actual filter being dealt with here + # This could be any one of the allowed filter types + attr_reader :filter + + # 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 + # @return [Integer] + def col_id=(column_index) + column_index = column_index.col if column_index.is_a?(Cell) + Axlsx.validate_unsigned_int column_index + @col_id = column_index + end + + # @param [Boolean] hidden Flag indicating whether the AutoFilter button for this column is hidden. + # @return [Boolean] + def hidden_button=(hidden) + Axlsx.validate_boolean hidden + @hidden_button = hidden + end + + # Flag indicating whether the AutoFilter button is show. This is + # undocumented in the spec, but exists in the schema file as an + # optional attribute. + # @param [Boolean] show Show or hide the button + # @return [Boolean] + def show_button=(show) + Axlsx.validate_boolean show + @show_botton = show + end + + # Serialize the object to xml + def to_xml_string(str='') + str << "<filterColumn #{serialized_attributes}>" + @filter.to_xml_string(str) + str << "</filterColumn>" + end + + private + + def serialized_attributes(str='') + instance_values.each do |key, value| + if %(show_button hidden_button col_id).include? key.to_s + str << "#{Axlsx.camel(key, false)}='#{value}' " + end + end + str + end + end +end diff --git a/lib/axlsx/workbook/worksheet/auto_filter/filters.rb b/lib/axlsx/workbook/worksheet/auto_filter/filters.rb new file mode 100644 index 00000000..5b46e087 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/auto_filter/filters.rb @@ -0,0 +1,220 @@ +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.each do |key, value| + self.send("#{key}=", value) if self.respond_to? "#{key}=" + end + end + + # Flag indicating whether to filter by blank. + # @return [Boolean] + attr_reader :blank + + # 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. + attr_reader :calendar_type + + # 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 #{serialized_attributes}>" + filter_items.each { |filter| filter.to_xml_string(str) } + date_group_items.each { |date_group_item| date_group_item.to_xml_string(str) } + str << '</filters>' + end + + # not entirely happy with this. + # filter_items should be a simple typed list that overrides << etc + # to create Filter objects from the inserted values. However this + # is most likely so rarely used...(really? do you know that?) + 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 + + def serialized_attributes(str='') + instance_values.each do |key, value| + if %(blank claendar_type).include? key.to_s + str << "#{Axlsx.camel(key, false)}='#{value}' " + end + end + str + end + # 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 + + # 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 +end diff --git a/lib/axlsx/workbook/worksheet/sheet_pr.rb b/lib/axlsx/workbook/worksheet/sheet_pr.rb index 6f03bf5b..33239868 100644 --- a/lib/axlsx/workbook/worksheet/sheet_pr.rb +++ b/lib/axlsx/workbook/worksheet/sheet_pr.rb @@ -1,9 +1,37 @@ module Axlsx - + #<xsd:complexType name="CT_SheetPr"> + #<xsd:sequence> + #<xsd:element name="tabColor" type="CT_Color" minOccurs="0" maxOccurs="1"/> + #<xsd:element name="outlinePr" type="CT_OutlinePr" minOccurs="0" maxOccurs="1"/> + #<xsd:element name="pageSetUpPr" type="CT_PageSetUpPr" minOccurs="0" maxOccurs="1"/> + #</xsd:sequence> + #<xsd:attribute name="syncHorizontal" type="xsd:boolean" use="optional" default="false"/> + #<xsd:attribute name="syncVertical" type="xsd:boolean" use="optional" default="false"/> + #<xsd:attribute name="syncRef" type="ST_Ref" use="optional"/> + #<xsd:attribute name="transitionEvaluation" type="xsd:boolean" use="optional" default="false"/> + #<xsd:attribute name="transitionEntry" type="xsd:boolean" use="optional" default="false"/> + #<xsd:attribute name="published" type="xsd:boolean" use="optional" default="true"/> + #<xsd:attribute name="codeName" type="xsd:string" use="optional"/> + #<xsd:attribute name="filterMode" type="xsd:boolean" use="optional" default="false"/> + #<xsd:attribute name="enableFormatConditionsCalculation" type="xsd:boolean" use="optional" default="true"/> + #</xsd:complexType> # The SheetPr class manages serialization fo a worksheet's sheetPr element. # Only fit_to_page is implemented class SheetPr + + # These attributes are all boolean so I'm doing a bit of a hand + # waving magic show to set up the attriubte accessors + # + BOOLEAN_ATTRIBUTES = [:sync_horizontal, + :sync_vertical, + :transtion_evaluation, + :transition_entry, + :published, + :filter_mode, + :enable_format_conditions_calculation] + + # Creates a new SheetPr object # @param [Worksheet] worksheet The worksheet that owns this SheetPr object def initialize(worksheet) @@ -11,14 +39,118 @@ module Axlsx @worksheet = worksheet end + # Dynamically create accessors for boolean attriubtes + BOOLEAN_ATTRIBUTES.each do |attr| + class_eval %{ + # The #{attr} attribute reader + # @return [Boolean] + attr_reader :#{attr} + + # The #{attr} writer + # @param [Boolean] value The value to assign to #{attr} + # @return [Boolean] + def #{attr}=(value) + Axlsx::validate_boolean(value) + @#{attr} = value + end + } + end + + # Anchor point for worksheet's window. + # @return [String] + attr_reader :code_name + + # Specifies a stable name of the sheet, which should not change over time, + # and does not change from user input. This name should be used by code + # to reference a particular sheet. + # @return [String] + attr_reader :sync_ref + + # The worksheet these properties apply to! + # @return [Worksheet] attr_reader :worksheet + # @see code_name + # @param [String] name + def code_name=(name) + @code_name = name + end + + # @see sync_ref + # @param [String] ref A cell reference (e.g. "A1") + def sync_ref=(ref) + @sync_ref = ref + end + # Serialize the object # @param [String] str serialized output will be appended to this object if provided. # @return [String] def to_xml_string(str = '') - return unless worksheet.fit_to_page? - str << "<sheetPr><pageSetUpPr fitToPage=\"%s\"></pageSetUpPr></sheetPr>" % worksheet.fit_to_page? + update_properties + str << "<sheetPr #{serialized_attributes}>" + page_setup_pr.to_xml_string(str) + str << "</sheetPr>" + end + + def page_setup_pr + @page_setup_pr ||= PageSetUpPr.new + end + + private + + def serialized_attributes(str = '') + instance_values.each do |key, value| + unless %(worksheet page_setup_pr).include? key + str << "#{Axlsx.camel(key, false)}='#{value}' " + end + end + str + end + + + def update_properties + page_setup_pr.fit_to_page = worksheet.fit_to_page? + if worksheet.auto_filter.columns.size > 0 + self.filter_mode = 1 + self.enable_format_conditions_calculation = 0 + end + end + end + + + class PageSetUpPr + + # creates a new page setup properties object + # @param [Hash] options + # @option [Boolean] fit_to_page Flag indicating whether the sheet displays Automatic Page Breaks. + # @option [Boolean] auto_page_breaks Flag indicating whether the Fit to Page print option is enabled. + def initialize(options = {}) + options.each do |key, value| + self.send("#{key}=",value) if self.respond_to?("#{key}=") + end + end + + # Flag indicating whether the sheet displays Automatic Page Breaks. + # @param [Boolean] value + # @return [Boolean] + def fit_to_page=(value) + Axlsx.validate_boolean value + @fit_to_page = value + end + + # Flag indicating whether the Fit to Page print option is enabled. + # @param [Boolean] value + # @return [Boolean] + def auto_page_breaks=(value) + Alxsx.validate_boolean value + @auto_page_breaks = value + end + + # serialize to xml + def to_xml_string(str='') + str << '<pageSetUpPr ' + instance_values.each { |key, value| str << "#{Axlsx.camel(key, false)}='#{value}' " } + str << '></pageSetUpPr>' end end end diff --git a/lib/axlsx/workbook/worksheet/table_style_info.rb b/lib/axlsx/workbook/worksheet/table_style_info.rb index f0d08d8b..778546e0 100644 --- a/lib/axlsx/workbook/worksheet/table_style_info.rb +++ b/lib/axlsx/workbook/worksheet/table_style_info.rb @@ -20,7 +20,7 @@ module Axlsx # @see Annex G. (normative) Predefined SpreadsheetML Style Definitions in part 1 of the specification. def initialize(options = {}) initialize_defaults - name= 'TableStyleMedium9' + @name = 'TableStyleMedium9' options.each do |k, v| send("#{k}=", v) if respond_to? "#{k}=" end diff --git a/test/workbook/worksheet/auto_filter/tc_auto_filter.rb b/test/workbook/worksheet/auto_filter/tc_auto_filter.rb new file mode 100644 index 00000000..77ee2b2c --- /dev/null +++ b/test/workbook/worksheet/auto_filter/tc_auto_filter.rb @@ -0,0 +1,32 @@ +require 'tc_helper.rb' + +class TestAutoFilter < Test::Unit::TestCase + + def setup + ws = Axlsx::Package.new.workbook.add_worksheet + 3.times { ws.add_row [1,2,3] } + @auto_filter = ws.auto_filter + @auto_filter.range = 'A1:C3' + end + + def test_defined_name + assert_equal("'Sheet1'!$A$1:$C$3", @auto_filter.defined_name) + end + + def test_to_xml_string + doc = Nokogiri::XML(@auto_filter.to_xml_string) + assert(doc.xpath("autoFilter[@ref='#{@auto_filter.range}']")) + end + + def test_columns + assert @auto_filter.columns.is_a?(Axlsx::SimpleTypedList) + assert_equal @auto_filter.columns.allowed_types, [Axlsx::FilterColumn] + end + + def test_add_column + @auto_filter.add_column(0, :filters) do |column| + assert column.is_a? FilterColumn + end + end + +end 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..ec74316c --- /dev/null +++ b/test/workbook/worksheet/auto_filter/tc_filter_column.rb @@ -0,0 +1,76 @@ +require 'tc_helper.rb' + +class TestFilterColumn < Test::Unit::TestCase + + def setup + @filter_column = Axlsx::FilterColumn.new(0, :filters, :filter_items => [200]) + end + + + def test_initialize_col_id + assert_raise ArgumentError do + Axlsx::FilterColumn.new(0, :bobs_house_of_filter) + end + assert_raise ArgumentError do + Axlsx::FilterColumn.new(:penut, :filters) + end + end + + def test_initailize_filter_type + assert @filter_column.filter.is_a?(Axlsx::Filters) + assert_equal 1, @filter_column.filter.filter_items.size + end + + def test_initialize_filter_type_filters_with_options + assert_equal 200, @filter_column.filter.filter_items.first.val + end + + def test_initialize_with_block + filter_column = Axlsx::FilterColumn.new(0, :filters) do |filters| + filters.filter_items = [700, 100, 5] + end + assert_equal 3, filter_column.filter.filter_items.size + assert_equal 700, filter_column.filter.filter_items.first.val + assert_equal 5, filter_column.filter.filter_items.last.val + end + + def test_default_show_button + assert_equal true, @filter_column.show_button + end + + def test_default_hidden_button + assert_equal false, @filter_column.hidden_button + end + + def test_show_button + assert_raise ArgumentError do + @filter_column.show_button = :foo + end + assert_nothing_raised { @filter_column.show_button = false } + end + + def test_hidden_button + assert_raise ArgumentError do + @filter_column.hidden_button = :hoge + end + assert_nothing_raised { @filter_column.hidden_button = true } + end + + def test_col_id= + assert_raise ArgumentError do + @filter_column.col_id = :bar + end + assert_nothing_raised { @filter_column.col_id = 7 } + end + + def test_to_xml_string + doc = Nokogiri::XML(@filter_column.to_xml_string) + assert doc.xpath("//filterColumn[@colId=#{@filter_column.col_id}]") + assert doc.xpath("//filterColumn[@hiddenButton=#{@filter_column.hidden_button}]") + assert doc.xpath("//filterColumn[@showButton=#{@filter_column.show_button}]") + + + + assert doc.xpath("//filterColumn/filters") + 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 + diff --git a/test/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb index 7188bc1d..0b332d86 100644 --- a/test/workbook/worksheet/tc_cell.rb +++ b/test/workbook/worksheet/tc_cell.rb @@ -277,7 +277,6 @@ class TestCell < Test::Unit::TestCase def test_font_size_with_bolding @c.style = @c.row.worksheet.workbook.styles.add_style :b => true - sz = @c.send(:font_size) assert_equal(@c.row.worksheet.workbook.styles.fonts.first.sz * 1.5, @c.send(:font_size)) end diff --git a/test/workbook/worksheet/table/tc_table.rb b/test/workbook/worksheet/tc_table.rb index de86b886..de86b886 100644 --- a/test/workbook/worksheet/table/tc_table.rb +++ b/test/workbook/worksheet/tc_table.rb diff --git a/test/workbook/worksheet/table/tc_table_style_info.rb b/test/workbook/worksheet/tc_table_style_info.rb index c0c452c9..c0c452c9 100644 --- a/test/workbook/worksheet/table/tc_table_style_info.rb +++ b/test/workbook/worksheet/tc_table_style_info.rb diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index a57fa811..4c8f2822 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -246,7 +246,7 @@ class TestWorksheet < Test::Unit::TestCase def test_to_xml_string_auto_filter @ws.add_row [1, "two"] - @ws.auto_filter = "A1:B1" + @ws.auto_filter.range = "A1:B1" doc = Nokogiri::XML(@ws.to_xml_string) assert_equal(doc.xpath('//xmlns:worksheet/xmlns:autoFilter[@ref="A1:B1"]').size, 1) end @@ -337,7 +337,7 @@ class TestWorksheet < Test::Unit::TestCase @ws.page_margins.set :left => 9 @ws.page_setup.set :fit_to_width => 1 @ws.print_options.set :headings => true - @ws.auto_filter = "A1:C3" + @ws.auto_filter.range = "A1:C3" @ws.merge_cells "A4:A5" @ws.add_chart Axlsx::Pie3DChart @ws.add_table "E1:F3" @@ -424,7 +424,14 @@ class TestWorksheet < Test::Unit::TestCase def test_auto_filter assert(@ws.auto_filter.range.nil?) assert_raise(ArgumentError) { @ws.auto_filter = 123 } - @ws.auto_filter = "A1:D9" + @ws.auto_filter.range = "A1:D9" assert_equal(@ws.auto_filter.range, "A1:D9") end + + def test_sheet_pr_for_auto_filter + @ws.auto_filter.range = 'A1:D9' + @ws.auto_filter.add_column 0, :filters, :filter_items => [1] + doc = Nokogiri::XML(@ws.to_xml_string) + assert(doc.xpath('//sheetPr[@filterMode="true"]')) + end end |