lib/write_xlsx/worksheet.rb in write_xlsx-0.58.0 vs lib/write_xlsx/worksheet.rb in write_xlsx-0.59.0

- old
+ new

@@ -3,10 +3,11 @@ require 'write_xlsx/colors' require 'write_xlsx/format' require 'write_xlsx/drawing' require 'write_xlsx/compatibility' require 'write_xlsx/utility' +require 'write_xlsx/package/conditional_format' require 'tempfile' module Writexlsx # # A new worksheet is created by calling the add_worksheet() method from a workbook object: @@ -352,10 +353,11 @@ attr_reader :vml_data_id # :nodoc: attr_reader :autofilter_area # :nodoc: attr_reader :writer, :set_rows, :col_formats # :nodoc: attr_accessor :vml_shape_id, :rel_count, :hlink_refs # :nodoc: attr_reader :comments_author # :nodoc: + attr_accessor :dxf_priority # :nodoc: def initialize(workbook, index, name) #:nodoc: @writer = Package::XMLWriterSimple.new @workbook = workbook @@ -2900,137 +2902,10 @@ formula = tokens.join('') write_formula(row, col, formula, format, value) end # - # convert_date_time(date_time_string) - # - # The function takes a date and time in ISO8601 "yyyy-mm-ddThh:mm:ss.ss" format - # and converts it to a decimal number representing a valid Excel date. - # - # Dates and times in Excel are represented by real numbers. The integer part of - # the number stores the number of days since the epoch and the fractional part - # stores the percentage of the day in seconds. The epoch can be either 1900 or - # 1904. - # - # Parameter: Date and time string in one of the following formats: - # yyyy-mm-ddThh:mm:ss.ss # Standard - # yyyy-mm-ddT # Date only - # Thh:mm:ss.ss # Time only - # - # Returns: - # A decimal number representing a valid Excel date, or - # nil if the date is invalid. - # - def convert_date_time(date_time_string) #:nodoc: - date_time = date_time_string - - days = 0 # Number of days since epoch - seconds = 0 # Time expressed as fraction of 24h hours in seconds - - # Strip leading and trailing whitespace. - date_time.sub!(/^\s+/, '') - date_time.sub!(/\s+$/, '') - - # Check for invalid date char. - return nil if date_time =~ /[^0-9T:\-\.Z]/ - - # Check for "T" after date or before time. - return nil unless date_time =~ /\dT|T\d/ - - # Strip trailing Z in ISO8601 date. - date_time.sub!(/Z$/, '') - - # Split into date and time. - date, time = date_time.split(/T/) - - # We allow the time portion of the input DateTime to be optional. - if time - # Match hh:mm:ss.sss+ where the seconds are optional - if time =~ /^(\d\d):(\d\d)(:(\d\d(\.\d+)?))?/ - hour = $1.to_i - min = $2.to_i - sec = $4.to_f || 0 - else - return nil # Not a valid time format. - end - - # Some boundary checks - return nil if hour >= 24 - return nil if min >= 60 - return nil if sec >= 60 - - # Excel expresses seconds as a fraction of the number in 24 hours. - seconds = (hour * 60* 60 + min * 60 + sec) / (24.0 * 60 * 60) - end - - # We allow the date portion of the input DateTime to be optional. - return seconds if date == '' - - # Match date as yyyy-mm-dd. - if date =~ /^(\d\d\d\d)-(\d\d)-(\d\d)$/ - year = $1.to_i - month = $2.to_i - day = $3.to_i - else - return nil # Not a valid date format. - end - - # Set the epoch as 1900 or 1904. Defaults to 1900. - # Special cases for Excel. - unless date_1904? - return seconds if date == '1899-12-31' # Excel 1900 epoch - return seconds if date == '1900-01-00' # Excel 1900 epoch - return 60 + seconds if date == '1900-02-29' # Excel false leapday - end - - - # We calculate the date by calculating the number of days since the epoch - # and adjust for the number of leap days. We calculate the number of leap - # days by normalising the year in relation to the epoch. Thus the year 2000 - # becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays. - # - epoch = date_1904? ? 1904 : 1900 - offset = date_1904? ? 4 : 0 - norm = 300 - range = year - epoch - - # Set month days and check for leap year. - mdays = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31] - leap = 0 - leap = 1 if year % 4 == 0 && year % 100 != 0 || year % 400 == 0 - mdays[1] = 29 if leap != 0 - - # Some boundary checks - return nil if year < epoch or year > 9999 - return nil if month < 1 or month > 12 - return nil if day < 1 or day > mdays[month - 1] - - # Accumulate the number of days since the epoch. - days = day # Add days for current month - (0 .. month-2).each do |m| - days += mdays[m] # Add days for past months - end - days += range * 365 # Add days for past years - days += ((range) / 4) # Add leapdays - days -= ((range + offset) /100) # Subtract 100 year leapdays - days += ((range + offset + norm)/400) # Add 400 year leapdays - days -= leap # Already counted above - - # Adjust for Excel erroneously treating 1900 as a leap year. - days += 1 if !date_1904? and days > 59 - - date_time = sprintf("%0.10f", days + seconds) - date_time = date_time.sub(/\.?0+$/, '') if date_time =~ /\./ - if date_time =~ /\./ - date_time.to_f - else - date_time.to_i - end - end - - # # :call-seq: # set_row(row [ , height, format, hidden, level, collapsed ] ) # # This method can be used to change the default properties of a row. # All parameters apart from row are optional. @@ -3220,10 +3095,13 @@ # :call-seq: # conditional_formatting(cell_or_cell_range, options) # # This method handles the interface to Excel conditional formatting. # + # This method contains a lot of parameters and is described in detail in + # the section below. + # # We allow the format to be called on one cell or a range of cells. The # hashref contains the formatting parameters and must be the last param: # # conditional_formatting(row, col, {...}) # conditional_formatting(first_row, first_col, last_row, last_col, {...}) @@ -3238,174 +3116,608 @@ # :value => 50, # :format => format1 # } # ) # - # This method contains a lot of parameters and is described in detail in - # a separate section "CONDITIONAL FORMATTING IN EXCEL". + # See also the conditional_format.rb program in the examples directory of + # the distro. # - # See also the conditional_format.rb program in the examples directory of the distro + # The conditional_formatting method is used to apply formatting based + # on user defined criteria to an write_xlsx file. # + # It can be applied to a single cell or a range of cells. + # You can pass 3 parameters such as (row, col, {...}) + # or 5 parameters such as (first_row, first_col, last_row, last_col, {...}). + # You can also use A1 style notation. For example: + # + # worksheet.conditional_formatting( 0, 0, {...} ) + # worksheet.conditional_formatting( 0, 0, 4, 1, {...} ) + # + # # Which are the same as: + # + # worksheet.conditional_formatting( 'A1', {...} ) + # worksheet.conditional_formatting( 'A1:B5', {...} ) + # + # Using A1 style notation is is also possible to specify + # non-contiguous ranges, separated by a comma. For example: + # + # worksheet.conditional_formatting( 'A1:D5,A8:D12', {...} ) + # The last parameter in conditional_formatting must be a hash containing + # the parameters that describe the type and style of the data validation. + # + # The main parameters are: + # + # :type + # :format + # :criteria + # :value + # :minimum + # :maximum + # Other, less commonly used parameters are: + # + # :min_type + # :mid_type + # :max_type + # :min_value + # :mid_value + # :max_value + # :min_color + # :mid_color + # :max_color + # :bar_color + # Additional parameters which are used for specific conditional format types + # are shown in the relevant sections below. + # + # == :type + # + # This parameter is passed in a hash to conditional_formatting. + # + # The type parameter is used to set the type of conditional formatting + # that you wish to apply. It is always required and it has no default value. + # Allowable type values and their associated parameters are: + # + # Type Parameters + # ====== ========== + # 'cell' :criteria + # :value + # :minimum + # :maximum + # + # 'date' :criteria + # :value + # :minimum + # :maximum + # + # 'time_period' :criteria + # + # 'text' :criteria + # :value + # + # 'average' :criteria + # + # 'duplicate' (none) + # + # 'unique' (none) + # + # 'top' :criteria + # :value + # + # 'bottom' :criteria + # :value + # + # 'blanks' (none) + # + # 'no_blanks' (none) + # + # 'errors' (none) + # + # 'no_errors' (none) + # + # '2_color_scale' (none) + # + # '3_color_scale' (none) + # + # 'data_bar' (none) + # + # 'formula' :criteria + # All conditional formatting types have a format parameter, see below. + # Other types and parameters such as icon sets will be added in time. + # + # == :type => 'cell' + # + # This is the most common conditional formatting type. It is used when + # a format is applied to a cell based on a simple criterion. For example: + # + # worksheet.conditional_formatting( 'A1', + # { + # :type => 'cell', + # :criteria => 'greater than', + # :value => 5, + # :format => red_format + # } + # ) + # Or, using the between criteria: + # + # worksheet.conditional_formatting( 'C1:C4', + # { + # :type => 'cell', + # :criteria => 'between', + # :minimum => 20, + # :maximum => 30, + # :format => green_format + # } + # ) + # == :criteria + # + # The criteria parameter is used to set the criteria by which the cell data + # will be evaluated. It has no default value. The most common criteria + # as applied to { type => 'cell' } are: + # + # 'between' + # 'not between' + # 'equal to' | '==' | '=' + # 'not equal to' | '!=' | '<>' + # 'greater than' | '>' + # 'less than' | '<' + # 'greater than or equal to' | '>=' + # 'less than or equal to' | '<=' + # You can either use Excel's textual description strings, + # in the first column above, or the more common symbolic alternatives. + # + # Additional criteria which are specific to other conditional format types + # are shown in the relevant sections below. + # + # == :value + # + # The value is generally used along with the criteria parameter to set the + # rule by which the cell data will be evaluated. + # + # :type => 'cell', + # :criteria => '>', + # :value => 5 + # :format => format + # The value property can also be an cell reference. + # + # :type => 'cell', + # :criteria => '>', + # :value => '$C$1', + # :format => format + # == :format + # + # The format parameter is used to specify the format that will be applied + # to the cell when the conditional formatting criterion is met. + # The format is created using the add_format method in the same way as cell + # formats: + # + # format = workbook.add_format( :bold => 1, :italic => 1 ) + # + # worksheet.conditional_formatting( 'A1', + # { + # :type => 'cell', + # :criteria => '>', + # :value => 5 + # :format => format + # } + # ) + # The conditional format follows the same rules as in Excel: + # it is superimposed over the existing cell format and not all font and + # border properties can be modified. Font properties that can't be modified + # are font name, font size, superscript and subscript. + # The border property that cannot be modified is diagonal borders. + # + # Excel specifies some default formats to be used with conditional + # formatting. You can replicate them using the following write_xlsx formats: + # + # # Light red fill with dark red text. + # + # format1 = workbook.add_format( + # :bg_color => '#FFC7CE', + # :color => '#9C0006' + # ) + # + # # Light yellow fill with dark yellow text. + # + # format2 = workbook.add_format( + # :bg_color => '#FFEB9C', + # :color => '#9C6500' + # ) + # + # # Green fill with dark green text. + # + # format3 = workbook.add_format( + # :bg_color => '#C6EFCE', + # :color => '#006100' + # ) + # == :minimum + # + # The minimum parameter is used to set the lower limiting value when the + # criteria is either 'between' or 'not between': + # + # :validate => 'integer', + # :criteria => 'between', + # :minimum => 1, + # :maximum => 100 + # == :maximum + # + # The maximum parameter is used to set the upper limiting value when the + # criteria is either 'between' or 'not between'. See the previous example. + # + # == :type => 'date' + # + # The date type is the same as the cell type and uses the same criteria + # and values. However it allows the value, minimum and maximum properties + # to be specified in the ISO8601 yyyy-mm-ddThh:mm:ss.sss date format which + # is detailed in the write_date_time() method. + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'date', + # :criteria => 'greater than', + # :value => '2011-01-01T', + # :format => format + # } + # ) + # == :type => 'time_period' + # + # The time_period type is used to specify Excel's "Dates Occurring" style + # conditional format. + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'time_period', + # :criteria => 'yesterday', + # :format => format + # } + # ) + # The period is set in the criteria and can have one of the following + # values: + # + # :criteria => 'yesterday', + # :criteria => 'today', + # :criteria => 'last 7 days', + # :criteria => 'last week', + # :criteria => 'this week', + # :criteria => 'next week', + # :criteria => 'last month', + # :criteria => 'this month', + # :criteria => 'next month' + # == :type => 'text' + # + # The text type is used to specify Excel's "Specific Text" style conditional + # format. It is used to do simple string matching using the criteria and + # value parameters: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'text', + # :criteria => 'containing', + # :value => 'foo', + # :format => format + # } + # ) + # The criteria can have one of the following values: + # + # :criteria => 'containing', + # :criteria => 'not containing', + # :criteria => 'begins with', + # :criteria => 'ends with' + # The value parameter should be a string or single character. + # + # == :type => 'average' + # + # The average type is used to specify Excel's "Average" style conditional + # format. + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'average', + # :criteria => 'above', + # :format => format + # } + # ) + # The type of average for the conditional format range is specified by the + # criteria: + # + # :criteria => 'above', + # :criteria => 'below', + # :criteria => 'equal or above', + # :criteria => 'equal or below', + # :criteria => '1 std dev above', + # :criteria => '1 std dev below', + # :criteria => '2 std dev above', + # :criteria => '2 std dev below', + # :criteria => '3 std dev above', + # :criteria => '3 std dev below' + # == :type => 'duplicate' + # + # The duplicate type is used to highlight duplicate cells in a range: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'duplicate', + # :format => format + # } + # ) + # == :type => 'unique' + # + # The unique type is used to highlight unique cells in a range: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'unique', + # :format => format + # } + # ) + # == :type => 'top' + # + # The top type is used to specify the top n values by number or percentage + # in a range: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'top', + # :value => 10, + # :format => format + # } + # ) + # The criteria can be used to indicate that a percentage condition is + # required: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'top', + # :value => 10, + # :criteria => '%', + # :format => format + # } + # ) + # == :type => 'bottom' + # + # The bottom type is used to specify the bottom n values by number or + # percentage in a range. + # + # It takes the same parameters as top, see above. + # + # == :type => 'blanks' + # + # The blanks type is used to highlight blank cells in a range: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'blanks', + # :format => format + # } + # ) + # == :type => 'no_blanks' + # + # The no_blanks type is used to highlight non blank cells in a range: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'no_blanks', + # :format => format + # } + # ) + # == :type => 'errors' + # + # The errors type is used to highlight error cells in a range: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'errors', + # :format => format + # } + # ) + # == :type => 'no_errors' + # + # The no_errors type is used to highlight non error cells in a range: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'no_errors', + # :format => format + # } + # ) + # == :type => '2_color_scale' + # + # The 2_color_scale type is used to specify Excel's "2 Color Scale" style + # conditional format. + # + # worksheet.conditional_formatting( 'A1:A12', + # { + # :type => '2_color_scale' + # } + # ) + # At the moment only the default colors and properties can be used. These + # will be extended in time. + # + # == :type => '3_color_scale' + # + # The 3_color_scale type is used to specify Excel's "3 Color Scale" style + # conditional format. + # + # worksheet.conditional_formatting( 'A1:A12', + # { + # :type => '3_color_scale' + # } + # ) + # At the moment only the default colors and properties can be used. + # These will be extended in time. + # + # == :type => 'data_bar' + # + # The data_bar type is used to specify Excel's "Data Bar" style conditional + # format. + # + # worksheet.conditional_formatting( 'A1:A12', + # { + # :type => 'data_bar', + # } + # ) + # At the moment only the default colors and properties can be used. These + # will be extended in time. + # + # == :type => 'formula' + # + # The formula type is used to specify a conditional format based on + # a user defined formula: + # + # worksheet.conditional_formatting( 'A1:A4', + # { + # :type => 'formula', + # :criteria => '=$A$1 > 5', + # :format => format + # } + # ) + # The formula is specified in the criteria. + # + # == :min_type, :mid_type, :max_type + # + # The min_type and max_type properties are available when the conditional + # formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_type + # is available for 3_color_scale. The properties are used as follows: + # + # worksheet.conditional_formatting( 'A1:A12', + # { + # :type => '2_color_scale', + # :min_type => 'percent', + # :max_type => 'percent' + # } + # ) + # The available min/mid/max types are: + # + # 'num' + # 'percent' + # 'percentile' + # 'formula' + # == :min_value, :mid_value, :max_value + # + # The min_value and max_value properties are available when the conditional + # formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_value + # is available for 3_color_scale. The properties are used as follows: + # + # worksheet.conditional_formatting( 'A1:A12', + # { + # :type => '2_color_scale', + # :min_value => 10, + # :max_value => 90 + # } + # ) + # == :min_color, :mid_color, :max_color, :bar_color + # + # The min_color and max_color properties are available when the conditional + # formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_color + # is available for 3_color_scale. The properties are used as follows: + # + # worksheet.conditional_formatting( 'A1:A12', + # { + # ;type => '2_color_scale', + # :min_color => "#C5D9F1", + # :max_color => "#538ED5" + # } + # ) + # The color can be specifies as an Excel::Writer::XLSX color index or, + # more usefully, as a HTML style RGB hex number, as shown above. + # + # == Conditional Formatting Examples + # + # === Example 1. Highlight cells greater than an integer value. + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'cell', + # :criteria => 'greater than', + # :value => 5, + # :format => format + # } + # ) + # === Example 2. Highlight cells greater than a value in a reference cell. + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'cell', + # :criteria => 'greater than', + # :value => '$H$1', + # :format => format + # } + # ) + # === Example 3. Highlight cells greater than a certain date: + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'date', + # :criteria => 'greater than', + # :value => '2011-01-01T', + # :format => format + # } + # ) + # === Example 4. Highlight cells with a date in the last seven days: + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'time_period', + # :criteria => 'last 7 days', + # :format => format + # } + # ) + # === Example 5. Highlight cells with strings starting with the letter b: + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'text', + # :criteria => 'begins with', + # :value => 'b', + # :format => format + # } + # ) + # === Example 6. Highlight cells that are 1 std deviation above the average for the range: + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'average', + # :format => format + # } + # ) + # === Example 7. Highlight duplicate cells in a range: + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'duplicate', + # :format => format + # } + # ) + # === Example 8. Highlight unique cells in a range. + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'unique', + # :format => format + # } + # ) + # === Example 9. Highlight the top 10 cells. + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'top', + # :value => 10, + # :format => format + # } + # ) + # === Example 10. Highlight blank cells. + # + # worksheet.conditional_formatting( 'A1:F10', + # { + # :type => 'blanks', + # :format => format + # } + # ) + # See also the conditional_format.rb example program in EXAMPLES. + # def conditional_formatting(*args) - # Check for a cell reference in A1 notation and substitute row and column - if args[0] =~ /^\D/ - # Check for a user defined multiple range like B3:K6,B8:K11. - user_range = args[0].gsub(/\s*,\s*/, ' ').gsub(/\$/, '') if args[0] =~ /,/ - end - row1, col1, row2, col2, param = row_col_notation(args) - if row2.respond_to?(:keys) - param = row2 - row2, col2 = row1, col1 - end - raise WriteXLSXInsufficientArgumentError if [row1, col1, row2, col2, param].include?(nil) - - # Check that row and col are valid without storing the values. - check_dimensions(row1, col1) - check_dimensions(row2, col2) - check_conditional_formatting_parameters(param) - - # Swap last row/col for first row/col as necessary - row1, row2 = row2, row1 if row1 > row2 - col1, col2 = col2, col1 if col1 > col2 - - # If the first and last cell are the same write a single cell. - if row1 == row2 && col1 == col2 - range = xl_rowcol_to_cell(row1, col1) - start_cell = range - else - range = xl_range(row1, row2, col1, col2) - start_cell = xl_rowcol_to_cell(row1, col1) - end - - # Override with user defined multiple range if provided. - range = user_range if user_range - - param[:format] = param[:format].get_dxf_index if param[:format] - param[:priority] = @dxf_priority - @dxf_priority += 1 - - # Special handling of text criteria. - if param[:type] == 'text' - case param[:criteria] - when 'containsText' - param[:type] = 'containsText'; - param[:formula] = %Q!NOT(ISERROR(SEARCH("#{param[:value]}",#{start_cell})))! - when 'notContains' - param[:type] = 'notContainsText'; - param[:formula] = %Q!ISERROR(SEARCH("#{param[:value]}",#{start_cell}))! - when 'beginsWith' - param[:type] = 'beginsWith' - param[:formula] = - %Q!LEFT(#{start_cell},#{param[:value].size})="#{param[:value]}"! - when 'endsWith' - param[:type] = 'endsWith' - param[:formula] = - %Q!RIGHT(#{start_cell},#{param[:value].size})="#{param[:value]}"! - else - raise "Invalid text criteria '#{param[:criteria]} in conditional_formatting()" - end - end - - # Special handling of time time_period criteria. - if param[:type] == 'timePeriod' - case param[:criteria] - when 'yesterday' - param[:formula] = "FLOOR(#{start_cell},1)=TODAY()-1" - when 'today' - param[:formula] = "FLOOR(#{start_cell},1)=TODAY()" - when 'tomorrow' - param[:formula] = "FLOOR(#{start_cell},1)=TODAY()+1" - when 'last7Days' - param[:formula] = - "AND(TODAY()-FLOOR(#{start_cell},1)<=6,FLOOR(#{start_cell},1)<=TODAY())" - when 'lastWeek' - param[:formula] = - "AND(TODAY()-ROUNDDOWN(#{start_cell},0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(#{start_cell},0)<(WEEKDAY(TODAY())+7))" - when 'thisWeek' - param[:formula] = - "AND(TODAY()-ROUNDDOWN(#{start_cell},0)<=WEEKDAY(TODAY())-1,ROUNDDOWN(#{start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))" - when 'nextWeek' - param[:formula] = - "AND(ROUNDDOWN(#{start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(#{start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))" - when 'lastMonth' - param[:formula] = - "AND(MONTH(#{start_cell})=MONTH(TODAY())-1,OR(YEAR(#{start_cell})=YEAR(TODAY()),AND(MONTH(#{start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))" - when 'thisMonth' - param[:formula] = - "AND(MONTH(#{start_cell})=MONTH(TODAY()),YEAR(#{start_cell})=YEAR(TODAY()))" - when 'nextMonth' - param[:formula] = - "AND(MONTH(#{start_cell})=MONTH(TODAY())+1,OR(YEAR(#{start_cell})=YEAR(TODAY()),AND(MONTH(#{start_cell})=12,YEAR(#{start_cell})=YEAR(TODAY())+1)))" - else - raise "Invalid time_period criteria '#{param[:criteria]}' in conditional_formatting()" - end - end - - # Special handling of blanks/error types. - case param[:type] - when 'containsBlanks' - param[:formula] = "LEN(TRIM(#{start_cell}))=0" - when 'notContainsBlanks' - param[:formula] = "LEN(TRIM(#{start_cell}))>0" - when 'containsErrors' - param[:formula] = "ISERROR(#{start_cell})" - when 'notContainsErrors' - param[:formula] = "NOT(ISERROR(#{start_cell}))" - when '2_color_scale' - param[:type] = 'colorScale' - - # Color scales don't use any additional formatting. - param[:format] = nil - - # Turn off 3 color parameters. - param[:mid_type] = nil - param[:mid_color] = nil - - param[:min_type] ||= 'min' - param[:max_type] ||= 'max' - param[:min_value] ||= 0 - param[:max_value] ||= 0 - param[:min_color] ||= '#FF7128' - param[:max_color] ||= '#FFEF9C' - - param[:max_color] = get_palette_color( param[:max_color] ) - param[:min_color] = get_palette_color( param[:min_color] ) - when '3_color_scale' - param[:type] = 'colorScale' - - # Color scales don't use any additional formatting. - param[:format] = nil - - param[:min_type] ||= 'min' - param[:mid_type] ||= 'percentile' - param[:max_type] ||= 'max' - param[:min_value] ||= 0 - param[:mid_value] ||= 50 - param[:max_value] ||= 0 - param[:min_color] ||= '#F8696B' - param[:mid_color] ||= '#FFEB84' - param[:max_color] ||= '#63BE7B' - - param[:max_color] = get_palette_color(param[:max_color]) - param[:mid_color] = get_palette_color(param[:mid_color]) - param[:min_color] = get_palette_color(param[:min_color]) - when 'dataBar' - # Color scales don't use any additional formatting. - param[:format] = nil - - param[:min_type] ||= 'min' - param[:max_type] ||= 'max' - param[:min_value] ||= 0 - param[:max_value] ||= 0 - param[:bar_color] ||= '#638EC6' - - param[:bar_color] = get_palette_color(param[:bar_color]) - end - - # Store the validation information until we close the worksheet. - @cond_formats[range] ||= [] - @cond_formats[range] << param + cond_format = Package::ConditionalFormat.factory(self, *args) + @cond_formats[cond_format.range] ||= [] + @cond_formats[cond_format.range] << cond_format end # # Add an Excel table to a worksheet. # @@ -3418,222 +3730,41 @@ # in detail in a separate section "TABLES IN EXCEL". # # See also the tables.rb program in the examples directory of the distro # def add_table(*args) - col_formats = [] -=begin - # We would need to order the write statements very carefully within this - # function to support optimisation mode. Disable add_table() when it is - # on for now. - if @optimization - carp "add_table() isn't supported when set_optimization() is on" - return -1 - end -=end - # Check for a cell reference in A1 notation and substitute row and column - row1, col1, row2, col2, param = row_col_notation(args) - - # Check for a valid number of args. - raise "Not enough parameters to add_table()" if [row1, col1, row2, col2].include?(nil) - - # Check that row and col are valid without storing the values. - check_dimensions_and_update_max_min_values(row1, col1, 1, 1) - check_dimensions_and_update_max_min_values(row2, col2, 1, 1) - - # The final hashref contains the validation parameters. - param ||= {} - - check_parameter(param, valid_table_parameter, 'add_table') - # Table count is a member of Workbook, global to all Worksheet. @workbook.table_count += 1 - table = {} - table[:_columns] = [] - table[:id] = @workbook.table_count + table = Package::Table.new(self, @workbook.table_count, *args) - # Turn on Excel's defaults. - param[:banded_rows] ||= 1 - param[:header_row] ||= 1 - param[:autofilter] ||= 1 - - # Set the table options. - table[:_show_first_col] = ptrue?(param[:first_column]) ? 1 : 0 - table[:_show_last_col] = ptrue?(param[:last_column]) ? 1 : 0 - table[:_show_row_stripes] = ptrue?(param[:banded_rows]) ? 1 : 0 - table[:_show_col_stripes] = ptrue?(param[:banded_columns]) ? 1 : 0 - table[:_header_row_count] = ptrue?(param[:header_row]) ? 1 : 0 - table[:_totals_row_shown] = ptrue?(param[:total_row]) ? 1 : 0 - - # Set the table name. - if param[:name] - table[:_name] = param[:name] - else - # Set a default name. - table[:_name] = "Table#{table[:id]}" - end - - # Set the table style. - if param[:style] - table[:_style] = param[:style] - # Remove whitespace from style name. - table[:_style].gsub!(/\s/, '') - else - table[:_style] = "TableStyleMedium9" - end - - # Swap last row/col for first row/col as necessary. - row1, row2 = row2, row1 if row1 > row2 - col1, col2 = col2, col1 if col1 > col2 - - # Set the data range rows (without the header and footer). - first_data_row = row1 - last_data_row = row2 - first_data_row += 1 if param[:header_row] != 0 - last_data_row -= 1 if param[:total_row] - - # Set the table and autofilter ranges. - table[:_range] = xl_range(row1, row2, col1, col2) - table[:_a_range] = xl_range(row1, last_data_row, col1, col2) - - # If the header row if off the default is to turn autofilter off. - param[:autofilter] = 0 if param[:header_row] == 0 - - # Set the autofilter range. - if param[:autofilter] && param[:autofilter] != 0 - table[:_autofilter] = table[:_a_range] - end - - # Add the table columns. - col_id = 1 - (col1..col2).each do |col_num| - # Set up the default column data. - col_data = { - :_id => col_id, - :_name => "Column#{col_id}", - :_total_string => '', - :_total_function => '', - :_formula => '', - :_format => nil - } - - # Overwrite the defaults with any use defined values. - if param[:columns] - # Check if there are user defined values for this column. - if user_data = param[:columns][col_id - 1] - # Map user defined values to internal values. - if user_data[:header] && !user_data[:header].empty? - col_data[:_name] = user_data[:header] - end - # Handle the column formula. - if user_data[:formula] - formula = user_data[:formula] - # Remove the leading = from formula. - formula.sub!(/^=/, '') - # Covert Excel 2010 "@" ref to 2007 "#This Row". - formula.gsub!(/@/,'[#This Row],') - - col_data[:_formula] = formula - - (first_data_row..last_data_row).each do |row| - write_formula(row, col_num, formula, user_data[:format]) - end - end - - # Handle the function for the total row. - if user_data[:total_function] - function = user_data[:total_function] - - # Massage the function name. - function = function.downcase - function.gsub!(/_/, '') - function.gsub!(/\s/,'') - - function = 'countNums' if function == 'countnums' - function = 'stdDev' if function == 'stddev' - - col_data[:_total_function] = function - - formula = table_function_to_formula(function, col_data[:_name]) - write_formula(row2, col_num, formula, user_data[:format]) - elsif user_data[:total_string] - # Total label only (not a function). - total_string = user_data[:total_string] - col_data[:_total_string] = total_string - - write_string(row2, col_num, total_string, user_data[:format]) - end - - # Get the dxf format index. - if user_data[:format] - col_data[:_format] = user_data[:format].get_dxf_index - end - - # Store the column format for writing the cell data. - # It doesn't matter if it is undefined. - col_formats[col_id - 1] = user_data[:format] - end - end - - # Store the column data. - table[:_columns] << col_data - - # Write the column headers to the worksheet. - if param[:header_row] != 0 - write_string(row1, col_num, col_data[:_name]) - end - - col_id += 1 - end # Table columns. - - # Write the cell data if supplied. - if data = param[:data] - - i = 0 # For indexing the row data. - (first_data_row..last_data_row).each do |row| - next unless data[i] - - j = 0 # For indexing the col data. - (col1..col2).each do |col| - token = data[i][j] - write(row, col, token, col_formats[j]) if token - j += 1 - end - i += 1 - end - end - - # Store the table data. + @external_table_links << ['/table', "../tables/table#{table.id}.xml"] @tables << table - - # Store the link used for the rels file. - @external_table_links << ['/table', "../tables/table#{table[:id]}.xml"] - - return table + table end - # List of valid input parameters. - def valid_table_parameter - [ - :autofilter, - :banded_columns, - :banded_rows, - :columns, - :data, - :first_column, - :header_row, - :last_column, - :name, - :style, - :total_row - ] - end - private :valid_table_parameter - # # Add sparklines to the worksheet. # + # The add_sparkline worksheet method is used to add sparklines to a cell or a range of cells. + # + # worksheet.add_sparkline( + # { + # :location => 'F2', + # :range => 'Sheet1!A2:E2', + # :type => 'column', + # :style => 12 + # } + # ) + # + # See also the sparklines1.rb and sparklines2.rb example programs in the examples directory of the distro. + # + # Note: Sparklines are a feature of Excel 2010+ only. + # You can write them to an XLSX file that can be read by Excel 2007 but they won't be displayed. + # + # Sparklines are a feature of Excel 2010+ which allows you to add small charts to worksheet cells. + # These are useful for showing visual trends in data in a compact format. + # def add_sparkline(param) sparkline = {} # Check for valid input parameters. param.each_key do |k| @@ -4838,33 +4969,35 @@ attributes = ['t', 'array', 'ref', range] @writer.data_element('f', formula, attributes) end - private + def date_1904? #:nodoc: + @workbook.date_1904? + end # - # Convert a table total function to a worksheet formula. + # Convert from an Excel internal colour index to a XML style #RRGGBB index + # based on the default or user defined values in the Workbook palette. # - def table_function_to_formula(function, col_name) - subtotals = { - :average => 101, - :countNums => 102, - :count => 103, - :max => 104, - :min => 105, - :stdDev => 107, - :sum => 109, - :var => 110 - } - - unless func_num = subtotals[function.to_sym] - raise "Unsupported function '#{function}' in add_table()" + def get_palette_color(index) #:nodoc: + if index =~ /^#([0-9A-F]{6})$/i + return "FF#{$~[1]}" end - "SUBTOTAL(#{func_num},[#{col_name}])" + + # Adjust the colour index. + index -= 8 + + # Palette is passed in from the Workbook class. + rgb = @workbook.palette[index] + + # TODO Add the alpha part to the RGB. + sprintf("FF%02X%02X%02X", *rgb[0, 3]) end + private + def check_for_valid_input_params(param) check_parameter(param, valid_validation_parameter, 'data_validation') unless param.has_key?(:validate) raise WriteXLSXOptionParameterError, "Parameter :validate is required in data_validation()" @@ -4997,143 +5130,10 @@ pos += 1 end [fragments, length] end - def check_conditional_formatting_parameters(param) # :nodoc: - # Check for valid input parameters. - unless (param.keys.uniq - valid_parameter_for_conditional_formatting).empty? && - param.has_key?(:type) && - valid_type_for_conditional_formatting.has_key?(param[:type].downcase) - raise WriteXLSXOptionParameterError, "Invalid type : #{param[:type]}" - end - - param[:direction] = 'bottom' if param[:type] == 'bottom' - param[:type] = valid_type_for_conditional_formatting[param[:type].downcase] - - # Check for valid criteria types. - if param.has_key?(:criteria) && valid_criteria_type_for_conditional_formatting.has_key?(param[:criteria].downcase) - param[:criteria] = valid_criteria_type_for_conditional_formatting[param[:criteria].downcase] - end - - # Convert date/times value if required. - if %w[date time cellIs].include?(param[:type]) - param[:type] = 'cellIs' - - param[:value] = convert_date_time_if_required(param[:value]) - param[:minimum] = convert_date_time_if_required(param[:minimum]) - param[:maximum] = convert_date_time_if_required(param[:maximum]) - end - - # 'Between' and 'Not between' criteria require 2 values. - if param[:criteria] == 'between' || param[:criteria] == 'notBetween' - unless param.has_key?(:minimum) || param.has_key?(:maximum) - raise WriteXLSXOptionParameterError, "Invalid criteria : #{param[:criteria]}" - end - else - param[:minimum] = nil - param[:maximum] = nil - end - - # Convert date/times value if required. - if param[:type] == 'date' || param[:type] == 'time' - unless convert_date_time_value(param, :value) || convert_date_time_value(param, :maximum) - raise WriteXLSXOptionParameterError - end - end - end - - def convert_date_time_if_required(val) - if val =~ /T/ - date_time = convert_date_time(val) - raise "Invalid date/time value '#{val}' in conditional_formatting()" unless date_time - date_time - else - val - end - end - - # List of valid input parameters for conditional_formatting. - def valid_parameter_for_conditional_formatting - [ - :type, - :format, - :criteria, - :value, - :minimum, - :maximum, - :min_type, - :mid_type, - :max_type, - :min_value, - :mid_value, - :max_value, - :min_color, - :mid_color, - :max_color, - :bar_color - ] - end - - # List of valid validation types for conditional_formatting. - def valid_type_for_conditional_formatting - { - 'cell' => 'cellIs', - 'date' => 'date', - 'time' => 'time', - 'average' => 'aboveAverage', - 'duplicate' => 'duplicateValues', - 'unique' => 'uniqueValues', - 'top' => 'top10', - 'bottom' => 'top10', - 'text' => 'text', - 'time_period' => 'timePeriod', - 'blanks' => 'containsBlanks', - 'no_blanks' => 'notContainsBlanks', - 'errors' => 'containsErrors', - 'no_errors' => 'notContainsErrors', - '2_color_scale' => '2_color_scale', - '3_color_scale' => '3_color_scale', - 'data_bar' => 'dataBar', - 'formula' => 'expression' - } - end - - # List of valid criteria types for conditional_formatting. - def valid_criteria_type_for_conditional_formatting - { - 'between' => 'between', - 'not between' => 'notBetween', - 'equal to' => 'equal', - '=' => 'equal', - '==' => 'equal', - 'not equal to' => 'notEqual', - '!=' => 'notEqual', - '<>' => 'notEqual', - 'greater than' => 'greaterThan', - '>' => 'greaterThan', - 'less than' => 'lessThan', - '<' => 'lessThan', - 'greater than or equal to' => 'greaterThanOrEqual', - '>=' => 'greaterThanOrEqual', - 'less than or equal to' => 'lessThanOrEqual', - '<=' => 'lessThanOrEqual', - 'containing' => 'containsText', - 'not containing' => 'notContains', - 'begins with' => 'beginsWith', - 'ends with' => 'endsWith', - 'yesterday' => 'yesterday', - 'today' => 'today', - 'last 7 days' => 'last7Days', - 'last week' => 'lastWeek', - 'this week' => 'thisWeek', - 'next week' => 'nextWeek', - 'last month' => 'lastMonth', - 'this month' => 'thisMonth', - 'next month' => 'nextMonth' - } - end # Pad out the rest of the area with formatted blank cells. def write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format) (row_first .. row_last).each do |row| (col_first .. col_last).each do |col| next if row == row_first && col == col_first @@ -5300,29 +5300,10 @@ [operator, token] end # - # Convert from an Excel internal colour index to a XML style #RRGGBB index - # based on the default or user defined values in the Workbook palette. - # - def get_palette_color(index) #:nodoc: - if index =~ /^#([0-9A-F]{6})$/i - return "FF#{$~[1]}" - end - - # Adjust the colour index. - index -= 8 - - # Palette is passed in from the Workbook class. - rgb = @workbook.palette[index] - - # TODO Add the alpha part to the RGB. - sprintf("FF%02X%02X%02X", *rgb[0, 3]) - end - - # # This is an internal method that is used to filter elements of the array of # pagebreaks used in the _store_hbreak() and _store_vbreak() methods. It: # 1. Removes duplicate entries from the list. # 2. Sorts the list. # 3. Removes 0 from the list if present. @@ -6763,19 +6744,10 @@ writer.empty_tag('vertAlign', attributes) end # - # Write the <color> element. - # - def write_color(writer, name, value) #:nodoc: - attributes = [name, value] - - writer.empty_tag('color', attributes) - end - - # # Write the <tableParts> element. # def write_table_parts # Return if worksheet doesn't contain any tables. return if @tables.empty? @@ -7311,40 +7283,34 @@ # displayHidden="1" # minAxisType="custom" # maxAxisType="custom" # rightToLeft="1"> # - def write_sparkline_group(opts) # :nodoc: - empty = opts[:_empty] - user_max = 0 - user_min = 0 - a = [] + def write_sparkline_group(sparkline) # :nodoc: + @writer.start_tag( + 'x14:sparklineGroup', + attributes_from_sparkline(sparkline) + ) + end - if opts[:_max] - if opts[:_max] == 'group' - opts[:_cust_max] = 'group' - else - a << 'manualMax' << opts[:_max] - opts[:_cust_max] = 'custom' - end - end + def attributes_from_sparkline(opts) # :nodoc: + opts[:_cust_max] = cust_max_min(opts[:_max]) if opts[:_max] + opts[:_cust_min] = cust_max_min(opts[:_min]) if opts[:_min] - if opts[:_min] - if opts[:_min] == 'group' - opts[:_cust_min] = 'group' - else - a << 'manualMin' << opts[:_min] - opts[:_cust_min] = 'custom' - end - end + opts[:_cust_max] = cust_max_min(opts[:_max]) if opts[:_max] + opts[:_cust_min] = cust_max_min(opts[:_min]) if opts[:_min] + a = [] + a << 'manualMax' << opts[:_max] if opts[:_max] && opts[:_max] != 'group' + a << 'manualMin' << opts[:_min] if opts[:_min] && opts[:_min] != 'group' + # Ignore the default type attribute (line). a << 'type' << opts[:_type] if opts[:_type] != 'line' a << 'lineWeight' << opts[:_weight] if opts[:_weight] a << 'dateAxis' << 1 if opts[:_date_axis] - a << 'displayEmptyCellsAs' << empty if ptrue?(empty) + a << 'displayEmptyCellsAs' << opts[:_empty] if ptrue?(opts[:_empty]) a << 'markers' << 1 if opts[:_markers] a << 'high' << 1 if opts[:_high] a << 'low' << 1 if opts[:_low] a << 'first' << 1 if opts[:_first] @@ -7353,12 +7319,15 @@ a << 'displayXAxis' << 1 if opts[:_axis] a << 'displayHidden' << 1 if opts[:_hidden] a << 'minAxisType' << opts[:_cust_min] if opts[:_cust_min] a << 'maxAxisType' << opts[:_cust_max] if opts[:_cust_max] a << 'rightToLeft' << 1 if opts[:_reverse] + a + end - @writer.start_tag('x14:sparklineGroup', a) + def cust_max_min(max_min) # :nodoc: + max_min == 'group' ? 'group' : 'custom' end # # Helper function for the sparkline color functions below. # @@ -7512,218 +7481,45 @@ formula = formula.sub(/^=/, '') if formula.respond_to?(:sub) @writer.data_element('formula2', formula) end - # in Perl module : _write_formula() # - def write_formula_tag(data) #:nodoc: - data = data.sub(/^=/, '') if data.respond_to?(:sub) - @writer.data_element('formula', data) - end - + # Write the Worksheet conditional formats. # - # Write the <colorScale> element. - # - def write_color_scale(param) - @writer.tag_elements('colorScale') do - write_cfvo(param[:min_type], param[:min_value]) - write_cfvo(param[:mid_type], param[:mid_value]) if param[:mid_type] - write_cfvo(param[:max_type], param[:max_value]) - write_color(@writer, 'rgb', param[:min_color]) - write_color(@writer, 'rgb', param[:mid_color]) if param[:mid_color] - write_color(@writer, 'rgb', param[:max_color]) + def write_conditional_formats #:nodoc: + @cond_formats.keys.sort.each do |range| + write_conditional_formatting(range, @cond_formats[range]) end end # - # Write the <dataBar> element. - # - def write_data_bar(param) - @writer.tag_elements('dataBar') do - write_cfvo(param[:min_type], param[:min_value]) - write_cfvo(param[:max_type], param[:max_value]) - - write_color(@writer, 'rgb', param[:bar_color]) - end - end - - # - # Write the <cfvo> element. - # - def write_cfvo(type, val) - attributes = [ - 'type', type, - 'val', val - ] - - @writer.empty_tag('cfvo', attributes) - end - - # - # Write the Worksheet conditional formats. - # - def write_conditional_formats #:nodoc: - ranges = @cond_formats.keys.sort - return if ranges.empty? - - ranges.each { |range| write_conditional_formatting(range, @cond_formats[range]) } - end - - # # Write the <conditionalFormatting> element. # - # The conditional_formatting() method is used to add formatting - # to a cell or range of cells based on user defined criteria. - # - # worksheet.conditional_formatting('A1:J10', - # { - # :type => 'cell', - # :criteria => '>=', - # :value => 50, - # :format => format1 - # } - # ) - # This method contains a lot of parameters and is described - # in detail in a separate section "CONDITIONAL FORMATTING IN EXCEL". - # - # See also the conditional_format.rb program in the examples directory - # of the distro - # - def write_conditional_formatting(range, params) #:nodoc: + def write_conditional_formatting(range, cond_formats) #:nodoc: attributes = ['sqref', range] @writer.tag_elements('conditionalFormatting', attributes) do - params.each { |param| write_cf_rule(param) } + cond_formats.each { |cond_format| cond_format.write_cf_rule } end end - # - # Write the <cfRule> element. - # - def write_cf_rule(param) #:nodoc: - attributes = ['type' , param[:type]] - - if param[:format] - attributes << 'dxfId' << param[:format] - end - attributes << 'priority' << param[:priority] - - case param[:type] - when 'cellIs' - attributes << 'operator' << param[:criteria] - @writer.tag_elements('cfRule', attributes) do - if param[:minimum] && param[:maximum] - write_formula_tag(param[:minimum]) - write_formula_tag(param[:maximum]) - else - write_formula_tag(param[:value]) - end - end - when 'aboveAverage' - attributes << 'aboveAverage' << 0 if param[:criteria] =~ /below/ - attributes << 'equalAverage' << 1 if param[:criteria] =~ /equal/ - if param[:criteria] =~ /([123]) std dev/ - attributes << 'stdDev' << $~[1] - end - @writer.empty_tag('cfRule', attributes) - when 'top10' - attributes << 'percent' << 1 if param[:criteria] == '%' - attributes << 'bottom' << 1 if param[:direction] - rank = param[:value] || 10 - attributes << 'rank' << rank - @writer.empty_tag('cfRule', attributes) - when 'duplicateValues', 'uniqueValues' - @writer.empty_tag('cfRule', attributes) - when 'containsText', 'notContainsText', 'beginsWith', 'endsWith' - attributes << 'operator' << param[:criteria] - attributes << 'text' << param[:value] - @writer.tag_elements('cfRule', attributes) do - write_formula_tag(param[:formula]) - end - when 'timePeriod' - attributes << 'timePeriod' << param[:criteria] - @writer.tag_elements('cfRule', attributes) do - write_formula_tag(param[:formula]) - end - when 'containsBlanks', 'notContainsBlanks', 'containsErrors', 'notContainsErrors' - @writer.tag_elements('cfRule', attributes) do - write_formula_tag(param[:formula]) - end - when 'colorScale' - @writer.tag_elements('cfRule', attributes) do - write_color_scale(param) - end - when 'dataBar' - @writer.tag_elements('cfRule', attributes) do - write_data_bar(param) - end - when 'expression' - @writer.tag_elements('cfRule', attributes) do - write_formula_tag(param[:criteria]) - end - end - end - def store_data_to_table(cell_data) #:nodoc: row, col = cell_data.row, cell_data.col if @cell_data_table[row] @cell_data_table[row][col] = cell_data else @cell_data_table[row] = {} @cell_data_table[row][col] = cell_data end end - # Check for a cell reference in A1 notation and substitute row and column - def row_col_notation(args) # :nodoc: - if args[0] =~ /^\D/ - substitute_cellref(*args) - else - args - end - end - - # - # Check that row and col are valid and store max and min values for use in - # other methods/elements. - # - # The ignore_row/ignore_col flags is used to indicate that we wish to - # perform the dimension check without storing the value. - # - # The ignore flags are use by set_row() and data_validate. - # - def check_dimensions_and_update_max_min_values(row, col, ignore_row = 0, ignore_col = 0) #:nodoc: - check_dimensions(row, col) - store_row_max_min_values(row) if ignore_row == 0 - store_col_max_min_values(col) if ignore_col == 0 - - 0 - end - - def check_dimensions(row, col) - if !row || row >= ROW_MAX || !col || col >= COL_MAX - raise WriteXLSXDimensionError - end - 0 - end - def store_row_col_max_min_values(row, col) store_row_max_min_values(row) store_col_max_min_values(col) end - def store_row_max_min_values(row) - @dim_rowmin = row if !@dim_rowmin || (row < @dim_rowmin) - @dim_rowmax = row if !@dim_rowmax || (row > @dim_rowmax) - end - - def store_col_max_min_values(col) - @dim_colmin = col if !@dim_colmin || (col < @dim_colmin) - @dim_colmax = col if !@dim_colmax || (col > @dim_colmax) - end - # # Calculate the "spans" attribute of the <row> tag. This is an XLSX # optimisation and isn't strictly required. However, it makes comparing # files easier. # @@ -7881,13 +7677,9 @@ !!@protect end def autofilter_ref? #:nodoc: !!@autofilter_ref - end - - def date_1904? #:nodoc: - @workbook.date_1904? end def print_options_changed? #:nodoc: !!@print_options_changed end