diff options
Diffstat (limited to 'lib/roo/excelx/cell')
| -rw-r--r-- | lib/roo/excelx/cell/base.rb | 94 | ||||
| -rw-r--r-- | lib/roo/excelx/cell/boolean.rb | 27 | ||||
| -rw-r--r-- | lib/roo/excelx/cell/date.rb | 28 | ||||
| -rw-r--r-- | lib/roo/excelx/cell/datetime.rb | 111 | ||||
| -rw-r--r-- | lib/roo/excelx/cell/empty.rb | 19 | ||||
| -rw-r--r-- | lib/roo/excelx/cell/number.rb | 87 | ||||
| -rw-r--r-- | lib/roo/excelx/cell/string.rb | 19 | ||||
| -rw-r--r-- | lib/roo/excelx/cell/time.rb | 43 |
8 files changed, 428 insertions, 0 deletions
diff --git a/lib/roo/excelx/cell/base.rb b/lib/roo/excelx/cell/base.rb new file mode 100644 index 0000000..aea8808 --- /dev/null +++ b/lib/roo/excelx/cell/base.rb @@ -0,0 +1,94 @@ +module Roo + class Excelx + class Cell + class Base + attr_reader :cell_type, :cell_value, :value + + # FIXME: I think style should be deprecated. Having a style attribute + # for a cell doesn't really accomplish much. It seems to be used + # when you want to export to excelx. + attr_reader :style + + + # FIXME: Updating a cell's value should be able tochange the cell's type, + # but that isn't currently possible. This will cause weird bugs + # when one changes the value of a Number cell to a String. e.g. + # + # cell = Cell::Number(*args) + # cell.value = 'Hello' + # cell.formatted_value # => Some unexpected value + # + # Here are two possible solutions to such issues: + # 1. Don't allow a cell's value to be updated. Use a method like + # `Sheet.update_cell` instead. The simple solution. + # 2. When `cell.value = ` is called, use injection to try and + # change the type of cell on the fly. But deciding what type + # of value to pass to `cell.value=`. isn't always obvious. e.g. + # `cell.value = Time.now` should convert a cell to a DateTime, + # not a Time cell. Time cells would be hard to recognize because + # they are integers. This approach would require a significant + # change to the code as written. The complex solution. + # + # If the first solution is used, then this method should be + # deprecated. + attr_writer :value + + def initialize(value, formula, excelx_type, style, link, coordinate) + @link = !!link + @cell_value = value + @cell_type = excelx_type + @formula = formula + @style = style + @coordinate = coordinate + @type = :base + @value = link? ? Roo::Link.new(link, value) : value + end + + def type + if formula? + :formula + elsif link? + :link + else + @type + end + end + + def formula? + !!@formula + end + + def link? + !!@link + end + + alias_method :formatted_value, :value + + def to_s + formatted_value + end + + # DEPRECATED: Please use link instead. + def hyperlink + warn '[DEPRECATION] `hyperlink` is deprecated. Please use `link` instead.' + end + + # DEPRECATED: Please use cell_value instead. + def excelx_value + warn '[DEPRECATION] `excelx_value` is deprecated. Please use `cell_value` instead.' + cell_value + end + + # DEPRECATED: Please use cell_type instead. + def excelx_type + warn '[DEPRECATION] `excelx_type` is deprecated. Please use `cell_type` instead.' + cell_type + end + + def empty? + false + end + end + end + end +end diff --git a/lib/roo/excelx/cell/boolean.rb b/lib/roo/excelx/cell/boolean.rb new file mode 100644 index 0000000..fe1f691 --- /dev/null +++ b/lib/roo/excelx/cell/boolean.rb @@ -0,0 +1,27 @@ +module Roo + class Excelx + class Cell + class Boolean < Cell::Base + attr_reader :value, :formula, :format, :cell_type, :cell_value, :link, :coordinate + + def initialize(value, formula, style, link, coordinate) + super(value, formula, nil, style, link, coordinate) + @type = @cell_type = :boolean + @value = link? ? Roo::Link.new(link, value) : create_boolean(value) + end + + def formatted_value + value ? 'TRUE'.freeze : 'FALSE'.freeze + end + + private + + def create_boolean(value) + # FIXME: Using a boolean will cause methods like Base#to_csv to fail. + # Roo is using some method to ignore false/nil values. + value.to_i == 1 ? true : false + end + end + end + end +end diff --git a/lib/roo/excelx/cell/date.rb b/lib/roo/excelx/cell/date.rb new file mode 100644 index 0000000..8e2c6cb --- /dev/null +++ b/lib/roo/excelx/cell/date.rb @@ -0,0 +1,28 @@ +require 'date' + +module Roo + class Excelx + class Cell + class Date < Roo::Excelx::Cell::DateTime + attr_reader :value, :formula, :format, :cell_type, :cell_value, :link, :coordinate + + def initialize(value, formula, excelx_type, style, link, base_date, coordinate) + # NOTE: Pass all arguments to the parent class, DateTime. + super + @type = :date + @format = excelx_type.last + @value = link? ? Roo::Link.new(link, value) : create_date(base_date, value) + end + + private + + def create_date(base_date, value) + date = base_date + value.to_i + yyyy, mm, dd = date.strftime('%Y-%m-%d').split('-') + + ::Date.new(yyyy.to_i, mm.to_i, dd.to_i) + end + end + end + end +end diff --git a/lib/roo/excelx/cell/datetime.rb b/lib/roo/excelx/cell/datetime.rb new file mode 100644 index 0000000..35d93ac --- /dev/null +++ b/lib/roo/excelx/cell/datetime.rb @@ -0,0 +1,111 @@ +require 'date' + +module Roo + class Excelx + class Cell + class DateTime < Cell::Base + attr_reader :value, :formula, :format, :cell_value, :link, :coordinate + + def initialize(value, formula, excelx_type, style, link, base_date, coordinate) + super(value, formula, excelx_type, style, link, coordinate) + @type = :datetime + @format = excelx_type.last + @value = link? ? Roo::Link.new(link, value) : create_datetime(base_date, value) + end + + # Public: Returns formatted value for a datetime. Format's can be an + # standard excel format, or a custom format. + # + # Standard formats follow certain conventions. Date fields for + # days, months, and years are separated with hyhens or + # slashes ("-", /") (e.g. 01-JAN, 1/13/15). Time fields for + # hours, minutes, and seconds are separated with a colon (e.g. + # 12:45:01). + # + # If a custom format follows those conventions, then the custom + # format will be used for the a cell's formatted value. + # Otherwise, the formatted value will be in the following + # format: 'YYYY-mm-dd HH:MM:SS' (e.g. "2015-07-10 20:33:15"). + # + # Examples + # formatted_value #=> '01-JAN' + # + # Returns a String representation of a cell's value. + def formatted_value + formatter = @format.downcase.split(' ').map do |part| + if (parsed_format = parse_date_or_time_format(part)) + parsed_format + else + warn 'Unable to parse custom format. Using "YYYY-mm-dd HH:MM:SS" format.' + return @value.strftime('%F %T') + end + end.join(' ') + + @value.strftime(formatter) + end + + private + + def parse_date_or_time_format(part) + date_regex = /(?<date>[dmy]+[\-\/][dmy]+([\-\/][dmy]+)?)/ + time_regex = /(?<time>(\[?[h]\]?+:)?[m]+(:?ss|:?s)?)/ + + if part[date_regex] == part + formats = DATE_FORMATS + elsif part[time_regex] + formats = TIME_FORMATS + else + return false + end + + part.gsub(/#{formats.keys.join('|')}/, formats) + end + + DATE_FORMATS = { + 'yyyy' => '%Y', # Year: 2000 + 'yy' => '%y', # Year: 00 + # mmmmm => J-D + 'mmmm' => '%B', # Month: January + 'mmm' => '%^b', # Month: JAN + 'mm' => '%m', # Month: 01 + 'm' => '%-m', # Month: 1 + 'dddd' => '%A', # Day of the Week: Sunday + 'ddd' => '%^a', # Day of the Week: SUN + 'dd' => '%d', # Day of the Month: 01 + 'd' => '%-d' # Day of the Month: 1 + # '\\\\'.freeze => ''.freeze, # NOTE: Fixes a custom format's output. + } + + TIME_FORMATS = { + 'hh' => '%H', # Hour (24): 01 + 'h' => '%-k'.freeze, # Hour (24): 1 + # 'hh'.freeze => '%I'.freeze, # Hour (12): 08 + # 'h'.freeze => '%-l'.freeze, # Hour (12): 8 + 'mm' => '%M', # Minute: 01 + # FIXME: is this used? Seems like 'm' is used for month, not minute. + 'm' => '%-M', # Minute: 1 + 'ss' => '%S', # Seconds: 01 + 's' => '%-S', # Seconds: 1 + 'am/pm' => '%p', # Meridian: AM + '000' => '%3N', # Fractional Seconds: thousandth. + '00' => '%2N', # Fractional Seconds: hundredth. + '0' => '%1N' # Fractional Seconds: tenths. + } + + def create_datetime(base_date, value) + date = base_date + value.to_f.round(6) + datetime_string = date.strftime('%Y-%m-%d %H:%M:%S.%N') + t = round_datetime(datetime_string) + + ::DateTime.civil(t.year, t.month, t.day, t.hour, t.min, t.sec) + end + + def round_datetime(datetime_string) + /(?<yyyy>\d+)-(?<mm>\d+)-(?<dd>\d+) (?<hh>\d+):(?<mi>\d+):(?<ss>\d+.\d+)/ =~ datetime_string + + ::Time.new(yyyy, mm, dd, hh, mi, ss.to_r).round(0) + end + end + end + end +end diff --git a/lib/roo/excelx/cell/empty.rb b/lib/roo/excelx/cell/empty.rb new file mode 100644 index 0000000..49a20e7 --- /dev/null +++ b/lib/roo/excelx/cell/empty.rb @@ -0,0 +1,19 @@ + +module Roo + class Excelx + class Cell + class Empty < Cell::Base + attr_reader :value, :formula, :format, :cell_type, :cell_value, :hyperlink, :coordinate + + def initialize(coordinate) + @value = @formula = @format = @cell_type = @cell_value = @hyperlink = nil + @coordinate = coordinate + end + + def empty? + true + end + end + end + end +end diff --git a/lib/roo/excelx/cell/number.rb b/lib/roo/excelx/cell/number.rb new file mode 100644 index 0000000..2015562 --- /dev/null +++ b/lib/roo/excelx/cell/number.rb @@ -0,0 +1,87 @@ +module Roo + class Excelx + class Cell + class Number < Cell::Base + attr_reader :value, :formula, :format, :cell_value, :link, :coordinate + + def initialize(value, formula, excelx_type, style, link, coordinate) + super + # FIXME: change @type to number. This will break brittle tests. + # FIXME: Excelx_type is an array, but the first value isn't used. + @type = :float + @format = excelx_type.last + @value = link? ? Roo::Link.new(link, value) : create_numeric(value) + end + + def create_numeric(number) + return number if Excelx::ERROR_VALUES.include?(number) + case @format + when /%/ + Float(number) + when /\.0/ + Float(number) + else + (number.include?('.') || (/\A[-+]?\d+E[-+]\d+\z/i =~ number)) ? Float(number) : Integer(number) + end + end + + def formatted_value + return @cell_value if Excelx::ERROR_VALUES.include?(@cell_value) + + formatter = formats[@format] + if formatter.is_a? Proc + formatter.call(@cell_value) + elsif zero_padded_number? + "%0#{@format.size}d" % @cell_value + else + Kernel.format(formatter, @cell_value) + end + end + + def formats + # FIXME: numbers can be other colors besides red: + # [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], [COLOR n] + { + 'General' => '%.0f', + '0' => '%.0f', + '0.00' => '%.2f', + '0.000000' => '%.6f', + '#,##0' => number_format('%.0f'), + '#,##0.00' => number_format('%.2f'), + '0%' => proc do |number| + Kernel.format('%d%', number.to_f * 100) + end, + '0.00%' => proc do |number| + Kernel.format('%.2f%', number.to_f * 100) + end, + '0.00E+00' => '%.2E', + '#,##0 ;(#,##0)' => number_format('%.0f', '(%.0f)'), + '#,##0 ;[Red](#,##0)' => number_format('%.0f', '[Red](%.0f)'), + '#,##0.00;(#,##0.00)' => number_format('%.2f', '(%.2f)'), + '#,##0.00;[Red](#,##0.00)' => number_format('%.2f', '[Red](%.2f)'), + # FIXME: not quite sure what the format should look like in this case. + '##0.0E+0' => '%.1E', + '@' => proc { |number| number } + } + end + + private + + def number_format(formatter, negative_formatter = nil) + proc do |number| + if negative_formatter + formatter = number.to_i > 0 ? formatter : negative_formatter + number = number.to_f.abs + end + + Kernel.format(formatter, number).reverse.gsub(/(\d{3})(?=\d)/, '\\1,').reverse + end + end + + def zero_padded_number? + @format[/0+/] == @format + end + end + end + end +end diff --git a/lib/roo/excelx/cell/string.rb b/lib/roo/excelx/cell/string.rb new file mode 100644 index 0000000..7967806 --- /dev/null +++ b/lib/roo/excelx/cell/string.rb @@ -0,0 +1,19 @@ +module Roo + class Excelx + class Cell + class String < Cell::Base + attr_reader :value, :formula, :format, :cell_type, :cell_value, :link, :coordinate + + def initialize(value, formula, style, link, coordinate) + super(value, formula, nil, style, link, coordinate) + @type = @cell_type = :string + @value = link? ? Roo::Link.new(link, value) : value + end + + def empty? + value.empty? + end + end + end + end +end diff --git a/lib/roo/excelx/cell/time.rb b/lib/roo/excelx/cell/time.rb new file mode 100644 index 0000000..d661ab8 --- /dev/null +++ b/lib/roo/excelx/cell/time.rb @@ -0,0 +1,43 @@ +require 'date' + +module Roo + class Excelx + class Cell + class Time < Roo::Excelx::Cell::DateTime + attr_reader :value, :formula, :format, :cell_value, :link, :coordinate + + def initialize(value, formula, excelx_type, style, link, base_date, coordinate) + # NOTE: Pass all arguments to DateTime super class. + super + @type = :time + @format = excelx_type.last + @datetime = create_datetime(base_date, value) + @value = link? ? Roo::Link.new(link, value) : (value.to_f * 86_400).to_i + end + + def formatted_value + formatter = @format.gsub(/#{TIME_FORMATS.keys.join('|')}/, TIME_FORMATS) + @datetime.strftime(formatter) + end + + alias_method :to_s, :formatted_value + + private + + # def create_datetime(base_date, value) + # date = base_date + value.to_f.round(6) + # datetime_string = date.strftime('%Y-%m-%d %H:%M:%S.%N') + # t = round_datetime(datetime_string) + # + # ::DateTime.civil(t.year, t.month, t.day, t.hour, t.min, t.sec) + # end + + # def round_datetime(datetime_string) + # /(?<yyyy>\d+)-(?<mm>\d+)-(?<dd>\d+) (?<hh>\d+):(?<mi>\d+):(?<ss>\d+.\d+)/ =~ datetime_string + # + # ::Time.new(yyyy.to_i, mm.to_i, dd.to_i, hh.to_i, mi.to_i, ss.to_r).round(0) + # end + end + end + end +end |