Class: RubyXL::Cell

Inherits:
PrivateClass show all
Defined in:
lib/rubyXL/cell.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(worksheet, row, column, value = nil, formula = nil, datatype = 's', style_index = 0, fmla_attr = {}) ⇒ Cell

Returns a new instance of Cell.



7
8
9
10
11
12
13
14
15
16
17
18
 
# File 'lib/rubyXL/cell.rb', line 7

def initialize(worksheet,row,column,value=nil,formula=nil,datatype='s',style_index=0, fmla_attr={})
  @worksheet = worksheet

  @workbook = worksheet.workbook
  @row = row
  @column = column
  @datatype = datatype
  @value = value
  @formula=formula
  @style_index = style_index
  @formula_attributes = fmla_attr
end

Instance Attribute Details

#column ⇒ Object

Returns the value of attribute column.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def column
  @column
end

#datatype ⇒ Object

Returns the value of attribute datatype.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def datatype
  @datatype
end

#formula ⇒ Object

Returns the value of attribute formula.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def formula
  @formula
end

#formula_attributes ⇒ Object (readonly)

Returns the value of attribute formula_attributes.



5
6
7
 
# File 'lib/rubyXL/cell.rb', line 5

def formula_attributes
  @formula_attributes
end

#row ⇒ Object

Returns the value of attribute row.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def row
  @row
end

#style_index ⇒ Object

Returns the value of attribute style_index.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def style_index
  @style_index
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook.



5
6
7
 
# File 'lib/rubyXL/cell.rb', line 5

def workbook
  @workbook
end

#worksheet ⇒ Object

Returns the value of attribute worksheet.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def worksheet
  @worksheet
end

Class Method Details

.convert_to_cell(row = 0, col = 0) ⇒ Object

returns Excel-style cell string from matrix indices



365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
 
# File 'lib/rubyXL/cell.rb', line 365

def Cell.convert_to_cell(row=0,col=0)
  row_string = (row + 1).to_s #+1 for 0 indexing
  col_string = ''

  if row < 0 || col < 0
    raise 'Invalid input: cannot convert negative numbers'
  end

  unless col == 0
    col_length = 1+Integer(Math.log(col) / Math.log(26)) #opposite of 26**
  else
    col_length = 1
  end

  1.upto(col_length) do |i|

    #for the last digit, 0 should mean A. easy way to do this.
    if i == col_length
      col+=1
    end

    if col >= 26**(col_length-i)
      int_val = col / 26**(col_length-i) #+1 for 0 indexing
      int_val += 64 #converts 1 to A, etc.

      col_string += int_val.chr

      #intval multiplier decrements by placeholder, essentially
      #a B subtracts more than an A this way.
      col -= (int_val-64)*26**(col_length-i)
    end
  end
  col_string+row_string
end

Instance Method Details

#border_bottom ⇒ Object

returns cell’s bottom border



355
356
357
 
# File 'lib/rubyXL/cell.rb', line 355

def border_bottom()
  return get_border(:bottom)
end

#border_diagonal ⇒ Object

returns cell’s diagonal border



360
361
362
 
# File 'lib/rubyXL/cell.rb', line 360

def border_diagonal()
  return get_border(:diagonal)
end

#border_left ⇒ Object

returns cell’s left border



345
346
347
 
# File 'lib/rubyXL/cell.rb', line 345

def border_left()
  return get_border(:left)
end

#border_right ⇒ Object

returns cell’s right border



350
351
352
 
# File 'lib/rubyXL/cell.rb', line 350

def border_right()
  return get_border(:right)
end

#border_top ⇒ Object

returns cell’s top border



340
341
342
 
# File 'lib/rubyXL/cell.rb', line 340

def border_top()
  return get_border(:top)
end

#change_border_bottom(weight = 'thin') ⇒ Object

changes bottom border of cell



225
226
227
 
# File 'lib/rubyXL/cell.rb', line 225

def change_border_bottom(weight='thin')
  change_border(:bottom, weight)
end

#change_border_diagonal(weight = 'thin') ⇒ Object

changes diagonal border of cell



230
231
232
 
# File 'lib/rubyXL/cell.rb', line 230

