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. More precisely it is equvalient to @worksheet.cells(5,2).value.

Constant Summary collapse

InternalDateFormat = 
'%Y-%m-%d'
InternalTimeFormat = 
'PT%HH%MM%SS'

Instance Attribute Summary collapse

XMLTiedItem related methods and extensions collapse

Constructor Details

#initialize(aworksheet, arowi, acoli) ⇒ Cell

Returns a new instance of Cell.



39
40
41
42
43
44
 
# File 'lib/rspreadsheet/cell.rb', line 39

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

Instance Attribute Details

#rowi ⇒ Object (readonly)

Returns the value of attribute rowi.



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

def rowi
  @rowi
end

#worksheet ⇒ Object

Returns the value of attribute worksheet.



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

def worksheet
  @worksheet
end

Instance Method Details

#address ⇒ Object 



206
207
208
 
# File 'lib/rspreadsheet/cell.rb', line 206

def address
  Tools.convert_cell_coordinates_to_address(coordinates)
end

#blank? ⇒ Boolean

Returns:

  • (Boolean) 


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

def blank?; self.type==:empty or self.type==:unassigned end

#border_bottom ⇒ Object 



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

def border_bottom; format.border_bottom end

#border_left ⇒ Object 



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

def border_left;   format.border_left end

#border_right ⇒ Object 



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

def border_right;  format.border_right end

#border_top ⇒ Object 



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

def border_top;    format.border_top end

#coli ⇒ Object 



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

def coli; index end

#coordinates ⇒ Object 



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

def coordinates; [rowi,coli] end

#format ⇒ Object 



203
204
205
 
# File 'lib/rspreadsheet/cell.rb', line 203

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

#formula ⇒ Object 



210
211
212
213
214
215
216
217
218
219
 
# File 'lib/rspreadsheet/cell.rb', line 210

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 



220
221
222
223
224
225
226
 
# File 'lib/rspreadsheet/cell.rb', line 220

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 



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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
 
# File 'lib/rspreadsheet/cell.rb', line 146

def guess_cell_type(avalue=nil)
  # try guessing by value
  valueguess = case avalue
    when Numeric then Float
    when Time then Time
    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 'time' then Time
      when 'date' then Date
      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 and xmlnode.andand.attributes['value-type'] == 'percentage'
    result = :percentage
  end
  result
end

#inspect ⇒ Object 



57
58
59
 
# File 'lib/rspreadsheet/cell.rb', line 57

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



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

def parent; row end

#relative(rowdiff, coldiff) ⇒ Object 



128
129
130
 
# File 'lib/rspreadsheet/cell.rb', line 128

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

#remove_all_type_attributes ⇒ Object 



125
126
127
 
# File 'lib/rspreadsheet/cell.rb', line 125

def remove_all_type_attributes
  set_type_attribute(nil)
end

#remove_all_value_attributes_and_content(node = xmlnode) ⇒ Object 



118
119
120
121
122
123
124
 
# File 'lib/rspreadsheet/cell.rb', line 118

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



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

def row; @worksheet.rows(rowi) end

#set_rowi(arowi) ⇒ Object

this should ONLY be used by parent row



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

def set_rowi(arowi); @rowi = arowi end

#set_type_attribute(typestring) ⇒ Object 



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

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 



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

def to_s; value.to_s end

#type ⇒ Object 



131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/rspreadsheet/cell.rb', line 131

def type
  gct = guess_cell_type
  case 
    when gct == Float  then :float
    when gct == String then :string
    when gct == Date   then :date
    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 ⇒ Object 



60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/rspreadsheet/cell.rb', line 60

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, InternalDateFormat)
      when gt == Time then Time.strptime(xmlnode.attributes['time-value'].to_s, InternalTimeFormat)
      when gt == :percentage then xmlnode.attributes['value'].to_f
      when gt == :currency then xmlnode.attributes['value'].to_d
    end
  elsif self.mode == :outbound
    nil
  else
    raise "Unknown cell mode #{self.mode}"
  end
end

#value=(avalue) ⇒ Object 



78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/rspreadsheet/cell.rb', line 78

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')
        avalue = avalue.strftime(InternalDateFormat)
        Tools.set_ns_attribute(xmlnode,'office','date-value', avalue)
        xmlnode << Tools.prepare_ns_node('text','p', avalue)
      when gt == Time then
        remove_all_value_attributes_and_content(xmlnode)
        set_type_attribute('time')
        Tools.set_ns_attribute(xmlnode,'office','time-value', avalue.strftime(InternalTimeFormat))
        xmlnode << Tools.prepare_ns_node('text','p', avalue.strftime('%H:%M'))
      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 



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

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



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

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

#valuexmlfindfirst(path) ⇒ Object 



54
55
56
 
# File 'lib/rspreadsheet/cell.rb', line 54

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

#valuexmlnode ⇒ Object 



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

def valuexmlnode; self.xmlnode.elements.first end

#xml_options ⇒ Object 



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

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