class Rspreadsheet::Cell

Represents a cell in spreadsheet which has coordinates, contains value, formula and can be formated. You can get this object like this (suppose that @worksheet contains {Rspreadsheet::Worksheet} object)

@worksheet.cells(5,2)

Note that when using syntax like `@worksheet` or `@worksheet.B5` you won't get this object, but rather the value of the cell. More precisely it is equvalient to @worksheet.cells(5,2).value. Brief overview can be faound at [README]

Constants

InternalDateFormat
InternalDateTimeFormat
InternalTimeFormat

Attributes

rowi[R]

Row index of a cell. If you want to access the row object, see row.

worksheet[RW]

RSpreadsheet::Worksheet in which the cell is contained.

Public Class Methods

new(aworksheet,arowi,acoli) click to toggle source
# File lib/rspreadsheet/cell.rb, line 40
def initialize(aworksheet,arowi,acoli)
  raise "First parameter should be Worksheet object not #{aworksheet.class}" unless aworksheet.kind_of?(Rspreadsheet::Worksheet)
  @worksheet = aworksheet
  @rowi = arowi
  initialize_xml_tied_item(row,acoli)
end
parse_time_value(svalue) click to toggle source
# File lib/rspreadsheet/cell.rb, line 100
def self.parse_time_value(svalue)
  if (m = /^PT((?<hours>[0-9]+)H)?((?<minutes>[0-9]+)M)?((?<seconds>[0-9]+(\.[0-9]+)?)S)$/.match(svalue.delete(' ')))
    # time was parsed manually
    (StartOfEpoch + m[:hours].to_i*60*60 + m[:minutes].to_i*60 + m[:seconds].to_f.round(5))
    #BASTL: Rounding is here because LibreOffice adds some fractions of seconds randomly
  else
    begin
      Time.strptime(svalue, InternalTimeFormat)
    rescue
      Time.parse(svalue) # maybe add defaults for year-mont-day
    end
  end
end

Public Instance Methods

address() click to toggle source
# File lib/rspreadsheet/cell.rb, line 48
def address
  Tools.convert_cell_coordinates_to_address(coordinates)
end
blank?() click to toggle source
# File lib/rspreadsheet/cell.rb, line 288
def blank?; self.type==:empty or self.type==:unassigned end
border_bottom() click to toggle source
# File lib/rspreadsheet/cell.rb, line 292
def border_bottom; format.border_bottom end
border_left() click to toggle source
# File lib/rspreadsheet/cell.rb, line 293
def border_left;   format.border_left end
border_right() click to toggle source
# File lib/rspreadsheet/cell.rb, line 291
def border_right;  format.border_right end
border_top() click to toggle source
# File lib/rspreadsheet/cell.rb, line 290
def border_top;    format.border_top end
coli() click to toggle source
# File lib/rspreadsheet/cell.rb, line 37
def coli; index end
coordinates() click to toggle source
# File lib/rspreadsheet/cell.rb, line 47
def coordinates; [rowi,coli] end
datetime_value() click to toggle source
# File lib/rspreadsheet/cell.rb, line 113
def datetime_value
  vs = xmlnode.attributes['date-value'].to_s
  begin
    DateTime.strptime(vs, InternalDateTimeFormat)
  rescue
    begin
      DateTime.strptime(vs, InternalDateFormat)
    rescue
      DateTime.parse(vs)
    end
  end
end
format() click to toggle source
# File lib/rspreadsheet/cell.rb, line 267
def format
  @format ||= CellFormat.new(self)
end
formula() click to toggle source
# File lib/rspreadsheet/cell.rb, line 271
def formula
  rawformula = Tools.get_ns_attribute(xmlnode,'table','formula',nil).andand.value
  if rawformula.nil?
    nil 
  elsif rawformula.match(/^of:(.*)$/)
    $1
  else
    raise "Mischmatched value in table:formula attribute - does not start with of: (#{rawformula.to_s})"
  end
end
formula=(formulastring) click to toggle source
# File lib/rspreadsheet/cell.rb, line 281
def formula=(formulastring)
  detach_if_needed
  raise 'Formula string must begin with "=" character' unless formulastring[0,1] == '='
  remove_all_value_attributes_and_content(xmlnode)
  remove_all_type_attributes
  Tools.set_ns_attribute(xmlnode,'table','formula','of:'+formulastring.to_s)