def change_border_diagonal(weight='thin')
  change_border(:diagonal, weight)
end

#change_border_left(weight = 'thin') ⇒ Object

changes left border of cell



215
216
217
 
# File 'lib/rubyXL/cell.rb', line 215

def change_border_left(weight='thin')
  change_border(:left, weight)
end

#change_border_right(weight = 'thin') ⇒ Object

changes right border of cell



220
221
222
 
# File 'lib/rubyXL/cell.rb', line 220

def change_border_right(weight='thin')
  change_border(:right, weight)
end

#change_border_top(weight = 'thin') ⇒ Object

changes top border of cell



210
211
212
 
# File 'lib/rubyXL/cell.rb', line 210

def change_border_top(weight='thin')
  change_border(:top, weight)
end

#change_contents(data, formula = nil) ⇒ Object

changes contents of cell, with formula option



235
236
237
238
239
240
241
242
243
244
245
246
 
# File 'lib/rubyXL/cell.rb', line 235

def change_contents(data, formula=nil)
  validate_worksheet
  @datatype='str'
  if data.is_a?(Date) || data.is_a?(DateTime)
    data = @workbook.date_to_num(data)
  end
  if (data.is_a?Integer) || (data.is_a?Float)
    @datatype = ''
  end
  @value=data
  @formula=formula
end

#change_fill(rgb = 'ffffff') ⇒ Object

changes fill color of cell



102
103
104
105
106
 
# File 'lib/rubyXL/cell.rb', line 102

def change_fill(rgb='ffffff')
  validate_worksheet
  Color.validate_color(rgb)
  @style_index = modify_fill(@workbook, @style_index,rgb)
end

#change_font(font) ⇒ Object

Helper method to update the font array and xf array



185
186
187
188
189
190
191
192
193
 
# File 'lib/rubyXL/cell.rb', line 185

def change_font(font)
  # Modify font array and retrieve new font id
  font_id = modify_font(@workbook, font, font_id())
  # Get copy of xf object with modified font id
  xf = deep_copy(xf_id())
  xf[:fontId] = Integer(font_id.to_i)
  # Modify xf array and retrieve new xf id
  @style_index = modify_xf(@workbook, xf)
end

#change_font_bold(bolded = false) ⇒ Object

Changes font bold settings of cell



155
156
157
158
159
160
161
162
 
# File 'lib/rubyXL/cell.rb', line 155

def change_font_bold(bolded=false)
  validate_worksheet
  # Get copy of font object with modified bold settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_bold(font, bolded)
  # Update font and xf array
  change_font(font)
end

#change_font_color(font_color = '000000') ⇒ Object

Changes font color of cell



133
134
135
136
137
138
139
140
141
142
 
# File 'lib/rubyXL/cell.rb', line 133

def change_font_color(font_color='000000')
  validate_worksheet
  #if arg is a color name, convert to integer
  Color.validate_color(font_color)
  # Get copy of font object with modified color
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_color(font, font_color.to_s)
  # Update font and xf array
  change_font(font)
end

#change_font_italics(italicized = false) ⇒ Object

Changes font italics settings of cell



145
146
147
148
149
150
151
152
 
# File 'lib/rubyXL/cell.rb', line 145

def change_font_italics(italicized=false)
  validate_worksheet
  # Get copy of font object with modified italics settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_italics(font, italicized)
  # Update font and xf array
  change_font(font)
end

#change_font_name(font_name = 'Verdana') ⇒ Object

Changes font name of cell



109
110
111
112
113
114
115
116
 
# File 'lib/rubyXL/cell.rb', line 109

def change_font_name(font_name='Verdana')
  validate_worksheet
  # Get copy of font object with modified name
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font[:name][:attributes][:val] = font_name.to_s
  # Update font and xf array
  change_font(font)
end

#change_font_size(font_size = 10) ⇒ Object

Changes font size of cell



119
120
121
122
123
124
125
126
127
128
129
130
 
# File 'lib/rubyXL/cell.rb', line 119

