Class: Axlsx::Cell

Inherits:
Object
  • Object
show all
Includes:
OptionsParser
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.

See Also:

Constant Summary collapse

INLINE_STYLES =

An array of available inline styes. TODO change this to a hash where each key defines attr name and validator (and any info the validator requires) then move it out to a module so we can re-use in in other classes. needs to define bla=(v) and bla methods on the class that hook into a set_attr method that kicks the suplied validator and updates the instance_variable for the key

[:value, :type, :font_name, :charset,
:family, :b, :i, :strike, :outline,
:shadow, :condense, :extend, :u,
:vertAlign, :sz, :color, :scheme].freeze
CELL_TYPES =

An array of valid cell types

[:date, :time, :float, :integer, :richtext,
:string, :boolean, :iso_8601, :text].freeze
CELL_REFERENCE_REGEX =

A regular expression to match the alpha(column)numeric(row) reference of a cell

/([A-Z]+)([0-9]+)/.freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from OptionsParser

#parse_options

Constructor Details

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

Returns a new instance of Cell.

Parameters:

  • row (Row) —

    The row this cell belongs to.

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

    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 value 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

  • formula_value (Number) —

    The value to cache for a formula cell.

  • scheme (Symbol) —

    must be one of :none, major, :minor

  • escape_formulas (Boolean) —

    Whether to treat values starting with an equals sign as formulas or as literal strings. Allowing user-generated data to be interpreted as formulas is a security risk. See https://www.owasp.org/index.php/CSV_Injection for details.



35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 35

def initialize(row, value = nil, options = {})
  @row = row
  # Do not use instance vars if not needed to use less RAM
  # And do not call parse_options on frequently used options
  # to get less GC cycles
  type = options.delete(:type) || cell_type_from_value(value)
  self.type = type unless type == :string

  val = options.delete(:style)
  self.style = val unless val.nil? || val.zero?
  val = options.delete(:formula_value)
  self.formula_value = val unless val.nil?
  val = options.delete(:escape_formulas)
  self.escape_formulas = val unless val.nil?

  parse_options(options) unless options.empty?

  self.value = value
  value.cell = self if contains_rich_text?
end

Instance Attribute Details

#b ⇒ Boolean

The inline bold property for the cell

Returns:

  • (Boolean) 


241
242
243
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 241

def b
  @b
end

#charset ⇒ String

The inline charset property for the cell As far as I can tell, this is pretty much ignored. However, based on the spec it should be one of the following: 0  ANSI_CHARSET 1 DEFAULT_CHARSET 2 SYMBOL_CHARSET 77 MAC_CHARSET 128 SHIFTJIS_CHARSET 129  HANGUL_CHARSET 130  JOHAB_CHARSET 134  GB2312_CHARSET 136  CHINESEBIG5_CHARSET 161  GREEK_CHARSET 162  TURKISH_CHARSET 163  VIETNAMESE_CHARSET 177  HEBREW_CHARSET 178  ARABIC_CHARSET 186  BALTIC_CHARSET 204  RUSSIAN_CHARSET 222  THAI_CHARSET 238  EASTEUROPE_CHARSET 255  OEM_CHARSET

Returns:

  • (String) 


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

def charset
  @charset
end

#color ⇒ Color

The inline color property for the cell

Returns:



315
316
317
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 315

def color
  @color
end

#condense ⇒ Boolean

The inline condense property for the cell

Returns:

  • (Boolean) 


286
287
288
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 286

def condense
  @condense
end

#extend ⇒ Boolean

The inline extend property for the cell

Returns:

  • (Boolean) 


295
296
297
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 295

def extend
  @extend
end

#family ⇒ Integer

The inline family property for the cell 1 Roman 2 Swiss 3 Modern 4 Script 5 Decorative

Returns:

  • (Integer) 


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

def family
  @family
end

#font_name ⇒ String

The inline font_name property for the cell

Returns:

  • (String) 


189
190
191
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 189

def font_name
  @font_name
end

#formula_value ⇒ Object

this is the cached value for formula cells. If you want the values to render in iOS/Mac OSX preview you need to set this.



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

def formula_value
  @formula_value
end

#i ⇒ Boolean

The inline italic property for the cell

Returns:

  • (Boolean) 


250
251
252
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 250

def i
  @i
end

#name ⇒ Object

returns the name of the cell



443
444
445
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 443

def name
  @name
end

#outline ⇒ Boolean

The inline outline property for the cell