end
guess_cell_type(avalue=nil) click to toggle source
# File lib/rspreadsheet/cell.rb, line 207
def guess_cell_type(avalue=nil)
  # try guessing by value
  valueguess = case avalue
    when Numeric then Float
    when Time then :time
    when Date, DateTime then :datetime
    when String,nil then nil
    else nil
  end
  result = valueguess
  
  if valueguess.nil?  # valueguess is most important if not succesfull then try guessing by type from node xml
    typ = xmlnode.nil? ? 'N/A' : xmlnode.attributes['value-type']
    typeguess = case typ
      when nil then nil
      when 'float' then Float
      when 'string' then String
      when 'time' then :time
      when 'date' then :datetime
      when 'percentage' then :percentage
      when 'N/A' then :unassigned
      when 'currency' then :currency
      else 
        if xmlnode.elements.size == 0
          nil
        else 
          raise "Unknown type at #{coordinates.to_s} from #{xmlnode.to_s} / elements size=#{xmlnode.elements.size.to_s} / type=#{xmlnode.attributes['value-type'].to_s}"
        end
    end

    result =
    if !typeguess.nil? # if not certain by value, but have a typeguess
      if !avalue.nil?  # with value we may try converting
        if (typeguess(avalue) rescue false) # if convertible then it is typeguess
          typeguess
        elsif (String(avalue) rescue false) # otherwise try string
          String
        else # if not convertible to anything concious then nil
          nil 
        end
      else             # without value we just beleive typeguess
        typeguess
      end
    else  # it not have a typeguess
      if (avalue.nil?) # if nil then nil
        NilClass
      elsif (String(avalue) rescue false) # convertible to String
        String
      else # giving up
        nil
      end
    end
  elsif valueguess == Float 
    case xmlnode.andand.attributes['value-type'] 
      when 'percentage' then result = :percentage
      when 'currency' then result = :currency
    end
  end
  result
end
inspect() click to toggle source
# File lib/rspreadsheet/cell.rb, line 62
def inspect
  "#<Rspreadsheet::Cell\n row:#{rowi}, col:#{coli} address:#{address}\n type: #{guess_cell_type.to_s}, value:#{value}\n mode: #{mode}, format: #{format.inspect}\n>"
end
parent() click to toggle source

@!group XMLTiedItem related methods and extensions

# File lib/rspreadsheet/cell.rb, line 36
def parent; row end
relative(rowdiff,coldiff) click to toggle source
# File lib/rspreadsheet/cell.rb, line 189
def relative(rowdiff,coldiff)
  @worksheet.cells(self.rowi+rowdiff, self.coli+coldiff)
end
remove_all_type_attributes() click to toggle source
# File lib/rspreadsheet/cell.rb, line 186
def remove_all_type_attributes
  set_type_attribute(nil)
end
remove_all_value_attributes_and_content(node=xmlnode) click to toggle source

TODO: using this is NOT in line with the general intent of forward compatibility

# File lib/rspreadsheet/cell.rb, line 179
def remove_all_value_attributes_and_content(node=xmlnode)
  if att = Tools.get_ns_attribute(node, 'office','value') then att.remove! end
  if att = Tools.get_ns_attribute(node, 'office','date-value') then att.remove! end
  if att = Tools.get_ns_attribute(node, 'office','time-value') then att.remove! end
  if att = Tools.get_ns_attribute(node, 'table','formula') then att.remove! end
  node.content=''
end
row() click to toggle source
# File lib/rspreadsheet/cell.rb, line 46
def row; @worksheet.rows(rowi) end
set_rowi(arowi) click to toggle source
# File lib/rspreadsheet/cell.rb, line 39
def set_rowi(arowi); @rowi = arowi end
set_type_attribute(typestring) click to toggle source
# File lib/rspreadsheet/cell.rb, line 174
def set_type_attribute(typestring)
  Tools.set_ns_attribute(xmlnode,'office','value-type',typestring)
  Tools.set_ns_attribute(xmlnode,'calcext','value-type',typestring)
end
time_value() click to toggle source

according to docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1417674_253892949 the value od time-value is in a “duration” format defined here www.w3.org/TR/xmlschema-2/#duration this method converts the time-value to Time object. Note that it does not check if the cell is in time-value or not, this is the responibility of caller. However beware that specification does not specify how the time should be interpreted. By observing LibreOffice behaviour, I have found these options

1. "Time only cell" has time is stored as PT16H22M35S (16:22:35) where the duration is duration from midnight.  
   Because ruby does NOT have TimeOfDay type we need to represent that as DateTime. I have chosen 1899-12-30 00:00:00 as 
   StartOfEpoch time, because it plays well with case 2.
2. "DateTime converted to Time only cell" has time stored as PT923451H33M00.000000168S (15:33:00 with date part 2005-05-05 
   before conversion to time only). It is strange format which seems to have hours meaning number of hours after 1899-12-30 00:00:00

Returns time-value of the cell. It does not check if cell has or should have this value, it is responibility of caller to do so.