def change_font_size(font_size=10)
  validate_worksheet
  if font_size.is_a?(Integer) || font_size.is_a?(Float)
    # Get copy of font object with modified size
    font = deep_copy(workbook.fonts[font_id().to_s][:font])
    font[:sz][:attributes][:val] = font_size
    # Update font and xf array
    change_font(font)
  else
    raise 'Argument must be a number'
  end
end

#change_font_strikethrough(struckthrough = false) ⇒ Object

Changes font strikethrough settings of cell



175
176
177
178
179
180
181
182
 
# File 'lib/rubyXL/cell.rb', line 175

def change_font_strikethrough(struckthrough=false)
  validate_worksheet
  # Get copy of font object with modified strikethrough settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_strikethrough(font, struckthrough)
  # Update font and xf array
  change_font(font)
end

#change_font_underline(underlined = false) ⇒ Object

Changes font underline settings of cell



165
166
167
168
169
170
171
172
 
# File 'lib/rubyXL/cell.rb', line 165

def change_font_underline(underlined=false)
  validate_worksheet
  # Get copy of font object with modified underline settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_underline(font, underlined)
  # Update font and xf array
  change_font(font)
end

#change_horizontal_alignment(alignment = 'center') ⇒ Object

changes horizontal alignment of cell



196
197
198
199
200
 
# File 'lib/rubyXL/cell.rb', line 196

def change_horizontal_alignment(alignment='center')
  validate_worksheet
  validate_horizontal_alignment(alignment)
  @style_index = modify_alignment(@workbook,@style_index,true,alignment)
end

#change_vertical_alignment(alignment = 'center') ⇒ Object

changes vertical alignment of cell



203
204
205
206
207
 
# File 'lib/rubyXL/cell.rb', line 203

def change_vertical_alignment(alignment='center')
  validate_worksheet
  validate_vertical_alignment(alignment)
  @style_index = modify_alignment(@workbook,@style_index,false,alignment)
end

#fill_color ⇒ Object

returns cell’s fill color



313
314
315
316
317
 
# File 'lib/rubyXL/cell.rb', line 313

def fill_color()
  validate_worksheet
  xf = @workbook.get_style_attributes(@workbook.get_style(@style_index))
  return @workbook.get_fill_color(xf)
end

#font_color ⇒ Object

returns cell’s font color



303
304
305
306
307
308
309
310
 
# File 'lib/rubyXL/cell.rb', line 303

def font_color()
  validate_worksheet
  if @workbook.fonts[font_id()][:font][:color].nil?
    '000000' #black
  else
    @workbook.fonts[font_id()][:font][:color][:attributes][:rgb]
  end
end

#font_name ⇒ Object

returns cell’s font name



291
292
293
294
 
# File 'lib/rubyXL/cell.rb', line 291

def font_name()
  validate_worksheet
  @workbook.fonts[font_id()][:font][:name][:attributes][:val]
end

#font_size ⇒ Object

returns cell’s font size



297
298
299
300
 
# File 'lib/rubyXL/cell.rb', line 297

def font_size()
  validate_worksheet
  return @workbook.fonts[font_id()][:font][:sz][:attributes][:val]
end

#horizontal_alignment ⇒ Object

returns cell’s horizontal alignment



320
321
322
323
324
325
326
327
 
# File 'lib/rubyXL/cell.rb', line 320

def horizontal_alignment()
  validate_worksheet
  xf_obj = @workbook.get_style(@style_index)
  if xf_obj[:alignment].nil? || xf_obj[:alignment][:attributes].nil?
    return nil
  end
  xf_obj[:alignment][:attributes][:horizontal].to_s
end

#inspect ⇒ Object 



400
401
402
403
404
405
 
# File 'lib/rubyXL/cell.rb', line 400

def inspect
  str = "(#{@row},#{@column}): #{@value}" 
  str += " =#{@formula}" if @formula
  str += ", datatype = #{@datatype}, style_index = #{@style_index}"
  return str
end

#is_bolded ⇒ Object

returns if font is bolded



259
260
261
262
263
264
265
266
 
# File 'lib/rubyXL/cell.rb', line 259

def is_bolded()
  validate_worksheet
  if @workbook.fonts[font_id()][:font][:b].nil?
    false
  else
    true
  end
