Class: Rspreadsheet::Cell

Inherits:
XMLTiedItem
  • Object
show all
Defined in:
lib/rspreadsheet/cell.rb

Overview

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 Worksheet object)

@worksheet.cells(5,2)

Note that when using syntax like @worksheet[5,2] or @worksheet.B5 you won't get this object, but rather the value of the cell.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(aworksheet, arowi, acoli) ⇒ Cell

Returns a new instance of Cell.



33
34
35
36
37
38
 
# File 'lib/rspreadsheet/cell.rb', line 33

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
  @coli = acoli
end

Instance Attribute Details

#coli ⇒ Object

Returns the value of attribute coli.



22
23
24
 
# File 'lib/rspreadsheet/cell.rb', line 22

def coli
  @coli
end

#rowi ⇒ Object

Returns the value of attribute rowi.



22
23
24
 
# File 'lib/rspreadsheet/cell.rb', line 22

def rowi
  @rowi
end

#worksheet ⇒ Object

Returns the value of attribute worksheet.



22
23
24
 
# File 'lib/rspreadsheet/cell.rb', line 22

def worksheet
  @worksheet
end

Instance Method Details

#address ⇒ Object 



187
188
189
 
# File 'lib/rspreadsheet/cell.rb', line 187

def address
  Tools.convert_cell_coordinates_to_address(coordinates)
end

#coordinates ⇒ Object 



40
 
# File 'lib/rspreadsheet/cell.rb', line 40

def coordinates; [rowi,coli] end

#format ⇒ Object 



184
185
186
 
# File 'lib/rspreadsheet/cell.rb', line 184

def format
  @format ||= CellFormat.new(self)
end

#formula ⇒ Object 



191
192
193
194
195
196
197
198
199
200
 
# File 'lib/rspreadsheet/cell.rb', line 191

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) ⇒ Object 



201
202
203
204
205
206
207
 
# File 'lib/rspreadsheet/cell.rb', line 201

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) ⇒ Object 



129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/rspreadsheet/cell.rb', line 129

def guess_cell_type(avalue=nil)
  # try guessing by value
  valueguess = case avalue
    when Numeric then Float
    when Date then Date
    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 'date' then Date
      when 'percentage' then :percentage
      when 'N/A' then :unassigned
      else 
        if xmlnode.children.size == 0
          nil
        else 
          raise "Unknown type at #{coordinates.to_s} from #{xmlnode.to_s} / children size=#{xmlnode.children.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 and xmlnode.andand.attributes['value-type'] == 'percentage'
    result = :percentage
  end
  result
end

#index ⇒ Object 



30
 
# File 'lib/rspreadsheet/cell.rb', line 30

def index; @coli end

#inspect ⇒ Object 



51
52
53
 
# File 'lib/rspreadsheet/cell.rb', line 51

def inspect
  "#<Rspreadsheet::Cell:Cell\n row:#{rowi}, col:#{coli} address:#{address}\n type: #{guess_cell_type.to_s}, value:#{value}\n mode: #{mode}\n>"
end

#parent ⇒ Object

defining abstract methods from XMLTiedItem returns parent XMLTiedArray object of myself (XMLTiedItem)



29
 
# File 'lib/rspreadsheet/cell.rb', line 29

def parent; row end

#relative(rowdiff, coldiff) ⇒ Object 



113
114
115
 
# File 'lib/rspreadsheet/cell.rb', line 113

def relative(rowdiff,coldiff)
  @worksheet.cells(self.rowi+rowdiff, self.coli+coldiff)
end

#remove_all_type_attributes ⇒ Object 



110
111
112
 
# File 'lib/rspreadsheet/cell.rb', line 110

def remove_all_type_attributes
  set_type_attribute(nil)
end

#remove_all_value_attributes_and_content(node = xmlnode) ⇒ Object 



104
105
106
107
108
109
 
# File 'lib/rspreadsheet/cell.rb', line 104

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, 'table','formula') then att.remove! end
  node.content=''
end

#row ⇒ Object 



39
 
# File 'lib/rspreadsheet/cell.rb', line 39

def row; @worksheet.rows(rowi) end

#set_index(value) ⇒ Object 



