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:

Constant Summary collapse

INLINE_STYLES =

An array of available inline styes.

['value', 'type', 'font_name', 'charset',
'family', 'b', 'i', 'strike','outline',
'shadow', 'condense', 'extend', 'u',
'vertAlign', 'sz', 'color', 'scheme']

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

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

Returns 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.

  • font_name (String)
  • charset (Integer)
  • family (String)
  • b (Boolean)
  • i (Boolean)
  • strike (Boolean)
  • outline (Boolean)
  • shadow (Boolean)
  • condense (Boolean)
  • extend (Boolean)
  • u (Boolean)
  • vertAlign (Symbol) —

    must be one of :baseline, :subscript, :superscript

  • sz (Integer)
  • color (String) —

    an 8 letter rgb specification

  • scheme (Symbol) —

    must be one of :none, major, :minor



185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 185

def initialize(row, value="", options={})
  self.row=row
  @font_name = @charset = @family = @b = @i = @strike = @outline = @shadow = nil
  @condense = @u = @vertAlign = @sz = @color = @scheme = @extend = @ssti = nil
  @styles = row.worksheet.workbook.styles
  @row.cells << self
  options.each do |o|
    self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
  end
  @style ||= 0
  @type ||= cell_type_from_value(value)
  @value = cast_value(value)
end

Instance Attribute Details

#b ⇒ Boolean

The inline bold property for the cell

Returns:

  • (Boolean) 


92
93
94
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 92

def b
  @b
end

#charset ⇒ String

The inline charset property for the cell

Returns:

  • (String) 


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

def charset
  @charset
end

#color ⇒ Color

The inline color property for the cell

Returns:



140
141
142
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 140

def color
  @color
end

#condense ⇒ Boolean

The inline condense property for the cell

Returns:

  • (Boolean) 


122
123
124
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 122

def condense
  @condense
end

#extend ⇒ Boolean

The inline extend property for the cell

Returns:

  • (Boolean) 


128
129
130
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 128

def extend
  @extend
end

#family ⇒ String

The inline family property for the cell

Returns:

  • (String) 


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

def family
  @family
end

#font_name ⇒ String

The inline font_name property for the cell

Returns:

  • (String) 


74
75
76
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 74

def font_name
  @font_name
end

#i ⇒ Boolean

The inline italic property for the cell

Returns:

  • (Boolean) 


98
99
100
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 98

def i
  @i
end

#outline ⇒ Boolean

The inline outline property for the cell

Returns:

  • (Boolean) 


110
111
112
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 110

def outline
  @outline
end

#row ⇒ Row

The row this cell belongs to.

Returns:



41
42
43
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 41

def row
  @row
end

#scheme ⇒ Symbol

The inline scheme property for the cell this must be one of [:none, major, minor]

Returns:

  • (Symbol) 


162
163
164
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 162

def scheme
  @scheme
end

#shadow ⇒ Boolean

The inline shadow property for the cell

Returns:

  • (Boolean) 


116
117
118
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 116

def shadow
  @shadow
end

#ssti ⇒ Integer

The Shared Strings Table index for this cell

Returns:

  • (Integer) 


201
202
203
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 201

def ssti
  @ssti
end

#strike ⇒ Boolean

The inline strike property for the cell

Returns:

  • (Boolean) 


104
105
106
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 104

def strike
  @strike
end

#style ⇒ Integer

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

Returns:

  • (Integer)

See Also:



37
38
39
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 37

def style
  @style
end

#sz ⇒ Boolean

The inline sz property for the cell

Returns:

  • (Boolean) 


148
149
150
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 148

def sz
  @sz
end

#type ⇒ Symbol

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 conversions 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 six types are supported, :date, :time, :float, :integer, :string and :boolean. 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 [:date, time, :float, :integer, :string, :boolean]

See Also:

  • #cell_type_from_value


54
55
56
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 54

def type
  @type
end

#u ⇒ Boolean

The inline underline property for the cell

Returns:

  • (Boolean) 


134
135
136
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 134

def u
  @u
end

#value ⇒ String, ...

The value of this cell.

Returns:

  • (String, Integer, Float, Time) —

    casted value based on cell’s type attribute.



65
66
67
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 65

def value
  @value
end

#vertAlign ⇒ Symbol

The inline vertical alignment property for the cell this must be one of [:baseline, :subscript, :superscript]