end

#is_date? ⇒ Boolean

Returns:

  • (Boolean) 


31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/rubyXL/cell.rb', line 31

def is_date?
  if !@value.is_a?(String)
    if @workbook.num_fmts_by_id
      num_fmt_id = xf_id()[:numFmtId]
      tmp_num_fmt = @workbook.num_fmts_by_id[num_fmt_id]
      num_fmt = (tmp_num_fmt &&tmp_num_fmt[:attributes] && tmp_num_fmt[:attributes][:formatCode]) ? tmp_num_fmt[:attributes][:formatCode] : nil
      if num_fmt && workbook.date_num_fmt?(num_fmt)
        return true
      end
    end
  end
  return false
end

#is_date_format?(num_fmt) ⇒ Boolean

Returns:

  • (Boolean) 


45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/rubyXL/cell.rb', line 45

def is_date_format?(num_fmt)
  skip_chars = ['$', '-', '+', '/', '(', ')', ':', ' ']
  num_chars = ['0', '#', '?']
  non_date_formats = ['0.00E+00', '##0.0E+0', 'General', 'GENERAL', 'general', '@']
  date_chars = ['y','m','d','h','s']

  state = 0
  s = ''
  num_fmt.split(//).each do |c|
    if state == 0
      if c == '"'
        state = 1
      elsif ['\\', '_', '*'].include?(c)
        state = 2
      elsif skip_chars.include?(c)
        next
      else
        s << c
      end
    elsif state == 1
      if c == '"'
        state = 0
      end
    elsif state == 2
      state = 0
    end
  end
  s.gsub!(/\[[^\]]*\]/, '')
  if non_date_formats.include?(s)
    return false
  end
  separator = ';'
  got_sep = 0
  date_count = 0
  num_count = 0
  s.split(//).each do |c|
    if date_chars.include?(c)
      date_count += 1
    elsif num_chars.include?(c)
      num_count += 1
    elsif c == separator
      got_sep = 1
    end
  end
  if date_count > 0 && num_count == 0
    return true
  elsif num_count > 0 && date_count == 0
    return false
  elsif date_count
    # ambiguous result
  elsif got_sep == 0
    # constant result
  end
  return date_count > num_count
end

#is_italicized ⇒ Object

returns if font is italicized



249
250
251
252
253
254
255
256
 
# File 'lib/rubyXL/cell.rb', line 249

def is_italicized()
  validate_worksheet
  if @workbook.fonts[font_id()][:font][:i].nil?
    false
  else
    true
  end
end

#is_struckthrough ⇒ Object

returns if font has a strike through it



280
281
282
283
284
285
286
287
288
 
# File 'lib/rubyXL/cell.rb', line 280

def is_struckthrough()
  validate_worksheet
  xf = @workbook.get_style_attributes(@workbook.get_style(@style_index))
  if @workbook.fonts[font_id()][:font][:strike].nil?
    false
  else
    true
  end
end

#is_underlined ⇒ Object

returns if font is underlined



269
270
271
272
273
274
275
276
277
 
# File 'lib/rubyXL/cell.rb', line 269

def is_underlined()
  validate_worksheet
  xf = @workbook.get_style_attributes(@workbook.get_style(@style_index))
  if @workbook.fonts[font_id()][:font][:u].nil?
    false
  else
    true
  end
end

#value(args = {}) ⇒ Object 



20
21
22
23
24
25
26
27
28
29
 
# File 'lib/rubyXL/cell.rb', line 20

def value(args = {})
  raw_values = args.delete(:raw) || false
  return @value if raw_values
  
  if is_date?
    return @workbook.num_to_date(@value)
  else
    return @value
  end
end

#vertical_alignment ⇒ Object

returns cell’s vertical alignment



330
331
332
333
334
335
336
337
 
# File 'lib/rubyXL/cell.rb', line 330

def vertical_alignment()
  validate_worksheet
  xf_obj = @workbook.get_style(@style_index)
  if xf_obj[:alignment].nil? || xf_obj[:alignment][:attributes].nil?
    return nil
  end
  xf_obj[:alignment][:attributes][:vertical].to_s
end