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
RSpreadsheet::Worksheet in which the cell is contained.
Public Class Methods
# 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
# 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
# File lib/rspreadsheet/cell.rb, line 48 def address Tools.convert_cell_coordinates_to_address(coordinates) end
# File lib/rspreadsheet/cell.rb, line 288 def blank?; self.type==:empty or self.type==:unassigned end
# File lib/rspreadsheet/cell.rb, line 292 def border_bottom; format.border_bottom end
# File lib/rspreadsheet/cell.rb, line 293 def border_left; format.border_left end
# File lib/rspreadsheet/cell.rb, line 291 def border_right; format.border_right end
# File lib/rspreadsheet/cell.rb, line 290 def border_top; format.border_top end
# File lib/rspreadsheet/cell.rb, line 37 def coli; index end
# File lib/rspreadsheet/cell.rb, line 47 def coordinates; [rowi,coli] end
# 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
# File lib/rspreadsheet/cell.rb, line 267 def format @format ||= CellFormat.new(self) end
# 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
# 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
# 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
# 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
@!group XMLTiedItem
related methods and extensions
# File lib/rspreadsheet/cell.rb, line 36 def parent; row end
# File lib/rspreadsheet/cell.rb, line 189 def relative(rowdiff,coldiff) @worksheet.cells(self.rowi+rowdiff, self.coli+coldiff) end
# File lib/rspreadsheet/cell.rb, line 186 def remove_all_type_attributes set_type_attribute(nil) end
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
# File lib/rspreadsheet/cell.rb, line 46 def row; @worksheet.rows(rowi) end
# File lib/rspreadsheet/cell.rb, line 39 def set_rowi(arowi); @rowi = arowi end
# 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
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
# File lib/rspreadsheet/cell.rb, line 52 def to_s; value.to_s end
# 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
# 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
# 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
# File lib/rspreadsheet/cell.rb, line 53 def valuexml; self.valuexmlnode.andand.inner_xml end
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
# File lib/rspreadsheet/cell.rb, line 59 def valuexmlfindfirst(path) valuexmlfindall(path).first end
# File lib/rspreadsheet/cell.rb, line 54 def valuexmlnode; self.xmlnode.elements.first end