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.



41
42
43
44
45
46
 
# File 'lib/rspreadsheet/cell.rb', line 41

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 



96
97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/rspreadsheet/cell.rb', line 96

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 



249
250
251
 
# File 'lib/rspreadsheet/cell.rb', line 249

def address
  Tools.convert_cell_coordinates_to_address(coordinates)
end

#blank? ⇒ Boolean

Returns:

  • (Boolean) 


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

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

#border_bottom ⇒ Object 



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

def border_bottom; format.border_bottom end

#border_left ⇒ Object 



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

def border_left;   format.border_left end

#border_right ⇒ Object 



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

def border_right;  format.border_right end

#border_top ⇒ Object 



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

def border_top;    format.border_top end

#coli ⇒ Object 



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

def coli; index end

#coordinates ⇒ Object 



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

def coordinates; [rowi,coli] end

#datetime_value ⇒ Object 



109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/rspreadsheet/cell.rb', line 109

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 



246
247
248
 
# File 'lib/rspreadsheet/cell.rb', line 246

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

#formula ⇒ Object 



253
254
255
256
257
258
259
260
261
262
 
# File 'lib/rspreadsheet/cell.rb', line 253

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 



263
264
265
266
267
268
269
 
# File 'lib/rspreadsheet/cell.rb', line 263

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 



189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
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
 
# File 'lib/rspreadsheet/cell.rb', line 189

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 and xmlnode.andand.attributes['value-type'] == 'percentage'
    result = :percentage
  end
  result
end

#inspect ⇒ Object 



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

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 



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

def parent; row end

#relative(rowdiff, coldiff) ⇒ Object 



171
172
173
 
# File 'lib/rspreadsheet/cell.rb', line 171

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

#remove_all_type_attributes ⇒ Object 



168
169
170
 
# File 'lib/rspreadsheet/cell.rb', line 168

def remove_all_type_attributes
  set_type_attribute(nil)
end

#remove_all_value_attributes_and_content(node = xmlnode) ⇒ Object 



161
162
163
164
165
166
167
 
# File 'lib/rspreadsheet/cell.rb', line 161

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 



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

def row; @worksheet.rows(rowi) end

#set_rowi(arowi) ⇒ Object

this should ONLY be used by parent row



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

def set_rowi(arowi); @rowi = arowi end

#set_type_attribute(typestring) ⇒ Object 



157
158
159
160
 
# File 'lib/rspreadsheet/cell.rb', line 157

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.



93
94
95
 
# File 'lib/rspreadsheet/cell.rb', line 93

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

#to_s ⇒ Object 



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

def to_s; value.to_s end

#type ⇒ Object 



174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'lib/rspreadsheet/cell.rb', line 174

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 



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

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 == :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 self.mode == :outbound
    nil
  else
    raise "Unknown cell mode #{self.mode}"
  end
end

#value=(avalue) ⇒ Object 



121
122
123
124
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
 
# File 'lib/rspreadsheet/cell.rb', line 121

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 == :datetime then 
        remove_all_value_attributes_and_content(xmlnode)
        set_type_attribute('date')
        avalue = avalue.strftime(InternalDateTimeFormat)
        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 



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

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



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

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

#valuexmlfindfirst(path) ⇒ Object 



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

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

#valuexmlnode ⇒ Object 



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

def valuexmlnode; self.xmlnode.elements.first end

#xml_options ⇒ Object 



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

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