Returns:

  • (Boolean) 


268
269
270
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 268

def outline
  @outline
end

#raw_style ⇒ Object

Returns the value of attribute raw_style.



90
91
92
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 90

def raw_style
  @raw_style
end

#row ⇒ Row (readonly)

The row this cell belongs to.

Returns:



118
119
120
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 118

def row
  @row
end

#scheme ⇒ Symbol

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

Returns:

  • (Symbol) 


346
347
348
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 346

def scheme
  @scheme
end

#shadow ⇒ Boolean

The inline shadow property for the cell

Returns:

  • (Boolean) 


277
278
279
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 277

def shadow
  @shadow
end

#ssti ⇒ Integer

The Shared Strings Table index for this cell

Returns:

  • (Integer) 


356
357
358
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 356

def ssti
  @ssti
end

#strike ⇒ Boolean

The inline strike property for the cell

Returns:

  • (Boolean) 


259
260
261
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 259

def strike
  @strike
end

#sz ⇒ Inteter

The inline sz property for the cell

Returns:

  • (Inteter) 


325
326
327
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 325

def sz
  @sz
end

#u ⇒ Boolean, String

The inline underline property for the cell. It must be one of :none, :single, :double, :singleAccounting, :doubleAccounting

Returns:

  • (Boolean)
  • (String) 


306
307
308
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 306

def u
  @u
end

#value ⇒ String, ...

The value of this cell.

Returns:

  • (String, Integer, Float, Time, Boolean) —

    casted value based on cell's type attribute.



159
160
161
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 159

def value
  @value
end

#vertAlign ⇒ Symbol

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

Returns:

  • (Symbol) 


335
336
337
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 335

def vertAlign
  @vertAlign
end

Instance Method Details

#add_style(style) ⇒ Object

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

Parameters:

  • style (Hash)

See Also:



95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 95

def add_style(style)
  self.raw_style ||= {}

  new_style = Axlsx.hash_deep_merge(raw_style, style)

  all_edges = [:top, :right, :bottom, :left]

  if !raw_style[:border].nil? && !style[:border].nil?
    border_at = (raw_style[:border][:edges] || all_edges) + (style[:border][:edges] || all_edges)
    new_style[:border][:edges] = border_at.uniq.sort
  elsif !style[:border].nil?
    new_style[:border] = style[:border]
  end

  self.raw_style = new_style

  wb = row.worksheet.workbook

  wb.styled_cells << self
end

#autowidth ⇒ Float

Attempts to determine the correct width for this cell's content

Returns:

  • (Float) 


447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 447

def autowidth
  return if value.nil? || is_formula?

  if contains_rich_text?
    string_width('', font_size) + value.autowidth
  elsif styles.cellXfs[style].alignment && styles.cellXfs[style].alignment.wrap_text
    max_width = 0
    value.to_s.split(/\r?\n/).each do |line|
      width = string_width(line, font_size)
      max_width = width if width > max_width
    end
    max_width
  else
    string_width(value, font_size)
  end
end

#clean_value ⇒ String

Returns the sanitized value TODO: find a better way to do this as it accounts for 30% of processing time in benchmarking...

Returns:

  • (String) —

    The sanitized value



468
469
470
471
472
473
474
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 468

def clean_value
  if (type == :string || type == :text) && !Axlsx.trust_input
    Axlsx.sanitize(::CGI.escapeHTML(@value.to_s))
  else
    @value.to_s
  end
end

#contains_rich_text? ⇒ Boolean

Returns:

  • (Boolean) 


173
174
175
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 173

def contains_rich_text?
  type == :richtext
end

#escape_formulas ⇒ Boolean

Whether to treat values starting with an equals sign as formulas or as literal strings. Allowing user-generated data to be interpreted as formulas is a security risk. See https://www.owasp.org/index.php/CSV_Injection for details.

Returns:

  • (Boolean) 


146
147
148
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 146

def escape_formulas
  defined?(@escape_formulas) ? @escape_formulas : row.worksheet.escape_formulas
end

#escape_formulas=(value) ⇒ Object

Sets whether to treat values starting with an equals sign as formulas or as literal strings.

Parameters:

  • value (Boolean) —

    The value to set.



152
153
154
155
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 152

def escape_formulas=(value)
  Axlsx.validate_boolean(value)
  @escape_formulas = value
end

#index ⇒ Integer

Returns The index of the cell in the containing row.