# File lib/rspreadsheet/cell.rb, line 97
def time_value
  Cell.parse_time_value(xmlnode.attributes['time-value'].to_s)
end
to_s() click to toggle source
# File lib/rspreadsheet/cell.rb, line 52
def to_s; value.to_s end
type() click to toggle source
# File lib/rspreadsheet/cell.rb, line 192
def type
  gct = guess_cell_type
  case 
    when gct == Float  then :float
    when gct == String then :string
    when gct == :datetime  then :datetime
    when gct == :time  then :time
    when gct == :percentage then :percentage
    when gct == :unassigned then :unassigned
    when gct == :currency then :currency
    when gct == NilClass then :empty
    when gct == nil then :unknown
    else :unknown
  end
end
value() click to toggle source
# File lib/rspreadsheet/cell.rb, line 65
def value
  gt = guess_cell_type
  amode = self.mode 
  if (amode == :regular) or (amode == :repeated)
    case 
      when gt == nil then nil
      when gt == Float then xmlnode.attributes['value'].to_f
      when gt == String then xmlnode.elements.first.andand.content.to_s
      when gt == :datetime then datetime_value
      when gt == :time then time_value
      when gt == :percentage then xmlnode.attributes['value'].to_f
      when gt == :currency then xmlnode.attributes['value'].to_d
    end
  elsif amode == :outbound
    nil
  else
    raise "Unknown cell mode #{amode}"
  end
end
value=(avalue) click to toggle source
# File lib/rspreadsheet/cell.rb, line 125
  def value=(avalue)
    detach_if_needed
    if self.mode == :regular
      gt = guess_cell_type(avalue)
#       raise 'here'+gt.to_s if avalue == 666.66
      case
        when gt == nil then raise 'This value type is not storable to cell'
        when gt == Float then
          remove_all_value_attributes_and_content
          set_type_attribute('float')
          sav=avalue.to_f.to_s # to_f handles case when avalue is decimal number
          Tools.set_ns_attribute(xmlnode,'office','value', sav) 
          xmlnode << Tools.prepare_ns_node('text','p', sav)
        when gt == String then
          remove_all_value_attributes_and_content
          set_type_attribute('string')
          xmlnode << Tools.prepare_ns_node('text','p', avalue.to_s)
        when gt == :datetime then 
          remove_all_value_attributes_and_content
          set_type_attribute('date')
          if avalue.kind_of?(DateTime) or avalue.kind_of?(Date) or avalue.kind_of?(Time)
            avalue = avalue.strftime(InternalDateTimeFormat)
            Tools.set_ns_attribute(xmlnode,'office','date-value', avalue)
            xmlnode << Tools.prepare_ns_node('text','p', avalue)
          end
        when gt == :time then
          remove_all_value_attributes_and_content
          set_type_attribute('time')
          if avalue.kind_of?(DateTime) or avalue.kind_of?(Date) or avalue.kind_of?(Time)
            Tools.set_ns_attribute(xmlnode,'office','time-value', avalue.strftime(InternalTimeFormat))
            xmlnode << Tools.prepare_ns_node('text','p', avalue.strftime('%H:%M'))
          end
        when gt == :percentage then
          remove_all_value_attributes_and_content
          set_type_attribute('percentage')
          Tools.set_ns_attribute(xmlnode,'office','value', ('%0.2d' % avalue.to_f)+'%') 
          xmlnode << Tools.prepare_ns_node('text','p', (avalue.to_f*100).round.to_s+'%')
        when gt == :currency then
          remove_all_value_attributes_and_content
          set_type_attribute('currency')
          unless avalue.nil?
            Tools.set_ns_attribute(xmlnode,'office','value', '%f' % avalue.to_d)
            xmlnode << Tools.prepare_ns_node('text','p', avalue.to_d.to_s+' '+self.format.currency)
          end
      end
    else
      raise "Unknown cell mode #{self.mode}"
    end
  end
valuexml() click to toggle source
# File lib/rspreadsheet/cell.rb, line 53
def valuexml; self.valuexmlnode.andand.inner_xml end
valuexmlfindall(path) click to toggle source

use this to find node in cell xml. ex. xmlfind('.//text:a') finds all link nodes

# File lib/rspreadsheet/cell.rb, line 56
def valuexmlfindall(path)
  valuexmlnode.nil? ? [] : valuexmlnode.find(path)
end
valuexmlfindfirst(path) click to toggle source
# File lib/rspreadsheet/cell.rb, line 59
def valuexmlfindfirst(path)
  valuexmlfindall(path).first
end
valuexmlnode() click to toggle source
# File lib/rspreadsheet/cell.rb, line 54
def valuexmlnode; self.xmlnode.elements.first end