31
 
# File 'lib/rspreadsheet/cell.rb', line 31

def set_index(value); @coli=value end

#set_rowi(arowi) ⇒ Object

this should ONLY be used by parent row



32
 
# File 'lib/rspreadsheet/cell.rb', line 32

def set_rowi(arowi); @rowi = arowi end

#set_type_attribute(typestring) ⇒ Object 



100
101
102
103
 
# File 'lib/rspreadsheet/cell.rb', line 100

def set_type_attribute(typestring)
  Tools.set_ns_attribute(xmlnode,'office','value-type',typestring)
  Tools.set_ns_attribute(xmlnode,'calcext','value-type',typestring)
end

#to_s ⇒ Object 



41
 
# File 'lib/rspreadsheet/cell.rb', line 41

def to_s; value.to_s end

#type ⇒ Object 



116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/rspreadsheet/cell.rb', line 116

def type
  gct = guess_cell_type
  case 
    when gct == Float  then :float
    when gct == String then :string
    when gct == Date   then :date
    when gct == :percentage then :percentage
    when gct == :unassigned then :unassigned
    when gct == NilClass then :empty
    when gct == nil then :unknown
    else :unknown
  end
end

#value ⇒ Object 



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/rspreadsheet/cell.rb', line 54

def value
  gt = guess_cell_type
  if (self.mode == :regular) or (self.mode == :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 == Date then Date.strptime(xmlnode.attributes['date-value'].to_s, '%Y-%m-%d')
      when gt == :percentage then xmlnode.attributes['value'].to_f
    end
  elsif self.mode == :outbound
    nil
  else
    raise "Unknown cell mode #{self.mode}"
  end
end

#value=(avalue) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/rspreadsheet/cell.rb', line 70

def value=(avalue)
  detach_if_needed
  if self.mode == :regular
    gt = guess_cell_type(avalue)
    case
      when gt == nil then raise 'This value type is not storable to cell'
      when gt == Float then
        remove_all_value_attributes_and_content(xmlnode)
        set_type_attribute('float')
        Tools.set_ns_attribute(xmlnode,'office','value', avalue.to_s) 
        xmlnode << Tools.prepare_ns_node('text','p', avalue.to_f.to_s)
      when gt == String then
        remove_all_value_attributes_and_content(xmlnode)
        set_type_attribute('string')
        xmlnode << Tools.prepare_ns_node('text','p', avalue.to_s)
      when gt == Date then 
        remove_all_value_attributes_and_content(xmlnode)
        set_type_attribute('date')
        Tools.set_ns_attribute(xmlnode,'office','date-value', avalue.strftime('%Y-%m-%d'))
        xmlnode << Tools.prepare_ns_node('text','p', avalue.strftime('%Y-%m-%d')) 
      when gt == :percentage then
        remove_all_value_attributes_and_content(xmlnode)
        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+'%')
    end
  else
    raise "Unknown cell mode #{self.mode}"
  end
end

#valuexml ⇒ Object 



42
 
# File 'lib/rspreadsheet/cell.rb', line 42

def valuexml; self.valuexmlnode.andand.inner_xml end

#valuexmlfindall(path) ⇒ Object

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



45
46
47
 
# File 'lib/rspreadsheet/cell.rb', line 45

def valuexmlfindall(path)
  valuexmlnode.nil? ? [] : valuexmlnode.find(path)
end

#valuexmlfindfirst(path) ⇒ Object 



48
49
50
 
# File 'lib/rspreadsheet/cell.rb', line 48

def valuexmlfindfirst(path)
  valuexmlfindall(path).first
end

#valuexmlnode ⇒ Object 



43
 
# File 'lib/rspreadsheet/cell.rb', line 43

def valuexmlnode; self.xmlnode.children.first end

#xml_options ⇒ Object

xml_options[:xml_items_node_name] gives the name of the tag representing cell xml_options[:number-columns-repeated] gives the name of the previous tag which sais how many times the item is repeated



25
 
# File 'lib/rspreadsheet/cell.rb', line 25

def xml_options; {:xml_items_node_name => 'table-cell', :xml_repeated_attribute => 'number-columns-repeated'} end