Returns:

  • (Integer) —

    The index of the cell in the containing row.



359
360
361
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 359

def index
  @row.index(self)
end

#is_array_formula? ⇒ Boolean

rubocop:disable Naming/PredicateName

Returns:

  • (Boolean) 


419
420
421
422
423
424
425
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 419

def is_array_formula? # rubocop:disable Naming/PredicateName
  return false if escape_formulas

  type == :string &&
    @value.to_s.start_with?(ARRAY_FORMULA_PREFIX) &&
    @value.to_s.end_with?(ARRAY_FORMULA_SUFFIX)
end

#is_formula? ⇒ Boolean

rubocop:disable Naming/PredicateName

Returns:

  • (Boolean) 


413
414
415
416
417
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 413

def is_formula? # rubocop:disable Naming/PredicateName
  return false if escape_formulas

  type == :string && @value.to_s.start_with?(FORMULA_PREFIX)
end

#is_text_run? ⇒ Boolean

Indicates that the cell has one or more of the custom cell styles applied.

Returns:

  • (Boolean) 


169
170
171
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 169

def is_text_run? # rubocop:disable Naming/PredicateName
  defined?(@is_text_run) && @is_text_run && !contains_rich_text?
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


395
396
397
398
399
400
401
402
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 395

def merge(target)
  start, stop = if target.is_a?(String)
                  [r, target]
                elsif target.is_a?(Cell)
                  Axlsx.sort_cells([self, target]).map(&:r)
                end
  row.worksheet.merge_cells "#{start}:#{stop}" unless stop.nil?
end

#plain_string? ⇒ Boolean

Indicates if the cell is good for shared string table

Returns:

  • (Boolean) 


178
179
180
181
182
183
184
185
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 178

def plain_string?
  (type == :string || type == :text) && # String typed
    !value.nil? &&
    !value.empty? &&
    !is_text_run? && # No inline styles
    !is_formula? &&
    !is_array_formula?
end

#pos ⇒ Array

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

Returns:

  • (Array) —

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



388
389
390
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 388

def pos
  [index, row.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.



366
367
368
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 366

def r
  Axlsx.cell_r index, @row.row_index
end

#r_abs ⇒ String

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

Examples:

Absolute Cell Reference

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

Returns:

  • (String) —

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



373
374
375
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 373

def r_abs
  "$#{CELL_REFERENCE_REGEX.match(r)[1, 2].join('$')}"
end

#reference(absolute = true) ⇒ String

returns the absolute or relative string style reference for this cell. returned.

Parameters:

  • absolute (Boolean) (defaults to: true) —

    -when false a relative reference will be

Returns:

  • (String) 


432
433
434
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 432

def reference(absolute = true)
  absolute ? r_abs : r
end

#style ⇒ Integer

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

Returns:

  • (Integer)

See Also:



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

def style
  defined?(@style) ? @style : 0
end

#style=(v) ⇒ Integer

Returns The cellXfs item index applied to this cell.

Returns:

  • (Integer) —

    The cellXfs item index applied to this cell.

Raises:

  • (ArgumentError) —

    Invalid cellXfs id if the value provided is not within cellXfs items range.



379
380
381
382
383
384
385
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 379

def style=(v)
  Axlsx.validate_unsigned_int(v)
  count = styles.cellXfs.size
  raise ArgumentError, "Invalid cellXfs id" unless v < count

  @style = v
end

#style_str ⇒ Object

Internal



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

def style_str
  defined?(@style) ? @style.to_s : '0'
end

#to_xml_string(r_index, c_index, str = +'')) ⇒ String

Serializes the cell

Parameters:

  • r_index (Integer) —

    The row index for the cell

  • c_index (Integer) —

    The cell index in the row.

  • str (String) (defaults to: +'')) —

    The string index the cell content will be appended to. Defaults to empty string.

Returns:

  • (String) —

    xml text for the cell



409
410
411
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 409

def to_xml_string(r_index, c_index, str = +'')
  CellSerializer.to_xml_string r_index, c_index, self, str
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. 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:

  • (ArgumentError) —

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

See Also:

  • #cell_type_from_value


131
132
133
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 131

def type
  defined?(@type) ? @type : :string
end

#type=(v) ⇒ Object

See Also:



136
137
138
139
140
 
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 136

def type=(v)
  RestrictionValidator.validate :cell_type, CELL_TYPES, v
  @type = v
  self.value = @value unless !defined?(@value) || @value.nil?
end