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. Brief overview can be faound at [README]

Instance Attribute Summary collapse

XMLTiedItem related methods and extensions collapse

Constructor Details

#initialize(aworksheet, arowi, acoli) ⇒ Cell

Returns a new instance of Cell.


40
41
42
43
44
45
 
# 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

Instance Attribute Details

#rowi ⇒ Object (readonly)

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


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

def rowi
  @rowi
end

#worksheet ⇒ Object

RSpreadsheet::Worksheet in which the cell is contained.


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

def worksheet
  @worksheet
end

Class Method Details

.parse_time_value(svalue) ⇒ Object 


100
101
102
103
104
105
106
107
108
109
110
111
112
 
# 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

Instance Method Details

#address ⇒ Object 


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

def address
  Tools.convert_cell_coordinates_to_address(coordinates)
end

#blank? ⇒ Boolean

Returns:

  • (Boolean) 

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

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

#border_bottom ⇒ Object 


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

def border_bottom; format.border_bottom end

#border_left ⇒ Object 


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

def border_left;   format.border_left end

#border_right ⇒ Object 


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

def border_right;  format.border_right end

#border_top ⇒ Object 


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

def border_top;    format.border_top end

#coli ⇒ Object 


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

def coli; index end

#coordinates ⇒ Object 


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

def coordinates; [rowi,coli] end

#datetime_value ⇒ Object 


113
114
115
116
117
118
119
120
121
122
123
124
 
# 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 ⇒ Object 


267
268
269
 
# File 'lib/rspreadsheet/cell.rb', line 267

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

#formula ⇒ Object 


271
272
273
274
275
276
277
278
279
280
 
# 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) ⇒ Object 


281
282
283
284
285
286
287
 
# 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) ⇒ Object 


207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
 
# 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 ⇒ Object 


62
63
64
 
# 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 ⇒ Object 


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

def parent; row end

#relative(rowdiff, coldiff) ⇒ Object 


189
190
191
 
# File 'lib/rspreadsheet/cell.rb', line 189

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

#remove_all_type_attributes ⇒ Object 


186
187
188
 
# 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) ⇒ Object

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


179
180
181
182
183
184
185
 
# 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 ⇒ Object 


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

def row; @worksheet.rows(rowi) end

#set_rowi(arowi) ⇒ Object

this should ONLY be used by parent row


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

def set_rowi(arowi); @rowi = arowi end

#set_type_attribute(typestring) ⇒ Object 


174
175
176
177
 
# 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 ⇒ Object

according to http://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 https://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.


97
98
99
 
# File 'lib/rspreadsheet/cell.rb', line 97

def time_value
  Cell.parse_time_value(xmlnode.attributes['time-value'].to_s)
end

#to_s ⇒ Object 


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

def to_s; value.to_s end

#type ⇒ Object 


192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
 
# 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 ⇒ Object 


65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# 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) ⇒ Object 


125
126
127
128
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
 
# 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 ⇒ Object 


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

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


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

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

#valuexmlfindfirst(path) ⇒ Object 


59
60
61
 
# File 'lib/rspreadsheet/cell.rb', line 59

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

#valuexmlnode ⇒ Object 


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

def valuexmlnode; self.xmlnode.elements.first end