Returns:

  • (Symbol) 


155
156
157
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 155

def vertAlign
  @vertAlign
end

Instance Method Details

#index ⇒ Integer

Returns The index of the cell in the containing row.

Returns:

  • (Integer) —

    The index of the cell in the containing row.



218
219
220
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 218

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

#merge(target) ⇒ Object

Merges all the cells in a range created between this cell and the cell or string name for a cell provided

Parameters:

  • target (Cell, String) —

    The last cell, or str ref for the cell in the merge range

See Also:

  • Axlsx::Cell.worksheetworksheet.merge_cells


253
254
255
256
257
258
259
260
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 253

def merge(target)
  range_end = if target.is_a?(String)
                target
              elsif(target.is_a?(Cell))
                target.r
              end
  self.row.worksheet.merge_cells "#{self.r}:#{range_end}" unless range_end.nil?
end

#pos ⇒ Array

Returns of x/y coordinates in the cheet for this cell.

Returns:

  • (Array) —

    of x/y coordinates in the cheet for this cell.



246
247
248
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 246

def pos
  [index, row.index]
end

#r ⇒ String

Returns 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.



225
226
227
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 225

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

#r_abs ⇒ String

Returns 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.



232
233
234
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 232

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

#run_xml(xml) ⇒ String

builds an xml text run based on this cells attributes. This is extracted from to_xml so that shared strings can use it.

Parameters:

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

    The document builder instance this output will be added to.

Returns:

  • (String) —

    the xml for this cell’s text run



265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 265

def run_xml(xml)
  if (self.instance_values.keys & INLINE_STYLES).size > 0
    xml.r {
      xml.rPr {
        xml.rFont(:val=>@font_name) if @font_name
        xml.charset(:val=>@charset) if @charset
        xml.family(:val=>@family) if @family
        xml.b(:val=>@b) if @b
        xml.i(:val=>@i) if @i
        xml.strike(:val=>@strike) if @strike
        xml.outline(:val=>@outline) if @outline
        xml.shadow(:val=>@shadow) if @shadow
        xml.condense(:val=>@condense) if @condense
        xml.extend(:val=>@extend) if @extend
        @color.to_xml(xml) if @color
        xml.sz(:val=>@sz) if @sz
        xml.u(:val=>@u) if @u
        # :baseline, :subscript, :superscript
        xml.vertAlign(:val=>@vertAlign) if @vertAlign
        # :none, major, :minor
        xml.scheme(:val=>@scheme) if @scheme
      }
      xml.t @value.to_s
    }
  else
    xml.t @value.to_s
  end
end

#shareable(v) ⇒ Object

equality comparison to test value, type and inline style attributes this is how we work out if the cell needs to be added or already exists in the shared strings table



205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 205

def shareable(v)

  #using reject becase 1.8.7 select returns an array...
  v_hash = v.instance_values.reject { |key, val| !INLINE_STYLES.include?(key) }
  self_hash = self.instance_values.reject { |key, val| !INLINE_STYLES.include?(key) }
  # required as color is an object, and the comparison will fail even though both use the same color.
  v_hash['color'] = v_hash['color'].instance_values if v_hash['color']
  self_hash['color'] = self_hash['color'].instance_values if self_hash['color']

  v_hash == self_hash
end

#to_xml(xml) ⇒ String

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



297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 297

def to_xml(xml)
  if @type == :string
    #parse formula
    if @value.start_with?('=')
      xml.c(:r => r, :t=>:str, :s=>style) {
        xml.f @value.to_s.gsub('=', '')
      }
    else
      #parse shared
      if @ssti
        xml.c(:r => r, :s=>style, :t => :s) { xml.v ssti }
      else
        #parse inline string
        xml.c(:r => r, :s=>style, :t => :inlineStr) {
          xml.is {
            run_xml(xml)
          }
        }
      end
    end
  elsif @type == :date
    # TODO: See if this is subject to the same restriction as Time below
    v = DateTimeConverter::date_to_serial @value
    xml.c(:r => r, :s => style) { xml.v v }
  elsif @type == :time
    v = DateTimeConverter::time_to_serial @value
    xml.c(:r => r, :s => style) { xml.v v }
  elsif @type == :boolean
    xml.c(:r => r, :s => style, :t => :b) { xml.v value }
  else
    xml.c(:r => r, :s => style) { xml.v value }
  end
end