Class: Axlsx::Cell

Inherits:
Object
  • Object
show all
Defined in:
lib/axlsx/workbook/worksheet/cell.rb

Overview

Note:

The recommended way to generate cells is via Worksheet#add_row

A cell in a worksheet. Cell stores inforamation requried to serialize a single worksheet cell to xml. You must provde the Row that the cell belongs to and the cells value. The data type will automatically be determed if you do not specify the :type option. The default style will be applied if you do not supply the :style option. Changing the cell’s type will recast the value to the type specified. Altering the cell’s value via the property accessor will also automatically cast the provided value to the cell’s type.

Examples:

Manually creating and manipulating Cell objects 

ws = Workbook.new.add_worksheet 
# This is the simple, and recommended way to create cells. Data types will automatically be determined for you.
ws.add_row :values => [1,"fish",Time.now]

# but you can also do this
r = ws.add_row
r.add_cell 1

# or even this
r = ws.add_row
c = Cell.new row, 1, :value=>integer

# cells can also be accessed via Row#cells. The example here changes the cells type, which will automatically updated the value from 1 to 1.0
r.cells.last.type = :float

See Also:

Instance Attribute Summary (collapse)

Instance Method Summary (collapse)

Constructor Details

- (Cell) initialize(row, value = "", options = {})

A new instance of Cell

Parameters:

  • row (Row) —

    The row this cell belongs to.

  • value (Any) (defaults to: "") —

    The value associated with this cell.

  • options (Hash) (defaults to: {}) —

    a customizable set of options

Options Hash (options):

  • type (Symbol) —

    The intended data type for this cell. If not specified the data type will be determined internally based on the vlue provided.

  • style (Integer) —

    The index of the cellXfs item to be applied to this cell. If not specified, the default style (0) will be applied. 



69
70
71
72
73
74
75
76
77
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 69

def initialize(row, value="", options={})
  self.row=row
  #reference for validation
  @styles = row.worksheet.workbook.styles
  @type= options[:type] || cell_type_from_value(value)
  self.style = options[:style] || 0 
  @value = cast_value(value)
  @row.cells << self
end

Instance Attribute Details

- (Row) row

The row this cell belongs to.

Returns:



33
34
35
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 33

def row
  @row
end

- (Integer) style

The index of the cellXfs item to be applied to this cell.

Returns:

  • (Integer)

See Also:



29
30
31
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 29

def style
  @style
end

- (Symbol) type

Note:

If the value provided cannot be cast into the type specified, type is changed to :string and the following logic is applied. 

 :string to :integer or :float, type coversions always return 0 or 0.0
 :string, :integer, or :float to :time conversions always return the original value as a string and set the cells type to :string.

No support is currently implemented for parsing time strings.

The cell’s data type. Currently only four types are supported, :time, :float, :integer and :string. Changing the type for a cell will recast the value into that type. If no type option is specified in the constructor, the type is automatically determed.

Returns:

  • (Symbol) —

    The type of data this cell’s value is cast to.

Raises:

  • (ArgumentExeption) —

    Cell.type must be one of [:time, :float, :integer, :string]

See Also:



46
47
48
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 46

def type
  @type
end

- (Object) value

The value of this cell.

Returns:

  • casted value based on cell’s type attribute. 



57
58
59
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 57

def value
  @value
end

Instance Method Details

- (Object) cast_value(v)

Note:

About Time - Time in OOXML is different from what you might expect. The history as to why is interesting, but you can safely assume that if you are generating docs on a mac, you will want to specify Workbook.1904 as true when using time typed values.

Cast the value into this cells data type.

See Also:

  • Axlsx#date1904


169
170
171
172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 169

def cast_value(v)
  if (@type == :time && v.is_a?(Time)) || (@type == :time && v.respond_to?(:to_time))
    v = v.to_time
    epoc = Workbook.date1904 ? Time.local(1904,1,1,0,0,0,0,v.zone) : Time.local(1900,1,1,0,0,0,0,v.zone)
    ((v - epoc) /60.0/60.0/24.0).to_f
  elsif @type == :float
    v.to_f
  elsif @type == :integer
    v.to_i
  else
    @type = :string
    v.to_s
  end
end

- (Symbol) cell_type_from_value(v)

Note:

This is only used when a cell is created but no :type option is specified, the following rules apply:

  1. If the value is an instance of Time, the type is set to :time
  2. :float and :integer types are determined by regular expression matching.
  3. Anything that does not meet either of the above is determined to be :string.

Determines the cell type based on the cell value.

Returns:

  • (Symbol) —

    The determined type 



153
154
155
156
157
158
159
160
161
162
163
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 153

def cell_type_from_value(v)      
  if v.is_a? Time
    :time
  elsif v.to_s.match(/\A[+-]?\d+?\Z/) #numeric
    :integer
  elsif v.to_s.match(/\A[+-]?\d+\.\d+?\Z/) #float
    :float
  else
    :string
  end
end

- (String) col_ref

Note:

This follows the standard spreadsheet convention of naming columns A to Z, followed by AA to AZ etc.

converts the column index into alphabetical values.

Returns:

  • (String) 


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/axlsx/workbook/worksheet/cell.rb', line 136

def col_ref
    chars = []
    index = self.index
    while index >= 26 do
      chars << ((index % 26) + 65).chr
      index /= 26
    end
    chars << ((chars.empty? ? index : index-1) + 65).chr
    chars.reverse.join
  end

  # Determines the cell type based on the cell value. 
  # @note This is only used when a cell is created but no :type option is specified, the following rules apply:
  #   1. If the value is an instance of Time, the type is set to :time
  #   2. :float and :integer types are determined by regular expression matching.
  #   3. Anything that does not meet either of the above is determined to be :string.
  # @return [Symbol] The determined type
  def cell_type_from_value(v)      
    if v.is_a? Time
      :time
    elsif v.to_s.match(/\A[+-]?\d+?\Z/) #numeric
      :integer
    elsif v.to_s.match(/\A[+-]?\d+\.\d+?\Z/) #float
      :float
    else
      :string
    end
  end

  # Cast the value into this cells data type. 
  # @note 
  #   About Time - Time in OOXML is *different* from what you might expect. The history as to why is interesting,  but you can safely assume that if you are generating docs on a mac, you will want to specify Workbook.1904 as true when using time typed values.
  # @see Axlsx#date1904
  def cast_value(v)
    if (@type == :time && v.is_a?(Time)) || (@type == :time && v.respond_to?(:to_time))
      v = v.to_time
      epoc = Workbook.date1904 ? Time.local(1904,1,1,0,0,0,0,v.zone) : Time.local(1900,1,1,0,0,0,0,v.zone)
      ((v - epoc) /60.0/60.0/24.0).to_f
    elsif @type == :float
      v.to_f
    elsif @type == :integer
      v.to_i
    else
      @type = :string
      v.to_s
    end
  end    
end

- (Integer) index

The index of the cell in the containing row.

Returns:

  • (Integer) —

    The index of the cell in the containing row. 



80
81
82
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 80

def index
  @row.cells.index(self)
end

- (String) r

The alpha(column)numeric(row) reference for this sell.

Examples:

Relative Cell Reference 

ws.rows.first.cells.first.r #=> "A1"

Returns:

  • (String) —

    The alpha(column)numeric(row) reference for this sell. 



87
88
89
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 87

def r
  "#{col_ref}#{@row.index+1}"      
end

- (String) r_abs

The absolute alpha(column)numeric(row) reference for this sell.

Examples:

Absolute Cell Reference 

ws.rows.first.cells.first.r #=> "$A$1"

Returns:

  • (String) —

    The absolute alpha(column)numeric(row) reference for this sell. 



94
95
96
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 94

def r_abs
  "$#{r.split('').join('$')}"
end

- (String) to_xml(xml)

Note:

Shared Strings are not used in this library. All values are set directly in the each sheet.

Serializes the cell

Parameters:

  • xml (Nokogiri::XML::Builder) —

    The document builder instance this objects xml will be added to.

Returns:

  • (String) —

    xml text for the cell 



114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 114

def to_xml(xml)
  # Both 1.8 and 1.9 return the same 'fast_xf'
  # &#12491;&#12507;&#12531;&#12468;
  # &#12491;&#12507;&#12531;&#12468;
  
  # however nokogiri does a nice 'force_encoding' which we shall remove!
  if @type == :string 
    xml.c(:r => r, :t=>:inlineStr, :s=>style) { xml.is { xml.t @value.to_s } }
  else
    xml.c(:r => r, :s => style) { xml.v value }
  end
end