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']

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from OptionsParser

#parse_options

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



32
33
34
35
36
37
38
39
40
41
42
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 32

def initialize(row, value="", options={})
  self.row=row
  @value = @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
  parse_options options
  @style ||= 0
  @type ||= cell_type_from_value(value)
  @value = cast_value(value)
end

Instance Attribute Details

#bBoolean

The inline bold property for the cell

Returns:

  • (Boolean)


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

def b
  @b
end

#charsetString

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)


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

def charset
  @charset
end

#colorColor

The inline color property for the cell

Returns:



209
210
211
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 209

def color
  @color
end

#condenseBoolean

The inline condense property for the cell

Returns:

  • (Boolean)


185
186
187
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 185

def condense
  @condense
end

#extendBoolean

The inline extend property for the cell

Returns:

  • (Boolean)


191
192
193
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 191

def extend
  @extend
end

#familyInteger

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

Returns:

  • (Integer)


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

def family
  @family
end

#font_nameString

The inline font_name property for the cell

Returns:

  • (String)


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

def font_name
  @font_name
end

#iBoolean

The inline italic property for the cell

Returns:

  • (Boolean)


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

def i
  @i
end

#outlineBoolean

The inline outline property for the cell

Returns:

  • (Boolean)


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

def outline
  @outline
end

#rowRow

The row this cell belongs to.

Returns:



62
63
64
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 62

def row
  @row
end

#schemeSymbol

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

Returns:

  • (Symbol)


235
236
237
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 235

def scheme
  @scheme
end

#shadowBoolean

The inline shadow property for the cell

Returns:

  • (Boolean)


179
180
181
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 179

def shadow
  @shadow
end

#sstiInteger

The Shared Strings Table index for this cell

Returns:

  • (Integer)


244
245
246
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 244

def ssti
  @ssti
end

#strikeBoolean

The inline strike property for the cell

Returns:

  • (Boolean)


167
168
169
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 167

def strike
  @strike
end

#styleInteger

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

Returns:

  • (Integer)

See Also:



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

def style
  @style
end

#szBoolean

The inline sz property for the cell

Returns:

  • (Boolean)


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

def sz
  @sz
end

#typeSymbol

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


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

def type
  @type
end

#uBoolean, String

Note:

true is for backwards compatability and is reassigned to :single

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

Returns:

  • (Boolean)
  • (String)


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

def u
  @u
end

#valueString, ...

The value of this cell.

Returns:

  • (String, Integer, Float, Time)

    casted value based on cell's type attribute.



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

def value
  @value
end

#vertAlignSymbol

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

Returns:

  • (Symbol)


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

def vertAlign
  @vertAlign
end

Instance Method Details

#autowidthObject

This is still not perfect...

  • scaling is not linear as font sizes increst
  • different fonts have different mdw and char widths


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

def autowidth
  return if is_formula? || value == nil
  mdw = 1.78 #This is the widest width of 0..9 in arial@10px)
  font_scale = (font_size/10.0).to_f
  ((value.to_s.count(Worksheet.thin_chars) * mdw + 5) / mdw * 256) / 256.0 * font_scale
end

#indexInteger

Returns The index of the cell in the containing row.

Returns:

  • (Integer)

    The index of the cell in the containing row.



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

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

#is_formula?Boolean

Returns:

  • (Boolean)


352
353
354
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 352

def is_formula?
  @type == :string && @value.to_s.start_with?('=')
end

#is_text_run?Boolean

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

Returns:

  • (Boolean)


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

def is_text_run?
  @is_text_run ||= false
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


282
283
284
285
286
287
288
289
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 282

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

#plain_string?Boolean

Indicates if the cell is good for shared string table

Returns:

  • (Boolean)


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

def plain_string?
  @type == :string &&         # String typed
    !is_text_run? &&          # No inline styles
    !@value.nil? &&           # Not nil
    !@value.empty? &&         # Not empty
    !@value.start_with?('=')  # Not a formula
end

#posArray

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

Returns:

  • (Array)

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



275
276
277
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 275

def pos
  [index, row.index]
end

#rString

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.



254
255
256
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 254

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

#r_absString

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.



261
262
263
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 261

def r_abs
  "$#{r.match(%r{([A-Z]+)([0-9]+)})[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)


371
372
373
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 371

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

#run_xml_string(str = '') ⇒ String

builds an xml text run based on this cells attributes.

Parameters:

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

    The string instance this run will be concated to.

Returns:

  • (String)


294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 294

def run_xml_string(str = '')
  if is_text_run?
    data = instance_values.reject{|key, value| value == nil || key == 'value' || key == 'type' }
    keys = data.keys & INLINE_STYLES
    str << "<r><rPr>"
    keys.each do |key|
      case key
      when 'font_name'
        str << "<rFont val='"<< @font_name << "'/>"
      when 'color'
        str << data[key].to_xml_string
      else
        str << "<" << key.to_s << " val='" << data[key].to_s << "'/>"
      end
    end
    str << "</rPr>" << "<t>" << value.to_s << "</t></r>"
  else
    str << "<t>" << value.to_s << "</t>"
  end
  str
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



321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
# File 'lib/axlsx/workbook/worksheet/cell.rb', line 321

def to_xml_string(r_index, c_index, str = '')
  str << '<c r="' << Axlsx::cell_r(c_index, r_index) << '" s="' << @style.to_s << '" '
  return str << '/>' if @value.nil?

  case @type

  when :string
    #parse formula
    if @value.start_with?('=')
      str  << 't="str"><f>' << @value.to_s.sub('=', '') << '</f>'
    else
      #parse shared
      if @ssti
        str << 't="s"><v>' << @ssti.to_s << '</v>'
      else
        str << 't="inlineStr"><is>' << run_xml_string << '</is>'
      end
    end
  when :date
    # TODO: See if this is subject to the same restriction as Time below
    str << '><v>' << DateTimeConverter::date_to_serial(@value).to_s << '</v>'
  when :time
    str << '><v>' << DateTimeConverter::time_to_serial(@value).to_s << '</v>'
  when :boolean
    str << 't="b"><v>' << @value.to_s << '</v>'
  else
    str << '><v>' << @value.to_s << '</v>'
  end
  str << '</c>'
end